How to pass python list of string to sql query

Question

How to pass a python list of strings to SQL query such as select * from table where name in (names_from_python_list) where names_from_python_list is comma separated strings from python list?

Doing ','.join(name for name in name_list) gives all the names in the list as a string i.e.

 select * from table where name in ('john,james,mary')

whereas, what I want to do is:

 select * from table where name in ('john','james','mary')

• Olvin Roght: ','.join('\'' + i + '\'' for i in name_list)
• bereal: It may depend on the database and the driver. Anyway, I'd not recommend using anything that involves string concatenation for security reasons.
• Mihai Chelaru: Could you provide the DB-API driver you're using to connect to the database and the code you're using to query it from Python? As some have already mentioned, some libraries provide the possibility to expand iterable parameters, which precludes the need to do string manipulations.

Answer 1

You can alternatively pass a tuple into your SQL query:

query = f"SELECT * FROM table WHERE name IN {tuple(names)}"
c.execute(query,conn)

It's also more robust than using:

query = "SELECT * FROM table WHERE name IN (?,?)" 
c.execute(query,conn,params)

As you don't get the error...

OperationalError: (sqlite3.OperationalError) too many SQL variables

... when passing a large number of variables into the query

• rdmolony: However, beware of SQL injection if your parameter is inputted by a user in say a web application!
• Rafa Barragan: Note not to include the enclosed parenthesis and doing this way allows you to print the query if you need to debug directly in the DB,

Answer 2

Rather than reinventing the wheel I'd suggest looking at native solutions mature db libraries provide.

psqycopg2 e.g. allows registering adapter so that handling lists (and other sequences) becomes transparent, you can just directly pass list as a query parameter. Here's an example: https://chistera.yi.org/~dato/blog/entries/2009/03/07/psycopg2_sql_in.html

pymysql also provides a good set of built-in escapers including one for dealing with sequences so that you don't have to worry about manual formatting (which is error-prone) and can directly use tuple as argument in IN clause. Example:

>>> conn = pymysql.connect(host='localhost', user='root', password='root', db='test')
>>> c.execute('select * from hosts where ip in %s', (('ip1', 'ip2'),))
>>> c.fetchall()
((1, 'mac1', 'ip1'), (3, None, 'ip2'))

Pretty sure many other mature libraries/frameworks provide similar functionality.

Answer 3

This may depend on the driver peculiarities, though with standard DB API it should look like:

connection.execute('SELECT * FROM table WHERE name IN (?)', (names,))

With some drivers ? may also be :1, %s etc.

Answer 4

Join by ',', and enclose everything by ' (don't forget to also replace ' in names with \' to escape them):

"'" + "','".join(name.replace("'", r"\'") for name in name_list) + "'") + "'"

Or you can just use str.format and get the str of the list (minus the [], hence the slicing), using this way will change the quotations surrounding the string, i.e., if the string is 'O\'Hara', it will be transformed to "O'Hara":

query = 'select * from table where name in ({})'.format(str(name_list)[1:-1])

• bereal: What if my name is Scarlett O'Hara, not to mention little Bobby Tables?
• Djaouad: @bereal Thanks for that note, I fixed it, though the str(names)[1:-1] automatically encloses strings that have ' by " (and vice-versa), and it escapes them if the string has both, but since some RDBMS don't support double quoted strings, I think manually doing is better.

Answer 5

Depending on what function you are using, ? can represent a python variable like.

*"select * from table where name in ?;", (list,))*

Answer 6

Please note that str(list)[1:-1] might throw str obj not callable error.

If you do:

list=[]
a= ['abc','def','ghi','jkl']
for i in a:
 list.append(i)
print(str(list)[1:-1])

It prints:

'abc','def','ghi','jkl'

However, if you do:

list=[list.append(i) for i in a]
print(str(list)[1:-1])

This throws str(obj) not callable.

The first snippet is less Pythonic but helps with getting the brackets off when your list is a string and you still need the brackets on for some sql query for e.g. an IN clause.

• LISTERINE: A few things to note about this answer. overwriting the built-in list is likely to cause problems and is discouraged by the community. In your second example, your comprehension is assigning the output of list.append to the list. list.append is an in-place operation and will result in a list of Nones. Using the comprehension list=[i for i in a] will give you the same output as example 1 and the same output when printing.