def remove_meter(self, name):
"""Remove a meter from the database
Args:
name (str): meter's name
Raises:
msqbot_error.MsqDataBaseError: Something went wrong with the database
"""
cursor = self.conn.cursor()
try:
cursor.execute(
'''
DELETE FROM scores
WHERE fk_meter = (
SELECT id_meter FROM meters
WHERE name_meter = ?
)
''', [name])
cursor.execute('DELETE FROM meters WHERE name_meter = ?', [name])
self.conn.commit()
except (mariadb.DatabaseError):
raise msqbot_error.MsqDataBaseError()
finally:
cursor.close()
def update_score(self, name_meter, id_user, score):
"""Update participant score in meter
Args:
name_meter (str): meter name
id_user (user): id of the user who scored
score (int): participant score point
Raises:
MsqDataBaseError: Something went wrong with the database
Returns:
int: updated score id
"""
cursor = self.conn.cursor()
try:
cursor.execute(
'''
UPDATE scores
SET score = score + ?
WHERE fk_meter = (
SELECT id_meter FROM meters WHERE name_meter=?
)
AND fk_user = ?;
''', (score, name_meter, id_user))
self.conn.commit()
return cursor.lastrowid
except (mariadb.DatabaseError):
raise msqbot_error.MsqDataBaseError()
finally:
cursor.close()
def select_one_participant_details(self, id_user):
"""Select participant details on meter's and score
Args:
id_user (int): participant's id
Raises:
MsqDataBaseError: Something went wrong with the database
Returns:
list: array of data on participant
"""
cursor = self.conn.cursor()
try:
cursor.execute(
'''
SELECT
u.name_user as name_participant,
s.score,
m.name_meter
FROM users u
INNER JOIN scores s ON s.fk_user = u.id_user
INNER JOIN meters m ON m.id_meter = s.fk_meter
WHERE u.id_user = ?
''', [id_user])
return cursor.fetchall()
except (mariadb.DatabaseError):
raise msqbot_error.MsqDataBaseError()
finally:
cursor.close()
def insert_score(self, name_meter, id_user):
"""Add a participant to a meter
Args:
name_meter (str): meter where the participant scored
id_user (int): participant to be added
Raises:
MsqDataBaseError: Something went wrong with the database
Returns:
int: inserted participant id
"""
cursor = self.conn.cursor()
try:
cursor.execute(
'''
INSERT INTO scores (
fk_meter,
fk_user
) VALUES(
(SELECT id_meter FROM meters WHERE name_meter=?),
?
);
''', (name_meter, id_user))
self.conn.commit()
return cursor.lastrowid
except (mariadb.DatabaseError):
raise msqbot_error.MsqDataBaseError()
finally:
cursor.close()
def delete_participant_from(self, id_user, meter):
"""Remove a participant from a meter
Args:
id_user (int): participant name
meter (str): meter's name
Raises:
MsqDataBaseError: Something went wrong with the database
"""
cursor = self.conn.cursor()
try:
cursor.execute(
'''
DELETE FROM scores
WHERE fk_user = ?
AND fk_meter = (
SELECT id_meter
FROM meters
WHERE name_meter = ?
)
''', (id_user, meter))
self.conn.commit()
except (mariadb.DatabaseError):
raise msqbot_error.MsqDataBaseError()
finally:
cursor.close()
def insert_meter(self, name, rules):
"""Insert a new meter in database
Args:
name (str): name of the meter
rules (str): rules for the meter
Raises:
MsqDataBaseError: Something went wrong with the database
Returns:
int: the inserted element id
"""
cursor = self.conn.cursor()
try:
cursor.execute(
'''
INSERT INTO meters (
name_meter,
rules_meter
) VALUES ( ?, ?);
''', [name, rules])
self.conn.commit()
return cursor.lastrowid
except (mariadb.DatabaseError):
raise msqbot_error.MsqDataBaseError()
finally:
cursor.close()
def select_movie_details(self, name):
"""Select a movie with rating details
Args:
name (str): chosen movie
Returns:
list: movie with all rating
Raises:
MsqDataBaseError: An occurred with the query on the database
"""
cursor = self.conn.cursor()
try:
cursor.execute(
'''
SELECT
m.name_movie as name,
m.date_added_movie as added,
m.seen_date_movie as seen,
r.rate as rate,
u.name_user as spectator
FROM movies m
INNER JOIN rates r ON r.fk_movie = m.id_movie
INNER JOIN users u ON u.id_user = r.fk_user
WHERE m.name_movie = ?
''', [name])
return cursor.fetchall()
except (mariadb.DatabaseError):
raise ex.MsqDataBaseError()
finally:
cursor.close()
def select_movies(self, sort, growth):
"""Select all movies in database oredered by sort parameter
Args:
sort (str): sorting parameter to order fetched data
growth (str, optional): order in ascendant or descendant
Returns:
list: List of movies ordered by sort
Raises:
MsqDataBaseError: An occurred with the query on the database
"""
cursor = self.conn.cursor()
try:
cursor.execute('''
SELECT
m.name_movie as name,
m.date_added_movie as added,
m.seen_date_movie as seen,
ROUND(AVG(r.rate), 2) as rate
FROM movies m
LEFT JOIN rates r ON r.fk_movie = m.id_movie
GROUP BY m.id_movie
ORDER BY {} {}
'''.format(sort, growth))
return cursor.fetchall()
except (mariadb.DatabaseError):
raise ex.MsqDataBaseError()
finally:
cursor.close()
def select_spectators_details(self, id_spectator):
"""Fetch all the spectator rated movie
Args:
id_spectator (int): database id of the spectator
Raises:
MsqDataBaseError: An occurred with the query on the database
Returns:
list: all the movies rated by the spectator
"""
cursor = self.conn.cursor()
try:
cursor.execute(
'''
SELECT
u.name_user as spectator,
r.rate as rate,
m.name_movie as movie
FROM users u
INNER JOIN rates r ON r.fk_user = u.id_user
INNER JOIN movies m ON m.id_movie = r.fk_movie
WHERE u.id_user = ?
''', [id_spectator])
return cursor.fetchall()
except (mariadb.DatabaseError):
raise ex.MsqDataBaseError()
finally:
cursor.close()
def insert_movie(self, name):
"""Insert a new movie in database
Args:
name (str): movie name
Returns:
int: Inserted element id
Raises:
MsqDataBaseError: An occurred with the query on the database
"""
cursor = self.conn.cursor()
try:
cursor.execute(
"""
INSERT INTO movies (name_movie) VALUES (?) ;
""", [name])
self.conn.commit()
return cursor.lastrowid
except (mariadb.DatabaseError):
raise ex.MsqDataBaseError()
finally:
cursor.close()
def select_meters(self):
"""Fetch all meters
Raises:
msqbot_error.MsqDataBaseError: Something went wrong with the database
Returns:
list: all meters
"""
cursor = self.conn.cursor()
try:
cursor.execute('SELECT * FROM meters')
return cursor.fetchall()
except (mariadb.DatabaseError):
raise msqbot_error.MsqDataBaseError()
finally:
cursor.close()
def insert_rate(self, rate, id_spectator, name_spectator, name_movie):
"""Insert a new rate for a movie by a spectator
If the spectator doesn't exist in database then he's created.
Args:
rate (decimal): rate given to the movie
id_spectator (int): rater id
name_spectator (str): rater name
name_movie (str): movie being rated
Raises:
MsqDataBaseError: An occurred with the query on the database
"""
cursor = self.conn.cursor()
try:
cursor.execute(
"""INSERT IGNORE INTO users (id_user, name_user) VALUES (?,?);""",
(id_spectator, name_spectator))
cursor.execute(
"""REPLACE INTO rates (fk_movie,fk_user,rate)
SELECT id_movie, ?, ?
FROM movies
WHERE name_movie = ?;
""", (id_spectator, rate, name_movie))
cursor.execute(
"""UPDATE movies SET seen_date_movie = CURRENT_TIMESTAMP
WHERE (
id_movie = (SELECT id_movie FROM movies WHERE name_movie = ?)
) AND (
seen_date_movie IS NULL
);
""", (name_movie, ))
self.conn.commit()
except (mariadb.DatabaseError):
self.conn.rollback()
raise ex.MsqDataBaseError()
finally:
cursor.close()
def insert_participant(self, id_user, name):
"""create a new participant
Args:
name (str): participant name
id_user (int): user id
Raises:
msqbot_error.MsqDataBaseError: Something went wrong with the database
"""
cursor = self.conn.cursor()
try:
cursor.execute(
'''
INSERT IGNORE users (id_user, name_user) VALUES (?, ?);
''', (id_user, name, id_user))
self.conn.commit()
except (mariadb.DatabaseError):
raise msqbot_error.MsqDataBaseError()
finally:
cursor.close()
def update_movie(self, name, new_name):
"""Update query to change movie name
Args:
name (str) : old name
new_name (str) : new name
Raises:
MsqDataBaseError: An occurred with the query on the database
"""
cursor = self.conn.cursor()
try:
cursor.execute(
"""
UPDATE movies SET name_movie = ? WHERE name_movie = ?
""", [new_name, name])
self.conn.commit()
except (mariadb.DatabaseError):
raise ex.MsqDataBaseError()
finally:
cursor.close()