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