Python执行SQL查询时比较运算符附近报语法错误如何解决？

你遇到的语法错误来自三个核心问题：

• SQL字符串拼接时缺少必要空格，相邻的SQL片段拼接后会变成非法连续字符串，比如原写法中FROM comparing_price"后面直接接"WHERE DATE>，拼接后变为FROM comparing_priceWHERE，完全不符合SQL语法规范
• 数值比较的右侧值29不需要加单引号，加引号会导致隐式类型转换，甚至触发语法错误
• OVER子句中ORDER BY DATE, DATE属于重复排序字段，无意义且可能引发部分数据库的语法兼容问题
• 参数化查询仅支持传递查询值，不支持直接传递运算符、表名、字段名这类SQL语法元素，直接用%s传运算符会被转义成字符串，导致语法错误

使用Python三引号定义多行SQL，无需手动处理转义和空格，数值类型参数直接传整数即可：

# 三引号自动处理多行SQL的空格和换行
sql = """
WITH comparing_price AS (
    SELECT CODE, DATE, OPEN, high, low, close, volume,
        LEAD(OPEN, 1) OVER (PARTITION BY CODE ORDER BY DATE) AS d1_open,
        LEAD(OPEN, 2) OVER (PARTITION BY CODE ORDER BY DATE) AS d2_open,
        LEAD(OPEN, 3) OVER (PARTITION BY CODE ORDER BY DATE) AS d3_open,
        LEAD(high, 1) OVER (PARTITION BY CODE ORDER BY DATE) AS d1_high,
        LEAD(high, 2) OVER (PARTITION BY CODE ORDER BY DATE) AS d2_high,
        LEAD(high, 3) OVER (PARTITION BY CODE ORDER BY DATE) AS d3_high,
        LEAD(high, 4) OVER (PARTITION BY CODE ORDER BY DATE) AS d4_high,
        LEAD(low, 1) OVER (PARTITION BY CODE ORDER BY DATE) AS d1_low,
        LEAD(low, 2) OVER (PARTITION BY CODE ORDER BY DATE) AS d2_low,
        LEAD(low, 3) OVER (PARTITION BY CODE ORDER BY DATE) AS d3_low,
        LEAD(close, 1) OVER (PARTITION BY CODE ORDER BY DATE) AS d1_close,
        LEAD(close, 2) OVER (PARTITION BY CODE ORDER BY DATE) AS d2_close,
        LEAD(close, 3) OVER (PARTITION BY CODE ORDER BY DATE) AS d3_close,
        LEAD(volume, 1) OVER (PARTITION BY CODE ORDER BY DATE) AS d1_volume,
        LEAD(volume, 2) OVER (PARTITION BY CODE ORDER BY DATE) AS d2_volume,
        AVG(close) OVER (PARTITION BY CODE ORDER BY DATE ROWS BETWEEN 2 preceding AND 2 following) AS d2_MA5,
        AVG(close) OVER (PARTITION BY CODE ORDER BY DATE ROWS BETWEEN 7 preceding AND 2 following) AS d2_MA10,
        AVG(close) OVER (PARTITION BY CODE ORDER BY DATE ROWS BETWEEN 17 preceding AND 2 following) AS d2_MA20,
        AVG(close) OVER (PARTITION BY CODE ORDER BY DATE ROWS BETWEEN 57 preceding AND 2 following) AS d2_MA60,
        AVG(close) OVER (PARTITION BY CODE ORDER BY DATE ROWS BETWEEN 117 preceding AND 2 following) AS d2_MA120,
        STD(close) OVER (PARTITION BY CODE ORDER BY DATE ROWS BETWEEN 17 preceding AND 2 following) AS d2_std
    FROM daily_price
)
SELECT * 
FROM comparing_price
WHERE DATE > %s AND 100*(d1_close-close)/close > %s
"""
# 数值参数直接传int类型，无需转成字符串
conditions = ('2021-06-01', 29)
curs.execute(sql, conditions)

因为参数化查询不能直接传语法元素，所以需要先做白名单校验，再把运算符拼接到SQL中，避免SQL注入风险：

# 定义允许使用的比较运算符白名单，禁止传入白名单外的内容
ALLOWED_OPERATORS = ['>', '<', '=', '>=', '<=', '!=']

# 假设从业务逻辑获取动态运算符
date_compare_op = '>'
value_compare_op = '>'

# 先校验运算符合法性
if date_compare_op not in ALLOWED_OPERATORS or value_compare_op not in ALLOWED_OPERATORS:
    raise ValueError("非法的比较运算符")

# 把校验通过的运算符拼入SQL模板
sql = f"""
WITH comparing_price AS (
    SELECT CODE, DATE, OPEN, high, low, close, volume,
        LEAD(OPEN, 1) OVER (PARTITION BY CODE ORDER BY DATE) AS d1_open,
        LEAD(OPEN, 2) OVER (PARTITION BY CODE ORDER BY DATE) AS d2_open,
        LEAD(OPEN, 3) OVER (PARTITION BY CODE ORDER BY DATE) AS d3_open,
        LEAD(high, 1) OVER (PARTITION BY CODE ORDER BY DATE) AS d1_high,
        LEAD(high, 2) OVER (PARTITION BY CODE ORDER BY DATE) AS d2_high,
        LEAD(high, 3) OVER (PARTITION BY CODE ORDER BY DATE) AS d3_high,
        LEAD(high, 4) OVER (PARTITION BY CODE ORDER BY DATE) AS d4_high,
        LEAD(low, 1) OVER (PARTITION BY CODE ORDER BY DATE) AS d1_low,
        LEAD(low, 2) OVER (PARTITION BY CODE ORDER BY DATE) AS d2_low,
        LEAD(low, 3) OVER (PARTITION BY CODE ORDER BY DATE) AS d3_low,
        LEAD(close, 1) OVER (PARTITION BY CODE ORDER BY DATE) AS d1_close,
        LEAD(close, 2) OVER (PARTITION BY CODE ORDER BY DATE) AS d2_close,
        LEAD(close, 3) OVER (PARTITION BY CODE ORDER BY DATE) AS d3_close,
        LEAD(volume, 1) OVER (PARTITION BY CODE ORDER BY DATE) AS d1_volume,
        LEAD(volume, 2) OVER (PARTITION BY CODE ORDER BY DATE) AS d2_volume,
        AVG(close) OVER (PARTITION BY CODE ORDER BY DATE ROWS BETWEEN 2 preceding AND 2 following) AS d2_MA5,
        AVG(close) OVER (PARTITION BY CODE ORDER BY DATE ROWS BETWEEN 7 preceding AND 2 following) AS d2_MA10,
        AVG(close) OVER (PARTITION BY CODE ORDER BY DATE ROWS BETWEEN 17 preceding AND 2 following) AS d2_MA20,
        AVG(close) OVER (PARTITION BY CODE ORDER BY DATE ROWS BETWEEN 57 preceding AND 2 following) AS d2_MA60,
        AVG(close) OVER (PARTITION BY CODE ORDER BY DATE ROWS BETWEEN 117 preceding AND 2 following) AS d2_MA120,
        STD(close) OVER (PARTITION BY CODE ORDER BY DATE ROWS BETWEEN 17 preceding AND 2 following) AS d2_std
    FROM daily_price
)
SELECT * 
FROM comparing_price
WHERE DATE {date_compare_op} %s AND 100*(d1_close-close)/close {value_compare_op} %s
"""

conditions = ('2021-06-01', 29)
curs.execute(sql, conditions)

内容的提问来源于stack exchange，提问作者koguma