Use pandas list to filter data using postgresql query

user_10829044 0
Viewed 559

I already referred these posts 1, 2. Am not sure whether I am using the suggestions from these posts incorrectly.

Basically, I would like to use my pandas list in a postgresql query (written in Jupyter notebook)

id_list = [1,2,3,4]

I would like to use my id_list in the below query. I tried the below 2 options

option-1

df_q = pd.read_sql('select * from tablea where subject_id in {id_list}', con=conn)

option-2

cur.execute("select * from tablea where subject_id in %s", id_list)

Can experts here help me with the solution on how to use the python variables directly in the query?

pandas psycopg2 postgresql sql python
2 Answers

The correct way to handle an IN clause is by building the placeholder clause separately, then using parameter substitution to bind the list elements to the query:

sql = "select * from tablea where subject_id in ({})"
# Create a string like "%s, %s, %s" with one "%s" per list element
placeholders = ', '.join(['%s'] * len(id_list))
sql = sql.format(placeholders)
# Use parameter substitution to bind values to the query
cur.execute(sql, id_list)

Using string formatting or concatenation, including f-strings, may cause errors if values are incorrectly escaped, or in the worst case expose your database to SQL injection attacks.

user_53209060

If you want to use a variable inside string in python you just want to add f at the start of the string like that

df_q = pd.read_sql(f'select * from tablea where subject_id in {id_list}', con=conn)

This will get translated to 'select * from tablea where subject_id in [1, 3, 4]'

user_79541070
Related