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.

Solution 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.

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

Comments

[Om Nom]

Works like a charm. Thank you!

[jdmarino]

I needed str(tuple(scoreDF.index)) to avoid errors. (Due to python 3, I think.)

[Giacomo]

I needed str(tuple(scoreDF.index)) as well and I'm using Python 2.7

[bolec_kolec]

You should use params parameter in read_sql to avoid sql injection

[Shashank]

How to use it for 2 or more tuples ?

[vks]

@Shashank join them to make a new tuple...using list or a new tuple..

[FinThusiast]

Will not work if length of scoreDF.index is 1, tuple(scoreDF.index) will result in (1048,), for example.