5

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')
PaCi
  • 143
  • 1
  • 1
  • 7
  • 1
    `','.join('\'' + i + '\'' for i in name_list)` – Olvin Roght Sep 12 '19 at 15:29
  • It may depend on the database and the driver. Anyway, I'd not recommend using anything that involves string concatenation for security reasons. – bereal Sep 12 '19 at 15:38
  • 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](https://docs.sqlalchemy.org/en/13/core/sqlelement.html?highlight=expanding#sqlalchemy.sql.expression.bindparam), which precludes the need to do string manipulations. – Mihai Chelaru Sep 12 '19 at 17:01

5 Answers5

3

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.

Vovan Kuznetsov
  • 461
  • 1
  • 4
  • 10
2

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
  • 601
  • 1
  • 7
  • 15
1

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.

bereal
  • 32,519
  • 6
  • 58
  • 104
0

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])
DjaouadNM
  • 22,013
  • 4
  • 33
  • 55
  • 1
    What if my name is `Scarlett O'Hara`, not to mention [little Bobby Tables](https://xkcd.com/327/)? – bereal Sep 12 '19 at 15:40
  • @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. – DjaouadNM Sep 12 '19 at 15:51
0

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

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

BillyN
  • 185
  • 2
  • 10