Python - Cursor - Multiple filters from lists

I want to run a query that filter two columns based on values from two list.

Basically, I want to simulate two filters like this:

SELECT *
FROM my_table
WHERE customers in ("John","Peter") AND customers_numbers IN ('1','2')

But the values from customers and customers_number are in two lists. To try this I am making the following code:

list1 = ["John","Peter"]
list2 = [1,2]
query_sql = "DELETE FROM vw_bill_details WHERE customers in (%s) and customers_numbers in (%s)" % ','.join(['?'] * len(list1)) % ','.join(['?'] * len(list2))
cursor.execute(query_sql, list1,list2)

But I am getting the following error:

    query_sql = "DELETE FROM vw_bill_details WHERE customers in (%s) and customers_numbers in (%s)" % ','.join(['?'] * len(list1)) % ','.join(['?'] * len(list2))
TypeError: not enough arguments for format string

How can I make the above query using python?

Thanks!

Solution

You've got an error in your query, there's an extra % between the two terms instead of a comma. Also, when you use % formatting with more than one term, you need to have the entire variables section after the % in parentheses:

query_sql = "DELETE FROM vw_bill_details WHERE customers in (%s) and customers_numbers in (%s)" % (','.join(['?'] * len(list1)), ','.join(['?'] * len(list2)))

Improvements:

consider writing your query in a docstring so that it's easier read, write, and debug:

query_sql = """DELETE FROM vw_bill_details
WHERE customers in (%s)
and customers_numbers in (%s)""" % (
','.join(['?'] * len(list1)), ','.join(['?'] * len(list2)))

str.join() works on any iterable, including strings, so the ','.join(['?'] * len(list1)) parts could be written as ','.join('?' * len(list1)) - the ? mark is a single string rather than a list with a single element.
There is potential for matching the wrong records: the WHERE customers in ("John","Peter") AND customers_numbers IN ('1','2') doesn't care/check of 'John' has cust_number 1 or 2. So it could match with a John-2 and a Peter-1, instead of you're intended John-1 and Peter-2.

Examples of the mismatch can be seen here: http://sqlfiddle.com/#!9/caa7f3/2

You can avoid this mismatch by specifying the name and number matches each:
```
WHERE (customers = 'John' AND customers_numbers = '1')
   OR (customers = 'Peter' AND customers_numbers = '2')
```
which could also be written as as a matching pair:
```
WHERE (customers, customers_numbers) = ('John', 1)
```
and you can extend it for multiple options with:
```
WHERE (customers, customers_numbers) IN (('John', 1), ('Peter', 2))
```
which is easier to parameterize with ?s than the extended AND/OR version above.