def get_all_ongoing_events(user: str): """ :return: An ongoing event list """ cnx = db_connector.get_connection() cursor = cnx.cursor() query = ("SELECT * FROM event " "where `time` >= now()") cursor.execute(query) events = [] for (eid, name, host, address, longi, lati, zipcode, event_time, description, image, num_likes, category) in cursor: new_event = Event(user=host, name=name, address=address, longitude=longi, latitude=lati, zipcode=zipcode, event_time=datetime.datetime.strptime( str(event_time), "%Y-%m-%d %H:%M:%S")) new_event.event_id = eid new_event.description = description new_event.image = image new_event.num_likes = num_likes new_event.category = category if user: new_event.liked = Like.exist(user, eid) new_event.attended = Join.user_is_attend(user=user, event=eid) else: new_event.liked = False new_event.attended = False new_event.comments = Comment.get_comment_by_event(eid) events.append(new_event) cursor.close() cnx.close() return events
def create_tables(): """ Create all tables when the project starts """ cnx = db_connector.get_connection() cursor = cnx.cursor() for table_name in TABLES: table_description = TABLES[table_name] try: print("Creating table {}: ".format(table_name), end='') cursor.execute(table_description) except mysql.connector.Error as err: print(err.msg) else: print("create all tables: OK") for func_name in FUNCTIONS: function_desc = FUNCTIONS[func_name] try: print("Creating function {}: ".format(func_name), end='') cursor.execute(function_desc) except mysql.connector.Error as err: print(err.msg) else: print("create all function: OK") cursor.close() cnx.close()
def delete_user_by_email(email: str): """ delete a user from database :param email: email """ cnx = db_connector.get_connection() cursor = cnx.cursor() sql = ("DELETE FROM `user` WHERE email='" + email + "'") cursor.execute(sql) cnx.commit() cursor.close() cnx.close()
def delete_event_by_id(event_id: str): """ Delte the event from the database :param event_id: event id """ cnx = db_connector.get_connection() cursor = cnx.cursor() sql = ("DELETE FROM `event` WHERE id='" + event_id + "'") cursor.execute(sql) cnx.commit() cursor.close() cnx.close()
def delete_join(join: 'Join'): """ Delete a join in database :param join: join object """ cnx = db_connector.get_connection() cursor = cnx.cursor() query = ("DELETE FROM `join` WHERE user='******' and event='" + join.event + "'") cursor.execute(query) cnx.commit() cursor.close() cnx.close()
def delete_like(like: 'Like'): """ Delete a like in Database :param like: like object """ cnx = db_connector.get_connection() cursor = cnx.cursor() query = ("DELETE FROM `like` WHERE user='******' and event='" + like.event + "'") cursor.execute(query) cnx.commit() cursor.close() cnx.close()
def delete_comment(comment_id: str): """ Delete comment with given id. :param comment_id: comment id :return: None """ cnx = db_connector.get_connection() cursor = cnx.cursor() sql = ("DELETE FROM `comment` WHERE id='" + comment_id + "'") cursor.execute(sql) cnx.commit() cursor.close() cnx.close()
def create_join(join: 'Join'): """ :param join: join object """ cnx = db_connector.get_connection() cursor = cnx.cursor() sql = "INSERT INTO `join` (`user`, `event`) " \ "VALUES (%s, %s);" join_data = (join.user, join.event) cursor.execute(sql, join_data) cnx.commit() cursor.close() cnx.close()
def create_user(user: '******'): """ Insert a user into the database :param user: user Object """ cnx = db_connector.get_connection() cursor = cnx.cursor() sql = "INSERT IGNORE INTO `user` (`email`, `name`) " \ "VALUES (%s, %s)" user_data = (user.email, user.username) cursor.execute(sql, user_data) cnx.commit() cursor.close() cnx.close()
def create_like(like: 'Like'): """ Insert a like into the database :param like: like object """ cnx = db_connector.get_connection() cursor = cnx.cursor() sql = "INSERT INTO `like` (`user`, `event`) " \ "VALUES (%s, %s);" like_data = (like.user, like.event) cursor.execute(sql, like_data) cnx.commit() cursor.close() cnx.close()
def get_join_by_event(event: str): """ Get a list of joins belongs to the event. :param event: event object :return: list of joins belongs to the event. """ cnx = db_connector.get_connection() cursor = cnx.cursor() query = ("SELECT * FROM `join` WHERE event='" + event + "'") cursor.execute(query) joins = [] for (join_user, join_event) in cursor: joins.append(Join(user=join_user, event=join_event)) cursor.close() cnx.close() return joins
def get_like_by_event(event: str): """ Get a list of events which are liked by user :param event: event id :return: a list of events which are liked by user """ cnx = db_connector.get_connection() cursor = cnx.cursor() query = ("SELECT * FROM `like` WHERE event='" + event + "'") cursor.execute(query) likes = [] for (user, cur_event) in cursor: likes.append(Like(user=user, event=cur_event)) cursor.close() cnx.close() return likes
def get_join_by_user(user: str): """ :param user: Email of the user :return: list of Joins which is related to user """ cnx = db_connector.get_connection() cursor = cnx.cursor() query = ("SELECT * FROM `join` WHERE user='******'") cursor.execute(query) joins = [] for (join_user, join_event) in cursor: joins.append(Join(user=join_user, event=join_event)) cursor.close() cnx.close() return joins
def get_like_by_user(user: str): """ Get a list of like objects from the user :param user: email of the user :return: a list of like objects related to the user """ cnx = db_connector.get_connection() cursor = cnx.cursor() query = ("SELECT * FROM `like` WHERE user='******'") cursor.execute(query) likes = [] for (usr, event) in cursor: likes.append(Like(user=usr, event=event)) cursor.close() cnx.close() return likes
def get_user_by_email(email: str): """ Get a user from the email :param email: email :return: A user object with the given email """ cnx = db_connector.get_connection() cursor = cnx.cursor() query = ("SELECT `name` FROM `user` WHERE `email`='" + email + "'") cursor.execute(query) res = None for name in cursor: res = User(email, name[0]) cursor.close() cnx.close() return res
def update_event(fields: dict, event_id: str): """ Update certain columns in the database :param fields: A dictionary of columns :param id: Id of the event. """ cnx = db_connector.get_connection() cursor = cnx.cursor() columns = ["`" + c + "`=%s" for c in list(fields.keys())] column_str = ", ".join(columns) values = list(fields.values()) values.append(event_id) sql = ("UPDATE `event` SET " + column_str + " WHERE (`id`=%s)") event_data = tuple(values) cursor.execute(sql, event_data) cnx.commit() cursor.close() cnx.close()
def user_is_attend(user: str, event: str): """ :param user: user email :param event: event id :return: boolean variable indicates whether the given user attends the event """ cnx = db_connector.get_connection() cursor = cnx.cursor() query = ("SELECT * FROM `join` WHERE event='" + event + "' AND user='******'") cursor.execute(query) find_result = False for _, _ in cursor: find_result = True cursor.close() cnx.close() return find_result
def exist(user: str, event: str): """ Check whether the event is liked by the user :param user: email of the user :param event: event id :return: boolean variable """ cnx = db_connector.get_connection() cursor = cnx.cursor() query = ("SELECT * FROM `like` WHERE user='******' and event='" + event + "'") cursor.execute(query) ret = False for _ in cursor: ret = True cursor.close() cnx.close() return ret
def create_comment(comment: 'Comment'): """ create a comment into the database using a comment object. :param comment: Comment object :return: comment id, string type """ cnx = db_connector.get_connection() cursor = cnx.cursor() sql = "INSERT INTO `comment` (`id`, `user`, `event`, `content`, `time`) " \ "VALUES (%s, %s, %s, %s, %s);" comment_id = str(round(time.time() * 1000)) + \ str(random.randint(0, 1000)) comment_data = (comment_id, comment.user, comment.event, comment.content, comment.time.strftime('%Y-%m-%d %H:%M:%S')) cursor.execute(sql, comment_data) cnx.commit() cursor.close() cnx.close() return comment_id
def get_attendees_by_event(event: str): """ Get all attendees from the event :param event: event id :return: A list of user who attends the event """ cnx = db_connector.get_connection() cursor = cnx.cursor() query = ( "with `tmp` as (select `user` from `join` where `event` = '" + event + "')" "select `user`.* from `user` join `tmp` on `user`.`email` = `tmp`.`user`;" ) cursor.execute(query) users = [] for (email, name) in cursor: users.append(User(email=email, username=name)) cursor.close() cnx.close() return users
def get_comment_by_event(event: str): """ Get all comments which is under the given event. :param event: event id :return: a list of comments """ cnx = db_connector.get_connection() cursor = cnx.cursor() query = ("SELECT * FROM `comment` WHERE event='" + event + "'") cursor.execute(query) comments = [] for (comment_id, user, event_id, content, comment_time) in cursor: new_comment = Comment(user=user, event=event_id, content=content, comment_time=datetime.datetime.strptime(str(comment_time), "%Y-%m-%d %H:%M:%S")) new_comment.comment_id = comment_id comments.append(new_comment) cursor.close() cnx.close() # return json.dumps([ob.__dict__ for ob in comments], use_decimal=True, default=str) return comments
def get_all_ongoing_event_by_user(email: str): """ :param email: Email of user :return: A list of event which is ongoing, and is attended by user. """ likes = Like.get_like_by_user(email) liked_events = set(like.event for like in likes) cnx = db_connector.get_connection() cursor = cnx.cursor() query = ("SELECT * FROM `event` " "WHERE `id` IN " "(SELECT `event` FROM `join` WHERE `user` = '" + email + "') and `time` >= now();") cursor.execute(query) events = [] for (event_id, name, host, address, longitude, latitude, zipcode, event_time, description, image, num_likes, category) in cursor: new_event = Event(user=host, name=name, address=address, longitude=longitude, latitude=latitude, zipcode=zipcode, event_time=datetime.datetime.strptime( str(event_time), "%Y-%m-%d %H:%M:%S")) new_event.event_id = event_id new_event.description = description new_event.image = image new_event.num_likes = num_likes new_event.category = category if event_id in liked_events: new_event.liked = True new_event.attended = True events.append(new_event) cursor.close() cnx.close() # return json.dumps([ob.__dict__ for ob in events], use_decimal=True, default=str) return events
def get_event_by_id(event_id: str, user=None): """ :param event_id: Id of the event :param user: user email :return: An event object whose id is the given event_id, and also indicates whether the user is attending the event """ cnx = db_connector.get_connection() cursor = cnx.cursor() query = ("SELECT * FROM `event` WHERE id='" + event_id + "'") cursor.execute(query) new_event = None for (eid, name, host, address, longitude, latitude, zipcode, event_time, description, image, num_likes, category) in cursor: new_event = Event(user=host, name=name, address=address, longitude=longitude, latitude=latitude, zipcode=zipcode, event_time=datetime.datetime.strptime( str(event_time), "%Y-%m-%d %H:%M:%S")) new_event.event_id = eid new_event.description = description new_event.image = image new_event.num_likes = num_likes new_event.category = category if user: new_event.liked = Like.exist(user, event_id) new_event.attended = Join.user_is_attend(user=user, event=event_id) else: new_event.liked = False new_event.attended = False new_event.comments = Comment.get_comment_by_event(event_id) cursor.close() cnx.close() return new_event
def get_all_event_liked_by_user(email: str): """ :param email: Email of user :return: A list of events which is liked by user. """ joins = Join.get_join_by_user(email) joined_events = set(join.event for join in joins) cnx = db_connector.get_connection() cursor = cnx.cursor() query = ("SELECT * FROM `event` WHERE `id` " "IN (SELECT `event` FROM `like` WHERE `user` = '" + email + "');") cursor.execute(query) events = [] for (event_id, name, host, address, longitude, latitude, zipcode, event_time, description, image, num_likes, category) in cursor: new_event = Event(user=host, name=name, address=address, longitude=longitude, latitude=latitude, zipcode=zipcode, event_time=datetime.datetime.strptime( str(event_time), "%Y-%m-%d %H:%M:%S")) new_event.event_id = event_id new_event.description = description new_event.image = image new_event.num_likes = num_likes new_event.liked = True new_event.category = category if event_id in joined_events: new_event.attended = True events.append(new_event) cursor.close() cnx.close() return events
def create_event(event: 'Event'): """ Create an event. :param event: event object. :return: The id of event. """ cnx = db_connector.get_connection() cursor = cnx.cursor() sql = "INSERT INTO `event` (`id`, `name`, `host`, `address`, `longitude`, `latitude`," \ " `zipcode`, `time`, `description`, `image`, `num_likes`, `category`) " \ "VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);" event_id = str(round(time.time() * 1000)) + \ str(random.randint(0, 1000)) event_data = (event_id, event.name, event.user_email, event.address, event.longitude, event.latitude, event.zipcode, event.time.strftime('%Y-%m-%d %H:%M:%S'), event.description, event.image, event.num_likes, event.category) cursor.execute(sql, event_data) cnx.commit() cursor.close() cnx.close() return event_id
def get_all_event_created_by_user(email: str): """ :param email: Email of user :return: A list of event which is created by the given user. """ likes = Like.get_like_by_user(email) liked_events = set(like.event for like in likes) cnx = db_connector.get_connection() cursor = cnx.cursor() query = ("SELECT * FROM `event` WHERE host='" + email + "'") cursor.execute(query) events = [] for (event_id, name, host, address, longitude, latitude, zipcode, event_time, description, image, num_likes, category) in cursor: new_event = Event(user=host, name=name, address=address, longitude=longitude, latitude=latitude, zipcode=zipcode, event_time=datetime.datetime.strptime( str(event_time), "%Y-%m-%d %H:%M:%S")) new_event.event_id = event_id new_event.description = description new_event.image = image new_event.category = category new_event.num_likes = num_likes if event_id in liked_events: new_event.liked = True new_event.attended = True events.append(new_event) cursor.close() cnx.close() return events