Pymsql之简单的事务回滚

# coding:utf-8
import pymysql

class TransferMoney(object):

    def __init__(self,conn):
        self.conn=conn

    def check_acct_available(self,acctid):
        cursor = self.conn.cursor()
        try:
            sql = "SELECT * FROM test_rowback WHERE =%s" % acctid
            cursor.execute(sql)
            rs = cursor.fetchall()
            if len(rs) != 1:
                raise Exception("抱歉，账户%s不存在，请核对您输入的信息时候有误" % acctid)
        finally:
            cursor.close()

    def has_enough_money(self,acctid, money):
        cursor = self.conn.cursor()
        try:
            sql = "SELECT * FROM test_rowback WHERE id=%s and user_money>=%s" % (acctid,money)
            cursor.execute(sql)
            rs = cursor.fetchall()
            if len(rs) != 1:
                raise Exception("因账户%s金额不足导致转账失败" % acctid)
        finally:
            cursor.close()

    def reduce_money(self,acctid, money):
        cursor = self.conn.cursor()
        try:
            sql = "UPDATE test_rowback SET user_money=user_money-%s WHERE id=%s" % (money,acctid)
            cursor.execute(sql)
            if cursor.rowcount!=1:
                raise Exception("账户%s转账失败" % acctid)
            else:
                print ("转账成功")
        finally:
            cursor.close()

    def add_money(self,acctid, money):
        cursor = self.conn.cursor()
        try:
            sql = 'UPDATE test_rowback SET user_money=user_money+%s WHERE id=%s' % (money, acctid)
            cursor.execute(sql)
            if cursor.rowcount != 1:
                raise Exception("账户%s收款失败" % acctid)
            else:
                print ("收款成功")
        finally:
            cursor.close()

    def transfer(self,source_acctid,target_acctid,money):
        try:
            self.check_acct_available(source_acctid)
            self.check_acct_available(target_acctid)
            self.has_enough_money(source_acctid, money)
            self.reduce_money(source_acctid, money)
            self.add_money(target_acctid, money)
            self.conn.commit()
        except Exception as e:
            self.conn.rollback()
            raise e


if __name__=="__main__":
    give_accid=1
    accept_accid=2
    money=100

    conn=pymysql.connect(
        host='127.0.0.1',
        port=3306,
        user='root',
        passwd='root',
        db='Python_test',
        charset='utf8'
    )

tr_money=TransferMoney(conn)

try:
    tr_money.transfer(give_accid, accept_accid, money)
except Exception as e:
    print(e)
finally:
    conn.close()

创建数据库语句

CREATE TABLE test_rowback(
    id INT,
    user_name VARCHAR(1000),
    user_passwd VARCHAR(1000),
    user_money INT    
)ENGINE =INNODB DEFAULT CHARSET = utf8;

注意：创建数据库时，必须加上这句话 ENGINE =INNODB DEFAULT CHARSET = utf8;