def set_goal(user_id, year, month, goal): # SQL conn = connect_sys_db() query = "INSERT INTO monthly_goal VALUES(\'{user_id}\', \'{year}\', \'{month}\', \'{goal}\')".format( user_id=user_id, year=year, month=month, goal=goal) with mysql(conn) as cursor: cursor.execute(query)
def get_read_history(user_id): collection_id = Collection.get_readcollection_id(user_id) # SQL conn = connect_sys_db() query = "SELECT book_id FROM collects WHERE collection_id = \'{collection_id}\'".format( collection_id=collection_id) db_result = read_sql(sql=query, con=conn) json_str = db_result.to_json(orient='index') ds = json.loads(json_str) result = [] for index in ds: finish_date = Collection.get_book_read_date( user_id, ds[index]['book_id']) # Add book's finish_time and title and cover_url to result ds[index]['finish_time'] = finish_date book = Book(ds[index]['book_id']) ds[index]['book_title'] = book.get_info().title ds[index]['book_cover_url'] = book.get_info().book_cover_url # Reformat result need for front-end # timeArray = time.strptime(finish_date, "%Y-%m-%d %H:%M:%S") date = datetime.datetime.fromtimestamp(int(finish_date) / 1000) target, finish_num, finish_flag = Collection.get_tag( user_id, date.year, date.month) ds[index]['tag'] = { 'target': target, 'finish_num': finish_num, 'finish_flag': finish_flag } result.append(ds[index]) return result
def get_read_history_by_date(user_id, year, month): # set default start date start_date = str(year) + "-" + str(month) + "-01 00:00:00" # set default finish date if month is 12: finish_date = str(year) + "-" + str(1) + "-01 00:00:00" else: finish_date = str(year) + "-" + str(month + 1) + "-01 00:00:00" # date -> timestamp start_timestamp = int( time.mktime(time.strptime(start_date, "%Y-%m-%d %H:%M:%S"))) * 1000 finish_timestamp = int( time.mktime(time.strptime(finish_date, "%Y-%m-%d %H:%M:%S"))) * 1000 collection_id = Collection.get_readcollection_id(user_id) # SQL conn = connect_sys_db() query = "SELECT book_id FROM collects WHERE collection_id = \'{collection_id}\'".format( collection_id=collection_id) db_result = read_sql(sql=query, con=conn) json_str = db_result.to_json(orient='index') ds = json.loads(json_str) result = [] for index in ds: finish_date = Collection.get_book_read_date( user_id, ds[index]['book_id']) # Compare finish time to make sure whether this book is finished in this month if start_timestamp <= finish_date < finish_timestamp: ds[index]['finish_time'] = finish_date book = Book(ds[index]['book_id']) ds[index]['book_title'] = book.get_info().title ds[index]['book_cover_url'] = book.get_info().book_cover_url result.append(ds[index]) return result
def get_popular_book(): Book.create_view_tables() # SQL conn = connect_sys_db() query = 'select distinct books.id, books.title, books.book_cover_url, view4.collect_time, view2.read_time, ' \ 'view3.avg_rating, books.google_rating from books left join collects on books.id = collects.book_id ' \ 'left join review_rate on books.id = review_rate.book_id left join view4 on books.id = view4.id left ' \ 'join view2 on books.id = view2.id left join view3 on books.id = view3.id ' db_result = read_sql(sql=query, con=conn) db_result = db_result.fillna(0) # Popular grade formula db_result.insert( 6, 'popular', db_result.collect_time * 0.1 + db_result.read_time * 0.2 + db_result.avg_rating * 0.3 + db_result.google_rating * 0.4) # Sort dataframe by popular db_result = db_result.sort_values(by=['popular'], ascending=False) # Get first ten books in the list db_result = db_result.head(10) json_str = db_result.to_json(orient='index') ds = json.loads(json_str) result = [] for index in ds: result.append({ 'id': ds[index]['id'], 'title': ds[index]['title'], 'book_cover_url': ds[index]['book_cover_url'] }) return result
def book_search_length(input, category, rating_from, rating_to): # SQL conn = connect_sys_db() query = "select id, authors, title, ISBN13, book_cover_url, description, publisher, published_date, categories, average from \ (select books.id, books.title, books.authors, books.ISBN13, books.book_cover_url, books.description, books.publisher, books.published_date, books.categories, avg(review_rate.rating) as average \ from books left join review_rate on books.id = review_rate.book_id \ where books.title like \'%{input}%\' or books.authors like \'%{input}%\' or books.isbn13 like \'%{input}%\' \ group by books.id \ order by average desc) as subquery \ where ((average >= \'{rating_from}\' and average <= \'{rating_to}\'))".format( input=input, rating_from=rating_from, rating_to=rating_to) # Reformat query if filter has no restriction of rating_from if rating_from == 0: query = query.rstrip(")") query += ") or average is null)" # Reformat query if filter has no restriction of category if category is not "": query += " and categories = \"" + category + "\"" db_result = read_sql(sql=query, con=conn) json_str = db_result.to_json(orient='index') ds = json.loads(json_str) result = [] for index in ds: result.append(ds[index]) return len(result), result
def get_username(self): # SQL conn = connect_sys_db() query = "Select username FROM users WHERE id = \'{id}\'".format( id=self._id) db_result = read_sql(sql=query, con=conn) return db_result.iloc[0].username
def get_num_book_collection(collection_id): # SQL conn = connect_sys_db() query = "SELECT count(*) as num FROM collects WHERE collection_id = \'{collection_id}\'".format( collection_id=collection_id) db_result = read_sql(sql=query, con=conn) return int(db_result.iloc[0].num)
def update_username(self, new_username): # SQL conn = connect_sys_db() query = 'UPDATE users SET username = \'{new_username}\' WHERE id = \'{id}\''.format( id=self._id, new_username=new_username) with mysql(conn) as cursor: cursor.execute(query)
def update_email(self, new_email): # SQL conn = connect_sys_db() query = 'UPDATE users SET email = \'{new_email}\' WHERE id = \'{id}\''.format( id=self._id, new_email=new_email) with mysql(conn) as cursor: cursor.execute(query)
def register_account(username, password, admin, email): # If username already existed if User.is_user_exists_by_username(username): return False, 'This username has already been registered' # SQL conn = connect_sys_db() query = 'SELECT email FROM users WHERE email = \'{email}\''.format( email=email) db_result = read_sql(sql=query, con=conn) # If email address already been registered if not db_result.empty: return False, 'This email has already been registered' # SQL query = 'INSERT INTO users VALUES(0, \'{username}\',' \ 'HEX(AES_ENCRYPT(\'{password}\', \'{key}\')), \'{admin}\', \'{email}\')' \ .format( username=username, password=password, admin=admin, email=email, key=SECRET_KEY ) with mysql(conn) as cursor: cursor.execute(query) return True, ''
def get_num_collection(user_id): # SQL conn = connect_sys_db() query = "SELECT count(*) as num FROM collections WHERE (user_id = \'{user_id}\')".format( user_id=user_id) db_result = read_sql(sql=query, con=conn) return int(db_result.iloc[0].num) - 1
def update_goal(user_id, year, month, goal): # SQL conn = connect_sys_db() query = "UPDATE monthly_goal SET goal = \'{goal}\' WHERE (user_id = \'{user_id}\' AND year = \'{year}\' AND month = \'{month}\')".format( user_id=user_id, year=year, month=month, goal=goal) with mysql(conn) as cursor: cursor.execute(query)
def get_collection_name(self): # SQL conn = connect_sys_db() query = "SELECT name as collection_name FROM collections WHERE id = \'{id}\'".format( id=self._id) db_result = read_sql(sql=query, con=conn) return db_result.iloc[0].collection_name
def delete_collection(collection_id): # SQL conn = connect_sys_db() query = "DELETE FROM collections WHERE (id = \'{id}\')".format( id=collection_id) with mysql(conn) as cursor: cursor.execute(query)
def get_num_read_collection(user_id, collection_id): read_collection_id = Collection.get_readcollection_id(user_id) # SQL conn = connect_sys_db() query = "select book_id from(select book_id from collects where collection_id = \'{collection_id}\' UNION all select book_id from collects where collection_id = \'{read_collection_id}\')a group by book_id having count(*) > 1".format( collection_id=collection_id, read_collection_id=read_collection_id) db_result = read_sql(sql=query, con=conn) return int(db_result.size)
def is_book_exists_by_id(id): # SQL conn = connect_sys_db() query = 'SELECT id FROM books Where id = \'{id}\''.format(id=id) db_result = read_sql(sql=query, con=conn) if db_result.empty: return False else: return True
def move_book_to_another_collection(self, new_collection_id, book_id): # SQL conn = connect_sys_db() query = "UPDATE collects SET collection_id = \'{new_collection_id}\' WHERE (book_id = \'{book_id}\' AND collection_id = \'{old_collection_id}\')".format( new_collection_id=new_collection_id, old_collection_id=self._id, book_id=book_id) with mysql(conn) as cursor: cursor.execute(query)
def is_review_exist_by_both_id(user_id, book_id): # SQL conn = connect_sys_db() query = "SELECT user_id, username, book_id, rating, review_content, review_time FROM review_rate WHERE (user_id = \'{user_id}\' AND book_id = \'{book_id}\') ORDER BY review_time DESC".format( user_id=user_id, book_id=book_id) db_result = read_sql(sql=query, con=conn) if db_result.empty: return False else: return True
def is_user_exists_by_username(username): # SQL conn = connect_sys_db() query = 'SELECT username FROM users Where username = \'{username}\''.format( username=username) db_result = read_sql(sql=query, con=conn) if db_result.empty: return False else: return True
def is_user_exists_by_email(email): # SQL conn = connect_sys_db() query = 'SELECT email FROM users Where email = \'{email}\''.format( email=email) db_result = read_sql(sql=query, con=conn) if db_result.empty: return False else: return True
def mark_as_unread(user_id, book_id): read_collection_id = Collection.get_readcollection_id(user_id) # SQL conn = connect_sys_db() query = "DELETE FROM collects WHERE (collection_id = \'{collection_id}\' AND book_id = \'{book_id}\')".format( book_id=book_id, collection_id=read_collection_id, ) with mysql(conn) as cursor: cursor.execute(query)
def is_book_exists_in_collection(collection_id, book_id): # SQL conn = connect_sys_db() query = 'SELECT * FROM collects Where (book_id = \'{book_id}\' AND collection_id = \'{collection_id}\')'.format( book_id=book_id, collection_id=collection_id) db_result = read_sql(sql=query, con=conn) if db_result.empty: return False else: return True
def delete_review(user_id, book_id): # SQL conn = connect_sys_db() query = 'DELETE FROM review_rate WHERE book_id = \'{book_id}\' AND user_id = \'{user_id}\'' \ .format( book_id=book_id, user_id=user_id ) with mysql(conn) as cursor: cursor.execute(query)
def get_readcollection_id(user_id): # SQL conn = connect_sys_db() query = "SELECT id FROM collections WHERE (user_id = \'{user_id}\' and name = 'read')".format( user_id=user_id) db_result = read_sql(sql=query, con=conn) if db_result.empty: return None else: return db_result.iloc[0].id
def is_collection_exists_by_name(user_id, collection_name): # SQL conn = connect_sys_db() query = "SELECT * FROM collections WHERE (user_id = \'{user_id}\' AND name = \'{collection_name}\')".format( user_id=user_id, collection_name=collection_name) db_result = read_sql(sql=query, con=conn) if db_result.empty: return False else: return True
def is_collection_exists_by_id(collection_id): # SQL conn = connect_sys_db() query = "SELECT * FROM collections WHERE id = \'{id}\'".format( id=collection_id) db_result = read_sql(sql=query, con=conn) if db_result.empty: return False else: return True
def get_user_list(): conn = connect_sys_db() sql = 'select id, username, admin from users' users = read_sql(con=conn, sql=sql) json_str = users.to_json(orient='index') ds = json.loads(json_str) result = [] for index in ds: result.append(ds[index]) return {"list": result}
def delete_user(username): if not User.is_user_exists_by_username(username): return False conn = connect_sys_db() # SQL query = 'DELETE FROM users WHERE username = \'{username}\' AND admin = \'{admin}\'' \ .format(username=username, admin=0) with mysql(conn) as cursor: cursor.execute(query) return True
def is_goal_exists_by_goal(user_id, year, month, goal): # SQL conn = connect_sys_db() query = "SELECT * FROM monthly_goal WHERE (user_id = \'{user_id}\' AND year = \'{year}\' AND month = \'{month}\' AND goal = \'{goal}\')".format( user_id=user_id, year=year, month=month, goal=goal) db_result = read_sql(sql=query, con=conn) if db_result.empty: return False else: return True
def is_book_read(user_id, book_id): collection_id = Collection.get_readcollection_id(user_id) # SQL conn = connect_sys_db() query = "SELECT book_id FROM collects WHERE (book_id = \'{book_id}\' AND collection_id = \'{collection_id}\')".format( book_id=book_id, collection_id=collection_id) db_result = read_sql(sql=query, con=conn) if db_result.empty: return False else: return True