CODEWITHSUNDEEP
python
PaCi
PaCi

Reputation: 153

How to pass python list of string to sql query

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')

Upvotes: 6

Views: 18173

Answers (6)

GARGEE SINGH
GARGEE SINGH

Reputation: 1

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.

Upvotes: 0

rdmolony
rdmolony

Reputation: 771

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

Upvotes: 5

Vovan Kuznetsov
Vovan Kuznetsov

Reputation: 491

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.

Upvotes: 3

Djaouad
Djaouad

Reputation: 22766

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])

Upvotes: 0

bereal
bereal

Reputation: 34272

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.

Upvotes: 1

BillyN
BillyN

Reputation: 185

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

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

Upvotes: 0

Related Questions