python mysql实例_python连接mysql数据库实例demo(银行管理系统数据库版)

"""类:bankFunction 银行对象

属性:银行功能"""

importrandomimportadminViewimportpymysqlglobaldb

db=pymysql.connect(

host='localhost',

port=3306,

user='root',

password='caiyishuai',

db='bank',

charset='utf8mb4')globalcursor

cursor=db.cursor()classBankFunction(object):def __init__(self, dictUser):

self.dictUser=dictUser#开户

defcreateUser(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))#设置密码

defsetPasswd(self):for i in range(3):

passwd1= input("请输入您的密码:")

passwd2= input("请再次输入您的密码:")if passwd1 ==passwd2:returnpasswd1if i == 2:return -1

print("对不起,您两次输入的密码不相同,请重新输入")#随机生成卡号

defcreateCardNumber(self):whileTrue:

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 isNone:returncardNumberprint(cardNumber)#查询

defquestUser(self):

cardNumber= input("请输入您的卡号:")#使用execute()方法执行SQL语句

cursor.execute("SELECT * FROM card where cardNumber= '%s'" %cardNumber)#使用fetall()获取全部数据

card =cursor.fetchone()#打印获取到的数据

#print(card)

if card isNone: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("非法用户!强制退出!")#存款

defsaveMoney(self):print('输入账号')

cardNumber= input("请输入您的卡号：")

cursor.execute("SELECT * FROM card where cardNumber= '%s'" %cardNumber)

card=cursor.fetchone()if card isNone: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 -1res=self.checkPwd(cardNumber)if res == -1: #密码输入错误次数过多

card[3] = '0'

return -1savemoney= 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))#取款

defgetMoney(self):#输入账号

cardNumber = input("请输入您的卡号：")

cursor.execute("SELECT * FROM card where cardNumber= '%s'" %cardNumber)

card=cursor.fetchone()if card isNone: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 -1res=self.checkPwd(cardNumber)if res == -1: #密码输入错误次数过多

card[3] = '0'

return -1getmoney= 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))#转账

deftransferMoney(self):

cardNumber= input("请输入您的卡号：")

cursor.execute("SELECT * FROM card where cardNumber= '%s'" %cardNumber)

card=cursor.fetchone()if card isNone: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 isNone:print("对不起,您输入的卡号不存在!")elif card2[3] == '0':print("此人的卡被锁了,不能转账")else:whileTrue:

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("非法用户!强制退出!")defcheckPwd(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("您的密码输入不正确，请重新输入：")#改密

defeditPasswd(self):

cardNumber= input("请输入您的卡号：")

cursor.execute("SELECT * FROM card where cardNumber= '%s'" %cardNumber)

card=cursor.fetchone()if card isNone: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])whileTrue:

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("非法用户!强制退出!")#锁卡

deflockCard(self):

cardNumber= input("请输入您的卡号：")

cursor.execute("SELECT * FROM card where cardNumber= '%s'" %cardNumber)

card=cursor.fetchone()if card isNone:print("您输入的卡号有误,请重新输入:")return -1res=self.checkPwd(cardNumber)if res == -1: #密码输入错误次数过多

card[3] = '0'

return -1flag= input("请问您确认锁卡吗？（YSE/NO）")if flag == "YES":

cursor.execute("UPDATE card SET isLock = '%s' WHERE cardNumber = '%s'" % ('0',cardNumber))

db.commit()print("您的账号已成功锁定")else:return

#解锁

defunlockCard(self):

cardNumber= input("请输入您的卡号：")

cursor.execute("SELECT * FROM card where cardNumber= '%s'" %cardNumber)

card=cursor.fetchone()if card isNone:print("您输入的卡号有误,请重新输入:")return -1res=self.checkPwd(cardNumber)if res == -1: #密码输入错误次数过多

card[3] = '0'

return -1cursor.execute("UPDATE card SET isLock = '%s' WHERE cardNumber = '%s'" % ('1',cardNumber))

db.commit()print("您的账号已成功解锁")#补卡

deffillCard(self):

cardNumber= input("请输入您的卡号：")

cursor.execute("SELECT * FROM card where cardNumber= '%s'" %cardNumber)

card=cursor.fetchone()if card isNone:print("您输入的卡号有误,请重新输入:")return -1res=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 #表示补卡成功

#销户

defkillCard(self):

cardNumber= input("请输入您的卡号：")

cursor.execute("SELECT * FROM card where cardNumber= '%s'" %cardNumber)

card=cursor.fetchone()if card isNone:print("您输入的卡号有误,请重新输入:")return -1res=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 #表示销户成功