pymysql写简易的银行转账系统

最新推荐文章于 2023-01-30 21:31:31 发布

原创最新推荐文章于 2023-01-30 21:31:31 发布 · 438 阅读

0 ·

CC 4.0 BY-SA版权

MySQL 同时被 2 个专栏收录

21 篇文章

订阅专栏

python

1 篇文章

订阅专栏

本文介绍了在开发简易银行转账系统时遇到的pymysql InternalError，具体表现为'Unknown column'错误。问题根源在于SQL动态拼接语句中，未对变量%s进行正确引用导致。

import  sys
import  pymysql

class TransferMoney(object):
       def __init__(self,conn):
           self.conn=conn
       def check_acct_available(self,acctid):
           cursor = self.conn.cursor()
           try:
               sql = "select *  from account where accid = '%s' " %acctid
               cursor.execute(sql)
               rs = cursor.fetchall()
               if len(rs) != 1:
                   raise Exception("账号'%s'不存在" % acctid)
           finally:
               cursor.close()

       def has_enough_money(self,acctid,money):
           cursor = self.conn.cursor()
           try:
               sql = "select *  from account where accid = '%s'  and money > '%d'" % (acctid,money)
               cursor.execute(sql)
               rs = cursor.fetchall()
               if len(rs) != 1:
                   raise Exception("账号'%s'没有足够的钱" % acctid)
           finally:
               cursor.close()

       def  reduce_money(self,acctid, money):
           cursor = self.conn.cursor()
           try:
               sql = "update account set money = money -'%d' WHERE  accid='%s'" % (money,acctid)
               cursor.execute(sql)
               print ("reduce_money:" + sql)
               if cursor.rowcount != 1:
                   raise Exception("账号'%s'减款异常" % acctid)
           finally:
               cursor.close()

       def add_money(self, acctid, money):
           cursor = self.conn.cursor()
           try:
               sql = "update account set money = money +'%s' WHERE  accid='%s'" % (money, acctid)
               cursor.execute(sql)
               print("reduce_money:" + sql)
               if cursor.rowcount != 1:
                   raise Exception("账号'%s'加款异常" % acctid)
           finally:
               cursor.close()

       def transfer(self,source_acctid, target_acctid, money):
           try:
               self.check_acct_available(source_acctid)
               self.check_acct_available(target_acctid)
               self.has_enough_money(source_acctid,money)
               self.reduce_money(source_acctid,money)
               self.add_money(target_acctid,money)
               self.conn.commit()
           except Exception as e:
               self.conn.rollback()
               raise e

if __name__=="__main__":
    #source_acctid = sys.argv[1]
    #target_acctid = sys.argv[2]
    #money = sys.argv[3]
    # 使用 cursor() 方法创建一个游标对象
    source_acctid = input("请输入减款人：")
    target_acctid = input("请输入收款人：")
    money = int(input("请输入转款数："))
    conn = pymysql.connect(host='localhost', user='root', password='', database='test', port=3306, charset='utf8')
    cursor = conn.cursor()
    tr_money = TransferMoney(conn)
    '''print(source_acctid)
    print(target_acctid)
    print(money)'''
    try:
        tr_money.transfer(source_acctid,target_acctid,money)
    except Exception as e:
        print("出栈问题"+str(e))
    finally:
        conn.close()

在编写的过程中出现pymysql.err.InternalError: (1054, "Unknown column~~~)

原因是SQL语句那里%s没有加引号。