python - AWS Lambda：如何将 RDS Aurora 查询作为原子事务执行？

Question

我在 AWS Lambda 函数中有以下 Python 代码，我希望 RDS Aurora DB 上的查询以原子方式运行，即全部运行或不运行。该conn.commit()声明会为我这样做吗？如果不是我怎么能做到这一点？conn是我的数据库连接对象。

# Connect to the DB
conn = pymysql.connect(rds_host, user=username,
                       passwd=password, db=db_name,
                       connect_timeout=10)
# Run queries
with conn.cursor() as cur:
    cur.execute("create table some_table_2020 like some_table;")
    cur.execute("insert into some_table_2020 select * from some_table;")
    cur.execute("rename table some_table to some_table_20200629;")
    cur.execute("rename table some_table_2020 to some_table;")
    conn.commit()

标签： pythonaws-lambda

Answer 1

感谢基尔尼的评论，这里是答案，每使用提交和回滚来管理 Python 中的 MySQL 事务

# Connect to the DB
conn = pymysql.connect(rds_host, user=username,
                       passwd=password, db=db_name,
                       connect_timeout=10)    
try:
    # Run queries
    with conn.cursor() as cur:
        cur.execute("create table some_table_2020 like some_table;")
        cur.execute("insert into some_table_2020 select * from some_table;")
        cur.execute("rename table some_table to some_table_20200629;")
        cur.execute("rename table some_table_2020 to some_table;")
    conn.commit()
exception:
    # Failed to commit changes in the DB, do rollback
    conn.rollback()
finally:
    #closing database connection.
    if(conn.is_connected()):
        cursor.close()
        conn.close()