Python连接MySQL执行SQL返回错误结果，与直接执行结果不符

现象 #

我用以下Python代码执行MySQL多语句查询：

import mysql.connector

try:
    conn = mysql.connector.connect(
        host="localhost", user="fanw", password="secret", database="fanw"
    )

    cursor = conn.cursor()
    query = """
        CREATE TEMPORARY TABLE IF NOT EXISTS temp_pairs AS

        SELECT
            fd.nid,
            fd.title
        FROM
            node__field_card_display cl
        JOIN 
            node_field_data fd ON cl.entity_id = fd.nid AND cl.langcode = fd.langcode
        WHERE
            cl.field_card_display_target_id = 2049
            AND cl.langcode = 'en'
            AND cl.bundle = 'person';

        SELECT
            t.entity_id,
            t.deleted,
            t.delta,
            t.langcode,
            t.body_value,
            GROUP_CONCAT(DISTINCT tp.nid SEPARATOR ',') AS nids,
            GROUP_CONCAT(DISTINCT tp.title SEPARATOR ',') AS titles
        FROM
            node__body AS t
        JOIN
            temp_pairs AS tp ON BINARY t.body_value LIKE CONCAT('%', tp.title, '%') 
            AND NOT (t.body_value LIKE CONCAT('%', tp.nid, '">', tp.title, '<', '%'))
        WHERE
            t.body_format <> 'plain_text'
            AND t.entity_id <> tp.nid
        GROUP BY
            t.entity_id, t.langcode;
    """
    cursor.execute(query, multi=True)
    result = cursor.fetchall()
    print(len(result))
    conn.commit()

except Exception as e:
    # Handle any errors and potentially roll back the transaction
    conn.rollback()
    print(f"Error: {str(e)}")

finally:
    cursor.close()
    conn.close()

print("DONE!")

执行后输出结果为 0，但直接在MySQL客户端执行相同SQL，能返回有效数据51条（含表头共52行）：

$ cat python_query.sql
CREATE TEMPORARY TABLE IF NOT EXISTS temp_pairs AS

SELECT
    fd.nid,
    fd.title
FROM
    node__field_card_display cl
JOIN 
    node_field_data fd ON cl.entity_id = fd.nid AND cl.langcode = fd.langcode
WHERE
    cl.field_card_display_target_id = 2049
    AND cl.langcode = 'en'
    AND cl.bundle = 'person';

SELECT
    t.entity_id,
    t.deleted,
    t.delta,
    t.langcode,
    t.body_value,
    GROUP_CONCAT(DISTINCT tp.nid SEPARATOR ',') AS nids,
    GROUP_CONCAT(DISTINCT tp.title SEPARATOR ',') AS titles
FROM
    node__body AS t
JOIN
    temp_pairs AS tp ON BINARY t.body_value LIKE CONCAT('%', tp.title, '%') 
    AND NOT (t.body_value LIKE CONCAT('%', tp.nid, '">', tp.title, '<', '%'))
WHERE
    t.body_format <> 'plain_text'
    AND t.entity_id <> tp.nid
GROUP BY
    t.entity_id, t.langcode;

$ mysql -u fanw -p fanw < python_query.sql | wc -l
Enter password:
52

第一行是列名，所以有效数据是51条。

环境信息 #

• Python版本：3.9.10
• 系统：MacBook Pro M1（macOS 12.7）
• 依赖库：

  $ pip freeze
  mysql-connector-python==8.3.0
  PyMySQL==1.1.0
  

尝试用PyMySQL执行，结果同样是0；用DBeaver执行该SQL能得到正确的51条结果。

请问哪里操作出错了？

解答 #

问题出在多语句执行时的结果处理逻辑上。当使用multi=True执行多条SQL语句时，cursor.fetchall()只会获取当前游标指向的第一个结果集——而你的第一条语句是CREATE TEMPORARY TABLE，它的结果集是空的，所以result自然是空列表，长度为0。

正确的处理方式是遍历所有结果集，跳过无数据的结果集，只获取目标SELECT语句的结果：

修改代码中执行查询和获取结果的部分：

cursor.execute(query, multi=True)
# 遍历所有结果集，筛选出带数据的结果
result = []
for res in cursor:
    if res.with_rows:  # 检查当前结果集是否包含数据行
        result = res.fetchall()
print(len(result))

额外补充两个注意点：

1. CREATE TEMPORARY TABLE语句不需要commit，临时表在当前数据库会话内有效，提交事务不影响它的存在；
2. 若排查问题，可以在遍历结果集时打印每个结果集的状态，确认临时表创建是否成功。

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