CODEWITHSUNDEEP
pythonmysqlstringsql-delete
Merlin
Merlin

Reputation: 25629

Delete multiple rows in MYSQL with info from python list

If list LL:

LL = ['foo', bar', 'noo', 'boo',]

is in a MySQL table, test in column ID with other ID's.

I could use the following to delete all rows with ID's in LL: 

 csr.execute("""DELETE FROM test.test WHERE ID = "Foo"; """)
  csr.execute("""DELETE FROM test.test WHERE ID = "bar"; """)  
  csr.execute("""DELETE FROM test.test WHERE ID = "noo"; """)
  csr.execute("""DELETE FROM test.test WHERE ID = "boo"; """)

How could I do it programatically?

Upvotes: 6

Views: 11508

Answers (6)

simranpal kohli
simranpal kohli

Reputation: 21

Just convert the list into a string format with comma-separated and use a normal where clause with in condition.

id_list = ['abc', 'def', 'ghi']
id_list_string = "', '".join(id_list)
delete_query = "delete from test where id in ('" +id_list_string+"')"
dbconnection.execute(delete_query)

Upvotes: 0

Biswajit Chopdar
Biswajit Chopdar

Reputation: 871

For MySQL you need to use %s instead of ? as the parameter marker. And don't forget to commit.

product_list = [645, 64, 9785, 587]
query = "DELETE FROM products WHERE id IN (%s)" % ",".join(["%s"] * len(product_list))
cursor.execute(query, product_list)
connection.commit()

Upvotes: 2

Chandan392
Chandan392

Reputation: 57

We can use one delete query and convert the list into tuple.

list_ids = ['foo', bar', 'noo', 'boo',]
delete_query = "DELETE FROM test.test WHERE ID=%s"
delete_records = tuple(list_ids)
cursor.executemany(delete_exec, delete_records)

Upvotes: -2

sapht
sapht

Reputation: 2829

You can do it with a single query:

id_list = ['abc', 'def', 'ghi']
query_string = "delete from test where id in (%s)" % ','.join(['?'] * len(id_list))
cursor.execute(query_string, id_list)

Since cursor.execute escapes strings when doing substitutions, this example is safe against SQL injections.

Upvotes: 12

tMC
tMC

Reputation: 19315

for item in LL:
    csr.execute("DELETE FROM test.test WHERE ID = '%s'", item)

like that?

Upvotes: 1

dfb
dfb

Reputation: 13289

String formatters - http://docs.python.org/library/string.html#format-string-syntax

["""DELETE FROM test.test WHERE ID = "%s"; """ % x for x in LL]

and then run each of the SQL statements in the list.

Upvotes: 1

Related Questions