Python_模拟银行对接mysql_24.6.14

1.创建数据库和数据表

import pymysql

CREATE_SCHEMA_SQL = "CREATE SCHEMA bank"

CREATE_TABLE_SQL = '''CREATE TABLE account(
        account_id varchar(20) not null,
        account_passwd char(6) not null,
        money DECIMAL(10,2) not null,
        primary key(account_id))default charset=utf8;
    '''

CREATE_ACCOUNT_SQL = "insert into account values('230303050','123456',100.00);"


def init():
    try:
        DB = pymysql.connect(host='localhost', user='root', password='123456')
        cursor1 = DB.cursor()
        cursor1.execute(CREATE_SCHEMA_SQL)
        DB = pymysql.connect(host='localhost', user='root', password='123456', database='bank')
        cursor2 = DB.cursor()
        cursor2.execute(CREATE_TABLE_SQL)
        cursor2.execute(CREATE_ACCOUNT_SQL)
        DB.commit()
        print("初始化成功")
    except Exception as e:
        print("初始化失败", e)
    finally:
        cursor1.close()
        cursor2.close()
        DB.close()


if __name__ == '__main__':
    init()

2.功能实现

import decimal
import pymysql

DB = None


class Account(object):
    def __init__(self, account_id, account_passwd):
        super(Account, self).__init__()
        # 账号
        self.account_id = account_id
        # 密码
        self.account_passwd = account_passwd

    # 登录检查
    def check_account(self):
        curses = DB.cursor()
        try:
            sql = ("select * from account where account_id = %s and account_passwd = %s" %
                   (self.account_id, self.account_passwd))
            curses.execute(sql)
            if curses.fetchall():
                return True
            else:
                return 0.00
        except Exception as e:
            print("系统错误", e)
        finally:
            curses.close()

    # 查询余额
    def query_money(self):
        curses = DB.cursor()
        try:
            sql = ("select money from account where account_id = %s and account_passwd = %s" %
                   (self.account_id, self.account_passwd))
            curses.execute(sql)
            money = curses.fetchone()[0]
            if money:
                return str(money.quantize(decimal.Decimal('0.00')))
            else:
                return '0.00'
        except Exception as e:
            print("系统错误", e)
        finally:
            curses.close()

    # 取钱
    def reduce_money(self, money):
        curses = DB.cursor()
        try:
            has_money = self.query_money()
            # 检查余额是否充足
            if decimal.Decimal(has_money) >= decimal.Decimal(money):
                sql = ("update account set money = money - %s where account_id = %s and account_passwd = %s" %
                       (money, self.account_id, self.account_passwd))
                curses.execute(sql)
                if curses.rowcount == 1:
                    DB.commit()
                    return True
                else:
                    DB.rollback()
                    return False
            else:
                print("余额不足")
        except Exception as e:
            DB.rollback()
            print("系统错误", e)
        finally:
            curses.close()

    # 存钱
    def add_money(self, money):
        curses = DB.cursor()
        try:
            sql = ("update account set money = money + %s where account_id = %s and account_passwd = %s" %
                   (money, self.account_id, self.account_passwd))
            curses.execute(sql)
            if curses.rowcount == 1:
                DB.commit()
                return True
            else:
                DB.rollback()
                return False
        except Exception as e:
            DB.rollback()
            print("系统错误", e)
        finally:
            curses.close()


def main():
    global DB
    # 连接数据库
    DB = pymysql.connect(host='localhost', user='root', password='123456', database='bank')

    # 登录
    from_account_id = input("欢迎使用，请输入账号：")
    from_account_pwd = input("密码：")
    account = Account(from_account_id, from_account_pwd)
    if account.check_account():
        # 登录成功
        choose = input('\n************\n登录成功，请输入你的操作：\n1.查询余额\n2.取钱\n3.存钱\n4.取卡\n')
        while choose != '4':
            if choose == '1':
                print('您的账户余额为%s元：' % (account.query_money()))
            elif choose == '2':
                money = input('您的账户余额为%s元,请输入取款金额：' % (account.query_money()))
                if account.reduce_money(money):
                    print('取款成功,您的余额还有%s元' % (account.query_money()))
                else:
                    print('取款失败')
            elif choose == '3':
                money = input('请输入存款金额：')
                if account.add_money(money):
                    print('存款成功,您的余额还有%s元' % (account.query_money()))
                else:
                    print('存款失败')
            choose = input('\n************\n登录成功，请输入你的操作：\n1.查询余额\n2.取钱\n3.存钱\n4.取卡\n')
    else:
        print('登录失败,账号或密码错误')
    print('感谢你的使用')
    DB.close()


if __name__ == '__main__':
    main()