python连接mysql数据库实例demo(银行管理系统数据库版)
主函数:
import adminView import os import pickle from bankFunction import BankFunction import time def main(): # pass # 欢迎界面 adminView.welcomeView() if adminView.loginView() == 1: pass adminView.functionView() bank = BankFunction({}) while True: number = input("请输入您要选择的功能编号: ") if number == "1": bank.createUser() elif number == "2": # 查询 bank.questUser() elif number == "3": # 存款 bank.saveMoney() elif number == "4": # 取款 bank.getMoney() elif number == "5": # 转账 bank.transferMoney() elif number == "6": # 改密码 bank.editPasswd() elif number == "7": # 锁卡 bank.lockCard() elif number == "8": # 解锁 bank.unlockCard() elif number == "9": # 补卡 bank.fillCard() elif number == "0": # 销户 bank.killCard() elif number == "q": print("正在推出系统,请稍后......") time.sleep(2) print("退出成功") break else: print("您输入的数字有误,请重新输入") def test(): adminView.welcomeView() adminView.loginView() adminView.functionView() if __name__ == '__main__': main() # test()
调用数据库的部分:
""" 类:bankFunction 银行对象 属性:银行功能 """ import random import adminView import pymysql global db db = pymysql.connect( host='localhost', port=3306, user='root', password='caiyishuai', db='bank', charset='utf8mb4' ) global cursor cursor = db.cursor() class BankFunction(object): def __init__(self, dictUser): self.dictUser = dictUser # 开户 def createUser(self): # 请输入您的姓名 name = input("请输入您的姓名: ") # 请输入您的身份证号码 idCard = input("请输入您的身份证号码: ") # 请输入您的电话号码 phone = input("请输入您的电话号码: ") cardNumber = self.createCardNumber() passwd = self.setPasswd() if passwd == -1: print("创建失败") return -1 # 设置金额:钱 money = float(input("请输入您想存入的金额: ")) # 目的:创建一个user,并且保存字典(数据库) sql = "INSERT INTO user VALUES(%s,%s,%s,%s)" cursor.execute(sql, (name, idCard, phone, cardNumber)) db.commit() # 提交数据 sql = "INSERT INTO card VALUES(%s,%s,%s,%s)" cursor.execute(sql, (cardNumber, passwd, money, 1)) db.commit() # 提交数据 print("%s,你好,你的卡号是 %s" % (name, cardNumber)) # 设置密码 def setPasswd(self): for i in range(3): passwd1 = input("请输入您的密码: ") passwd2 = input("请再次输入您的密码: ") if passwd1 == passwd2: return passwd1 if i == 2: return -1 print("对不起,您两次输入的密码不相同,请重新输入") # 随机生成卡号 def createCardNumber(self): while True: cardNumber = "" for i in range(6): cardNumber += str(random.randrange(0, 10)) cursor.execute("SELECT * FROM card where cardNumber = '%s' " % cardNumber) card = cursor.fetchone() print("card", card) if card is None: return cardNumber print(cardNumber) # 查询 def questUser(self): cardNumber = input("请输入您的卡号: ") # 使用execute()方法执行SQL语句 cursor.execute("SELECT * FROM card where cardNumber= '%s' " % cardNumber) # 使用fetall()获取全部数据 card = cursor.fetchone() # 打印获取到的数据 # print(card) if card is None: print("对不起,您输入的卡号不存在!") elif card[3]=='0': print("对不起,您的卡已被锁定") else: cursor.execute("SELECT * FROM user where cardNumber='%s' " % cardNumber) data = cursor.fetchall()[0] print(data[0], "您好!") for i in range(3): pswd = input("请输入您的密码: ") if pswd == card[1]: print("您有金额: ", card[2]) break else: print("对不起,您输入的密码错误") if i == 2: print("非法用户!强制退出!") # 存款 def saveMoney(self): print('输入账号') cardNumber = input("请输入您的卡号：") cursor.execute("SELECT * FROM card where cardNumber= '%s' " % cardNumber) card = cursor.fetchone() if card is None: print("您输入的卡号有误,请重新输入:") return -1 # 获得卡 cursor.execute("SELECT * FROM user where cardNumber='%s' " % cardNumber) data = cursor.fetchall()[0] print(data[0], "您好!") if card[3] == '0': print("你的卡被锁了,不能存款") return -1 res = self.checkPwd(cardNumber) if res == -1: # 密码输入错误次数过多 card[3] = '0' return -1 savemoney = float(input("请输入您要存入的金额：")) money = str(float(card[2]) + savemoney) cursor.execute("UPDATE card SET money = '%s' WHERE cardNumber = '%s' " % (money,cardNumber)) db.commit() # 提示消息 print("存款成功，您当前用户可用余额为：%s元" % (money)) # 取款 def getMoney(self): # 输入账号 cardNumber = input("请输入您的卡号：") cursor.execute("SELECT * FROM card where cardNumber= '%s' " % cardNumber) card = cursor.fetchone() if card is None: print("您输入的卡号有误,请重新输入:") return -1 # 获得卡 cursor.execute("SELECT * FROM user where cardNumber='%s' " % cardNumber) data = cursor.fetchall()[0] print(data[0], "您好!") if card[3] == '0': print("你的卡被锁了,不能存款") return -1 res = self.checkPwd(cardNumber) if res == -1: # 密码输入错误次数过多 card[3] = '0' return -1 getmoney = float(input("请输入您要取出的金额：")) if getmoney > float(card[2]): print("对不起，您的余额当前余额为%s元，余额不足！！！" % float(card[2])) return -1 else: card_money = str(float(card[2]) - getmoney) cursor.execute("UPDATE card SET money = '%s' WHERE cardNumber = '%s' " % (card_money,cardNumber)) db.commit() print("取款成功，您当前用户可用余额为：%s元" % (card_money)) # 转账 def transferMoney(self): cardNumber = input("请输入您的卡号：") cursor.execute("SELECT * FROM card where cardNumber= '%s' " % cardNumber) card = cursor.fetchone() if card is None: print("您输入的卡号有误,请重新输入:") return -1 elif card[3] == '0': print("你的卡被锁了,不能存款") return -1 else: cursor.execute("SELECT * FROM user where cardNumber='%s' " % cardNumber) data = cursor.fetchall()[0] print(data[0], "您好!") for i in range(3): pswd = input("请输入您的密码: ") # 密码输入正确 if pswd == card[1]: # 你有的钱 have_money = float(card[2]) print("您有金额: ", have_money) cardNumber2 = input("请输入您要转账的卡号: ") cursor.execute("SELECT * FROM card where cardNumber= '%s' " % cardNumber2) card2 = cursor.fetchone() if card2 is None: print("对不起,您输入的卡号不存在!") elif card2[3] == '0': print("此人的卡被锁了,不能转账") else: while True: turn_money = float(input("请输入您要转的金额: ")) if turn_money > have_money: print("对不起,您没有那么多钱,请重新输入") else: your_card_money =str( float(card[2])-turn_money) his_card_money =str( float( card2[2])+turn_money) cursor.execute("UPDATE card SET money = '%s' WHERE cardNumber = '%s' " % (your_card_money,cardNumber)) db.commit() cursor.execute("UPDATE card SET money = '%s' WHERE cardNumber = '%s' " % (his_card_money,cardNumber2)) db.commit() print("恭喜你转账成功,你还有", your_card_money, "元") break break else: print("对不起,您输入的密码错误") if i == 2: print("非法用户!强制退出!") def checkPwd(self, cardNumber): cursor.execute("SELECT * FROM card where cardNumber= '%s' " % cardNumber) card = cursor.fetchone() card_p = card[1] for i in range(3): # 最多验证3次 Pwd = input("请输入您的密码:") if Pwd == card_p: return 0 # 表示密码输入正确 if i == 2: return -1 # 3次输入密码错误 print("您的密码输入不正确，请重新输入：") # 改密 def editPasswd(self): cardNumber = input("请输入您的卡号：") cursor.execute("SELECT * FROM card where cardNumber= '%s' " % cardNumber) card = cursor.fetchone() if card is None: print("您输入的卡号有误,请重新输入:") else: cursor.execute("SELECT * FROM user where cardNumber='%s' " % cardNumber) data = cursor.fetchall()[0] print(data[0], "您好!") for i in range(3): pswd = input("请输入您的密码: ") if pswd == card[1]: print("您有金额: ", card[2]) while True: new_passwd1 = input("请输入您新密码: ") new_passwd2 = input("请再次输入您新密码: ") if new_passwd1 != new_passwd2: print("对不起,您两次输入的新密码不同,请重新输入!") else: cursor.execute("UPDATE card SET passwd = '%s' WHERE cardNumber = '%s' " % (new_passwd1,cardNumber)) db.commit() print("恭喜你!改密成功!") break break else: print("对不起,您输入的密码错误") if i == 2: print("非法用户!强制退出!") # 锁卡 def lockCard(self): cardNumber = input("请输入您的卡号：") cursor.execute("SELECT * FROM card where cardNumber= '%s' " % cardNumber) card = cursor.fetchone() if card is None: print("您输入的卡号有误,请重新输入:") return -1 res = self.checkPwd(cardNumber) if res == -1: # 密码输入错误次数过多 card[3] = '0' return -1 flag = input("请问您确认锁卡吗？（YSE/NO）") if flag == "YES": cursor.execute("UPDATE card SET isLock = '%s' WHERE cardNumber = '%s' " % ('0',cardNumber)) db.commit() print("您的账号已成功锁定") else: return # 解锁 def unlockCard(self): cardNumber = input("请输入您的卡号：") cursor.execute("SELECT * FROM card where cardNumber= '%s' " % cardNumber) card = cursor.fetchone() if card is None: print("您输入的卡号有误,请重新输入:") return -1 res = self.checkPwd(cardNumber) if res == -1: # 密码输入错误次数过多 card[3] = '0' return -1 cursor.execute("UPDATE card SET isLock = '%s' WHERE cardNumber = '%s' " % ('1',cardNumber)) db.commit() print("您的账号已成功解锁") # 补卡 def fillCard(self): cardNumber = input("请输入您的卡号：") cursor.execute("SELECT * FROM card where cardNumber= '%s' " % cardNumber) card = cursor.fetchone() if card is None: print("您输入的卡号有误,请重新输入:") return -1 res = self.checkPwd(cardNumber) if res == -1: # 密码输入错误次数过多 card[3] = '0' return -1 if input("是否确认补卡号为:%s的账户(y/n)" % cardNumber) == "y": new = self.createCardNumber() cursor.execute("UPDATE card SET cardNumber = '%s' WHERE cardNumber = '%s' " % (new,cardNumber)) db.commit() cursor.execute("UPDATE user SET cardNumber = '%s' WHERE cardNumber = '%s' " % (new,cardNumber)) db.commit() print("补卡成功,新卡号为%s,即将返回功能选择页面" % new) return 0 # 表示补卡成功 # 销户 def killCard(self): cardNumber = input("请输入您的卡号：") cursor.execute("SELECT * FROM card where cardNumber= '%s' " % cardNumber) card = cursor.fetchone() if card is None: print("您输入的卡号有误,请重新输入:") return -1 res = self.checkPwd(cardNumber) if res == -1: # 密码输入错误次数过多 card[3] = '0' return -1 if input("是否确认注销卡号为:%s的账户(y/n)" % cardNumber) == "y": sql = "DELETE FROM card WHERE cardNumber = %s" cursor.execute(sql, (cardNumber)) db.commit() # 提交数据 sql = "DELETE FROM user WHERE cardNumber = %s" cursor.execute(sql, (cardNumber)) db.commit() # 提交数据 print("销户成功,已注销账户:%s,即将返回功能选择页面" % cardNumber) return 0 # 表示销户成功
""" 模块:adminView.py 欢迎页界面-----登录------主功能 """ import time def welcomeView(): print("*****************************") print("******* *******") print("****** 浙江帅帅银行 ******") print("****** V4.1.1 ******") print("******* *******") print("*****************************") # 让用户多看会儿广告/logo #time.sleep(1) # 登录界面 # 模拟银行账号和密码 dictUser = {"cys": "1", "ss": "11"} def loginView(): userNameInput = input("请输入您的管理员账号: ") passwdInput = input("请输入您的管理员密码: ") # 验证 # 判断用户名存不存在 if userNameInput not in dictUser: # 判断它不存在 print("您输入的账号不存在") return -1 # 表示不存在 # 先根据用户名来获取密码 passwd = dictUser[userNameInput] # 判断密码是否正确 if passwd != passwdInput: print("您输入的密码不正确...算了,让你进吧") return -1 # 登录成功 print("恭喜你登录成功,系统正在加载中......") #time.sleep(3) # 主功能界面 def functionView(): print("********************************") print("******* 主功能 *******") print("****** 开户(1) 查询(2) ******") print("****** 存款(3) 取款(4) ******") print("****** 转账(5) 改密(6) ******") print("****** 锁卡(7) 解锁(8) ******") print("****** 补卡(9) 销户(0) ******") print("******* 退出(q) *******") print("********************************")
数据库部分:
设计思路(部分):
