def editpassword():
"""
修改账号密码
:return: Userinfo.html
"""
userid = session['userid']
try:
if request.method == 'POST':
password1 = request.form['password1']
password2 = request.form['password2']
if password1 == password2:
try:
sql = "UPDATE User SET Location='{}' WHERE UserID='{}'".format(
password1, userid)
mysql.exe(sql)
logger.info(
"UPDATE password --> username:{},password:{} ".format(
userid, password1))
except Exception as e:
mysql.rollback()
logger.exception(
"username:{},password:{} UPDATE password filed".format(
username, password1))
return redirect(url_for('user'))
except Exception as e:
logger.exception("add user info error: {}".format(e))
return redirect(url_for('user'))
def deleteuserinfo():
'''
delete students info
:return: userinfo.html
'''
login, userid = True, None
print(request.method, 'method')
keysearch = []
try:
if request.method == 'GET':
keyUIN = request.values.get('UIN')
keyUIN = keyUIN.strip()
sql = '''DELETE FROM Students where UIN={} '''.format(keyUIN)
mysql.exe(sql)
logger.info("delete user success,sql:{}".format(sql))
sql = """ select * from Students"""
keysearch = mysql.fetchall_db(sql) # fetch data from database
print(keysearch, 'keysearch')
keysearch = [[v for k, v in row.items()] for row in keysearch]
except Exception as e:
logger.exception("delete user error: {}".format(e))
return render_template("userinfo.html",
login=login,
keysearch=keysearch,
useid=userid)
def editinfo():
"""
修改个人信息
:return: Userinfo.html
"""
userid = session['userid']
try:
if request.method == 'POST':
password = request.form['password']
age = request.form['age']
try:
sql = "UPDATE User SET Location='{}',Age= '{}' WHERE UserID='{}'".format(
password, age, userid)
mysql.exe(sql)
logger.info(
"UPDATE userinfo --> username:{},password:{},age:{} ".
format(userid, password, age))
except Exception as e:
mysql.rollback()
logger.exception(
"username:{},password:{},age:{} UPDATE filed".format(
username, password, age))
return redirect(url_for('user'))
except Exception as e:
logger.exception("add user info error: {}".format(e))
return redirect(url_for('user'))
def rating():
"""
书籍评分
:return: update
"""
userid = session['userid']
try:
if request.method == 'POST':
rank = request.values.get('rank')
bookid = request.values.get('book_id')
sql = '''SELECT COUNT(1) as count FROM Bookrating WHERE UserID="{0}" and BookID="{1}" '''.format(
userid, bookid)
count = mysql.fetchone_db(sql)
if count['count']:
sql = '''UPDATE Bookrating SET Rating='{2}' WHERE UserID="{0}" and BookID="{1}" '''.format(
userid, bookid,
int(rank) * 2)
else:
sql = '''INSERT INTO Bookrating (UserID,BookID,Rating) values ('{0}','{1}','{2}') '''.format(
userid, bookid,
int(rank) * 2)
mysql.exe(sql)
logger.info("update book rating success,sql:{}".format(sql))
except Exception as e:
logger.exception("rating books error: {}".format(e))
return redirect(url_for('root'))
def register():
"""
注册
:return: Register.html
"""
try:
if request.method == 'POST':
username = request.form['username']
password = request.form['password']
age = request.form['age']
try:
sql = "insert into User (UserID,Location,Age) values ('{}','{}','{}')".format(
username, password, age)
mysql.exe(sql)
logger.info(
"username:{},password:{},age:{} register success".format(
username, password, age))
except Exception as e:
mysql.rollback()
logger.exception(
"username:{},password:{},age:{} register filed".format(
username, password, age))
return render_template('Login.html')
except Exception as e:
logger.exception("register function error: {}".format(e))
return render_template('Register.html', error='注册出错')
def delete_book():
'''
删除书籍
'''
userid = session['userid']
try:
if request.method == 'GET':
bookid = request.values.get('bookid')
sql = '''DELETE FROM Books WHERE BookID="{0}" '''.format(bookid)
mysql.exe(sql)
logger.info("delete Books success,sql:{}".format(sql))
except Exception as e:
logger.exception("delete books error: {}".format(e))
return redirect(url_for('app.adminbook'))
def delete_user():
'''
删除用户
'''
userid = session['userid']
try:
if request.method == 'GET':
userid = request.values.get('userid')
sql = '''DELETE FROM User WHERE UserID="{0}" '''.format(userid)
mysql.exe(sql)
logger.info("delete User success,sql:{}".format(sql))
except Exception as e:
logger.exception("delete User books error: {}".format(e))
return redirect(url_for('app.adminuser'))
def delete():
'''
删除购物车
'''
userid = session['userid']
try:
if request.method == 'GET':
bookid = request.values.get('bookid')
sql = '''DELETE FROM Cart WHERE UserID="{0}" and BookID="{1}" '''.format(userid,bookid)
mysql.exe(sql)
logger.info("delete Cart success,sql:{}".format(sql))
except Exception as e:
logger.exception("delete Cart books error: {}".format(e))
return redirect(url_for('app.order'))
def insertuserinfo():
"""
insert a new user
:return: userinfo.html
"""
login, userid = True, None
print(request.method, 'method')
formKeys = [k for k in request.args]
key_dict = {
'UIN': None,
'studentName': None,
'Gender': None,
'Standing': None,
'Major': None,
'GPA': None
}
key_dict['UIN'] = request.args.get('UIN')
key_dict['studentName'] = request.args.get('studentName')
key_dict['Gender'] = request.args.get('Gender')
key_dict['Standing'] = request.args.get('Standing')
key_dict['Major'] = request.args.get('Major')
key_dict['GPA'] = request.args.get('GPA')
print(key_dict)
keysearch = []
try:
if request.method == 'GET':
sql = """ insert into Students
values ({}, '{}', '{}', '{}', '{}', {})""".format(
*[n for n in key_dict.values()])
mysql.exe(sql)
logger.info("insert user success,sql:{}".format(sql))
sql = """ select * from Students"""
keysearch = mysql.fetchall_db(sql) # fetch data from database
print(keysearch, 'keysearch')
keysearch = [[v for k, v in row.items()] for row in keysearch]
except Exception as e:
logger.exception("insert user error: {}".format(e))
return render_template("userinfo.html",
login=login,
keysearch=keysearch,
useid=userid)
def register():
"""
注册
:return: Register.html
"""
try:
if request.method == 'POST':
username = request.form['username']
try:
#在此仅仅使用Userid，其他信息写死
sql = "insert into User (UserID,Location,Age) values ('{}','China','20')".format(username)
mysql.exe(sql)
except Exception as e:
mysql.rollback()
logger.exception("username:{} register filed".format(username))
return render_template('Login.html')
except Exception as e:
logger.exception("register function error: {}".format(e))
return render_template('Register.html', error='注册出错')
def addbook():
'''
添加书籍
'''
userid = session['userid']
try:
if request.method == 'POST':
bookid = request.form['bookid']
title = request.form['title']
author = request.form['author']
public = request.form['public']
Image = "http://photocdn.sohu.com/20140424/Img398717878.jpg"
sql = '''INSERT INTO Books (BookID,BookTitle,BookAuthor,PubilcationYear,Publisher,ImageS,ImageM,ImageL)
values ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}')'''.format(bookid,title,author,"2018",
public,Image,Image,Image)
mysql.exe(sql)
logger.info("add Books success,sql:{}".format(sql))
return redirect(url_for('app.adminbook'))
except Exception as e:
logger.exception("delete books error: {}".format(e))
return render_template('AdminAddBook.html')
def updateuserinfo():
'''
update user info
:return: userinfo.html
'''
login, userid = True, None
print(request.method, 'method')
keysearch = []
try:
if request.method == 'GET':
keyUIN = request.values.get('UIN')
keyname = request.values.get('studentName')
keygender = request.values.get('Gender')
keystanding = request.values.get('Standing')
keymajor = request.values.get('Major')
keygpa = request.values.get('GPA')
keyUIN = keyUIN.strip()
keyname = keyname.strip()
keygender = keygender.strip()
keystanding = keystanding.strip()
keymajor = keymajor.strip()
keygpa = keygpa.strip()
sql = """ update Students
set studentName='{}', Gender='{}', Standing='{}', Major='{}', GPA='{}'
where UIN = '{}' """.format(keyname, keygender, keystanding,
keymajor, keygpa, keyUIN)
mysql.exe(sql)
logger.info("update user success,sql:{}".format(sql))
sql = """ select * from Students"""
keysearch = mysql.fetchall_db(sql) # fetch data from database
print(keysearch, 'keysearch')
keysearch = [[v for k, v in row.items()] for row in keysearch]
except Exception as e:
logger.exception("update user error: {}".format(e))
return render_template("userinfo.html",
login=login,
keysearch=keysearch,
useid=userid)
def update_recommend_book(UserID, BookID):
"""
更新推荐数据
"""
sql = '''SELECT score FROM Booktuijian WHERE UserID="{0}" and BookID="{1}"'''.format(UserID, BookID)
score = mysql.fetchone_db(sql)
if score:
score = int(score['score'])
if score + 0.5 > 10: score =10
else: score += 0.5
sql = '''UPDATE Booktuijian SET score='{2}' WHERE UserID="{0}" and BookID="{1}" '''.format(UserID, BookID,
int(score))
logger.info("update_recommend_book, sql:{}".format(sql))
mysql.exe(sql)
else:
score = 0.5
sql = ''' insert into Booktuijian (UserID,BookID,score) values ('{0}','{1}','{2}') '''.format(UserID, BookID,
int(score))
logger.info("update_recommend_book, sql:{}".format(sql))
mysql.exe(sql)
def add():
'''
添加购物车
'''
login, userid = False, None
if 'userid' not in session:
return redirect(url_for('app.loginForm'))
else:
login, userid = True, session['userid']
try:
if request.method == 'GET':
bookid = request.values.get('bookid')
sql = '''SELECT COUNT(1) as count FROM Cart WHERE UserID="{0}" and BookID="{1}" '''.format(userid,
bookid)
count = mysql.fetchone_db(sql)
if not count['count']:
sql = '''INSERT INTO Cart (UserID,BookID ) values ('{0}','{1}') '''.format(userid, bookid)
mysql.exe(sql)
logger.info("update Cart success,sql:{}".format(sql))
except Exception as e:
logger.exception("update Cart books error: {}".format(e))
return redirect(url_for('app.order'))
