[SOLVED] pandas' read_sql with a list of values for WHERE condition

Issue

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.

Solution

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.

Answered By – vks

Answer Checked By – Dawn Plyler (BugsFixing Volunteer)