Specify parameter (list or array) in operator - python, sql
I have a list in python.
article_ids = [1,2,3,4,5,6]
I need to use this list in sql statement like this:
SELECT id FROM table WHERE article_id IN (article_ids)
How can I provide this list to my proposal IN()
?
I've already tried some of the ways that I googled but I couldn't figure it out.
>> print ids_list
placeholders= ', '.join('?'*len(ids_list)) # "?, ?, ?, ... ?"
query = 'SELECT article_id FROM article_author_institution WHERE institution_id IN ({})'.format(placeholders)
print query
cursor.execute(query, ids_list)
Error message:
SELECT article_id FROM article_author_institution WHERE institution_id IN (?)
query = query % db.literal(args)
TypeError: not all arguments converted during string formatting
For example, for:
ids_list = [9,10]
placeholders= ', '.join('%'*len(ids_list)) # "?, ?, ?, ... ?"
query = 'SELECT aid FROM article_author_institution WHERE instid IN ({})'.format(placeholders)
print query
cursor.execute(query, ids_list)
Output and error message:
SELECT aid FROM article_author_institution WHERE instid IN (%, %)
Traceback (most recent call last):
File "deneme3.py", line 11, in <module>
cursor.execute(query, ids_list)
File "/usr/lib/python2.7/dist-packages/MySQLdb/cursors.py", line 159, in execute
query = query % db.literal(args)
ValueError: unsupported format character ',' (0x2c) at index 61
+3
source to share
1 answer
The idea is to have a query like this:
cursor.execute("SELECT ... IN (%s, %s, %s)", (1, 2, 3))
where each %s
will be replaced with items in your list. To build this string query, you can:
placeholders= ', '.join(['%s']*len(article_ids)) # "%s, %s, %s, ... %s"
query = 'SELECT name FROM table WHERE article_id IN ({})'.format(placeholders)
finally
cursor.execute(query, tuple(article_ids))
+5
source to share