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
