慕課 python 操作資料庫2 銀行轉賬執行個體

標籤：

 

CREATE TABLE `account` (  `acctid` int(11) DEFAULT NULL COMMENT ‘賬戶ID‘,  `money` int(11) DEFAULT NULL COMMENT ‘餘額‘) ENGINE=InnoDB DEFAULT CHARSET=utf8

 

 1 # coding:utf8 2  3 import MySQLdb 4 import sys 5  6 class TransferMoney(object): 7     """docstring for TransferMoney""" 8     def __init__(self, conn): 9         self.conn = conn10 11     def check_acct_avaiable(self,acctid):12         cursor = self.conn.cursor()13         try:14             sql = "select * from account where acctid = %s" % acctid15             cursor.execute(sql)16             print "check_acct_avaiable:" + sql17             rs = cursor.fetchall()18             if len(rs) != 1:19                 raise Exception("帳號%s不存在" % acctid)20         finally:21             cursor.close()22 23     def has_enough_money(self,acctid,money):24         cursor = self.conn.cursor()25         try:26             sql = "select * from account where acctid = %s and money > %s" % (acctid,money)27             cursor.execute(sql)28             print "has_enough_money:" + sql29             rs = cursor.fetchall()30             if len(rs) != 1:31                 raise Exception("帳號%s沒有足夠的錢" % acctid)32         finally:33             cursor.close()34 35     def reduce_money(self,acctid,money):36         cursor = self.conn.cursor()37         try:38             sql = "update account set money = money - %s where acctid = %s" % (money,acctid)39             cursor.execute(sql)40             print "reduce_money:" + sql41             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             sql = "update account set money = money + %s where acctid = %s" % (money,acctid)50             cursor.execute(sql)51             print "add_money:" + sql52             if cursor.rowcount != 1:53                 raise Exception("帳號%s加款失敗" % acctid)54         finally:55             cursor.close()56 57     def transfer(self,source_acctid,target_acctid,money):58         try:59             self.check_acct_avaiable(source_acctid)60             self.check_acct_avaiable(target_acctid)61             self.has_enough_money(source_acctid,money)62             self.reduce_money(source_acctid,money)63             self.add_money(target_acctid,money)64             self.conn.commit()65         except Exception, e:66             self.conn.rollback()67             raise e68 69         70         71 72 if __name__ == "__main__":73     source_acctid = sys.argv[1]74     target_acctid = sys.argv[2]75     money = sys.argv[3]76 77     conn = MySQLdb.Connect(78         host = ‘127.0.0.1‘,79         port = 3306,80         user = ‘njczy2010‘,81         passwd = ‘57040516‘,82         db = ‘czy‘,83         charset = ‘utf8‘84     )85 86     tr_money = TransferMoney(conn)87 88     try:89         tr_money.transfer(source_acctid,target_acctid,money)90     except Exception as e:91         print "出現問題 " + str(e)92     finally:93         conn.close()

 

正確情況：

check_acct_avaiable:select * from account where acctid = 11check_acct_avaiable:select * from account where acctid = 12has_enough_money:select * from account where acctid = 11 and money > 100reduce_money:update account set money = money - 100 where acctid = 11add_money:update account set money = money + 100 where acctid = 12

 

 

錯誤情況：

check_acct_avaiable:select * from account where acctid = 11check_acct_avaiable:select * from account where acctid = 12has_enough_money:select * from account where acctid = 11 and money > 100出現問題 帳號11沒有足夠的錢

 

慕課 python 操作資料庫2 銀行轉賬執行個體