Python执行SQL查询遇OperationalError语法错误求助

尝试在Python程序中使用SQL语句查询SQLite数据库，但持续遇到语法错误，以下是运行的代码：

df3=pd.read_csv(r'path')

conn = sqlite3.connect("Any_Database_Name.db")

df3.to_sql('t1', conn)

sql_string ="""
    SELECT
        tipo,
        AVG(valor) AS avg_trim,
        ((MAX(valor) - MIN(valor)) / MIN(valor)) * 100 AS per_incr
    FROM t5
    WHERE tipo = 'debt'
      AND date >= DATE_SUB('2023-12-21', INTERVAL 2 YEAR)
    GROUP BY tipo, YEAR(date), QUARTER(date)
    HAVING YEAR(date) = YEAR('2023-12-21') OR YEAR((data) = YEAR('2022-12-21') - 1) 
    ORDER BY tipo, YEAR(date), QUARTER(date)
 """
df4 = pd.read_sql(sql_string, conn)

错误信息 #

初始错误 #

OperationalError                          Traceback (most recent call last)

~\Anaconda3\lib\site-packages\pandas\io\sql.py in execute(self, *args, **kwargs)
   2022         try:
-> 2023             cur.execute(*args, **kwargs)
   2024             return cur

OperationalError: near "2": syntax error

后续异常 #

DatabaseError                             Traceback (most recent call last)
~\AppData\Local\Temp\ipykernel_26132\1287340392.py in <module>
     22     ORDER BY tipo, YEAR(data), QUARTER(data)
     23  """
---> 24 df4 = pd.read_sql(sql_string, conn)
     25
     26 df4

 ~\Anaconda3\lib\site-packages\pandas\io\sql.py in read_sql(sql, con, index_col, coerce_float, params, parse_dates, columns, chunksize)
    565
    566     if isinstance(pandas_sql, SQLiteDatabase):
---> 567         return pandas_sql.read_query(
    568             sql,
    569             index_col=index_col,
 
~\Anaconda3\lib\site-packages\pandas\io\sql.py in read_query(self, sql, index_col, coerce_float, params, parse_dates, chunksize, dtype)
   2081
   2082         args = _convert_params(sql, params)
-> 2083         cursor = self.execute(*args)
   2084         columns = [col_desc[0] for col_desc in cursor.description]
   2085

~\Anaconda3\lib\site-packages\pandas\io\sql.py in execute(self, *args, **kwargs)
   2033
   2034             ex = DatabaseError(f"Execution failed on sql '{args[0]}': {exc}")
-> 2035             raise ex from exc
   2036
   2037     @staticmethod

DatabaseError: Execution failed on sql '
    SELECT
        tipo,
        AVG(valor) AS avg_trim,
        ((MAX(valor) - MIN(valor)) / MIN(valor)) * 100 AS per_incr
    FROM t7
    WHERE tipo = 'debt'
      AND data >= DATE_SUB('2023-12-21', INTERVAL 2 YEAR)
    GROUP BY tipo, YEAR(date), QUARTER(date)
    HAVING YEAR(date) = YEAR('2023-12-21') OR YEAR(data) = YEAR('2022-12-21') - 1
    AND QUARTER(date) = YEAR('22-12-21')
    ORDER BY tipo, YEAR(date), QUARTER(date)
': near "2": syntax error

错误根源是SQLite不支持MySQL风格的函数语法，同时SQL语句存在括号错误和字段名不一致问题，修正步骤如下：

• 替换SQLite不支持的函数

  • 用DATE('2023-12-21', '-2 years')替代DATE_SUB('2023-12-21', INTERVAL 2 YEAR)
  • 用strftime('%Y', date)替代YEAR(date)，用strftime('%q', date)替代QUARTER(date)

• 修正语法与逻辑错误

  • 移除HAVING子句中多余的括号，修正字段名混用问题（统一用date或data，需与数据表字段一致）
  • 删除QUARTER(date) = YEAR('22-12-21')的无效判断，该语句将季度与年份比较，逻辑错误

• 修正后的完整SQL语句

sql_string ="""
    SELECT
        tipo,
        AVG(valor) AS avg_trim,
        ((MAX(valor) - MIN(valor)) / MIN(valor)) * 100 AS per_incr
    FROM t5
    WHERE tipo = 'debt'
      AND date >= DATE('2023-12-21', '-2 years')
    GROUP BY tipo, strftime('%Y', date), strftime('%q', date)
    HAVING strftime('%Y', date) = strftime('%Y', '2023-12-21') 
       OR strftime('%Y', date) = (strftime('%Y', '2022-12-21') - 1)
    ORDER BY tipo, strftime('%Y', date), strftime('%q', date)
 """

• 额外检查项
  • 确认数据表名：代码中写入的是t1，但查询用的是t5/t7，需保证表名匹配
  • 确认date字段格式：需为SQLite可识别的YYYY-MM-DD格式

内容的提问来源于stack exchange，提问作者Gustavo C. Alberto