SQLAlchemy PostgreSQL引擎连接自动提交问题及显式提交需求

我遇到了SQLAlchemy引擎连接自动提交的问题，这不符合预期，我希望能显式执行提交操作。我的代码与官方教程完全一致，但即使未执行conn.commit()，数据依然被自动提交到数据库。

代码示例 #

from sqlalchemy import Table, Column, ForeignKey, Integer, String
from sqlalchemy.orm import relationship
from sqlalchemy.orm import registry
from sqlalchemy import create_engine
from sqlalchemy import ForeignKey, insert

engine = create_engine('postgresql+psycopg2://user:password@localhost:5432/test_db', echo=True)

mapper_registry = registry()
Base = mapper_registry.generate_base()

user_table = Table(
    "user_account",
    mapper_registry.metadata,
    Column("id", Integer, primary_key=True),
    Column("name", String(30)),
    Column("fullname", String),
)

mapper_registry.metadata.create_all(engine)

stmt = insert(user_table).values(name="spongebob", fullname="Spongebob Squarepants")
print(stmt)
    
with engine.connect() as conn:
    result = conn.execute(stmt)
    # conn.commit() 

自动提交的日志现象 #

即使注释掉conn.commit()，日志仍显示自动执行了COMMIT，且数据已存入数据库：

INSERT INTO user_account (name, fullname) VALUES (:name, :fullname)
2023-02-09 16:12:05,033 INFO sqlalchemy.engine.Engine INSERT INTO user_account (name, fullname) VALUES (%(name)s, %(fullname)s) RETURNING user_account.id
2023-02-09 16:12:05,034 INFO sqlalchemy.engine.Engine [generated in 0.00123s] {'name': 'spongebob', 'fullname': 'Spongebob Squarepants'}
2023-02-09 16:12:05,037 INFO sqlalchemy.engine.Engine COMMIT

尝试过的无效操作 #

• 设置conn.execution_options(isolation_level="SERIALIZABLE")
• 在create_engine后调用.execution_options(isolation_level="SERIALIZABLE")

设置isolation_level="AUTOCOMMIT"时日志有变化，说明参数被正确识别，但这不是我需要的显式提交行为：

INSERT INTO user_account (name, fullname) VALUES (:name, :fullname)
2023-02-09 16:17:35,889 INFO sqlalchemy.engine.Engine INSERT INTO user_account (name, fullname) VALUES (%(name)s, %(fullname)s) RETURNING user_account.id
2023-02-09 16:17:35,890 INFO sqlalchemy.engine.Engine [generated in 0.00132s] {'name': 'spongebob', 'fullname': 'Spongebob Squarepants'}
2023-02-09 16:17:35,893 INFO sqlalchemy.engine.Engine COMMIT using DBAPI connection.commit(), DBAPI should ignore due to autocommit mode

当前使用的SQLAlchemy版本：1.4.46

重要更新 #

添加future=True参数到create_engine后，行为符合预期：会隐式开启事务，未调用commit()时最终自动回滚，数据不会存入数据库。
修改后的引擎初始化代码：

engine = create_engine('...url', echo=True, future=True)

对应的日志：

INSERT INTO user_account (name, fullname) VALUES (:name, :fullname)
2023-02-09 17:42:08,499 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-02-09 17:42:08,499 INFO sqlalchemy.engine.Engine INSERT INTO user_account (name, fullname) VALUES (%(name)s, %(fullname)s) RETURNING user_account.id
2023-02-09 17:42:08,500 INFO sqlalchemy.engine.Engine [generated in 0.00085s] {'name': 'spongebob', 'fullname': 'Spongebob Squarepants'}
<sqlalchemy.engine.cursor.CursorResult object at 0x7fef30f76ac0>
2023-02-09 17:42:10,511 INFO sqlalchemy.engine.Engine ROLLBACK

后续疑问 #

这基本解答了问题，但我仍想了解：是否能在不开启future=True的前提下实现相同的显式提交行为？因为当前项目无法启用该参数。

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