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

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