pymysql的sql查询语句有时能正常执行有时又异常什么

Question

下面的语句,涉及到两个表格： p_sale表格、collect_goods(通过format传递进去的)

语句执行逻辑如下：
1、筛选p_sale表格的日期psl_date

2、筛选p_sale表格的psl_pro_id(这个id要位于collect_goods中)

3、筛选p_sale表格中的psl_bill_no(这个no要在shift_bill_no中)
      备注：shift_bill_no的代码段放在下面

4、筛选p_sale表格中的psl_amt

5、把p_sale表格和collect_goods表格左联合起来

sql = "select a.psl_date,a.psl_bill_no,a.psl_pro_id,b.pro_name,a.psl_qty,a.psl_amt,b.commission " \
              "from p_sale a " \
              "left join {0} b on a.psl_pro_id=b.pro_id " \
              f"where psl_date=DATE_FORMAT('{query_date}','%Y-%m-%d') " \
              "and psl_pro_id in (select pro_id from {0}) " \
              "and psl_bill_no in {1} " \
              "and psl_amt>0"
cursor.execute(sql.format('collect_goods', shift_bill_no))
resp = cursor.fetchall()

这段语句有时执行正常，有时执行错误，错误有两种情况：
1、返回空的数据，但实际是有数据的
2、返回错误，错误提示如下：

Traceback (most recent call last):
  File "C:/Users/Administrator/Desktop/python文件/schedule_work.py", line 82, in <module>
    res = task.calculate_salary(text)
  File "C:/Users/Administrator/Desktop/python文件/schedule_work.py", line 47, in calculate_salary
    cursor.execute(sql.format('collect_goods', shift_bill_no))
  File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\pymysql\cursors.py", line 148, in execute
    result = self._query(query)
  File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\pymysql\cursors.py", line 310, in _query
    conn.query(q)
  File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\pymysql\connections.py", line 548, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\pymysql\connections.py", line 775, in _read_query_result
    result.read()
  File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\pymysql\connections.py", line 1156, in read
    first_packet = self.connection._read_packet()
  File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\pymysql\connections.py", line 725, in _read_packet
    packet.raise_for_error()
  File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\pymysql\protocol.py", line 221, in raise_for_error
    err.raise_mysql_exception(self._data)
  File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\pymysql\err.py", line 143, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') and psl_amt>0' at line 1")

Process finished with exit code 1

排查了好久，百思不得其解啊！！！

备注： shift_bill_no的代码段如下：

cursor.execute(f"select piv_bill_no from p_inv "
               f"where piv_date=DATE_FORMAT('{query_date}','%Y-%m-%d') and piv_emp like '%{shift}%'")
shift_bill_no = tuple([i[0] for i in cursor.fetchall()])

Answer 1

写代码，特别是用字符串拼凑SQL语句时，建议养成在关键字前后留空格的习惯。