pandas' read_sql with a list of values for WHERE condition

Question

Suppose a dataframe scoreDF:

          date       time      score
sec_code
1048      2015-02-25 09:21:00     28
2888      2015-02-25 09:21:00     25
945       2015-02-25 09:21:00     23
4         2015-02-25 09:21:00     22
669       2015-02-25 09:21:00     15

I need to make a MySQL query to retrieve all rows matching the values in scoreDF.index i.e. sec_code column.

Normally I'd go for a loop:

    finalResultDF = DataFrame()

    queryString = 'SELECT * FROM tableA WHERE sec_code = ' + code

    for code in scoreDF.index:
        queryResultDF = sql.read_sql(queryString, con)
        finalResultDF.append(queryResultDF)

Would it be possible to do this differently without a loop passing a list of values i.e. scoreDF.index as WHERE condition? I Googled for hours and some mentions 'parameter' to read_sql but I couldn't figure it out.

Answer 1

You can actually do this without any loop.

queryString = 'SELECT * FROM tableA WHERE sec_code in '+tuple(scoreDF.index)

This will give the results directly.This is assuming scoreDF.index is a list.If it is already a tuple then no typecasting is required.

Answer 2

As bolec_kolec suggested, I think best practice is to use params when calling read_sql. Here's how I generally do it (Python 3.7):

scoreIndex = scoreDF.index.tolist() 
queryString = 'SELECT * FROM tableA WHERE sec_code = ANY(%(scoreIndex)s)'

queryParams = {'scoreIndex': scoreIndex}
queryResultDF = sql.read_sql(sql = queryString, con, params = queryParams)