pymysql - 实现mysql数据库银行转账功能的实现

先给数据库里传入银行卡信息：卡号和金额

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| Blog               |
| backup             |
| helloTest          |
| mysql              |
| performance_schema |
+--------------------+
6 rows in set (0.06 sec)

MariaDB [(none)]> use helloTest;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [helloTest]> create table bankData (id int primary key,money float);
Query OK, 0 rows affected (0.06 sec)

MariaDB [helloTest]> insert into bankData values(13888,500);
Query OK, 1 row affected (0.35 sec)

MariaDB [helloTest]> insert into bankData values(13889,500);
Query OK, 1 row affected (0.07 sec)

MariaDB [helloTest]> select * from bankData;
    
+-------+-------+
| id    | money |
+-------+-------+
| 13888 |   500 |
| 13889 |   500 |
+-------+-------+

primary key意思是主键。
主要的作用主要确定该数据的唯一性。比如说ID=1,NAME=张三。我们要在数据库中，找到这条数据可以使用select * from 表 where id=1 这样就可以把张三查找出来了。而这个张三，也可以出现同名，所有用ID来做主键。
而你说的insert into 是插入操作。当ID设置为了主键，再插入一个相同的主键值，就为报错误，并不会更新，你想要个更新就必须执行UPDATE。

思路：
1). 判断两个银行卡号是否存在?
2). 判断source_id是否有足够的钱？
3). source_id扣钱
4). target_id加钱

根据思路先写出程序框架

import pymysql
class TransferMoney(object):
    def __init__(self,conn):
        self.conn = conn
        self.cur = conn.cursor()
    def transfer(self,source_id,target_id,money):
    # 实现转账
    pass
	def check_accout_avaialbe(self,accid):
	#银行卡是否存在
    	pass
	def has_enough_money(self,acc_id,money):
	# 是否有足够的钱
		pass		
	def add_money(self,acc_id,money):
	# 加钱
    	pass
	def reduce_money(self,acc_id,money):\
    # 减钱	
    	pass
	def __del__(self):
	# 结束执行
    	self.cur.close()
    	self.conn.close()

---

判断帐号是否存在函数：

import pymysql
    class TransferMoney(object):
        def __init__(self,conn):
            self.conn = conn
            self.cur = conn.cursor()
def transfer(self,source_id,target_id,money):
    # 实现转账
    pass

def check_account_avaialbe(self, acc_id):
    """判断帐号是否存在， 传递的参数是银行卡号的id"""
    select_sqli = "select * from bankData where id=%d;" % (acc_id)
    print("execute sql:", select_sqli)
    res_count = self.cur.execute(select_sqli)
    if res_count == 1:
        return True
    else:
        return False
def has_enough_money(self,acc_id,money):
# 是否有足够的钱
	pass		
def add_money(self,acc_id,money):
# 加钱
	pass
def reduce_money(self,acc_id,money):\
# 减钱	
	pass
def __del__(self):
# 结束执行
	self.cur.close()
	self.conn.close()	    

测试：    
if __name__ == '__main__':
    # 1. 连接数据库，
    conn = pymysql.connect(
        host='localhost',
        user='root',
        password='westos',
        db='helloTest',
        charset='utf8',
        autocommit=True,    # 如果插入数据，， 是否自动提交? 和conn.commit()功能一致。
    )
    trans = TransferMoney(conn)
    assert trans.check_account_avaialbe(14255632) == False
    assert  trans.check_account_avaialbe(13888) == True

可以根据测试方法测试每个函数
完整代码：

import pymysql


class TransferMoney(object):
    # 构造方法
    def __init__(self, conn):
        self.conn = conn
        self.cur = conn.cursor()

def transfer(self, source_id, target_id, money):
    # 1). 判断两个银行卡号是否存在?
    # 2). 判断source_id是否有足够的钱？
    # 3). source_id扣钱
    # 4). target_id加钱
    if not self.check_account_avaialbe(source_id):
        raise  Exception("账户不存在")
    if not self.check_account_avaialbe(target_id):
        raise  Exception("账户不存在")

    if self.has_enough_money(source_id, money):
        try:
            self.reduce_money(source_id, money)
            self.add_money(target_id, money)
        except Exception as e:
            print("转账失败:", e)
            self.conn.rollback()
        else:
            self.conn.commit()
            print("%s给%s转账%s金额成功" % (source_id, target_id, money))

def check_account_avaialbe(self, acc_id):
    """判断帐号是否存在， 传递的参数是银行卡号的id"""
    select_sqli = "select * from bankData where id=%d;" % (acc_id)
    print("execute sql:", select_sqli)
    res_count = self.cur.execute(select_sqli) # 长度为1，返回的是记录数
    if res_count == 1:
        return True
    else:
        # raise  Exception("账户%s不存在" %(acc_id))
        return False

def has_enough_money(self, acc_id, money):
    """判断acc_id账户上金额> money"""
    # 查找acc_id存储金额?
    select_sqli = "select money from bankData where id=%d;" % (acc_id)
    print("execute sql:", select_sqli)
    self.cur.execute(select_sqli)  # ((1, 500), )

    # 获取查询到的金额钱数;
    acc_money = self.cur.fetchone()[0]
    # 判断
    if acc_money >= money:
        return True
    else:
        return False

def add_money(self, acc_id, money):
    update_sqli = "update bankData set money=money+%d  where id=%d" % (money, acc_id)
    print("add money:", update_sqli)
    self.cur.execute(update_sqli)

def reduce_money(self, acc_id, money):
    update_sqli = "update bankData set money=money-%d  where id=%d" % (money, acc_id)
    print("reduce money:", update_sqli)
    self.cur.execute(update_sqli)

# 析构方法
def __del__(self):
    self.cur.close()
    self.conn.close()

if __name__ == '__main__':
    conn = pymysql.connect(
        host='localhost',
        user='root',
        password='westos',
        db='helloTest',
        charset='utf8',
        autocommit=True,    # 如果插入数据，， 是否自动提交? 和conn.commit()功能一致。
    )
    trans = TransferMoney(conn)
    trans.transfer(13888,13889,200)