Python操作MySQL模拟银行转账

时间:2022-09-14 16:00:46

今天在慕课网上学习了有关于python操作MySQL的相关知识,在此做些总结。python操作数据库还是相对比较简单的,由于python统一了各个数据库的接口程序,也就是所谓的Python DB,所以无论使用何种数据可,都可以用统一的接口对数据库进行操作。操作中主要涉及connection对象的操作和cursor的操作,前者主要是为了建立起python与数据库的数据交换通道,后者则是访问数据的游标,也可以理解为指针。数据库的相关结构化语言在Python中均是以字符串的形式呈现的。另外注意rollback的重要性,一旦操作失败,所有操作都要回滚到之前的状态,否则会发生错误。

另外,在编写实例的时候,对于面向对象的编程思路又有了新的认识,自顶向下的程序编写模式非常有利于拆分程序的功能,分而治之。面向对象的封装性在此提醒的淋漓尽致!

代码如下,在原有基础上,我又增加了添加记录的功能。

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
#coding=utf8
import MySQLdb
import sys
 
class TranseferMonet(object):
 
  def __init__(self,conn):
    self.conn = conn
 
  def createNewUser(self,userID,money):
    cursor = self.conn.cursor()
    try:
      sql = 'INSERT account VALUES(%s,%s)' %(str(userID),str(money))
      cursor.execute(sql)
      self.conn.commit()
    except Exception as e:
      self.conn.rollback()
      raise e
 
  def transferMoney(self,transeferID,recivierID,money):
    try:
      self.checkID(transeferID)
      self.checkID(receiverID)
      self.checkEnoughMoney(transferID,money)
      self.subMoney(transferID,money)
      self.addMoney(receiverID,money)
      self.conn.commit()
    except Exception as e:
      self.conn.rollback()
      raise e
 
  def checkID(self,userID):
    cursor = self.conn.cursor()
    try:
      sql = 'SELECT userID FROM account WHERE userID = %s' %str(userID)
      cursor.execute(sql)
      rs = cursor.fetchall()
      if len(rs) != 1:
        raise Exception("ID错误!")
    finally:
      cursor.close()
 
  def checkEnoughMoney(self,transferID,money):
    cursor = self.conn.cursor()
    try:
      sql = 'SELECT money FROM account WHERE userID = %s and money >= %s' %(str(transferID),str(money))
      cursor.execute(sql)
      rs = cursor.fetchall()
      if len(rs) != 1:
        raise Exception("余额不足!")
    finally:
      cursor.close()
  def subMoney(self,transferID,money):
    cursor = self.conn.cursor()
    try:
      sql = 'UPDATE account SET money = money-%s WHERE userID = %s' %(str(money),str(transferID))
      cursor.execute(sql)
      if cursor.rowcount != 1:
        raise Exception('减款失败!')
    finally:
      cursor.close()
 
  def addMoney(self,receiverID,money):
 
    cursor = self.conn.cursor()
    try:
      sql = 'UPDATE account SET money = money+%s WHERE userID = %s' %(str(money),str(receiverID))
      cursor.execute(sql)
      if cursor.rowcount != 1:
        raise Exception('加款失败!')
    finally:
      cursor.close()
 
if __name__=="__main__":
 
  transferID = 2002
  receiverID = 2001
  money = 300
 
  newID = 2003
  newmoney = 900
 
  conn = MySQLdb.connect(host = '127.0.0.1',port = 3306,user = 'root',passwd = '914767195',db = 'test',charset = 'utf8')
 
  trMoney = TranseferMonet(conn)
 
  try:
    trMoney.transferMoney(transferID,receiverID,money)
  except Exception as e:
    print "转账错误"+str(e)
  try:
    trMoney.createNewUser(newID,newmoney)
  except Exception as e:
    print "创建用户失败!"+str(e)
  finally:
    conn.close()

 以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持服务器之家。

原文链接:http://blog.csdn.net/freedom098/article/details/50277199