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, Nov 15 '18 at 22:46
Your string is not being escaped/quoted. It's treated like a column name. — Garr Godfrey, Nov 15 '18 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, Nov 15 '18 at 23:09
@PrimitiveSource - Since you have only one item, you need an extra comma: `(new,)` to make it a "tuple". — Rick James, Nov 15 '18 at 23:45

score 0 · Answer 1 · edited Jun 20 '20 at 09:12

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.

Python DELETE mysql/mariadb with a python variable not working

1 Answers1