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 # 表示销户成功