def read_user_babbles(self, username: str):
try:
with self.connection.cursor() as cursor:
sql = "SELECT id, username, message, time_s, nbLikes, nbComments "\
"FROM babbles WHERE username = %s" \
"GROUP BY babbles.time_s DESC, babbles.username;"
cursor.execute(sql, (username, ))
results = cursor.fetchall()
for result in results:
result['time_s'] = "{}".format(result['time_s'])
elapsed = get_elapsed_time(result['time_s'])
result['elapsed'] = elapsed
result['tags'] = self.read_tags(result['id'])
if results:
return results
else:
return []
except Exception as e:
print(e)
def read_babbles_with_tag(self, tag: str):
try:
with self.connection.cursor() as cursor:
sql = "SELECT * "\
"FROM babbles WHERE id IN (SELECT DISTINCT id FROM tag WHERE tag = %s)" \
"GROUP BY babbles.time_s DESC, babbles.username;"
cursor.execute(sql, (tag, ))
results = cursor.fetchall()
for result in results:
result['time_s'] = "{}".format(result['time_s'])
elapsed = get_elapsed_time(result['time_s'])
result['elapsed'] = elapsed
result['tags'] = self.read_tags(result['id'])
if results:
return results
else:
return []
except Exception as e:
print(e)
def get_comments_of_babble(self, babbleID):
try:
with self.connection.cursor() as cursor:
sql = """
SELECT B.commentID, B.username, B.message, B.time_s, nbLikes
FROM comments B
WHERE B.babbleID = %s
GROUP BY B.time_s DESC, B.username;"""
cursor.execute(sql, (babbleID, ))
results = cursor.fetchall()
if not results:
return []
for result in results:
result['time_s'] = "{}".format(result['time_s'])
result['elapsed'] = get_elapsed_time(result['time_s'])
#result['tags'] = self.read_tags(result['id'])
return results
except Exception as e:
print(e)
def get_recent_babbles(self):
try:
with self.connection.cursor() as cursor:
sql = """
SELECT B.id, B.username, B.message, B.time_s, B.nbLikes, B.nbComments
FROM babbles B
ORDER BY B.time_s DESC
LIMIT 100;"""
cursor.execute(sql)
results = cursor.fetchall()
for result in results:
result['time_s'] = "{}".format(result['time_s'])
result['elapsed'] = get_elapsed_time(result['time_s'])
result['tags'] = self.read_tags(result['id'])
if not results:
return []
return results
except Exception as e:
print(e)
def get_babbles_from_followed_babblers(self, username):
try:
with self.connection.cursor() as cursor:
sql = """
SELECT B.id, B.username, B.message, B.time_s, B.nbLikes, B.nbComments
FROM babbles B, follows F
WHERE F.follower LIKE %s AND F.followed = B.username OR B.username = %s
GROUP BY B.time_s DESC, B.username;"""
cursor.execute(sql, (
username,
username,
))
results = cursor.fetchall()
for result in results:
result['time_s'] = "{}".format(result['time_s'])
result['elapsed'] = get_elapsed_time(result['time_s'])
result['tags'] = self.read_tags(result['id'])
if not results:
return []
return results
except Exception as e:
print(e)