SQLAlchemy 1.4.44报错：Connection对象无commit属性，如何解决？

SQLAlchemy 1.4中，engine.connect()默认处于隐式事务模式，此时返回的Connection对象并不直接提供commit()方法，这就是触发AttributeError的原因。官方文档提及的"commit-as-you-go"模式需要显式开启自动提交，或是通过事务对象完成提交操作。

有三种可行的修正方式：

方式1：显式管理事务 #

通过connection.begin()获取事务对象，操作完成后提交事务：

from sqlalchemy import create_engine, text

engine = create_engine("postgresql://user:password@connection_string:5432/database_name")
with engine.connect() as connection:
    with connection.begin() as transaction:
        sql = "create table test as (select count(1) as result from userquery);"
        result = connection.execute(text(sql))
        transaction.commit()

方式2：启用自动提交模式 #

在connect()时指定执行选项开启自动提交，此时Connection对象会拥有commit()方法，也可让每个执行语句自动提交：

from sqlalchemy import create_engine, text

engine = create_engine("postgresql://user:password@connection_string:5432/database_name")
with engine.connect(execution_options={"isolation_level": "AUTOCOMMIT"}) as connection:
    sql = "create table test as (select count(1) as result from userquery);"
    result = connection.execute(text(sql))
    connection.commit()  # 方法此时可用，也可省略依赖自动提交

方式3：简化事务管理 #

使用engine.begin()自动创建带事务的连接，退出with块时自动提交事务，无需手动调用commit：

from sqlalchemy import create_engine, text

engine = create_engine("postgresql://user:password@connection_string:5432/database_name")
with engine.begin() as connection:
    sql = "create table test as (select count(1) as result from userquery);"
    result = connection.execute(text(sql))
# 退出with块时自动完成提交

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