为何SQLAlchemy返回新行号却未向MySQL插入数据？

问题背景 #

代码实现 #

def insert_book(name):
   with engine.connect() as connection:
        insert_statement = sqlalchemy.text(
            """
            INSERT INTO books (title_id) 
            SELECT 
                title_id
            FROM titles
            WHERE name = :name;
            """
        ).bindparams(name=name)

        result = connection.execute(insert_statement)
       
        return result.lastrowid

数据库表结构 #

CREATE TABLE `titles` (
  `title_id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(36),
  PRIMARY KEY (`title_id`),
  UNIQUE KEY `name` (`name`)
);

CREATE TABLE `books` (
  `book_id` int NOT NULL AUTO_INCREMENT,
  `title_id` int DEFAULT NULL,
  PRIMARY KEY (`book_id`),
  KEY `client_id` (`title_id`),
  CONSTRAINT `titles_fk` FOREIGN KEY (`title_id`) REFERENCES `titles` (`title_id`)
);

异常现象 #

• 调用insert_book并传入titles表中存在的name值时，result.lastrowid返回新的自增整数值，重复执行时数值正常递增
• books表中始终无新数据行插入
• 云MySQL实例日志报错：

终止连接... 数据库：'...' 用户：'...' 主机：'...' (读取通信数据包时出错)

依赖版本 #

• Python 3.9.13
• Flask==2.2.2
• PyMySQL==1.1.0
• SQLAlchemy==2.0.19

原因分析 #

1. 事务未提交：SQLAlchemy 2.x版本中，engine.connect()创建的连接默认关闭自动提交，所有操作都处于未提交的事务中。当with代码块执行完毕，连接被回收时，事务会自动回滚，导致插入操作被撤销。
2. 自增ID的特性：MySQL的自增计数器是预分配机制，即使事务回滚，计数器也不会回退，因此lastrowid会返回递增的数值，但实际数据并未持久化到表中。
3. 通信数据包错误：事务未提交导致连接回收时出现异常，触发了日志中的报错，这是事务回滚过程中连接异常的表现形式。

修复方案 #

方案1：手动提交事务 #

在执行插入操作后显式提交事务，确保数据持久化：

def insert_book(name):
   with engine.connect() as connection:
        insert_statement = sqlalchemy.text(
            """
            INSERT INTO books (title_id) 
            SELECT 
                title_id
            FROM titles
            WHERE name = :name;
            """
        ).bindparams(name=name)

        result = connection.execute(insert_statement)
        connection.commit()  # 新增事务提交操作
        return result.lastrowid

方案2：开启自动提交模式 #

若无需事务控制，可在创建连接时开启自动提交：

def insert_book(name):
   with engine.connect().execution_options(isolation_level="AUTOCOMMIT") as connection:
        insert_statement = sqlalchemy.text(
            """
            INSERT INTO books (title_id) 
            SELECT 
                title_id
            FROM titles
            WHERE name = :name;
            """
        ).bindparams(name=name)

        result = connection.execute(insert_statement)
        return result.lastrowid

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