0

Say I have a tuple of IDs:

ids = (1,2,3,4,5)

Using python, I want to delete every row from a database table (called schedule) that doesn't contain one of these IDs.

This is what I've tried:

delete_stmt = "DELETE from schedule WHERE ID NOT IN %s"
...
cursor.execute(delete_stmt, ids)

I've excluded the unnecessary code like connecting to the database, but I am connected properly.

I get an error message saying:

You have an error in your SQL syntax...

How can I fix my code to delete rows from the schedule table that aren't found in the ids tuple?

Daniel C Jacobs
  • 691
  • 8
  • 18
  • You cannot parameterize tuples; you need a parameter for each tuple element. – Uueerdo May 31 '19 at 19:40
  • How could I do this if I don't know how long the tuple is? – Daniel C Jacobs May 31 '19 at 19:41
  • 1
    Usually it involves dynamically constructing a query string with a corresponding number of parameters. I am not very familiar with python, does [this](https://stackoverflow.com/questions/17847297/mysql-connector-python-in-operator-stored-as-list) help? – Uueerdo May 31 '19 at 19:42

1 Answers1

1

IN is a MySql Function, try it with ()

delete_stmt = "DELETE from schedule WHERE ID NOT IN (%s)"
Toxi
  • 109
  • 5
  • This yields an error saying `"Not all parameters were used in the SQL statement"` when I get to the execute line. – Daniel C Jacobs May 31 '19 at 19:39
  • WTF ^^ OK, then u have to debug this statement, plz show me your parsed stmt if possible. Also the DB layout would be good... – Toxi May 31 '19 at 19:41
  • Try `delete_stmt = """DELETE from schedule WHERE ID NOT IN (%s)"""` and set "schedule" in commas: "`". Also you can take a look at this post: https://stackoverflow.com/questions/20818155/not-all-parameters-were-used-in-the-sql-statement-python-mysql/20818201#20818201 – Toxi Jun 01 '19 at 07:55