0

I have a list l= ['A','B','C'...] which has a dynamic number of elements. I want to run the below statement in using SQL via Python. 

sqlstr = "select * from [table] where ID in (%s) and column2=?" 
sqlstr = sqlstr % ','.join('?' * len(l))

However, when I try to run 

pd.read_sql(sqlstr,conn,params=[l,parameter2])

There is an error

The SQL contains 4 parameter markers, but 2 parameter supplied

I understand why there is an error because the list is parsed as 1 single parameter rather than individually. But I don't know how to fix it.

pault
  • 41,343
  • 15
  • 107
  • 149
Felton Wang
  • 153
  • 8
  • 2
    `pd.read_sql(sqlstr,conn,params=[*l,parameter2]`? – pault Apr 14 '20 at 19:35
  • [The `.format()` method is meant to replace the old `%` formatting syntax. The latter has been de-emphasised, (but not officially deprecated *yet*)](https://stackoverflow.com/a/13452357/1422451) – Parfait Apr 14 '20 at 21:58
  • @pault, cheers that solved my issue! – Felton Wang Apr 15 '20 at 08:21
  • @FeltonWang I also think `sqlstr = " select * from [table] where ID in ? and column2=?"; pd.read_sql(sqlstr,conn,params=[l,parameter2])` might work. If so, that's probably a better solution than using `'?'.join()` – pault Apr 15 '20 at 12:26
  • @pault Thanks a lot. I tried without the .join but failed to work. Error with mismatching number of parameters. – Felton Wang Apr 16 '20 at 13:28

0 Answers0