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']

CezarySzulc · Accepted Answer · 2018-02-27 06:57:32Z

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';"

answered Feb 27, 2018 at 6:57

CezarySzulc

2,0191 gold badge18 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Shuvayan Das · Accepted Answer · 2018-02-27 07:04:41Z

1

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:])))

edited Feb 27, 2018 at 7:04

Shuvayan Das

1,0485 gold badges23 silver badges43 bronze badges

answered Feb 27, 2018 at 6:54

Vasyl Moskalov

4,6403 gold badges24 silver badges30 bronze badges

Comments

gautamaggarwal · Accepted Answer · 2018-02-27 07:17:13Z

1

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.

edited Feb 27, 2018 at 7:17

answered Feb 27, 2018 at 7:02

gautamaggarwal

3491 gold badge2 silver badges11 bronze badges

Collectives™ on Stack Overflow

Passing a list to SQL query in Python

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related