使用 SQLAlchemy 2+ 控制自动提交行为的最佳方法是什么？-解网

问：

我们有一个泛型类，用于在 SQL Alchemy 中打开数据库连接。然后，引擎将返回到调用代码。泛型类如下。这个问题已经简化了。

class Database(db_name, autocommit=True):
    get_parameters()
    connection_string = (conf.connection_string + f"?autocommit={str(autocommit).lower()}")
    engine = create_engine(connection_string, echo=conf.echo)
    return engine

这将生成一个类似于以下内容的连接字符串mysql+pymysql://<dummy_username>:<dummy_password>@<db_server>:3306/<db_schema>?autocommit=true

然后，调用代码调用此函数来检索引擎，并使用引擎创建会话等，如下面的代码所示

engine = Database(Databases.GUDRUN, autocommit=False)
session = session_maker(bind=engine.engine)
with session() as session:
    try:
        code here
        session.commit()
    except Exception as e:
        session.rollback()

这奏效了，并允许我们默认将自动提交值设置为 True，但如果您需要包含特定用例的事务，则允许用户覆盖自动提交值。

但是，现在在使用 SQL Alchemy 2.0.23 时，我们注意到设置 autocommit=False 不再有效。无论传递到 Database 类中的连接字符串的自动提交值如何，数据库都会在输入时提交每个命令，而不是等待函数。session.commit()

事实证明，如果我们从连接字符串中完全排除（即然后，连接将等待提交前。autocommitmysql+pymysql://<dummy_username>:<dummy_password>@<db_server>:3306/<db_schema>session.commit()

我可以看到连接字符串中的选项已被弃用，因此我理解为什么它不再起作用。但看起来如果我在连接字符串中包含任何内容，无论值如何，if 的行为就好像自动提交已设置为 true。?autocommitautocommit

但是现在处理自动提交的最佳方法是什么？我们希望它设置为默认选项为 autocommit=True，并且开发人员必须有意识地决定在代码中有一个事务以避免 autocommit = true。

MySQL SQLALCHEMY 自动提交

答：

1赞 Gord Thompson 11/18/2023 #1

我们希望它设置为默认选项为 autocommit=True，并且开发人员必须有意识地决定在代码中进行事务

在这种情况下，请指定何时创建引擎，然后在要绕过自动提交时使用：isolation_level="AUTOCOMMIT".execution_options()

import sqlalchemy as sa

engine = sa.create_engine(
    "mysql+pymysql://scott:tiger@localhost:3307/mydb",
    isolation_level="AUTOCOMMIT",
    echo=True,
)

tbl = sa.Table("so77502001", sa.MetaData(), autoload_with=engine)

# typical usage: `with engine.begin()`
with engine.begin() as conn:
    conn.execute(sa.insert(tbl), {"txt": "autocommit me"})
"""
BEGIN (implicit; DBAPI should not BEGIN due to autocommit mode)
INSERT INTO so77502001 (txt) VALUES (%(txt)s)
[generated in 0.00042s] {'txt': 'autocommit me'}
COMMIT using DBAPI connection.commit(), DBAPI should ignore due to autocommit mode
"""

# we get the same results using `with engine.connect()`
with engine.connect() as conn:
    conn.execute(sa.insert(tbl), {"txt": "autocommit me, too"})
"""
BEGIN (implicit; DBAPI should not BEGIN due to autocommit mode)
INSERT INTO so77502001 (txt) VALUES (%(txt)s)
[cached since 0.09482s ago] {'txt': 'autocommit me, too'}
ROLLBACK using DBAPI connection.rollback(), DBAPI should ignore due to autocommit mode
"""

# "turn off" autocommit for this action
with engine.connect() as conn, conn.execution_options(
    isolation_level="READ COMMITTED"
):
    conn.execute(sa.insert(tbl), {"txt": "explicitly commit me"})
    conn.commit()
"""
BEGIN (implicit)
INSERT INTO so77502001 (txt) VALUES (%(txt)s)
[cached since 0.1856s ago] {'txt': 'explicitly commit me'}
COMMIT
"""

# same thing but with rollback
with engine.connect() as conn, conn.execution_options(
    isolation_level="READ COMMITTED"
):
    conn.execute(sa.insert(tbl), {"txt": "don't commit me"})
    conn.rollback()
"""
BEGIN (implicit)
INSERT INTO so77502001 (txt) VALUES (%(txt)s)
[cached since 0.2973s ago] {'txt': "don't commit me"}
ROLLBACK
"""

我们可以用：Session

with Session(engine) as sess:
    sess.add(Thing(txt="autocommit me"))
    sess.flush()
"""
BEGIN (implicit; DBAPI should not BEGIN due to autocommit mode)
INSERT INTO so77502001 (txt) VALUES (%(txt)s)
[generated in 0.00046s] {'txt': 'autocommit me'}
ROLLBACK using DBAPI connection.rollback(), DBAPI should ignore due to autocommit mode
"""

with Session(engine) as sess, sess.connection(
    execution_options={"isolation_level": "READ COMMITTED"}
):
    sess.add(Thing(txt="commit required"))
    sess.commit()  # automatically flushes
"""
BEGIN (implicit)
INSERT INTO so77502001 (txt) VALUES (%(txt)s)
[cached since 0.09807s ago] {'txt': 'commit required'}
COMMIT
"""

with Session(engine) as sess, sess.connection(
    execution_options={"isolation_level": "READ COMMITTED"}
):
    sess.add(Thing(txt="no commit -> not persisted"))
    sess.flush()
"""
BEGIN (implicit)
INSERT INTO so77502001 (txt) VALUES (%(txt)s)
[cached since 0.1569s ago] {'txt': 'no commit -> not persisted'}
ROLLBACK
"""

上一个：如何设置事务隔离级别（MySQL）

下一个：即使MySQL重新启动，如何将“event_scheduler”保持为“ON”？

使用 SQLAlchemy 2+ 控制自动提交行为的最佳方法是什么？

What is the best way to control autocommit behaviour with SQLAlchemy 2+?

评论