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']
asked Sep 12, 2019 at 15:27
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: //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.
answered Sep 12, 2019 at 16:27
Vovan KuznetsovVovan Kuznetsov
3511 gold badge3 silver badges9 bronze badges
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
answered Feb 21, 2020 at 11:06
rdmolonyrdmolony
4651 gold badge6 silver badges14 bronze badges
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]]
answered Sep 12, 2019 at 15:29
DjaouadNMDjaouadNM
21.5k4 gold badges29 silver badges52 bronze badges
2
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.
answered Sep 12, 2019 at 15:52
berealbereal
30.2k6 gold badges53 silver badges93 bronze badges
Depending on what function you are using, ? can represent a python variable like.
*"select * from table where name in ?;", [list,]]*
answered Sep 12, 2019 at 15:35
BillyNBillyN
1752 silver badges9 bronze badges