pandas sqlite read_sql dynamic in clause

Question

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

Stef · Accepted Answer · 2020-12-21T13:56:19.740

1

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)

edited Dec 21 '20 at 13:56

answered Dec 21 '20 at 13:10

Stef

28,728
2
24
52

@Neel: sorry for the multiple edits/deletions, now it's correct and tested. – Stef Dec 21 '20 at 14:02
thanks this worked. Although I was able to find another variation that worked – Neel Dec 25 '20 at 16:55

score 0 · Answer 2 · answered Dec 25 '20 at 16:59

0

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.

answered Dec 25 '20 at 16:59

Neel

613
4
14
32

I guess it's just a matter of taste: `playerIdList + [battingDownNum, "'T20'"]` converts the two last arguments to a list and than combines the two lists whereas `(*playerIdList, battingDownNum, "'T20'")` first unpacks the list and then converts all the arguments back into a tuple. So in both cases we get a list or tuple of all the arguments which [eventually are turned into a list and combined with the query statement](https://github.com/pandas-dev/pandas/blob/b5958ee1999e9aead1938c0bba2b674378807b3d/pandas/io/sql.py#L60-L65). – Stef Dec 25 '20 at 22:07
So I'd prefer the former solution as unpacking the list just to re-pack it into another list seems less intuitive to me, but as I said it's appears to be a matter of personal preference. – Stef Dec 25 '20 at 22:08

pandas sqlite read_sql dynamic in clause

2 Answers2