四.mysql演示银行转账
代码演示:
1 #conding:utf8 2 import pymysql 3 import sys 4 5 class TransferMoney(object): 6 def __init__(self,conn): 7 self.conn=conn 8 def check_acct_available(self,acctid): 9 cursor = self.conn.cursor() 10 try: 11 cursor=self.conn.cursor() 12 sql="select * from account where acctid=%s"%acctid 13 cursor.execute(sql) 14 print("check_acct_available:"+sql) 15 rs=cursor.fetchall() 16 if len(rs)!=1: 17 raise Exception("账号%s不存在:"%acctid) 18 finally: 19 cursor.close() 20 21 def has_enough_money(self,acctid,money): 22 cursor = self.conn.cursor() 23 try: 24 cursor = self.conn.cursor() 25 sql = "select * from account where acctid=%s and money>%s" % (acctid,money) 26 cursor.execute(sql) 27 print("has_enough_money:" + sql) 28 rs = cursor.fetchall() 29 if len(rs) != 1: 30 raise Exception("账号%s没有足够的钱:" % acctid) 31 finally: 32 cursor.close() 33 def reduce_money(self,acctid,money): 34 cursor = self.conn.cursor() 35 try: 36 cursor = self.conn.cursor() 37 sql = "update account set money = money-%s where acctid=%s" % (money,acctid) 38 cursor.execute(sql) 39 print("reduce_money:" + sql) 40 rs = cursor.fetchall() 41 if cursor.rowcount!=1: 42 raise Exception("账号%s减款失败:" % acctid) 43 finally: 44 cursor.close() 45 46 def add_money(self,acctid,money): 47 cursor = self.conn.cursor() 48 try: 49 cursor = self.conn.cursor() 50 sql = "update account set money = money+%s where acctid=%s" % (money, acctid) 51 cursor.execute(sql) 52 print("add_money:" + sql) 53 rs = cursor.fetchall() 54 if cursor.rowcount != 1: 55 raise Exception("账号%s加款失败:" % acctid) 56 finally: 57 cursor.close() 58 59 60 def transfer(self,source_acctid,target_acctid,money): 61 '''检查付款人和收款人账号是否存在,检查付款人是否有足够的钱''' 62 try: 63 self.check_acct_available(source_acctid) 64 self.check_acct_available(target_acctid) 65 self.has_enough_money(source_acctid,money) 66 self.reduce_money(source_acctid,money) 67 self.add_money(target_acctid,money) 68 self.conn.commit() 69 except Exception as e: 70 self.conn.rollback() 71 raise e 72 73 74 75 if __name__=="__main__": 76 '''付款人账号id,收款人账号id,转账金额''' 77 source_acctid=sys.argv[1] 78 target_acctid=sys.argv[2] 79 money=sys.argv[3] 80 conn=pymysql.connect( 81 host='192.168.199.249', 82 port=3306, 83 user='root', 84 passwd='123456', 85 db='imooc', 86 charset='utf8' 87 ) 88 tr_money=TransferMoney(conn) 89 90 try: 91 tr_money.transfer(source_acctid,target_acctid,money) 92 except Exception as e: 93 print("出现问题:"+str(e)) 94 finally: 95 conn.close()