python一套完整的事务操作
1 #coding=utf-82 import sys
3 import MySQLdb
4
5 class TransferMoney(object):
6 def __init__(self,conn):
7 self.conn = conn
8
9 #检查账户是否合法
10 def check_acct_avaiable(self,acctid):
11 cursor = self.conn.cursor()
12 try:
13 sql = "select * from account where acctid=%s" % acctid
14 cursor.execute(sql)
15 print "check account:" + sql
16 rs = cursor.fetchall()
17 if len(rs) != 1:
18 raise Exception("account %s illega" % acctid)
19 finally:
20 cursor.close()
21
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:" + sql
29 rs = cursor.fetchall()
30 if len(rs) != 1:
31 raise Exception("account %s not enough money" % acctid)
32 finally:
33 cursor.close()
34
35 #账户减钱
36 def reduce_money(self,acctid,money):
37 cursor = self.conn.cursor()
38 try:
39 sql = "update account set money = money-%s where acctid = %s" % (money,acctid)
40 cursor.execute(sql)
41 print "reduce_money:" + sql
42 if cursor.rowcount != 1:
43 raise Exception("reduce money fail %s" % acctid)
44 finally:
45 cursor.close()
46
47 #账户加钱
48 def add_money(self,acctid,money):
49 cursor = self.conn.cursor()
50 try:
51 sql = "update account set money = money+%s where acctid = %s" % (money,acctid)
52 cursor.execute(sql)
53 print "add_money:" + sql
54 if cursor.rowcount != 1:
55 raise Exception("add money fail %s" % acctid)
56 finally:
57 cursor.close()
58 #主执行语句
59 def transfer(self,source_acctid,target_acctid,money):
60 try:
61 self.check_acct_avaiable(source_acctid)
62 self.check_acct_avaiable(target_acctid)
63 self.has_enough_money(source_acctid,money)
64 self.reduce_money(source_acctid,money)
65 self.add_money(target_acctid,money)
66 self.conn.commit()
67 except Exception as e:
68 self.conn.rollback()
69 raise e
70
71 if __name__ == "__main__":
72 source_acctid = sys.argv
73 target_acctid = sys.argv
74 money = sys.argv
75 conn = MySQLdb.Connect(host = '127.0.0.1',port=3306,user='root',passwd='',db='test',charset='utf8')
76 tr_money = TransferMoney(conn)
77
78 try:
79 tr_money.transfer(source_acctid,target_acctid,money)
80 except Exception as e:
81 print "Happen:" + str(e)
82 finally:
83 conn.close()
页:
[1]