Python DELETE mysql/mariadb with a python variable not working

Question

I am trying to do something as described in the post below, I have simplified this with a basic python program, not using flask, just to prove my concept, but I'm having some issues.

flask_mysqldb Delete FROM variable table

My code is as follows;

import mysql.connector as mariadb

new = 'ksk'
mariadb_connection = mariadb.connect(user='root', password='mypwd', 
database='customers')
cursor = mariadb_connection.cursor()

cursor.execute("DELETE FROM customer_info WHERE name = %s" % (new))

mariadb_connection.commit()
print('its gone')

this throws up an error saying in my table,

"unknown column 'ksk' in where clause"

this isn't true I can see a row where the name is ksk.

Furthermore to prove this, if I change my delete statement to:

cursor.execute("DELETE FROM customer_info WHERE name = 'ksk'

the row ksk gets successfully deleted. Where am I going wrong?

Tried this, it throws up an error that a string is not callable. The comma method works when using the SELECT feature not in the DELETE feature for some reason? — PrimitiveSource
– PrimitiveSource, Commented Nov 15, 2018 at 22:46
Your string is not being escaped/quoted. It's treated like a column name. — Geoduck
– Geoduck, Commented Nov 15, 2018 at 23:01
Please could you show me how to rectify that? Is putting the new = 'ksk' not suitable? Or do I need to quote as 'new' in my DELETE statement? — PrimitiveSource
– PrimitiveSource, Commented Nov 15, 2018 at 23:09
@PrimitiveSource - Since you have only one item, you need an extra comma: (new,) to make it a "tuple". — Rick James
– Rick James, Commented Nov 15, 2018 at 23:45

Community · Accepted Answer · 2020-06-20 09:12:55Z

0

https://pynative.com/python-mysql-execute-parameterized-query-using-prepared-statement/ --

We then added those two columns value in the input tuple in sequential order and passed SQL update query and input tuple t0 cursor.execute() function, remember tuple contain user data in sequential order of placeholders.

The CURSOR’s execute() method takes an optional second argument containing a list of data values. each entry in the list must be in tuple format to associate with parameter markers in the statement.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Nov 15, 2018 at 23:50

Rick James

144k15 gold badges144 silver badges255 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Python DELETE mysql/mariadb with a python variable not working

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related