give parameter(list or array) to in operator - python, sql [duplicate]

Question

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 IN() clause?

I have already try some ways which 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 I get:

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)

The output and error message are:

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

Antivenin

Answer 1

The idea is to have a query like this one:

cursor.execute("SELECT ... IN (%s, %s, %s)", (1, 2, 3))

where each %s will be substituted by elements in your list. To construct this string query you can do:

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

Venlo

Not work again, i editted and put the output and my code to the question. If you check and help, i will appreciated – Antivenin

Are you using sqlite? What DB are you using? What python driver? – Venlo

Db is mysql , python version is 2.7 – Antivenin

Which module are you using to access the DB from python? – Venlo

Thats the problem, I assumed Sqlite, in MySQL we need %s instead of ?. Please try my updated answer. – Venlo

You made a small mistake copying the code, in placeholders= line, replace '%' with ['%s'], or copy paste directly from answer. – Venlo

thank you to the question poster and this answer! – Orelle

Answer 2

I think the right option is to use tuples data structure. Hope below code helps you.

article_ids = [1,2,3,4,5,6]
ids = tuple(article_ids)
query = "SELECT name FROM table WHERE article_id IN {}".format(ids)
cursor.execute(query)

Clubfoot

I honestly feel like this is the best, cleanest answer. Not sure why I'm the only upvote ... 🤔 – Magritte

cleanest answer! thank you – Impertinent

Thank you, Andrew Einhorn & Hanan Atallah – Clubfoot

This solution is prone to sql injection attacks. The accepted answer is the safest way to do it. – Crowther

Jp Reddy thanks for this clarification! – Orelle

You will get a syntax error if the list has only 1 item, this is because Python will convert the tuple like this (1,) ... so the comma will cause a syntax error in the database statement – Pallid

@AndrewEinhorn that's a SQL injection bug, not a clean answer. – Frankenstein

Answer 3

For Python3 you can use

article_ids = [1,2,3,4,5,6]
sql_list = str(tuple([key for key in article_ids])).replace(',)', ')')
query ="""
    SELECT id FROM table WHERE article_id IN {sql_list}
""".format(sql_list=sql_list)

resulting in

>>> print(query)

    SELECT id FROM table WHERE article_id IN (1, 2, 3, 4, 5, 6)

which you can feed to

cursor.execute(query)

This also works for article_ids of only 1 value (where tuple generates (1,)).

It also works for arrays of strings:

article_titles = ['some', 'article titles']

resulting in

    SELECT id FROM table WHERE article_title IN ('some', 'article titles')

Goral

str(tuple([key for key in article_ids])).replace(',)', ')') saved me a lot of time. thanks! – Maharanee

This method is also prone to SQL injection attacks – Bhatt

Can you explain the .format(sqllist=sqllist) at the end of the query please? – Righteous

.format(sql_list=sql_list) means replace the string {sql_list} in the query in line 4 with the value of sql_list as set in line 2. – Goral