Iterating through a list Python SQL

Question

I'm trying to search a database, by iterating through a list of search values. I'm almost there as this works for integers but not strings. The code below won't work, but if I replace the list values with numbers it does:

mycursor = mydb.cursor()

lst = []

select_query = "SELECT * FROM fruit WHERE content LIKE "
ids = ["apple", "pear"]

for x in ids:
    var = select_query + str(x)
    
    mycursor.execute(var)
    lst.extend(mycursor.fetchall())

print(lst)

prefer using prepared statements like in the below answer, rather than usin concatenation which might cause vulnerability to SQL injection attacks. — Barbaros Özhan
– Barbaros Özhan, Commented Dec 22, 2021 at 12:44
What do you want to select with LIKE ? All fruits where content is exactly equal to 'apple' or where it contains the keyword %apple% matching also 'pineapple' ? Why not WHERE content IN ('apple', 'pear") ? — hc_dev
– hc_dev, Commented Dec 22, 2021 at 12:58

user23952 · Accepted Answer · 2021-12-22 12:41:37Z

2

It's because you have to enclose strings in quotation marks in SQL. So for example

SELECT * FROM fruit WHERE content LIKE 'pears'

will work, and it will only work with the single quotations around "pears". Even better, type conversion can (and should) be done automatically with psycopg2:

select_query = "SELECT * FROM fruit WHERE content LIKE %s"
...
mycursor.execute(select_query, (x,))

https://www.psycopg.org/docs/usage.html#strings-adaptation

answered Dec 22, 2021 at 12:41

user23952

6824 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Iterating through a list Python SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related