Search code examples
pythonsqlpandaspandasql

How to use dynamic variables(tuple) in pandasql query?

number_tuple = (1,4,6,3) sensex_quaterly_df = psql.sqldf("SELECT * FROM sensex_df
WHERE 'Num' IN ('number_tuple')")

"HERE number_tuple has the values that I want to retrieve from sensex_df database"

Solution

  • Because pandasql allows you to run SQL on data frames, you can build SQL with concatenated values of tuple into comma-separated string using string.join().

    number_tuple = (1,4,6,3)
in_values = ", ".join(str(i) for i in number_tuple)

sql = f"SELECT * FROM sensex_df WHERE Num IN ({in_values})"

sensex_quaterly_df = psql.sqldf(sql)

    However, concatenated SQL strings is not recommended if you use an actual relational database as backend. If so, use parameterization where you develop a prepared SQL statement with placeholders like %s of ? and in subsequent step binding values. Below demonstrates with pandas read_sql:

    number_tuple = (1,4,6,3)
in_values = ", ".join('?' for i in number_tuple)

sql = f"SELECT * FROM sensex_df WHERE Num IN ({in_values})"

sensex_quaterly_df = pd.read_sql(sql, conn, params=number_tuple)