银行信息管理系统

实验目的：完成一个简易的银行管理系统（python连接MySQL）

要求：首先创建一个银行数据库 Bank,然后数据库中创建两个表 card 和user，card 表的属性，卡号、密码，卡里金额、卡的标记(是否锁卡)，user表的属性:姓名、身份证号、电话号码、银行卡号。

功能要求：书写10个信息要求

1（开户）.创建账户信息，包括卡的信息和用户的信息。用户输入姓名、身份证号、电话号码，系统自动生成一定位数的卡号。输入存入金额，分别存入用户表和卡表中

2.（查询）根据提供的银行卡号查询账户信息

3.（存款）向银行卡中存入指定金额，存款成功后显示卡中余额

4.（取款）从银行卡中取出指定金额，取款成功后显示余额，取款金额不足时给予提示

5. （转账）从一个银行卡中转账指定金额到另一个银行卡中

6. （改密）修改账户密码

7.（锁卡）设置卡的标记为锁卡状态

8.（解锁）设置卡的标记为解锁

9.（补卡）输入原来的卡号和德码获取账户信息，然后创建新的卡号，关联用户信息

0（销户）输入卡迪哎翱暗薄唇册长挨艾挨艾雷渎鉴门刹蠢炳叭靶楚苍霸菠爱菜拌班叭 幢匙钳从数据库中剧除用户信息和卡的信息

q  退出系统

我的库是bank,,,,在运行这个的条件上需要在MySQL中添加库bank,和user  和  card这两个表

话不多说直接上代码

这个写的比较草率没有优化没时间

import random
import pymysql
def dql(sql):# 查看card
    # 打开数据库连接
    db = pymysql.connect(host="localhost", user="root",password="123456", db="bank", port=3306,charset='utf8')
    # 使用cursor()方法获取操作游标
    cur = db.cursor()
    # 1.查询操作
    try:
        cur.execute(sql)  # 执行sql语句
        results = cur.fetchall()  # 获取查询的所有记录
        print("")
        # 遍历结果
        for row in results:
            id_card_bank = row[0]
            password = row[1]
            card_yu_e = row[2]
            card_mark = row[3]
            print('id_card_bank', "\t", 'password', "\t\t\t", 'card_yu_e', "\t\t", 'card_mark')
            print(id_card_bank, "\t\t\t", password, "\t\t\t", card_yu_e,"\t\t\t",card_mark)
    except Exception as e:
        raise e
    finally:
        db.close()  # 关闭连接
def dfl(sql):# user查看
    # 打开数据库连接
    db = pymysql.connect(host="localhost", user="root",password="123456", db="bank", port=3306,charset='utf8')
    # 使用cursor()方法获取操作游标
    cur = db.cursor()
    # 1.查询操作
    try:
        cur.execute(sql)  # 执行sql语句
        results = cur.fetchall()  # 获取查询的所有记录
        print("")
        # 遍历结果
        for row in results:
            name = row[0]
            id_card_person = row[1]
            ph_number = row[2]
            id_card_bank = row[3]
            print('name', "\t\t\t", 'id_card_person', "\t\t\t\t", 'ph_number', "\t\t\t", 'id_card_bank')
            print(name, "\t\t\t", id_card_person, "\t\t\t", ph_number,"\t\t\t",id_card_bank)
    except Exception as e:
        raise e
    finally:
        db.close()  # 关闭连接

def dml(sql):
    # 打开数据库连接
    db = pymysql.connect(host="localhost", user="root",password="123456", db="bank", port=3306,charset='utf8')
    # 使用cursor()方法获取操作游标
    cur = db.cursor()
    try:
        cur.execute(sql)
        # 提交
        db.commit()
    except Exception as e:
        db.rollback()
    finally:
        db.close()
def create_user(name,id_card_person,ph_number,id_card_bank):# 与功能一相关
    return f"insert into user values('{name}','{id_card_person}','{ph_number}',{id_card_bank});"

def menu():
    print("***********************")
    print("*******    主功能  *******")
    print("******开户(1) 查询(2)******")
    print("******存款(3) 取款(4)******")
    print("******转账(5) 改密(6)******")
    print("******锁卡(7) 解锁(8)******")
    print("******补卡(9) 销户(0)******")
    print("******    退出(q)   ******")
    print("***********************")

def main():
    while True:
        menu()
        one = input("请选择你所要进行的操作：")
        if one == '1':
            name = input("输入你的姓名：")  # 姓名
            id_card_person = input("输入你的身份证号码：")  # 身份证
            ph_number = input("输入你的电话号码：")  # 电话号码
            id_card_bank = random.randint(111111, 999999)  # 银行卡号
            a = create_user(name,id_card_person,ph_number,id_card_bank)
            dml(a)
            password = int(input("输入你所设置的密码："))
            card_yu_e = float(input("输入你要存入的钱："))
            card_mark = input("输入是或否（卡的状态是否锁卡）：")
            b = f"insert into card values({id_card_bank},{password},{card_yu_e},'{card_mark}');"
            dml(b)
        elif one == '2':
            tt = int(input("输入u你索要查询的卡号："))
            aa = f'select *from user where id_card_bank = {tt};'
            bb = f'select *from card where id_card_bank = {tt};'
            print("这是user: ")
            dfl(aa)
            print("这是card: ")
            dql(bb)
        elif one == '3':
            c = float(input("输入你要存储的金额："))
            cccc = int(input("输入你所要取钱的账户："))
            cc = f"update card set card_yu_e = card_yu_e + {c} where id_card_bank = {cccc};"
            dml(cc)
            db = pymysql.connect(host="localhost", user="root", password="123456", db="bank", port=3306, charset='utf8')
            cur = db.cursor()
            cur.execute(f'select *from card where id_card_bank = {cccc}')  # 执行sql语句
            results = cur.fetchall()  # 获取查询的所有记录
            print("你的现在余额是：")
            for i in results:
                vv = i[2]
                print(vv)
        elif one == '4':
            d = float(input("输入你所要取出的钱："))
            dddd = int(input("输入你所要取出的账户："))
            db = pymysql.connect(host="localhost", user="root", password="123456", db="bank", port=3306, charset='utf8')
            cur = db.cursor()
            cur.execute(f'select *from card where id_card_bank = {dddd}')  # 执行sql语句
            results = cur.fetchall()  # 获取查询的所有记录
            print("")
            # 遍历结果
            for row in results:
                card_yu_e = row[2]
            qwe = card_yu_e
            db.close()  # 关闭连接
            if qwe < d:
                print("余额不足！！！")
                continue
            dd = f"update card set card_yu_e = card_yu_e - {d} where id_card_bank = {dddd};;"
            dml(dd)
            print("取钱成功！！！")
        elif one == '5':
            dddd = int(input("输入你所在的账户："))
            e = float(input("输入你所要转出的余额："))
            ee = input("输入你所要转入的账户名: ")
            eee = f"update card set card_yu_e  = card_yu_e - {e} where id_card_bank = {dddd};"
            eeee = f"update card set card_yu_e = card_yu_e + {e} where id_card_bank = '{ee}';"
            dml(eee)
            dml(eeee)
            print("转账成功")
        elif one == '6':
            dddd = int(input("输入你所在的账户："))
            ff = int(input("输入你将要改成的密码："))
            fff = f"update card set password = {ff} where id_card_bank = {dddd};"
            dml(fff)
            print("改密成功！！！")
        elif one == '7':
            dddd = int(input("输入你所在的账户："))
            gg = f"update card set card_mark = '是' where id_card_bank = {dddd};"
            dml(gg)
            print("锁卡成功！！！")
        elif one == '8':
            dddd = int(input("输入你所在的账户："))
            hh = f"update card set card_mark = '否' where id_card_bank = {dddd};"
            dml(hh)
            print("解卡成功！！！")
        elif one == '9':
            i = int(input("输入你原来的卡号："))
            ii = int(input("输入原来的密码："))
            iii = f"select *from card where id_card_bank = {i};"
            iiii = f"select *from user where id_card_bank = {i}"
            print("这时候你之前的账户信息请你核对！！！")
            print("card: ")
            dql(iii)
            print("user: ")
            dfl(iiii)
            db = pymysql.connect(host="localhost", user="root", password="123456", db="bank", port=3306, charset='utf8')
            # 使用cursor()方法获取操作游标
            cur = db.cursor()
            # 1.查询操作
            cur.execute(f'select *from card where id_card_bank = {i};')
            results = cur.fetchall()
            print("")
            aa = int(input("输入新的密码: "))
            # 遍历结果
            for row in results:
                card_yu_e = row[2]
                card_mark = row[3]
                id_card_bank = random.randint(111111, 999999)
                password = aa
                dml(f'delete from card where id_card_bank = {i};')
                print(id_card_bank, password, card_yu_e, card_mark)
                dddddd = f"insert into card values ({id_card_bank},{password},{card_yu_e},'{card_mark}');"
                dml(dddddd)
                print("新的卡信息为：")
                kk = f"select *from card where id_card_bank = {id_card_bank};"
                dql(kk)
                tgb = f"update user set id_card_bank = {id_card_bank} where id_card_bank = {i};"
                dml(tgb)
                tgb2 = f"select *from user where id_card_bank = {id_card_bank};"
                dfl(tgb2)
            db.close()  # 关闭连接
        elif one == '0':
            jj = int(input("输入你的卡号："))
            jjj = int(input("输入你的密码："))
            jjjj = f"delete from card where password = {jjj} and id_card_bank = {jj};"
            jjjjj = f"delete from user where id_card_bank = {jj};"
            dml(jjjj)
            dml(jjjjj)
            print("注销成功！！！")
        elif one == 'q':
            break
main()