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"
Add a comment
|
1 Answer
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)
2 Comments
vyas gottipati
The above-mentioned answer is working. Thanks. But, I have a doubt. As we are converting each value in the tuple to a string but actual values to match in the data frame are type int. So, how is this working correctly.
Parfait
In order to use
string.join the numbers have to be converted to string. However, we do not quote the values in SQL so they are treated as numbers.