【SQLAlchemy】第四篇——事务

本篇文章中用到的sqlalchemy的版本为： 1.4.41

可以把事务理解为一系列操作的集合：这些操作要么全部执行，要么一个也不执行——这样就可以保证数据的一致性和可靠性。在执行更新和删除操作时，尤其要注意利用事务的这个特征。

SQLAlchemy中提供了许多方法来利用事务。

1、如何确保操作生效以及为操作“上保险”？

已经知道，从Engine生成一个Connection对象并执行一些操作的方式如下：

from sqlalchemy import create_engine, text
engine = create_engine("sqlite+pysqlite:///:memory:", echo=True, future=True)
# 存储在内存中的sqlite数据库，这样可以无须启动任何服务，也不会产生任何文件。

with engine.connect() as conn:
    result = conn.execute(text("select 1+1;"))

由于设置了打印执行日志（echo=True），可以在python console中看到整个执行过程：

2023-06-01 16:03:30,189 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-06-01 16:03:30,190 INFO sqlalchemy.engine.Engine select 1+1;
2023-06-01 16:03:30,190 INFO sqlalchemy.engine.Engine [generated in 0.00025s] ()
2023-06-01 16:03:30,190 INFO sqlalchemy.engine.Engine ROLLBACK

指定future=True的作用是利用SQLAlchemy 2.0的特性。如果未指定（默认），那么调用.connect()方法是会自动提交的。这点需要尤其注意。

实际上，上述语句的执行过程是：先开启了一个事务，然后执行对应的操作，最后再回滚。也就是说，通过engine.connect()获取的连接对象，在执行完操作后默认是将事务回滚的，从而不对数据库的数据产生任何影响。因此，如果用户想要执行增删改等操作，那么必须显示地进行提交：

with engine.connect() as conn:
    conn.execute(text("CREATE TABLE some_table (x int primary key, y int)"))
    conn.execute(
        text("INSERT INTO some_table (x, y) VALUES (:x, :y)"),
        [{"x": 1, "y": 1}, {"x": 2, "y": 4}],
    )
    conn.commit()  # 必须手动提交，否则更改不会生效

利用上述特性，将所有需要进行的操作放到一起，最后提交，就可以保证要么全部生效，要么都不生效：

with engine.connect() as conn:
    conn.execute(text("delete from some_table where x=1"))
    conn.execute(text("insert into some_table (x, y) values (2, 3)"))
    # 插入操作由于主键冲突无法执行，那么上一条删除操作也不会生效
    conn.commit()

显示地提交才会生效并不会影响到表的创建。也就是说，如果在with语句中执行的是CREATE TABLE语句时，不需要显示提交也可以建表成功。甚至在建表语句后跟一个无法成功执行的写入语句，在回滚时也不会把建表操作撤销：

with engine.connect() as conn:
   conn.execute(
       text("CREATE TABLE some_table10 (x int primary key, y int);")
   )
   # 由于主键冲突，下面的插入语句不会成功
   conn.execute(
       text("INSERT INTO some_table (x, y) VALUES (:x, :y)"),
       [{"x": 1, "y": 1}, {"x": 2, "y": 4}],
   )
   conn.commit()  

尽管最后的提交会因为主键限制触发回滚，但第一条建表语句却被成功执行了。

利用try...except...，可以进一步对上述语句进行包装：

with engine.connect() as conn:
    try:
        conn.execute(text("delete from some_table where x=1"))
        conn.execute(text("insert into some_table (x, y) values (2, 3)"))
        conn.commit()
        print("成功")
    except:
        conn.rollback()
        print("失败，回滚")

2、通过engine.begin()来封装为事务

通过engine.begin()也可以获得一个连接对象，所不同的是，这样获得的连接对象在执行操作后，会自动进行提交。如果执行过程中出错，则会自动回滚：

with engine.begin() as conn:
    conn.execute(text("delete from some_table where x=1"))
    conn.execute(text("insert into some_table (x, y) values (2, 3)"))

可以在console中看到如下信息：

2023-06-01 17:38:06,526 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-06-01 17:38:06,527 INFO sqlalchemy.engine.Engine delete from some_table where x=1
2023-06-01 17:38:06,527 INFO sqlalchemy.engine.Engine [cached since 1692s ago] ()
2023-06-01 17:38:06,527 INFO sqlalchemy.engine.Engine insert into some_table (x, y) values (2, 3)
2023-06-01 17:38:06,528 INFO sqlalchemy.engine.Engine [cached since 1692s ago] ()
2023-06-01 17:38:06,528 INFO sqlalchemy.engine.Engine ROLLBACK
[错误信息]

最后的ROLLBACK表明，语句执行过程中出现了错误。

类似地，利用try...except...，可以进一步捕获错误：

try:
    with engine.begin() as conn:
        conn.execute(text("delete from some_table where x=1"))
        conn.execute(text("insert into some_table (x, y) values (2, 3)"))
except Exception as e:
    print(f"出错，回滚, {e}")