Passing a Dataframe column in where clause of a SQL (Column is of type string)

Question

I am trying to pass a Pandas Dataframe column in the where clause of a SQL. This column is of type string.

bill_id, product_name
101, prod_a
102, prod_b
103, prod_c
104, prod_d


prod_list = ','.join(["'" + str(x) + "'" for x in df['product_name'].tolist()])

I am trying to pass product name in the where clause of a SQL as below:

query = """select prod_id, product_name from products where product_name in (%s)""" % prod_list

This however seems to return back an empty Dataframe when I know there are matches available for values in that list

Tim Biegeleisen · Accepted Answer · 2021-06-29 10:15:31Z

1

One common way to handle this is to manually build the WHERE IN (...) clause using the correct number of placeholders corresponding to the input list. Then, bind that list to the statement as you typically would do.

products = df['product_name'].tolist()
where = "(%s" + ", %s"*(len(products) - 1) + ")"
query = """
SELECT prod_id, product_name
FROM products
WHERE product_name IN """
query = query + where

cursor.execute(query, products)

Note that for a product input list of 3 elements, the above code snippet would generate the following raw SQL query:

SELECT prod_id, product_name
FROM products
WHERE product_name IN (%s, %s, %s)

answered Jun 29, 2021 at 10:15

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

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

Collectives™ on Stack Overflow

Passing a Dataframe column in where clause of a SQL (Column is of type string)

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related