def takeMoneyDeal(self, money):
sql = "select balance from tb_bank_user where user_name = '%s'" % self.userName
bRes, result = mysqlDB.execute(sql)
balance = float(result[0].get("balance"))
resultMsg = msgData.ResultMsg()
if balance < money:
resultMsg.status = 2
resultMsg.msgText = "balance not enough"
else:
balance -= money
sql = "update tb_bank_user set balance = %.2f where user_name = '%s'" % (
balance, self.userName)
bRes, result = mysqlDB.execute(sql)
resultMsg.status = 0
resultMsg.msgText = "take money success"
self.saveRecord(self.userName, 2, money)
return resultMsg
def saveMoney(self, msg):
resultMsg = msgData.ResultMsg()
if not self.userName:
resultMsg.status = 1
resultMsg.msgText = "please login first"
else:
saveMsg = msgData.SaveMsg()
saveMsg.deserialization(msg)
sql = "select balance from tb_bank_user where user_name = '%s'" % self.userName
bRes, result = mysqlDB.execute(sql)
balance = float(result[0].get("balance")) + saveMsg.money
sql = "update tb_bank_user set balance = %.2f where user_name = '%s'" % (
balance, self.userName)
bRes, result = mysqlDB.execute(sql)
resultMsg.status = 0
resultMsg.msgText = "save money success"
self.saveRecord(self.userName, 1, saveMsg.money)
self.request.send(resultMsg.serialization())
def checkLogin(self, userName):
'''检查是否已经登录'''
sql = "select status from tb_bank_user where user_name = '%s'" % userName
bRes, result = mysqlDB.execute(sql)
if not result:
bRes = False
elif result[0].get("status") == 0:
bRes = False
else:
bRes = True
return bRes
def registerDeal(self, userName, passwd):
'''用户注册处理函数'''
resultMsg = msgData.ResultMsg()
if self.userName:
resultMsg.status = 2
resultMsg.msgText = "你已经登录了,请退出登录后再进行账号注册"
return resultMsg
sql = "select * from tb_bank_user where user_name = '%s'" % userName
bRes, result = mysqlDB.execute(sql)
if not result:
sql = "insert into tb_bank_user(user_name, passwd) values('%s', '%s')" % (
userName, passwd)
bRes, result = mysqlDB.execute(sql)
resultMsg.status = 0
resultMsg.msgText = "register success"
else:
resultMsg.status = 1
resultMsg.msgText = "user already exits"
return resultMsg
def loginDeal(self, userName, passWd):
'''用户登录处理函数'''
resultMsg = msgData.ResultMsg()
if self.userName or self.checkLogin(userName):
resultMsg.status = 3
resultMsg.msgText = "you already login"
return resultMsg
sql = "select passwd from tb_bank_user where user_name = '%s'" % userName
bRes, result = mysqlDB.execute(sql)
if not result or len(result) == 0:
resultMsg.status = 1
resultMsg.msgText = "user not exits"
elif result[0].get("passwd") != passWd:
resultMsg.status = 2
resultMsg.msgText = "passwd error"
else:
self.userName = userName
sql = "update tb_bank_user set status = 1 where user_name = '%s'" % userName
bRes, result = mysqlDB.execute(sql)
resultMsg.status = 0
resultMsg.msgText = "login success"
return resultMsg
def queryBalance(self, msg):
resultMsg = msgData.ResultMsg()
if not self.userName:
resultMsg.status = 1
resultMsg.msgText = "please login first"
else:
queryMsg = msgData.QueryMsg()
queryMsg.deserialization(msg)
sql = "select balance from tb_bank_user where user_name = '%s'" % self.userName
bRes, result = mysqlDB.execute(sql)
resultMsg.status = 0
resultMsg.msgText = "query success"
resultMsg.data["balance"] = float(result[0].get("balance"))
self.request.send(resultMsg.serialization())
def checkRecord(self, msg):
resultMsg = msgData.ResultMsg()
if not self.userName:
resultMsg.status = 1
resultMsg.msgText = "please login first"
else:
sql = "select type, money, date from tb_bank_record where user_name = '%s'" % self.userName
bRes, result = mysqlDB.execute(sql)
resultList = []
for res in result:
recordType = "transfer in" if res.get(
"type") == 1 else "transfer out"
recordMoney = res.get("money")
recordTime = res.get("date")
resultList.append("[%s]%s %.2f" %
(recordTime, recordType, recordMoney))
resultMsg.status = 0
resultMsg.msgText = "check record success"
resultMsg.data["record"] = resultList
self.request.send(resultMsg.serialization())
def saveRecord(self, userName, type, money):
sql = "insert into tb_bank_record(user_name, type, money, date) values('%s', %d, %f, now())" % (
userName, type, money)
bRes, result = mysqlDB.execute(sql)
def logoutDeal(self):
sql = "update tb_bank_user set status = 0 where user_name = '%s'" % self.userName
bRes, result = mysqlDB.execute(sql)
if bRes: self.userName = None
return bRes
#将 0001 题生成的 200 个激活码（或者优惠券）保存到 MySQL 关系型数据库中
from mysqlDB import mysqlDB
import uuid
if __name__ == "__main__":
mysqlDB.connectDB({
"host": "localhost",
"port": 3306,
"user": "******",
"passwd": "123456",
"db": "mytest",
"charset": "utf8",
})
for i in range(200):
id = str(uuid.uuid1())
sql = "insert into tb_coupon(id) values('{id}')".format(id=id)
bRes, result = mysqlDB.execute(sql)
if not bRes:
print("execute {sql} error!".format(sql=sql))