Use pandas list to filter data using postgresql query

Question

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?

snakecharmerb · Accepted Answer · 2021-06-21 12:57:41Z

2

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.

answered Jun 21, 2021 at 12:57

snakecharmerb

57.1k13 gold badges136 silver badges200 bronze badges

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

3 Comments

snakecharmerb Over a year ago

What is the result of print(cur.mogrify(sql, id_list))?

The Great Over a year ago

I fixed it. It was an unrelated issue. May I know what does mogrify stand for?

snakecharmerb Over a year ago

It prints the query that will be sent to the database psycopg.org/docs/cursor.html#cursor.mogrify (assuming that your are using psycopg2, which I should checked :-)

Mohamed Sayed · Accepted Answer · 2021-06-21 10:51:03Z

0

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]'

answered Jun 21, 2021 at 10:51

Mohamed Sayed

5359 silver badges14 bronze badges

2 Comments

Mohamed Sayed Over a year ago

However, Postgresql does not use [ to express lists. You might want to convert list to tuple as it use (. df_q = pd.read_sql(f'select * from tablea where subject_id in {tuple(id_list)}', con=conn)

snakecharmerb Over a year ago

Using string formatting to bind values to SQL queries is error-prone and a security risk. Please don;t do it.

Collectives™ on Stack Overflow

Use pandas list to filter data using postgresql query

2 Answers 2

3 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related