Python SQL executemany statement doesn't work

I am trying to execute a delete statement that checks if the table has any SKU that exists in the SKU column of the dataframe. And if it does, it deletes the row.

supplier_name = input("Enter supplier name of the supplier you are updating: ")
df = pd.read_csv("update.csv",sep=',')
cursor = mydb.cursor()
column = df["SKU"]
print(column)
query="""DELETE FROM price_calculations1(Supplier_Name, SKU) VALUES(?,?)"""
cursor.executemany(query,(supplier_name, column))
mydb.commit()
cursor.close()

Changed the code to;

cursor = mydb.cursor()
column = df["SKU"]
print(column)
query="""DELETE FROM price_calculations1 WHERE Supplier_Name=? AND SKU=?"""
cursor.executemany(query,(supplier_name, column))
mydb.commit()
cursor.close()

It gives me the following error:

mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(Supplier_Name, SKU) VALUES(?,?)' at line 1 mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '? AND SKU=?' at line 1

Solution

That's not the correct syntax for a delete statement.

You can refer to the docs for more details: https://dev.mysql.com/doc/refman/8.0/en/delete.html

The correct syntax would be:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name [[AS] tbl_alias]
    [PARTITION (partition_name [, partition_name] ...)]
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

In your case, your statement should look like:

DELETE FROM price_calculations1
WHERE Supplier_Name=%s
AND SKU=%s

To use parameterized statements (aka prepared statements), you will have to create your cursor as follows:

cursor = mydb.cursor(prepared=True)

Also note that executemany expects an iterable of iterables (or a "sequence or parameters") and supplier_name is only a string, so you will have to change that, too.

Example of executemany from the docs:

data = [
  ('Jane', date(2005, 2, 12)),
  ('Joe', date(2006, 5, 23)),
  ('John', date(2010, 10, 3)),
]
stmt = "INSERT INTO employees (first_name, hire_date) VALUES (%s, %s)"
cursor.executemany(stmt, data)

Source: https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-executemany.html