def index(): comment_id = request.form.get('comment_id') like_book_ssid = request.args.get('ssid') user_name = session.get('user_name') if user_name: user_id = tool.get_user_id_by_name(user_name) else: user_id = None if comment_id: with OpenDB() as con: user_id = tool.get_user_id_by_name(user_name) sql = "INSERT INTO `book_list`.`comment_like_record` (`comment_id`, `user_id`) VALUES ('{}', '{}')".format( comment_id, user_id) con.execute(sql) if request.args.get('type'): book_list_id = request.args.get('book_list_id') user_id = tool.get_user_id_by_name(user_name) tool.update_like_number_by_user_id(user_id, book_list_id) if like_book_ssid: with OpenDB() as con: sql = 'select user from user_information where user="******"'.format(user_name) con.execute(sql) # todo 这里以后需要写个sql 转换ssid new_list = tool.get_list("new", user_id=None) # 最新漂流 elite_list = tool.get_list("elite", user_id=None) # 精品书单 your_like_list = tool.get_list("you_like", user_id=user_id) comment_list, comment_dict = tool.get_user_comment() # comment_list_by_book=tool.get_comment_by_book_id() return render_template('index.html', new_list=new_list, elite_list=elite_list, your_like_list=your_like_list, comment_list=comment_list[:30], comment_dict=comment_dict, user_name=user_name)
def get_you_like(user_id): with OpenDB() as con: sql = "select distinct c.user_id from comment c where book_id in (select c.book_id from comment c where c.user_id='{}')".format( user_id) # 找到 同样 喜欢这个用户喜欢书的相识用户 con.execute(sql) res = con.fetchall() if len(res) < 2: return None same_user_list = [] for i in res: same_user_list.append("{}".format(i[0])) same_user_list = same_user_list print(same_user_list) del same_user_list[same_user_list.index(str(user_id))] k = ','.join(same_user_list) sql = "select book_list from comment where user_id in ({})".format(k) # 找到 同样 喜欢这个用户喜欢书的相识用户 喜欢书 的书单 print(sql) con.execute(sql) res = con.fetchall() book_list = [k[0] for k in res] sql = "select sum(c.star_num) total_star,c.book_list,b.book_list_name from comment c,book_list b where c.book_list in{}and b.ssid=c.book_list group by book_list order by total_star desc" \ "".format(str(tuple(book_list))) # 根据书单的被喜欢数排序 con.execute(sql) res = con.fetchall() book_like_list = [ dict(ssid=k[1], like_number=int(k[0]), book_list_name=k[2]) for k in res ] return book_like_list
def regist(): if request.method == "POST": user = request.form['username'] password = request.form['password'] confirm_password = request.form['confirm_password'] if password == confirm_password: pass else: flash('密码不一致') with OpenDB() as con: sql = 'select user from user_information where user="******"'.format(user) con.execute(sql) if len(con.fetchall()) > 0: return redirect(url_for('regist')) else: sql = 'insert into user_information(`user`,`password`)values("{}","{}")'.format(user, password) try: con.execute(sql) session['user_name'] = user return redirect(url_for('index')) except: print('发生位置错误,无法插入') return render_template('zhuceye.html') else: return render_template('zhuceye.html')
def get_user_book_list_by_user_name(user_name): book_list_list = [] with OpenDB() as con: # sql="select B.book_list_name,B.create_time,B.like_number,B.unlike_number,B.type from comment C,book_list B,user_infomation U where C.user_id=(select ssid from user_information where user='******')," user_id = get_user_id_by_name(user_name) sql = """select B.ssid,B.book_list_name, B.create_time,B.like_number,B.unlike_number, B.type,U.sum_of_booklist from book_list B,user_book_list U where B.ssid in (select book_list_id from user_book_list where user_id={} and type=1)and U.book_list_id=B.ssid and U.type=1 """.format(user_id) print(sql) con.execute(sql) res = con.fetchall() for i in res: book_dict = dict(book_list_id=i[0], book_list_name=i[1], create_time=i[2], like_number=i[3], unlike_number=i[4], type=i[5], sum_of_book_list=i[6]) book_list_list.append(book_dict) return book_list_list
def insert_new_book_list(type, book_title_name, user_name, book_intro, book, comment): with OpenDB() as con: sql = "select * from book_list where book_list_name='{}'".format( book_title_name) con.execute(sql) if len(con.fetchall()) < 1: sql = "INSERT INTO `book_list`.`book_list` (`book_list_name`,`book_list_intro`,`type`) VALUES ('{}','{}','{}')".format( book_title_name, book_intro, type) con.execute(sql) sql = "select ssid from book_list where book_list_name ='{}'".format( book_title_name) con.execute(sql) user_id = get_user_id_by_name(user_name) book_list_id = con.fetchone()[0] book_id = get_book_information_by_book_name(book)[0]['book_id'] print(user_id, book_list_id, book_id) sql="INSERT INTO `book_list`.`comment` (`user_id`, `book_id`, `comment`, `book_list`, `star_num`) VALUES ('{}', '{}', '{}', '{}', '{}')"\ .format(user_id,book_id,comment,book_list_id,5) con.execute(sql) sql = "select * from user_book_list where user_id ='{}'and book_list_id='{}'".format( user_id, book_list_id) con.execute(sql) if len(con.fetchall()) < 1: sql = "INSERT INTO `book_list`.`user_book_list` (`user_id`, `book_list_id`, `type`) VALUES ('{}', '{}', '{}')".format( user_id, book_list_id, 1) print(sql) con.execute(sql) return True
def get_user_information(user_id=None): if not user_id: with OpenDB() as con: sql = """ SELECT * FROM book_list.user_information""" con.execute(sql) res = con.fetchall() return res
def get_comment_by_book_id(book_id): with OpenDB() as con: sql = "SELECT * FROM book_list.comment where book_id={} LIMIT 0, 1000".format( book_id) con.execute(sql) res = con.fetchall() comment_list = [i for i in res] return comment_list
def get_create_user_name_by_book_list_id(book_list_id): with OpenDB() as con: sql = "SELECT * FROM user_book_list,user_information where book_list_id={} and user_information.ssid=user_book_list.user_id".format( book_list_id) print(sql) con.execute(sql) res = con.fetchone() return res[6]
def get_user_id_by_name(user_name): with OpenDB() as con: sql = "select ssid from user_information where user='******'".format( user_name) print(sql) con.execute(sql) res = con.fetchone()[0] # print(res[0]) return res
def delete_book_list_by_book_list_id(book_list_id): with OpenDB() as con: sql = """DELETE FROM `book_list`.`user_book_list` WHERE (`book_list_id` = '{}')""".format( book_list_id) con.execute(sql) sql = """DELETE FROM `book_list`.`book_list` WHERE (`ssid` = '{}')""".format( book_list_id) con.execute(sql) return True
def update_like_number_by_user_id(user_id, book_list_id): with OpenDB() as con: sql = """UPDATE `book_list`.`book_list` SET like_number=like_number+1 WHERE (`ssid` = '{}'); """.format(book_list_id) con.execute(sql) sql = """INSERT INTO `book_list`.`user_like_book_record` (`user_id`, `book_list_id`, `like_number`) VALUES ('{}', '{}', '1'); """.format(user_id, book_list_id) con.execute(sql)
def get_user_focus_list(user_name): with OpenDB() as con: user_id = get_user_id_by_name(user_name) sql = "select book_list_id from user_book_list where user_id={} and type=2 ".format( user_id) con.execute(sql) res = con.fetchall() user_focus_list = [] for i in res: user_focus_list.append(i[0]) return list(set(user_focus_list))
def get_list(type, user_id=None): if type == "new": with OpenDB() as con: sql = 'select book_list_name,type,ssid,like_number from book_list order by create_time desc limit 0,10 ' con.execute(sql) res = con.fetchall() return res elif type == "elite": with OpenDB() as con: sql = 'select book_list_name,type,like_number,ssid from book_list order by like_number desc limit 0,10' con.execute(sql) res = con.fetchall() return res elif type == "you_like": if user_id == None: return None else: you_like_book_list = get_you_like(user_id) # you_like_book_list : [{ssid:"aaa",like_number:"aaa"}] return you_like_book_list
def update_book_information_by_book_id(book_id, like_type): with OpenDB() as con: if like_type == 1: sql = "update book_imformation set like_number=like_number+1 where ssid={}".format( book_id) print(sql) else: sql = "update book_imformation set unlike_number=unlike_number+1 where ssid={}".format( book_id) con.execute(sql) return True
def get_user_comment(): with OpenDB() as con: # sql = from comment,book_imformation B,user_information U where comment.user_id=U.ssid and comment.book_id=B.ssid" sql = "select * from comment,book_imformation B,user_information U,book_list where comment.user_id=U.ssid and comment.book_id=B.ssid and book_list.ssid=comment.book_list order by time desc limit 50" con.execute(sql) res = con.fetchall() sql = "SELECT comment_id,sum(like_number) FROM book_list.comment_like_record group by comment_id" con.execute(sql) res2 = con.fetchall() comment_dict = {i[0]: i[1] for i in res2} comment_list = [i for i in res] return comment_list, comment_dict
def get_user_other_book_list_by_user_name(user_nane): user_id = get_user_id_by_name(user_nane) with OpenDB() as con: sql = "SELECT * FROM book_list.user_book_list,book_list where user_id={} and book_list_id=book_list.ssid group by book_list_id".format( user_id) con.execute(sql) ress = con.fetchall() book_list = [] for res in ress: book_dict = dict(user_id=res[1], book_list_id=res[2], book_list_name=res[6]) book_list.append(book_dict) return book_list
def delete_focus(): user_name = session.get('user_name') user_id = tool.get_user_id_by_name(user_name) a = request.values # 把Ajax中的数据取出来 for i in a: i = eval(i) dict2 = i book_list_id = dict2['book_list_id'] with OpenDB() as con: sql = "DELETE FROM `book_list`.`user_book_list` WHERE `user_id` = '{}' and book_list_id='{}' and type=2".format( user_id, book_list_id) print(sql) con.execute(sql) return redirect(url_for('my_focus_list'))
def search_book_name(book_name): with OpenDB() as con: print(book_name) if book_name == "": return None sql = "select book_name,author from book_imformation where book_name like '%{}%'".format( book_name) con.execute(sql) res = con.fetchall() k = [] for i in res: book_name = i[0] author = i[1] k.append({"book_name": book_name, "author": author}) return k
def update_focus(): user_name = session.get('user_name') user_id = tool.get_user_id_by_name(user_name) a = request.values # 把Ajax中的数据取出来 for i in a: i = eval(i) dict2 = i book_list_id = dict2['book_list_id'] with OpenDB() as con: sql = "INSERT INTO `book_list`.`user_book_list` (`user_id`, `book_list_id`, `type`) VALUES ({}, {}, '2')".format( user_id, book_list_id) print(sql) con.execute(sql) return redirect(url_for('all_book_list'), 200)
def get_all_book_list_infomation(): with OpenDB() as con: sql = """select comment.comment,count(book_list_id),user_book_list.user_id,book_list_id,book_list.type,book_list_name from user_book_list , user_information,book_list,comment where user_information.ssid=user_book_list.user_id and book_list.ssid=user_book_list.book_list_id and comment.user_id=user_book_list.user_id and comment.book_list=user_book_list.book_list_id group by book_list_id""" con.execute(sql) res = con.fetchall() book_list = [] for i in res: book_dict = dict(book_list_id=i[3], book_list_name=i[5], created_userid=i[-1], type=i[4], sum_of_booklist=i[1]) book_list.append(book_dict) return book_list
def get_user_book_list_infomation_by_user_name(user_name): user_id = get_user_id_by_name(user_name) with OpenDB() as con: sql = """select user_book_list.user_id,book_list_id,book_list.type,user_book_list.type,book_list_name from user_book_list , user_information,book_list where user_information.ssid=user_book_list.user_id and book_list.ssid=user_book_list.book_list_id and user_book_list.type=2 and user_information.ssid={}""".format(user_id) con.execute(sql) res = con.fetchall() book_list = [] for i in res: book_dict = dict( book_list_id=i[1], book_list_name=i[4], created_userid=i[-1], type=i[2], ) book_list.append(book_dict) return book_list
def get_book_list_information_by_book_list_name(book_list_name): with OpenDB() as con: sql = 'select B.ssid,B.book_name,B.like_number,B.author,B.intro,B.unlike_number,C.comment,C.star_num from comment C,book_imformation B ' \ 'where C.book_id=B.ssid and C.book_list=(select ssid from book_list where book_list_name={})'.format( book_list_name) con.execute(sql) res = con.fetchall() book_list_detail = [] for i in res: book_dict = dict( book_id=i[0], book_name=i[1], like_number=i[2], author=i[3], intro=i[4], unlike_number=i[5], comment=i[6], star_num=i[7], ) book_list_detail.append([book_dict]) return book_list_detail,
def get_book_list_detail_information_by_book_list_id(book_list_id): with OpenDB() as con: sql = "SELECT * FROM book_list.comment,book_imformation,book_list B where book_list={} and book_imformation.ssid=comment.book_id and B.ssid=book_list".format( book_list_id) con.execute(sql) res = con.fetchall() book_list = [] for i in res: book_dict = dict(book_id=i[2], book_name=i[8], comment=i[3], star_num=i[5], author=i[10], intro=i[11], book_list_intro=i[-1], like_number=i[-9], unlike_number=i[-8], book_list_name=i[-6]) book_list.append(book_dict) print(book_list) return book_list
def get_book_information_by_book_name(book_name): with OpenDB() as con: sql = "select * from book_imformation where book_name='{}'".format( book_name) print(sql) con.execute(sql) res = con.fetchone() print(res) book_list = [] book_dict = dict(book_id=res[0], book_name=res[1], update_time=res[2], author=res[3], intro=res[4], like_number=res[9], unlike_number=res[10]) book_list.append(book_dict) print(book_list) return book_list
def login(): if request.method == 'POST': user = request.form['username'] password = request.form['password'] with OpenDB()as con: sql = 'select user,password from user_information where user="******"and password="******"'.format(user, password) # try: con.execute(sql) number = len(con.fetchall()) if number == 0: flash('') print('账号或密码错误,请重新aaa登陆') return render_template('登录框.html') else: print('登录成功') session['user_name'] = user return redirect(url_for('index')) else: return render_template('登录框.html')
def get_book_infomation_by_type(type=None): with OpenDB() as con: if not type: sql = "select * from book_imformation " else: sql = "select * from book_imformation where type={}".format( int(type)) con.execute(sql) res = con.fetchall() book_list = [] for i in res: book_dict = dict(book_id=i[0], book_name=i[1], update_time=i[2], author=i[3], intro=i[4], like_number=i[9], unlike_number=i[10]) book_list.append(book_dict) return book_list
def update_user_password_by_user_id(user_id, passowrd): with OpenDB() as con: sql = """UPDATE `book_list`.`user_information` SET `password` = '{}' WHERE (`ssid` = '{}')""".format( passowrd, user_id) con.execute(sql) return True
def insert_new_user(user_name, password): with OpenDB() as con: sql = """INSERT INTO `book_list`.`user_information` (`user`, `password`) VALUES ('{}', '{}') """.format(user_name, password) con.execute(sql) return True
def delete_user_information_by_user_id(user_id): with OpenDB() as con: sql = """DELETE FROM `book_list`.`user_information` WHERE (`ssid` = '{}') """.format(user_id) con.execute(sql) return True
def get_book_list_by_create_user(user_name): user_id = get_user_id_by_name(user_name) with OpenDB() as con: sql = "select * from "