在Python中将列表传递给SQL查询

Question

因此，我尝试将数月作为元组传递给python中的sql查询，如下所示：

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

但是，这会引发如下错误：

TypeError: not all arguments converted during string formatting

有人能帮我解决这个问题吗？你知道吗

如果需要月份列表：

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

Answer 1

试试这个：

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

试试这个：

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

我想这应该是技巧：-你知道吗

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

它更简单，更有效。你知道吗