python sql 问题, 在sql里面用替代

Question

period = '573'
SHYA= sqldf('select PPMONTH, REGION, WEARSEG, sum(uncal_value) as uncal_value \
           from Ftable where region in ("Shanghai") and WEARSEG in ("Men","Women")\
           and PPMONTH in (period) group by PPMONTH,WEARSEG')
           
在sql里面用 in (period') 为什么没用？

PandaSQLException: (sqlite3.OperationalError) no such column: period [SQL: 'select PPMONTH, REGION, WEARSEG, sum(uncal_value) as uncal_value### 题目描述

题目来源及自己的思路

相关代码

// 请把代码文本粘贴到下方（请勿用图片代替代码）

你期待的结果是什么？实际看到的错误信息又是什么？

Answer 1

老铁, 报错中说:no such column: period意思是没有这一列,你是不是没有定义period呀？或者period是个字符串？你没用双引号？？或者你把数据贴出来, 我看下

Answer 2

SQL不能直接写period,要转成period对应的值字符串 'select PPMONTH, REGION, WEARSEG, sum(uncal_value) as uncal_value \ from Ftable where region in ("Shanghai") and WEARSEG in ("Men","Women")\ and PPMONTH in ("{}") group by PPMONTH,WEARSEG'.format(period)

Answer 3

为了防止SQL注入不推荐直接拼sql，需要走占位符的方式，如果你的period是一个字符串，那直接用等于不要用in，如果是个list那需要生成占位符 sql='select PPMONTH, REGION, WEARSEG, sum(uncal_value) as uncal_value \ from Ftable where region in ("Shanghai") and WEARSEG in ("Men","Women")\ and PPMONTH in ({}) group by PPMONTH,WEARSEG'.format(','.join(['%s' for _ in period])) cursor.execute(sql, period)