Passing a list to SQL query in Python

Question

So I am trying to pass months as a tuple to a sql query in python as below:

no_of_months = 6
months = tuple(months_list[no_of_months:])
months
Out[14]: ('201708', '201709', '201710', '201711', '201712', '201801')
wrk_hours = str("""select * from dbo.month_step where month_id IN \
 %s and tier = 'Sil';""") %months

However this throws an error as below:

TypeError: not all arguments converted during string formatting

Can someone please help me resolve this?

In case the months_list is needed:

['201702',
 '201703',
 '201704',
 '201705',
 '201706',
 '201707',
 '201708',
 '201709',
 '201710',
 '201711',
 '201712',
 '201801']

Answer 1

Try this:

wrk_hours = str("""select * from dbo.month_step where month_id IN %s and tier = 'Optimum Silver';""") %[x for x in months]
wrk_hours
Out[1]: "select * from dbo.month_step where month_id IN 
 ['201708', '201709', '201710', '201711', '201712', '201801'] and tier = 'Optimum Silver';"

Answer 2

Try this:

wkr_hours = str("""select * from dbo.month_step where mont_id in \
(%s) and tier = 'Sil';""")%(",".join(map(lambda x:"'"+x+"'",months_list[no_of_months:])))

Answer 3

I think this should the trick:-

list_args = ['201708', '201709', '201710', 
'201711', '201712', '201801'] 
str("""select * from dbo.month_step where month_id IN (%s) and tier = 
'Sil';""") % (','.join(list_args))

It is simpler and works.