[SOLVED] pandas sqlite read_sql dynamic in clause

pandas sqlite read_sql dynamic in clause

I am trying to use pandas read_sql function to query some data from sqlite DB. I need to use parameterized SQL which contains in clause (List) and some static parameters.

Below is my query

battingDataQuery = ('SELECT ID, MATCH_DATE, ROLE, DOWN_NUM, NAME, RUNS,' 
                    'MATCH_ID, TEAM_NAME, VERSUS_TEAM_NAME, GROUND_NAME ' 
               'FROM BATTING_DATA WHERE ID in ({1}) '
                'AND DOWN_NUM < {0} AND MATCH_TYPE = {0}')

I have added the placeholders appropriately using format

battingDataQuery = battingDataQuery.format('?', ','.join('?' * len(playerIdList)))

My generated SQL is as following

'SELECT ID FROM BATTING_DATA WHERE ID in (?,?,?,?,?) AND DOWN_NUM < ? AND MATCH_TYPE = ?'

I am stuck at the last part where I am sending the parameters as following:

battingDataDF = pd.read_sql_query(battingDataQuery , conn, params=(playerIdList,battingDownNum,'\'T20\''))

I am getting following error when using this

Incorrect number of bindings supplied. The current statement uses 7, and there are 3 supplied.

I have tried using following variations but still get the same error

battingDataDF = pd.read_sql_query(battingDataQuery , conn, params=[playerIdList,battingDownNum,'\'T20\'']) # same error

battingDataDF = pd.read_sql_query(battingDataQuery , conn, params=[playerIdList,battingDownNum,'\'T20\'']) # same error

battingDataDF = pd.read_sql_query(battingDataQuery , conn, params=[tuple(playerIdList),battingDownNum,'\'T20\'']) # same error

Solution

You should supply a list of 7 parameters for your 7 question marks:

battingDataDF = pd.read_sql_query(battingDataQuery , conn, params=playerIdList + [battingDownNum, "'T20'"])

(you supplied 3 parameters: a list of 5 numbers, a number and a string, hence the error)