PyMySQL - 回滚测试

原创已于 2022-08-25 10:52:45 修改 · 668 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #python #mysql

于 2022-08-23 13:16:13 首次发布

python 专栏收录该内容

3 篇文章

订阅专栏

本文探讨了Python中如何使用多个cursor在Connection上执行SQL操作，包括并发执行、异常处理与回滚，以及不同情况下的数据行为。重点展示了单个rollback对所有cursor的影响和跨函数的回滚规则。

cursor唯一？

结论：一个Connection可以创建多个cursor

def test():
    conn = connect()
    cursor = conn.cursor()
    print(cursor == conn.cursor())  # False

rollback demo1: 已提交数据

结果：插入成功
结论：已经提交的无法回滚

def test():
    conn = connect()
    cursor = conn.cursor()
    try:
        cursor.execute('insert into `pymysql_rollback_test`(field1, field2) values (%s, %s)', ['a1', 'a2'])
        conn.commit()  # 提交
        print(1 / 0)
    except Exception as e:
        print(e)  # division by zero
        conn.rollback()
    finally:
        cursor.close()
        conn.close()

rollback demo2: 代码异常 & 单cursor

插入两条数据，无异常

def test():
    conn = connect()
    cursor = conn.cursor()
    try:
        cursor.execute('insert into `pymysql_rollback_test`(field1, field2) values (%s, %s)', ['a1', 'a2'])
        cursor.execute('insert into `pymysql_rollback_test`(field1, field2) values (%s, %s)', ['b1', 'b2'])
    except Exception as e:
        print(e)
        conn.rollback()
    finally:
        conn.commit()
        cursor.close()
        conn.close()

查看数据库：

再插入两条数据，发生异常，触发了回滚

def test():
    conn = connect()
    cursor = conn.cursor()
    try:
        cursor.execute('insert into `pymysql_rollback_test`(field1, field2) values (%s, %s)', ['a1', 'a2'])
        print(1 / 0)
        cursor.execute('insert into `pymysql_rollback_test`(field1, field2) values (%s, %s)', ['b1', 'b2'])
    except Exception as e:
        print(e)  # division by zero
        conn.rollback()
    finally:
        conn.commit()
        cursor.close()
        conn.close()

查看数据库：

再无异常插入两条，此时id已经从4开始了

rollback demo3: SQL异常 & 多cursor

结果：触发回滚，未插入一条数据
结论：rollback()是对当前Connection中的所有的cursor进行回滚操作

def test():
    conn = connect()
    cursor1 = conn.cursor()
    cursor2 = conn.cursor()
    try:
        cursor1.execute('insert into `pymysql_rollback_test`(field1, field2) values (%s, %s)', ['c1-01', 'c1-02'])
        cursor2.execute('insert into `pymysql_rollback_test`(field1, field2) values (%s, %s)', ['c2-01', 'c2-02'])
        cursor1.execute('insert into1 `pymysql_rollback_test`(field1, field2) values (%s, %s)', ['c1-11', 'c1-12'])
    except Exception as e:
        print(e)  # (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '`pymysql_rollback_test`(field1, field2) values ('c1-11', 'c1-12')' at line 1")
        conn.rollback()
    finally:
        conn.commit()
        cursor1.close()
        cursor2.close()
        conn.close()

去除异常的SQL语句，再次插入两条数据：

def test():
    conn = connect()
    cursor1 = conn.cursor()
    cursor2 = conn.cursor()
    try:
        cursor1.execute('insert into `pymysql_rollback_test`(field1, field2) values (%s, %s)', ['c1-01', 'c1-02'])
        cursor2.execute('insert into `pymysql_rollback_test`(field1, field2) values (%s, %s)', ['c2-01', 'c2-02'])
        # cursor1.execute('insert into1 `pymysql_rollback_test`(field1, field2) values (%s, %s)', ['c1-11', 'c1-12'])
    except Exception as e:
        print(e)
        conn.rollback()
    finally:
        conn.commit()
        cursor1.close()
        cursor2.close()
        conn.close()

查看数据库，id从3开始：
在这里插入图片描述

rollback demo3: 跨函数

结果：无论是err1()还是err2()，都会触发回滚，最终未插入一条数据
结论：与所处函数无关

def test():
    conn = connect()
    cursor1 = conn.cursor()
    cursor2 = conn.cursor()
    try:
        cursor1.execute('insert into `pymysql_rollback_test`(field1, field2) values (%s, %s)', ['c1-01', 'c1-02'])
        cursor2.execute('insert into `pymysql_rollback_test`(field1, field2) values (%s, %s)', ['c2-01', 'c2-02'])
        err1()
        # err2(cursor2)
    except Exception as e:
        print(e)  # No active exception to reraise
        conn.rollback()
    finally:
        conn.commit()
        cursor1.close()
        cursor2.close()
        conn.close()


def err1():
    raise


def err2(cursor):
    cursor.execute('insert into1 `pymysql_rollback_test`(field1, field2) values (%s, %s)', ['c2-21', 'c2-22'])