CODEWITHSUNDEEP
pandassqlitedataframeparametersread-sql
Neel
Neel

Reputation: 625

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

Upvotes: 1

Views: 505

Answers (2)

Neel
Neel

Reputation: 625

Answer given my @stef worked but I was able to find another variation that worked. So wanted to post that for the sake of completion

battingDataDF = pd.read_sql_query(battingDataQuery , conn, params=(*playerIdList,battingDownNum,matchType))

*causes the list to be unpacked and thus resulting in supply of the correct number of arguments

Not sure which approach is better. If someone can post some light on this, it will be great.

Upvotes: 0

Stef
Stef

Reputation: 30639

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)

Upvotes: 1

Related Questions