Multiple In statements in SQL through python

Question

Currently I am running an sql query through python to extract information from a database.

My code is:

cities = ('London')
sql = 'SELECT * FROM Customers WHERE City IN ({});'
cur.execute(sql, cities)

I now want to put in an additional IN statement so my sql query would look something like this:

coun = ('uk')
cities = ('london')
sql = 'SELECT * FROM Customers WHERE City IN ({}) AND Country IN ({});'
cur.execute(sql, cities, coun)

This code doesn't work because I have specified two variable lists in the execute command but I'm also wondering if SQL doesn't like having two IN statements? I tried to test it separately but it didn't like the two IN statements.

Is there a way I can get this code to work? I'm running python 2.7 and the sqlite3 module. The cities and coun lists may change for each user input so I can't just put the raw values into the SQL statement.

What is cur and have you read the documentation for that class and for the execute method? Why are you declaring your strings within parantheses? ('london') == 'london' If you want to create a tuple literal, you need to use a comma ('london',) — Håken Lid
– Håken Lid, Commented Jul 12, 2015 at 12:03
It is not a SQL issue. Multiple IN statements are no problem. The issue must be related to the binding of cities and coun to the appropriate parameter markers. — Matthias J. Sax
– Matthias J. Sax, Commented Jul 12, 2015 at 13:20

Paul Rooney · Accepted Answer · 2015-07-12 13:15:38Z

1

One way to do it is to firstly build up the correct number of required '?' values in your select query string, using the length of the coun and cities tuples (I made them tuples rather than the parenthesized strings shown in your question).

This is not vulnerable to an sql injection attack, as you are only formatting question mark characters in the query on user data.

After that you can simply combine the coun and cities tuples and pass it to execute.

import sqlite3
from itertools import repeat

con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute("create TABLE Customers (Name, City, Country)")

companylist = [
    ("Microsoft", "Redmond", "United States"),
    ("BSkyB", "London", "UK"),
    ("Freesat", "London", "UK"),
    ("BBC", "Manchester", "UK"),
    ("Aqsacom", "Melbourne", "Australia"),
    ('Blah', 'London', 'Canada')
]

# multiple case
#coun = ('UK', 'Australia')
#cities = ('London', 'Melbourne')

# single case
coun = ('UK',)
cities = ('London',)


def makeqmarks(i):
    return ', '.join(repeat('?', i))

for company in companylist:
    cur.execute("INSERT INTO Customers Values (?,?,?)", company)

sql = 'SELECT * FROM Customers WHERE City IN (%s) AND Country IN (%s);' \
% (makeqmarks(len(cities)), makeqmarks(len(coun)))

cur.execute(sql, cities + coun)

for res in cur.fetchall():
    print(res)

I am not an sql expert so I would appreciate feedback on any drawbacks of this approach.

answered Jul 12, 2015 at 13:15

Paul Rooney

21.7k9 gold badges47 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

qwertylpc Over a year ago

Seems to do what I need it to do. Did you ever find a reason not to do it this way?

Paul Rooney Over a year ago

No I didn’t. Seems it’s fine.

Collectives™ on Stack Overflow

Multiple In statements in SQL through python

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related