def sql_process_information(): result_dict = {} sql1 = "select id,user_account,readers,cast(time as char) time, feedbacks from feedback fd INNER join user on fd.user_id = user.user_id and fd.state = '1'" result_dict["untreated"] = mysql_module(sql1)[1] sql2 = "select id,user_account,readers,cast(time as char) time, feedbacks from feedback fd INNER join user on fd.user_id = user.user_id and fd.state = '0'" result_dict["processed"] = mysql_module(sql2)[1] return result_dict
def cancel_collect_book(user_name, book_id): sql_user_id = "select user_id from user where user_account = '{}'".format( user_name) user_id = mysql_module(sql_user_id)[1][0]['user_id'] sql = "DELETE from my_bookshelf where user_id = {} and book_id = '{}'".format( user_id, book_id) mysql_module(sql) return [True, "删除成功"]
def sql_voluntary_activities(contestant, user_name, phone, email, days, times): sql_user_id = "select user_id from user where user_account = '{}'".format( user_name) user_id = mysql_module(sql_user_id)[1][0]['user_id'] sql_select = "select id from voluntary_activities where user_id = {}".format( user_id) if mysql_module(sql_select)[1]: return [False, "您已报名过该活动"] sql = "insert into voluntary_activities(contestant,phone,email,days,times,user_id) values('{}','{}','{}','{}','{}',{})".format( contestant, phone, email, days, times, user_id) result = mysql_module(sql) return result
def sql_thematic_activities(contestant, phone, works, user_name): sql_user_id = "select user_id from user where user_account = '{}'".format( user_name) user_id = mysql_module(sql_user_id)[1][0]['user_id'] sql_select = "select id from thematic_activities where user_id = {}".format( user_id) if mysql_module(sql_select)[1]: return [False, "您已报名过该活动"] sql = "insert into thematic_activities(contestant,phone,works,user_id) values('{}','{}','{}',{})".format( contestant, phone, works, user_id) result = mysql_module(sql) return result
def sql_collect_book(user_name, book_id): sql_user_id = "select user_id from user where user_account = '{}'".format( user_name) user_id = mysql_module(sql_user_id)[1][0]['user_id'] sql_select = "select id from my_bookshelf where user_id = {} and book_id = {}".format( user_id, book_id) if mysql_module(sql_select)[1]: return [False, "该书已在您的书架中"] sql = "insert into my_bookshelf(user_id,book_id) values('{}','{}')".format( user_id, book_id) mysql_module(sql) return [True, "收藏成功"]
def sql_add_book_category(category1, category2): sql = "select id from book_category where category1 = '{}' and category2 = '{}'".format( category1, category2) result = mysql_module(sql) if not result[0]: return [False, "信息查询失败"] if result[1]: return [False, "该分类已存在"] sql = "insert into book_category(category1,category2) values ('{}','{}')".format( category1, category2) result = mysql_module(sql) if not result[0]: return [False, "分类保存失败"] return [True, "分类保存成功"]
def user_login(username, password, code): if code == '0': sql = 'select user_id from user where user_account = "{}" and user_password = "******"'.format( username, password) result = mysql_module(sql) if not result[1]: return [False, "账号或密码错误"] return [True, result[1][0]["user_id"]] elif code == '1': sql = 'select work_id from admin where work_id = "{}" and work_password = "******"'.format( username, password) result = mysql_module(sql) if not result[1]: return [False, "账号或密码错误"] return [True, result[1][0]["work_id"]]
def sql_borrowing_books(user_id): sql = "select book.book_id,book_name,book.book_publisher,cast(borrow_time as char) borrow_time,cast(return_time as char) return_time,book_room from (select book_id,book_name,book_publisher,book_room from book_info where book_id in (select book_id from borrow_info where user_id = {} and state = 1)) book LEFT JOIN (select borrow_time,return_time,book_id from borrow_info where user_id = {} and state = 1) borrow on book.book_id = borrow.book_id ".format( user_id, user_id) result = mysql_module(sql) if not result[0]: return [False, "查询出错"] return result
def sql_my_bookshelf(user_id): sql = 'SELECT book_id, book_name,book_publisher,book_auther, book_room FROM book_info WHERE book_id IN ( SELECT book_id FROM my_bookshelf WHERE user_id = {} )'.format( user_id) result = mysql_module(sql) if not result[0]: return [False, "查询出错"] return [True, result[1]]
def sql_query_book_info_1(book_name, user_id): sql = "select book_id,book_name, book_img_path from book_info where instr(book_name,'{}') and book_state = '1' and book_id in ( select book_id from borrow_info where user_id = {})".format( book_name, user_id) result = mysql_module(sql) if not result[0]: return [False, "用户信息查询失败"] return [True, result[1]]
def sql_query_photo(username): sql1 = 'select user_photo from user where user_account = "{}"'.format( username) result1 = mysql_module(sql1) if result1[1]: return result1[1][0]["user_photo"] return None
def sql_update_info(user_account, user_name, user_sex, email, phone): sql = "UPDATE user SET user_email = '{}', user_phone = '{}',user_name = '{}',user_sex = '{}' WHERE user_account = '{}'".format( email, phone, user_name, user_sex, user_account) result = mysql_module(sql) if not result[0]: return [False, "信息更新失败"] return [True]
def sql_update_password(user_account, new_password): sql = "update user set user_password = '******' where user_account = '{}'".format( new_password, user_account) result = mysql_module(sql) if not result[0]: return [False, "密码更改失败"] return [True]
def sql_email(user_name): sql = "select user_email from user where user_account = '{}' ".format( user_name) result = mysql_module(sql) if not result[1]: return [False, "用户名不存"] return [True, result[1][0]["user_email"]]
def sql_borrowed_records(user_id): sql = 'SELECT book.book_id,book_name,book_auther,book.book_publisher, cast(borrow_time as char) as borrow_time,cast(actual_return_time as char) as actual_return_time,book_room FROM ( SELECT book_id,book_publisher, book_name, book_auther, book_room FROM book_info WHERE book_id IN ( SELECT book_id FROM borrow_info WHERE user_id = {} ) ) AS book LEFT JOIN borrow_info borrow ON borrow.book_id = book.book_id and borrow.user_id = 21'.format( user_id) result = mysql_module(sql) if not result[0]: return [False, "查询出错"] return [True, result[1]]
def sql_query_book_info(book_id): sql = "select book_id,book_name,book_auther,book_category,book_publisher,book_room,book_bookshelf,book_synopsis,book_state,cast(book_publication_date as char) as book_publication_date ,cast(books_add_time as char) as books_add_time, book_language,book_img_path from book_info where book_id = '{}' ".format( book_id) result = mysql_module(sql) if not result[0]: return [False, "查询出错"] return result
def sql_feedbacks(user_id, readers, phone, feedbacks): st = set_time() sql = "insert into feedback(user_id,readers,phone,feedbacks,state,time) value ('{}','{}','{}','{}','1','{}')".format( user_id, readers, phone, feedbacks, st.today()) result = mysql_module(sql) if not result[0]: return False return True
def sql_query_book_info_0(book_name): sql = "select book_id,book_name,book_img_path from book_info where book_state = '0'" if book_name: sql = "select book_id,book_name,book_img_path from book_info where instr(book_name,'{}') and book_state = '0'".format( book_name) result = mysql_module(sql) if not result[0]: return [False, "用户信息查询失败"] return [True, result[1]]
def sql_verify_old_password(user_account, verify_password): sql = "select user_account from user where user_account = '{}' and user_password = '******'".format( user_account, verify_password) result = mysql_module(sql) if not result[0]: return [False, "查询出错"] if not result[1]: return [False, '原密码错误'] return [True]
def into_register_info(user_account, user_phone, username, password, email): st = set_time() select_user = "******".format( username) result = mysql_module(select_user) if result[1]: return [False, "该昵称已存在"] select_user = "******".format( username) result = mysql_module(select_user) if result[1]: return [False, "该昵称已存在"] into_user = '******' \ 'user_phone,user_registration_time) VALUES("{}","{}","{}","{}","{}","{}")'.format( user_account, password, username, email, user_phone, st.today()) result = mysql_module(into_user) if not result[0]: return [False, "注册失败"] return [True, "注册成功"]
def sql_query_user_info(user_name): if user_name: sql = "select user_id,user_account, user_email, cast(user_registration_time as char) as user_registration_time from user where instr(user_account,'{}')".format( user_name) else: sql = "select user_id,user_account, user_email, cast(user_registration_time as char) as user_registration_time from user" result = mysql_module(sql) if not result[0]: return [False, "用户信息查询失败"] return [True, result[1]]
def sql_change_book_info(**kwargs): list_key = list(kwargs.keys()) list_value = list(kwargs.values()) sql = "UPDATE book_info SET {} = '{}',{} = '{}',{} = '{}',{} = {},{} = '{}',{} = '{}',{} = '{}',{} = '{}',{} = '{}',{} = '{}' where {} = '{}'".format( list_key[0], list_value[0], list_key[1], list_value[1], list_key[2], list_value[2], list_key[3], list_value[3], list_key[4], list_value[4], list_key[5], list_value[5], list_key[6], list_value[6], list_key[7], list_value[7], list_key[8], list_value[8], list_key[9], list_value[9], list_key[10], list_value[10]) return mysql_module(sql)
def insertnewbook(**kwargs): list_key = list(kwargs.keys()) list_value = list(kwargs.values()) sql = "insert into book_info ({},{},{},{},{},{},{},{},{},{},{},{}) values ('{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}')".format( list_key[0], list_key[1], list_key[2], list_key[3], list_key[4], list_key[5], list_key[6], list_key[7], list_key[8], list_key[9], list_key[10], list_key[11], list_value[0], list_value[1], list_value[2], list_value[3], list_value[4], list_value[5], list_value[6], list_value[7], list_value[8], list_value[9], list_value[10], list_value[11]) into_result = mysql_module(sql) if not into_result[0]: return [False, "数据存储失败"] return [True]
def sql_class_lookup(category1, category2, language): sql1 = "select id from book_category where 1 = 1 " if category1: sql1 = sql1 + " and category1 = '{}'".format(category1) if category2: sql1 = sql1 + " and category2 = '{}'".format(category2) sql2 = "select book_id,book_name,book_auther,book_img_path from book_info where book_category in ({}) ".format( sql1) if language: sql2 = sql2 + " and book_language = '{}'".format(language) result = mysql_module(sql2) return result
def sql_delete_user(user_name): sql_id = "select user_id from user where user_account = '{}'".format( user_name) user_id = mysql_module(sql_id)[1][0]["user_id"] sql_de_bookshelf = "delete from my_bookshelf where user_id = '{}'".format( user_id) sql_de_feedback = "delete from feedback where user_id = '{}'".format( user_id) sql_de_borrow = "delete from borrow_info where user_id = '{}'".format( user_id) sql_de_user = "******".format(user_id) result = mysql_modules(sql_de_bookshelf, sql_de_feedback, sql_de_borrow, sql_de_user) return result
def sql_borrowing_condition(): st = set_time() time_dict = st.first_half_year() key = list(time_dict.keys()) times = list(time_dict.values()) result_list = {} number = [] month = [] sql = 'SELECT * from (select count(borrow_id) number from borrow_info where instr(borrow_time,"{}") UNION ALL select count(borrow_id) number from borrow_info where instr(borrow_time,"{}") UNION ALL select count(borrow_id) number from borrow_info where instr(borrow_time,"{}") UNION ALL select count(borrow_id) number from borrow_info where instr(borrow_time,"{}") UNION ALL select count(borrow_id) number from borrow_info where instr(borrow_time,"{}") UNION ALL select count(borrow_id) number from borrow_info where instr(borrow_time,"{}")) as total'.format( times[0], times[1], times[2], times[3], times[4], times[5]) result = mysql_module(sql) for i in range(6): number.append(result[1][i]['number']) month.append(str(key[i]) + "月") result_list["number"] = number result_list["month"] = month return result_list
def sql_conditional_book_info(book_id, book_name, book_publisher, book_room, book_state): sql = "1 = 1" if book_id: sql = sql + " and book_id = {}".format(book_id) if book_name: sql = sql + " and instr(book_name,'{}')".format(book_name) if book_publisher: sql = sql + " and instr(book_publisher,'{}')".format(book_publisher) if book_room: sql = sql + " and instr(book_room,'{}')".format(book_room) if book_state: sql = sql + " and book_state = '{}'".format(book_state) sql = "select book_id,book_code,book_name,book_auther,category1,category2,book_publisher,book_room,book_bookshelf,book_synopsis,cast(book_publication_date as char) book_publication_date,book_language,book_state from book_info inner JOIN book_category on id = book_category where {}".format( sql) result = mysql_module(sql) return result
def sql_new_arrivals(today_time, past_time, language, category1, category2): sql1 = "where 1 = 1" sql_cate = "select id from book_category where 1 = 1" if today_time: sql1 = sql1 + " and books_add_time<='{}'".format(today_time) if past_time: sql1 = sql1 + " and books_add_time >='{}' ".format(past_time) if language: sql1 = sql1 + " and book_language = '{}'".format(language) if category1: sql_cate = sql_cate + " and category1 = '{}'".format(category1) if category2: sql_cate = sql_cate + " and category2 = '{}'".format(category2) sql2 = 'select book_id,book_name,book_auther, book_img_path from book_info {} and book_category in ({}) order by books_add_time desc limit 50 '.format( sql1, sql_cate) result = mysql_module(sql2) return [True, result[1]]
def sql_popular_recommendation(today_time, past_time, language, category1, category2): sql1 = "where 1 = 1" sql_cate = "select id from book_category where 1 = 1" if today_time: sql1 = sql1 + " and books_add_time<='{}'".format(today_time) if past_time: sql1 = sql1 + " and books_add_time >='{}' ".format(past_time) if language: sql1 = sql1 + " and book_language = '{}'".format(language) if category1: sql_cate = sql_cate + " and category1 = '{}'".format(category1) if category2: sql_cate = sql_cate + " and category2 = '{}'".format(category2) sql2 = "select distinct book.book_id,book.book_name,book_auther, count(book.book_id) as count, book.book_img_path from borrow_info borrow left join book_info book on borrow.book_id = book.book_id {} and book.book_category in ({}) group by book_id order by count desc limit 50".format( sql1, sql_cate) result = mysql_module(sql2) return [True, result[1]]
def sql_conditional_borrow_record(book_id, book_name, book_publisher, borrow_time_satrt, borrow_time_end, user_name): sql = "select borrow_id,book.book_id, book.book_code, book.book_name,book.book_publisher,cast(borrow.borrow_time as char) borrow_time,cast(actual_return_time as char) actual_return_time,user.user_account from borrow_info borrow inner join book_info book on borrow.book_id = book.book_id inner join user on borrow.user_id = user.user_id where borrow.state = '0'" if book_id: sql = sql + " and instr(borrow.book_id,'{}')".format(book_id) if book_name: sql = sql + " and instr(book_name,'{}')".format(book_name) if book_publisher: sql = sql + " and instr(book_publisher,'{}')".format(book_publisher) if borrow_time_satrt: sql = sql + " and borrow_time >= '{}'".format(borrow_time_satrt) if borrow_time_end: sql = sql + " and borrow_time <= '{}'".format(borrow_time_end) if user_name: sql = sql + " and instr(user_account,'{}')".format(user_name) result = mysql_module(sql) return result