undefinedfix
Sign in

Python SQL problem, replace it in SQL

user3511269 edited in Sat, 25 Jun 2022
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### 题目描述

The source of the topic and my own ideas

Related codes

// Please paste the code text below (do not use pictures instead of codes)

What are your expectations? What is the actual error message?

3 Replies
MenInTheMiddle
commented on Sat, 25 Jun 2022

Buddy , Report mistakes : no such column : Period means there is no such column , Don't you have a period? Or is period a string? You didn't use double quotes?? Or you post the data , Let me see

MrGorilla
commented on Sat, 25 Jun 2022

SQL can't write period directly. It should be converted to the value string corresponding to 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)
Bogdan
commented on Sun, 26 Jun 2022

In order to prevent SQL injection, it is not recommended to spell SQL directly. You need to use a placeholder. If your period is a string, using it directly means not using in. If it is a list, you need to generate a placeholder

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)
lock This question has been locked and the reply function has been disabled.