python连接mysql数据库实例demo（银行管理系统数据库版）

主函数:

import adminViewimport osimport pickle

from bankFunction import BankFunctionimport 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 randomimport adminViewimport 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 # 表⽰销户成功View Code

\"\"\"

模块: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(\"********************************\")

数据库部分:

设计思路(部分):