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.
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()
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.
LL contains only trusted items. If it contains data from untrusted sources and not properly escaped, SQL injection is possible.for item in LL:
csr.execute("DELETE FROM test.test WHERE ID = '%s'", item)
like that?
each should be item. fixed.
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)
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)
