Python调用MySQL查询报1064语法错误（Workbench可正常运行）求助

核心报错原因 #

你将SET和SELECT两条独立SQL语句合并到同一次查询请求中执行，Python的绝大多数MySQL驱动（比如pymysql、mysql-connector-python、mysqlclient）默认禁止单次执行多条SQL语句，而MySQL Workbench默认支持多语句批量执行，这就是两边执行结果不一致的根本原因。
报错提示中指向SELECT附近语法错误，就是因为驱动识别到分号后的第二条语句属于非法的单次查询内容。

可行解决方案 #

方案1（最推荐，无安全风险）：拆分语句分两次执行 #

把SQL拆成两个独立的请求，先执行sql_mode设置，再执行业务查询：

# 第一步：单独设置sql_mode
set_sql = "SET sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';"
cursor.execute(set_sql)

# 第二步：执行业务查询语句，移除原来的SET部分即可
query_sql = """
SELECT i.ID, i.post_title AS 'Title', pm1.meta_value AS 'Prix', pm2.meta_value AS 'Surface', pm4.meta_value AS 'Ville', 
GROUP_CONCAT(t.name) AS 'Category', pm5.meta_value AS 'annee_de_construction', pm6.meta_value AS 'Alarme', pm7.meta_value AS 'aspect', 
pm8.meta_value AS 'assainissement', pm9.meta_value AS 'Cave', pm10.meta_value AS 'certificat_assainissement', pm11.meta_value AS 'cuisine' 
FROM mod803_posts i 
LEFT JOIN mod803_postmeta pm1 ON pm1.post_id = i.ID AND pm1.meta_key = 'Prix de vente' 
LEFT JOIN mod803_postmeta pm2 ON pm2.post_id = i.ID AND pm2.meta_key = 'Surface' 
LEFT JOIN mod803_postmeta pm4 ON pm4.post_id = i.ID AND pm4.meta_key = 'Ville' 
LEFT JOIN mod803_postmeta pm5 ON pm5.post_id = i.ID AND pm5.meta_key = 'annee_de_construction' 
LEFT JOIN mod803_postmeta pm6 ON pm6.post_id = i.ID AND pm6.meta_key = 'Alarme' 
LEFT JOIN mod803_postmeta pm7 ON pm7.post_id = i.ID AND pm7.meta_key = 'aspect' 
LEFT JOIN mod803_postmeta pm8 ON pm8.post_id = i.ID AND pm8.meta_key = 'assainissement' 
LEFT JOIN mod803_postmeta pm9 ON pm9.post_id = i.ID AND pm9.meta_key = 'Cave(s)' 
LEFT JOIN mod803_postmeta pm10 ON pm10.post_id = i.ID AND pm10.meta_key = 'certificat_assainissement' 
LEFT JOIN mod803_postmeta pm11 ON pm11.post_id = i.ID AND pm11.meta_key = 'cuisine' 
LEFT JOIN mod803_term_relationships AS tr ON tr.object_id = i.ID 
LEFT JOIN mod803_term_taxonomy AS tt ON tr.term_taxonomy_id = tt.term_taxonomy_id AND tt.taxonomy = 'typedestock' 
LEFT JOIN mod803_terms AS t ON tt.term_id = t.term_id 
WHERE i.post_type in ('stock') 
AND i.post_status = 'publish' 
AND i.ID = %s 
ORDER BY i.ID ASC
"""
cursor.execute(query_sql, (你的ID参数,))

方案2（不推荐，存在SQL注入风险）：开启多语句执行支持 #

如果确实需要单次执行多条语句，可以在数据库连接时添加多语句支持的参数，以pymysql为例：

import pymysql
from pymysql.constants import CLIENT

conn = pymysql.connect(
    host='你的主机地址',
    user='用户名',
    password='密码',
    database='库名',
    client_flag=CLIENT.MULTI_STATEMENTS
)

额外检查项 #

可以把最终拼接完成的SQL语句打印出来，复制到MySQL Workbench中二次执行，确认没有字符串拼接导致的空格缺失、字符遗漏等低级语法问题。

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