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 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 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 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 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 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 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 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 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 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 mark_as_read(user_id, book_id, date): read_collection_id = Collection.get_readcollection_id(user_id) date = date + "-10 10:00:00" print(date) # SQL conn = connect_sys_db() query = "INSERT INTO collects VALUES(\'{book_id}\', \'{collection_id}\', \'{collect_time}\')".format( book_id=book_id, collection_id=read_collection_id, # collect_time="2020-06-29 06:06:18.423409" collect_time=date) with mysql(conn) as cursor: cursor.execute(query)
def create_view_tables(): # SQL conn = connect_sys_db() query1 = "create or replace view view4 as select books.id, count(*) as collect_time from books join collects " \ "on books.id = collects.book_id group by books.id " query2 = "create or replace view view2 as select books.id, count(*) as read_time from books join collects on " \ "books.id = collects.book_id join collections on collects.collection_id = collections.id where " \ "collections.name = 'read' group by books.id " query3 = "create or replace view view3 as select books.id, avg(review_rate.rating) as avg_rating from books " \ "join review_rate on books.id = review_rate.book_id group by books.id " with mysql(conn) as cursor: cursor.execute(query1) cursor.execute(query2) cursor.execute(query3)
def post_new_collection(user_id, name): # SQL conn = connect_sys_db() query = "SELECT * FROM collections WHERE (user_id = \'{user_id}\' AND name = \'{name}\')".format( user_id=user_id, name=name) db_result = read_sql(sql=query, con=conn) # If collection's name already existed if db_result.empty: query = "INSERT INTO collections VALUES(0,\'{user_id}\',\'{name}\',\'{time}\')".format( user_id=user_id, name=name, time=datetime.datetime.utcnow()) with mysql(conn) as cursor: cursor.execute(query) return True else: return False
def update_collection_name(self, user_id, new_name): # SQL conn = connect_sys_db() query = "SELECT * FROM collections WHERE (user_id = \'{user_id}\' AND name = \'{name}\')".format( user_id=user_id, name=new_name) db_result = read_sql(sql=query, con=conn) # Is new collection name already exist if not db_result.empty: return False, 'This collection already existed' # SQL query = "UPDATE collections SET name = \'{name}\' WHERE (user_id = \'{user_id}\' AND id = \'{id}\')".format( name=new_name, user_id=user_id, id=self._id) with mysql(conn) as cursor: cursor.execute(query) return True, 'Collection update successfully'
def update_user_password(username, new_password): if not User.is_user_exists_by_username(username): return False conn = connect_sys_db() # SQL query = 'UPDATE users SET password = HEX(AES_ENCRYPT(\'{new_password}\', \'{key}\'))' \ ' WHERE username = \'{username}\' AND' \ ' admin = \'{admin}\'' \ .format( username=username, new_password=new_password, key=SECRET_KEY, admin=0 ) with mysql(conn) as cursor: cursor.execute(query) return True
def delete_book_in_collection(self, book_id): # SQL conn = connect_sys_db() query = "SELECT * FROM collects WHERE (collection_id = \'{collection_id}\' and book_id = \'{book_id}\')".format( collection_id=self._id, book_id=book_id, ) db_result = read_sql(sql=query, con=conn) # Is book exist in this collection if db_result.empty: return False # SQL query = "DELETE FROM collects WHERE (collection_id = \'{collection_id}\' AND book_id = \'{book_id}\')".format( book_id=book_id, collection_id=self._id) with mysql(conn) as cursor: cursor.execute(query) return True
def add_book_to_collection(self, book_id): # SQL conn = connect_sys_db() query = "SELECT * FROM collects WHERE (collection_id = \'{collection_id}\' and book_id = \'{book_id}\')".format( collection_id=self._id, book_id=book_id, ) db_result = read_sql(sql=query, con=conn) # Is book already existed in collection if not db_result.empty: return 201, "This book already existed in this collection" # SQL query = "INSERT INTO collects VALUES(\'{book_id}\', \'{collection_id}\', \'{collect_time}\')".format( book_id=book_id, collection_id=self._id, collect_time=datetime.datetime.utcnow()) with mysql(conn) as cursor: cursor.execute(query) return 200, "Add book to collection successfully"
def edit_review(user_id, book_id, rating, content): # SQL conn = connect_sys_db() query = "SELECT * FROM review_rate WHERE (user_id = \'{user_id}\' AND book_id = \'{book_id}\')".format( user_id=user_id, book_id=book_id) db_result = read_sql(sql=query, con=conn) # If review is existed if not db_result.empty: query = 'UPDATE review_rate SET rating = \'{rating}\', review_content = \'{review_content}\', review_time = \'{review_time}\' WHERE (user_id = \'{user_id}\' AND book_id = \'{book_id}\')'.format( rating=rating, review_content=content, review_time=datetime.datetime.utcnow(), user_id=user_id, book_id=book_id) with mysql(conn) as cursor: cursor.execute(query) return True else: return False
def update_password(self, old_password, new_password): # SQL # Check old password conn = connect_sys_db() query = "SELECT * FROM users WHERE (password = HEX(AES_ENCRYPT(\'{old_password}\', \'{key}\')) AND id = \'{id}\')".format( old_password=old_password, id=self._id, key=SECRET_KEY) db_result = read_sql(sql=query, con=conn) if db_result.empty: return False # SQL # Update to new password conn = connect_sys_db() query = 'UPDATE users SET password = HEX(AES_ENCRYPT(\'{new_password}\', \'{key}\')) WHERE id = \'{id}\'' \ .format( id=self._id, new_password=new_password, key=SECRET_KEY ) with mysql(conn) as cursor: cursor.execute(query) return True
def new_review(user_id, book_id, rating, content): user = User(user_id) username = user.get_username() # SQL conn = connect_sys_db() query = "SELECT * FROM review_rate WHERE (user_id = \'{user_id}\' AND book_id = \'{book_id}\')".format( user_id=user_id, book_id=book_id) db_result = read_sql(sql=query, con=conn) # If review does not exist if db_result.empty: query = "INSERT INTO review_rate VALUES(\'{book_id}\',\'{user_id}\',\'{username}\',\'{rating}\',\'{content}\',\'{time}\')".format( user_id=user_id, book_id=book_id, username=username, rating=rating, content=content, time=datetime.datetime.utcnow()) with mysql(conn) as cursor: cursor.execute(query) return True else: return False