如何用Python打印SQL存储过程执行后的每条记录？

现有Python代码调用SQL存储过程，目前仅能打印更新的记录数。尝试通过cur.fetchall()生成DataFrame打印单条记录，但结果为空。该存储过程用于对比两日table_snapshot表数据，将差异记录插入table1，希望实现打印每条完整记录。

原始代码 #

def exceptions_function(self):
    exceptions_function_EXCEPTIONS_QUERY = "CALL sp_exceptions_function_Exceptions();"
    vfcaa = self.config["verafin-exceptions"]["functions"]["exceptions_function"]
    if vfcaa:
        with self.DB_connection() as cnxn:
            with cnxn.cursor() as cur:
                try:
                    cur.execute(exceptions_function_EXCEPTIONS_QUERY)
                    print("exceptions_function exceptions query ran successfully, {} exceptions updated.".format(cur.rowcount))

                except pm.Error as e:
                    print(f"Error: {e}")

                except Exception as e:
                    logging.exception(e)

                else:
                    cnxn.commit()

尝试添加的无效果代码：

data=DataFrame(cur.fetchall())
for row in data:
    print(row)

更新后的代码：

def run_all(self):
    def function_name(self):
        function_name_EXCEPTIONS_QUERY = "CALL sp_function_name_Exceptions();"
        vfcaa = self.config["verafin-exceptions"]["functions"]["function_name"]
        cnxn=self.DB_connection()
        cur=cnxn.cursor()            
        if vfcaa:
            try:
                cur.execute(function_name_EXCEPTIONS_QUERY)
                print("function_name exceptions query ran successfully, {} exceptions updated.".format(cur.rowcount))
                data=cur.fetchall()
                print(data)
            except pm.Error as e:
                print(f"Error: {e}")

            except Exception as e:
                logging.exception(e)

            else:
                cnxn.commit()
    function_name(self)

存储过程代码：

INSERT INTO table1 (
`updated`,
`inst_num`,
`brnc_num`,
`cust_num`,
`snp_date`
) 
SELECT 
table1.`updated`,
table1.`inst_num`,
table1.`brnc_num`,
table1.`cust_num`,
table1.`snp_date`
FROM table_snapshot table1
LEFT JOIN table_snapshot table2 
    ON table1.`cust_num` = table2.`cust_num`
    AND table2.`snp_date` = CURDATE()
WHERE table1.`snp_date` = CURDATE()- INTERVAL 1 DAY
AND table2.`cust_num` IS NULL;

问题核心是：你的存储过程仅执行了INSERT操作，没有返回任何结果集。cur.fetchall()只能获取存储过程主动返回的数据集，单纯的INSERT不会自动输出插入的行数据。

以下两种方法可实现打印每条完整记录：

方法一：修改存储过程，返回插入的记录 #

在存储过程的INSERT语句后，添加SELECT逻辑返回刚插入的记录。如果你的数据库支持（如MySQL 8.0.20+、PostgreSQL），可以直接用INSERT ... RETURNING语法：

INSERT INTO table1 (
`updated`,
`inst_num`,
`brnc_num`,
`cust_num`,
`snp_date`
) 
SELECT 
table1.`updated`,
table1.`inst_num`,
table1.`brnc_num`,
table1.`cust_num`,
table1.`snp_date`
FROM table_snapshot table1
LEFT JOIN table_snapshot table2 
    ON table1.`cust_num` = table2.`cust_num`
    AND table2.`snp_date` = CURDATE()
WHERE table1.`snp_date` = CURDATE()- INTERVAL 1 DAY
AND table2.`cust_num` IS NULL
RETURNING *;

如果数据库不支持RETURNING，可以在INSERT后追加查询逻辑：

-- 原INSERT语句保持不变
INSERT INTO table1 (...) SELECT ...;

-- 查询并返回插入的记录
SELECT * FROM table1 
WHERE snp_date = CURDATE()- INTERVAL 1 DAY 
AND cust_num IN (
    SELECT cust_num FROM table_snapshot table1
    LEFT JOIN table_snapshot table2 
        ON table1.`cust_num` = table2.`cust_num`
        AND table2.`snp_date` = CURDATE()
    WHERE table1.`snp_date` = CURDATE()- INTERVAL 1 DAY
    AND table2.`cust_num` IS NULL
);

修改后，Python中调用cur.fetchall()就能获取到插入的记录，直接遍历或生成DataFrame即可。

方法二：Python代码中追加查询逻辑 #

如果无法修改存储过程，可在执行完存储过程后，单独执行一条SELECT语句查询table1中最新插入的记录：

def run_all(self):
    def function_name(self):
        function_name_EXCEPTIONS_QUERY = "CALL sp_function_name_Exceptions();"
        # 新增查询插入记录的SQL
        SELECT_INSERTED_SQL = """
            SELECT * FROM table1 
            WHERE snp_date = CURDATE()- INTERVAL 1 DAY 
            AND cust_num IN (
                SELECT cust_num FROM table_snapshot table1
                LEFT JOIN table_snapshot table2 
                    ON table1.`cust_num` = table2.`cust_num`
                    AND table2.`snp_date` = CURDATE()
                WHERE table1.`snp_date` = CURDATE()- INTERVAL 1 DAY
                AND table2.`cust_num` IS NULL
            );
        """
        vfcaa = self.config["verafin-exceptions"]["functions"]["function_name"]
        cnxn=self.DB_connection()
        cur=cnxn.cursor()            
        if vfcaa:
            try:
                cur.execute(function_name_EXCEPTIONS_QUERY)
                print("function_name exceptions query ran successfully, {} exceptions updated.".format(cur.rowcount))
                # 执行查询获取插入的记录
                cur.execute(SELECT_INSERTED_SQL)
                data = cur.fetchall()
                # 打印每条记录
                for row in data:
                    print(row)
                # 可选：生成带表头的DataFrame
                df = pd.DataFrame(data, columns=[desc[0] for desc in cur.description])
                print(df)
            except pm.Error as e:
                print(f"Error: {e}")
            except Exception as e:
                logging.exception(e)
            else:
                cnxn.commit()
            finally:
                # 释放资源
                cur.close()
                cnxn.close()
    function_name(self)

注意：

• 利用cur.description可以获取列名，让DataFrame拥有正确的表头。
• 务必在finally块中关闭游标和连接，避免数据库资源泄漏。

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