PyMySQL - 回滚测试

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

1. 插入两条数据，无异常

   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()
   

   查看数据库：
2. 再插入两条数据，发生异常，触发了回滚

   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()
   

   查看数据库：
3. 再无异常插入两条，此时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'])