基于数据库的银行储蓄卡管理系统

这是一个用python的tkinter模块实现的简易的银行管理系统,基于SQL Server数据库
(注:本系统,若本地无正确的SQL数据库表格式将无法正常运行)
SQL数据表结构如下所示:
在这里插入图片描述

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
account为账号,code 为密码
python代码如下:

# -*- coding: utf8 -*-
# 开发人员 : X
# 开发时间 : 2020/5/22 11:50
# 文件名称 : test.py
# 开发工具 : PyCharm

import pymssql
from tkinter import *


def bug1():
    root8 = Tk()
    root8.title('警告')
    root8.geometry('200x100')
    root8.wm_attributes('-topmost', 1)

    lb11 = Label(root8, text='请用正确的格式输入',
                 fg='red', width=50, height=2, font=('华文新魏', 15))
    lb11.pack()


def bug2():
    root9 = Tk()
    root9.title('警告')
    root9.geometry('200x100')
    root9.wm_attributes('-topmost', 1)

    lb12 = Label(root9, text='卡号已存在',
                 fg='red', width=50, height=2, font=('华文新魏', 15))
    lb12.pack()


def bug3():
    root13 = Tk()
    root13.title('警告')
    root13.geometry('200x100')
    root13.wm_attributes('-topmost', 1)

    lb15 = Label(root13, text='您的余额不足',
                 fg='red', width=50, height=2, font=('华文新魏', 15))
    lb15.pack()


def success1():
    root10 = Tk()
    root10.title('消息')
    root10.geometry('200x100')
    root10.wm_attributes('-topmost', 1)

    lb13 = Label(root10, text='已成功创建账户',
                 fg='red', width=50, height=2, font=('华文新魏', 15))
    lb13.pack()


def success2():
    root11 = Tk()
    root11.title('消息')
    root11.geometry('200x100')
    root11.wm_attributes('-topmost', 1)

    lb14 = Label(root11, text='成功存入',
                 fg='red', width=50, height=2, font=('华文新魏', 15))
    lb14.pack()


def success3():
    root12 = Tk()
    root12.title('消息')
    root12.geometry('200x100')
    root12.wm_attributes('-topmost', 1)

    lb15 = Label(root12, text='取款成功',
                 fg='red', width=50, height=2, font=('华文新魏', 15))
    lb15.pack()


def deposit_database(account, money, s):
    try:
        money = float(money)
    except ValueError as e:
        print('ValueError', e)
        bug1()
        return 0
    print(account)
    search2 = conn.cursor()
    search2.execute('UPDATE Card SET money+=%f WHERE user_id=%s' % (money, account))

    search10 = conn.cursor()
    search10.execute('SELECT record_num FROM Record ORDER BY record_num DESC')
    temp = search10.fetchone()
    record_num = int(temp[0])
    record_num += 1
    record_num = str(record_num)
    print(record_num)

    search10.execute('SELECT time FROM Record')
    temp = search10.fetchone()
    print(temp[0])
    print(money)
    b = 0
    search10.execute("INSERT INTO Record VALUES (%s, %s, %5.2f, %5.2d, GETDATE())" % (record_num, s, money, b))

    conn.commit()
    print('成功')
    success2()


def withdraw_database(account, money, s):
    try:
        money = float(money)
    except ValueError as e:
        print('ValueError', e)
        bug1()
        return 0

    print(account)
    search2 = conn.cursor()
    search2.execute('SELECT money FROM Card WHERE user_id=%s' % account)
    temp = search2.fetchone()
    if temp[0]<money:
        bug3()
        return 0
    else:
        search2.execute('UPDATE Card SET money-=%f WHERE user_id=%s' % (money, account))

    search9 = conn.cursor()
    search9.execute('SELECT record_num FROM Record ORDER BY record_num DESC')
    temp = search9.fetchone()
    record_num = int(temp[0])
    record_num += 1
    record_num = str(record_num)
    print(record_num)

    search9.execute('SELECT time FROM Record')
    temp = search9.fetchone()
    print(temp[0])
    b = 0.00
    print(money)
    search9.execute("INSERT INTO Record VALUES (%s, %s, %f, %f, GETDATE())" % (record_num, s, b, money))
    conn.commit()
    print('成功')


def deposit(s, account):
    print('存款')
    root3 = Tk()
    root3.title('消息')
    root3.geometry('600x300')
    root3.wm_attributes('-topmost', 1)

    lb5 = Label(root3, text='请输入存款数:',
                fg='blue', bg='white', width=30, height=2, font=('华文新魏', 20))
    lb5.pack()

    text3 = Entry(root3, font=('华文新魏', 15))
    text3.pack()
    text3.place(relx=0.3, rely=0.5)

    btn10 = Button(root3, text='确定', command=lambda: deposit_database(account, text3.get(), s),
                  width=10, height=2)
    btn10.pack()
    btn10.place(relx=0.3, rely=0.7)

    btn11 = Button(root3, text='取消', command=lambda: root3.destroy(),
                   width=10, height=2)
    btn11.pack()
    btn11.place(relx=0.5, rely=0.7)


def withdraw(account, s):
    print('取款')
    root4 = Tk()
    root4.title('消息')
    root4.geometry('600x300')
    root4.wm_attributes('-topmost', 1)

    lb6 = Label(root4, text='请输入取款数:',
                fg='blue', bg='white', width=30, height=2, font=('华文新魏', 20))
    lb6.pack()

    text5 = Entry(root4, font=('华文新魏', 15))
    text5.pack()
    text5.place(relx=0.3, rely=0.5)

    btn12 = Button(root4, text='确定', command=lambda: withdraw_database(account, text5.get(), s),
                   width=10, height=2)
    btn12.pack()
    btn12.place(relx=0.3, rely=0.7)

    btn13 = Button(root4, text='取消', command=lambda: root4.destroy(),
                   width=10, height=2)
    btn13.pack()
    btn13.place(relx=0.5, rely=0.7)


def balance(account):
    print('查询余额')
    print(account)
    search5 = conn.cursor()
    search5.execute('SELECT money FROM Card WHERE user_id = %s' % account)
    temp = search5.fetchone()
    print(temp[0])
    str1 = '您的余额为%s' % temp[0]
    print(str1)
    root13 = Tk()
    root13.title('余额查询')
    root13.geometry('300x100')
    root13.wm_attributes('-topmost', 1)

    lb10 = Label(root13, text=str1,
                 fg='red', width=20, height=1, font=('华文新魏', 15))
    lb10.pack()


def history(account):
    print('历史纪录')
    print(account)
    search5 = conn.cursor()
    search5.execute('SELECT * FROM Record WHERE account = %s' % account)
    temp = search5.fetchone()
    str2 = []

    root6 = Tk()
    root6.title('存取款历史纪录')
    root6.geometry('300x300')
    root6.wm_attributes('-topmost', 1)

    theLB = Listbox(root6, width='80')
    theLB.pack()

    while temp:
        print('1')
        if temp[2] != 0:
            str2.append('%s存款%f' % (temp[4], temp[2]))
        else:
            str2.append('%s取款%f' % (temp[4], temp[3]))
        temp = search5.fetchone()
    print (str2)
    for item in str2:
        theLB.insert(END, item)


def destroy_all(root, root1, root7):
    root.destroy()
    root1.destroy()
    root7.destroy()
    print('系统关闭')


def cancellation_database(acccount, root, root1, root7):
    print('开始注销')
    search4 = conn.cursor()
    search4.execute('UPDATE Card SET money=0,status=%s where account=%s' % ('0', acccount))
    destroy_all(root, root1, root7)


def cancellation(account, root, root1):
    print('注销')

    root7 = Tk()
    root7.title('警告')
    root7.geometry('400x100')
    root7.wm_attributes('-topmost', 1)

    lb15 = Label(root7, text='确定要注销该账户吗?',
                 fg='blue', width=30, height=2, font=('华文新魏', 20))
    lb15.pack()

    btn14 = Button(root7, text='确定', command=lambda: cancellation_database(account, root, root1, root7),
                   width=10, height=2)
    btn14.pack()
    btn14.place(relx=0.1, rely=0.5)

    btn15 = Button(root7, text='取消', command=lambda: root7.destroy(),
                   width=10, height=2)
    btn15.pack()
    btn15.place(relx=0.7, rely=0.5)


def resister_database(user_id, code, name):
    try:
        code = int(code)
        code = str(code)
    except ValueError as e:
        bug1()
        return 0
    try:
        user_id = int(user_id)
        user_id = str(user_id)
    except ValueError as e:
        bug1()
        return 0
    print(user_id, code, name)
    user_id_group = []
    code_group = []
    name_group = []
    account = '0'
    search4 = conn.cursor()
    sql = (
        'SELECT Card.user_id,code,user_name,account FROM Card,Users where Card.user_id = Users.user_id '+
        'ORDER BY account')
    search4.execute(sql)
    temp = search4.fetchone()
    while temp:
        user_id_group.append(temp[0])
        code_group.append(temp[1])
        name_group.append(temp[2])
        account = temp[3]
        temp = search4.fetchone()
    print(user_id_group)
    print(code_group)
    print(name_group)
    account = int(account) + 1
    account = str(account)
    print(account)

    if (len(user_id) >= 6) & (len(code) >= 6) & (len(code) <= 10) & (len(user_id) <= 15) & (name != ''):
        print('成功')
        if user_id in user_id_group:
            bug2()
        else:
            create1 = conn.cursor()
            create1.execute("INSERT INTO Users VALUES ('%s', '%s',1)" % (user_id, name))
            create1.execute('INSERT INTO Card VALUES (%s,%s,%s,0, 1)' % (account, user_id, code))
            success1()
            conn.commit()

    else:
        bug1()


def sign_in(root):
    search1 = conn.cursor()
    search1.execute('SELECT * FROM Card,Users where Card.user_id = Users.user_id')
    temp = search1.fetchone()
    judge = 0
    account = text1.get()
    code = text2.get()
    account = str(account)
    code = str(code)

    while temp:
        print(temp[1])
        print(temp[2])
        if (temp[1] == account) & (temp[2][0:len(code)] == code) & (temp[4] == '1'):
            print(temp[1], temp[2], temp[4])
            root1 = Tk()
            root1.title(temp[6])
            root1.geometry('600x300')
            root1.wm_attributes('-topmost', 1)
            judge = 1

            lb4 = Label(root1, text='欢迎登录,请选择您的操作',
                        fg='blue', bg='white', width=30, height=2, font=('华文新魏', 20))
            lb4.pack()

            btn4 = Button(root1, text='退出', command=lambda: root1.destroy(),
                          width=10, height=2)
            btn4.pack()
            btn4.place(relx=0.4, rely=0.85)

            btn5 = Button(root1, text='存款', command=lambda: deposit(temp[0], account),
                          width=10, height=2)
            btn5.pack()
            btn5.place(relx=0, rely=0.5)

            btn6 = Button(root1, text='取款', command=lambda: withdraw(account, temp[0]),
                          width=10, height=2)
            btn6.pack()
            btn6.place(relx=0.2, rely=0.5)

            btn7 = Button(root1, text='查询余额', command=lambda: balance(account),
                          width=10, height=2)
            btn7.pack()
            btn7.place(relx=0.4, rely=0.5)

            btn8 = Button(root1, text='查看历史纪录', command=lambda: history(temp[0]),
                          width=10, height=2)
            btn8.pack()
            btn8.place(relx=0.6, rely=0.5)

            btn9 = Button(root1, text='卡注销', command=lambda: cancellation(temp[0], root, root1),
                          width=10, height=2)
            btn9.pack()
            btn9.place(relx=0.8, rely=0.5)
            break
        temp = search1.fetchone()
    if judge == 0:
        root2 = Tk()
        root2.title('消息提醒')
        root2.geometry('500x100')
        root2.wm_attributes('-topmost', 1)
        lb4 = Label(root2, text='账号(密码)错误或该账号已被注销',
                    fg='red', width=50, height=2, font=('华文新魏', 15))
        lb4.pack()


def resister():
    root2 = Tk()
    root2.title('注册面板')
    root2.geometry('600x300')
    root2.wm_attributes('-topmost', 1)

    lb7 = Label(root2, text='请输入账户的基本信息:',
                fg='blue', bg='white', width=30, height=2, font=('华文新魏', 20))
    lb7.pack()

    lb8 = Label(root2, text='卡号(至少六位):',
                fg='red', width=15, height=1, font=('华文新魏', 15))
    lb8.pack()
    lb8.place(relx=0.1, rely=0.25)

    lb9 = Label(root2, text='密码(至少六位):',
                fg='red', width=15, height=1, font=('华文新魏', 15))
    lb9.pack()
    lb9.place(relx=0.1, rely=0.45)

    lb10 = Label(root2, text='真实姓名:',
                 fg='red', width=10, height=1, font=('华文新魏', 15))
    lb10.pack()
    lb10.place(relx=0.15, rely=0.65)

    text5 = Entry(root2, font=('华文新魏', 15))
    text5.pack()
    text5.place(relx=0.4, rely=0.25)

    text6 = Entry(root2, font=('华文新魏', 15))
    text6.pack()
    text6.place(relx=0.4, rely=0.45)

    text7 = Entry(root2, font=('华文新魏', 15))
    text7.pack()
    text7.place(relx=0.4, rely=0.65)

    btn12 = Button(root2, text='确定', command=lambda: resister_database(text5.get(), text6.get(), text7.get()),
                   width=10, height=2)
    btn12.pack()
    btn12.place(relx=0.3, rely=0.8)

    btn13 = Button(root2, text='取消', command=lambda: root2.destroy(),
                   width=10, height=2)
    btn13.pack()
    btn13.place(relx=0.5, rely=0.8)


# 数据库初始化
serverName = 'localhost'
userName = 'sa'
passWord = '141592'
conn = pymssql.connect(serverName, userName, passWord, port='1433', database='BankCard_Management_System',
                       charset="utf8")

# 窗口初始化
root = Tk()
root.title('银行储蓄卡管理系统')
root.geometry('500x350')

# 标签初始化
lb1 = Label(root, text='银行储蓄卡管理系统',
            fg='blue', bg='white', width=20, height=2, font=('华文新魏', 20))
lb1.pack()
lb1.place(relx=0.2, rely=0.1)

lb2 = Label(root, text='卡号:',
            fg='red', width=5, height=1, font=('华文新魏', 15))
lb2.pack()
lb2.place(relx=0.15, rely=0.35)

lb3 = Label(root, text='密码:',
            fg='red', width=5, height=1, font=('华文新魏', 15))
lb3.pack()
lb3.place(relx=0.15, rely=0.55)

# 输入框初始化
text1 = Entry(root, font=('华文新魏', 15))
text1.pack()
text1.place(relx=0.3, rely=0.35)

text2 = Entry(root, font=('华文新魏', 15))
text2.pack()
text2.place(relx=0.3, rely=0.55)

# 按键初始化
btn1 = Button(root, text='登录', width=10, height=2, command=lambda: sign_in(root))
btn1.pack()
btn1.place(relx=0.3, rely=0.7)

btn2 = Button(root, text='注册', width=10, height=2, command=lambda: resister())
btn2.pack()
btn2.place(relx=0.5, rely=0.7)

btn3 = Button(root, text='退出程序', command=lambda: root.destroy())
btn3.pack()
btn3.place(relx=0.42, rely=0.85)

root.mainloop()
try:
    conn.commit()
except Exception as e:
    print('Exception', e)
    print('数据库无变动')

conn.close()

实际效果图如下:

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

评论 3
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值