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()