python连接mysql数据库-银行转账实验源码

#引入开发包

import sys

import pymysql

#创建TransferMoney类

class TransferMoney(object):

    #创建TransferMoney类构造函数并定义属性connection接收外部connection

    def __init__(self, connection):

        self.connection = connection

    #创建transfer方法

    def transfer(self,source_acctid, target_acctid, money):

       try:

        self.check_acct_avaliable(source_acctid)

        self.check_acct_avaliable(target_acctid)

        self.has_enough_money(source_acctid, money)

        self.reduce_money(source_acctid, money)

        self.add_money(target_acctid, money)

        self.connection.commit()

       except Exception as e:

         self.connection.rollback()

         raise e

    #创建check_acct_avaliable函数，检查账户可用性

    def check_acct_avaliable(self, acctid):

        cursor = self.connection.cursor()

        try:

            sql = "select * from account where acctid = %s" % acctid

            cursor.execute(sql)

            print("check_acct_avaliable"+sql)

            rs = cursor.fetchall()

            if len(rs) != 1:

                raise Exception("账号不存在"%acctid)

        finally:

            cursor.close()

    #创建has_enough_money函数，检查账户的钱是否足够转账

    def has_enough_money(self, acctid, money):

        try:

            cursor = self.connection.cursor()

            sql = "select * from account where acctid = %s and money >=%s" % (acctid, money)

            cursor.execute(sql)

            rs = cursor.fetchall()

            if len(rs) != 1:

                raise Exception("账号%s没有足够的钱" % acctid)

        finally:

            cursor.close()

    # 创建reduce_money函数，转出账户钱减少

    def reduce_money(self, acctid, money):

        try:

            cursor = self.connection.cursor()

            sql = "update account set money = money-%s where acctid = %s " % (money,acctid )

            cursor.execute(sql)

            print("reduce_money:"+sql)

            if cursor.rowcount != 1:

                raise Exception("账号%s减款失败" % acctid)

        finally:

            cursor.close()

    # 创建add_money函数，转入账户钱增加

    def add_money(self, acctid, money):

        try:

            cursor = self.connection.cursor()

            sql = "update account set money = money+%s where acctid = %s " % (money, acctid, )

            cursor.execute(sql)

            print("reduce_money:"+sql)

            if cursor.rowcount != 1:

                raise Exception("账号%s加款失败" % acctid)

        finally:

            cursor.close()

#程序主入口

if __name__== "__main__":

    #通过argv从程序外部获取实参

    source_acctid = sys.argv[1]

    target_acctid = sys.argv[2]

    money = sys.argv[3]

    connection = pymysql.connect(host='localhost',

                                   user='root',

                                   password='123456',

                                   db='wikiurl',

                                   charset='utf8')

    #创建TransferMoney类的对象tr_money

    tr_money = TransferMoney(connection)

    try:

        tr_money.transfer(source_acctid, target_acctid, money)

    except Exception as e:

        print("出现问题"+str(e))

    finally:

        connection.close()