python列表删除行_从python列表中删除带有信息的MYSQL中的多行

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?

解决方案

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.