def index(): """ displays homepage """ # get a bunch of albums from itunes response = requests.get( "https://itunes.apple.com/search?media=music&entity=album&limit=15&term=rap" ) response = response.json() # get list of genres for sidebar menu query = "SELECT GenreName, GenreID FROM Genres" connection = connect() genres = execute_query(connection, query) first_genre_id = genres[0][1] # get corresponding albums for first genre connection = connect() albums_query = f"""Select Albums.AlbumID, Albums.AlbumName, Artists.ArtistName, Artists.ArtistID, Genres.GenreName, Albums.Price FROM Genres INNER JOIN Album_Genres ON Genres.GenreID = Album_Genres.GenreID INNER JOIN Albums ON Album_Genres.AlbumID = Albums.AlbumID INNER JOIN Album_Artists ON Albums.AlbumID = Album_Artists.AlbumID INNER JOIN Artists ON Album_Artists.ArtistID = Artists.ArtistID WHERE Genres.GenreID = {first_genre_id}""" albums = execute_query(connection, albums_query) connection.close() return render_template('index.html', context={ "genres": genres, "albums": albums })
def test_init(self): # initial read should fail as tables not exist delete_db() connect(db_) self.assertRaises(sqlite3.OperationalError, read_room_types_data, db_) self.assertRaises(sqlite3.OperationalError, read_neighbourhood_groups_data, db_) self.assertRaises(sqlite3.OperationalError, read_neighbourhoods_data, db_) self.assertRaises(sqlite3.OperationalError, read_ab_data, db_) # creating tables create_all_tables(db_) # after creating tables, read should return 0 items self.assertEqual(0, len(read_room_types_data(db_))) self.assertEqual(0, len(read_neighbourhood_groups_data(db_))) self.assertEqual(0, len(read_neighbourhoods_data(db_))) self.assertEqual(0, len(read_ab_data(db_)))
def dialog_seeder(): connect().Dialog.insert_many([{ 'command': 'hello Houser', 'answer': 'how can I help you?' }, { 'command': 'can you turn on the light', 'answer': 'yes, please tell me the room' }, { 'command': 'goodbye Houser', 'answer': 'goodbye' }, { 'command': 'add another person', 'answer': "what's the person's name?" }, { 'command': 'who are you', 'answer': "Hello, my name is Houser and I'm Eduardo Cerutti's final project, nice to meet you" }, { 'command': 'thank you', 'answer': "you're welcome" }, { 'command': 'command say houser 3 times', 'answer': 'alright, please, when I ask you will say hey houser three times' }, { 'command': 'command ask frequency 0', 'answer': 'say the first time please' }, { 'command': 'command ask frequency 1', 'answer': 'the second' }, { 'command': 'command ask frequency 2', 'answer': 'and the last one' }, { 'command': 'command nice to meet you', 'answer': 'nice to meet you' }, { 'command': 'command thanks', 'answer': 'thank you' }, { 'command': 'command listening error', 'answer': "sorry, I couldn't understand, can you please repeat?" }])
def get_album_from_id_or_name(id=None, name=None): connection = connect() query = f"""SELECT Albums.AlbumID, Albums.AlbumName, Albums.Price, Genres.GenreID, Artists.ArtistID, Albums.ReleasedYear, Albums.CopiesInStock FROM Albums LEFT JOIN Album_Artists ON Albums.AlbumID = Album_Artists.AlbumID LEFT JOIN Artists ON Artists.ArtistID = Album_Artists.ArtistID LEFT JOIN Album_Genres ON Albums.AlbumID = Album_Genres.AlbumID LEFT JOIN Genres ON Album_Genres.GenreID = Genres.GenreID""" if id: query += f" WHERE Albums.AlbumID = {id}" else: query += f" WHERE Albums.AlbumName LIKE '%{name}%'" try: album_info = execute_query(connection, query) except Exception: album_info = () #Get track info. # if album_info != []: # album_id = album_info[0][0] # track_query = f"""SELECT Tracks.TrackName, Tracks.TrackLength FROM Tracks WHERE Tracks.AlbumID = {album_id}""" # try: # tracks = execute_query(connection, track_query) # except Exception: # tracks = () # print(tracks) connection.close() return album_info
def add_album(name, artist_id, price, copies_in_stock, year, genre_id, second_artist_id=None, second_genre_id=None): """ Adds a new album to the DB with the given parametes. """ connection = connect() # Insert album into Albums table. query = f"""INSERT INTO Albums (AlbumName, Price, ReleasedYear, CopiesInStock) VALUES ('{name}', '{price}', '{year}', '{copies_in_stock}')""" execute_non_select_query(connection, query) connection.close() # Get ID of new album. album_id = get_album_id_from_name_year(name, year) # Insert new row into Album_Genres table. add_album_genres(album_id, genre_id) # Insert second row into Album_Genres table if needed. if second_genre_id and second_genre_id != "None": add_album_genres(album_id, second_genre_id) # Insert new row into Album_Artists table. add_album_artists(album_id, artist_id) # Insert second row into Album_Artists table if needed. if second_artist_id: add_album_artists(album_id, second_artist_id) print("second " + str(second_artist_id))
def delete_customer_by_id(id): """ Deletescustomer with specified ID from Customers table.""" connection = connect() # Delete from Albums table. query = f"""DELETE FROM Customers WHERE Customers.CustomerID = {id}""" execute_non_select_query(connection, query)
def render_album(id): # album_name = request.args. connection = connect() query = f"""SELECT Albums.AlbumID, Albums.AlbumName, Albums.Price, Artists.ArtistName FROM Albums INNER JOIN Album_Artists ON Albums.AlbumID = Album_Artists.AlbumID INNER JOIN Artists ON Album_Artists.ArtistID = Artists.ArtistID WHERE Albums.AlbumID = {id} """ album_data = execute_query(connection, query) # return a 404 if an album wasn't found if not album_data: return render_template("404-template.html") album_data = album_data[0] query = f"""SELECT Genres.GenreName FROM Genres INNER JOIN Album_Genres ON Genres.GenreID = Album_Genres.GenreID INNER JOIN Albums ON Album_Genres.AlbumID = Albums.AlbumID WHERE Albums.AlbumID = {id}""" genres = execute_query(connection, query) genres = [name[0] for name in genres] query = f"""SELECT * FROM Tracks WHERE AlbumID = {id}""" tracks = execute_query(connection, query) connection.close() return render_template('album-template.html', context={ "data": album_data, "genres": genres, "tracks": tracks })
def render_account(): connection = connect() user_id = session["user_id"] # get user info to display, user favGenreID to get genre name query = f"""select firstName, lastName, email, GenreName from Customers INNER JOIN Genres ON Customers.favGenre = Genres.GenreID where Customers.CustomerID = {user_id} """ user = execute_query(connection, query) firstName, lastName, email, favGenre = user[0] # query = f"""SELECT Orders.OrderID, Customers.FirstName, SUM(Albums.Price) # FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID # INNER JOIN Order_Albums ON Orders.OrderID = Order_Albums.OrderID # INNER JOIN Albums ON Order_Albums.AlbumID = Albums.AlbumID # WHERE Customers.CustomerID = {user_id} GROUP BY OrderID;""" # orders = execute_query(connection, query) connection.close() return render_template( 'account-template.html', context={ "firstName": firstName, "lastName": lastName, "email": email, "favGenre": favGenre, #"orders": orders })
def delete_artist_genres_by_id(id): """ Deletes Artist_Genres row with specified row ID.""" connection = connect() query = f"""DELETE FROM Artist_Genres WHERE Artist_Genres.RowID = {id}""" execute_non_select_query(connection, query) connection.close()
def get_all_artist_genres(): connection = connect() query = f""" SELECT * FROM Artist_Genres""" table_info = execute_query(connection, query) connection.close() return table_info
def voice(): dialog_collection = connect().Dialog for dialog in dialog_collection.find(): if (os.path.isfile('app/assets/audios/' + dialog['command'] + '.mp3') == False): tts = gTTS(text=dialog['answer'], lang='en') # I chose to name the audio by question bc question never have special char tts.save('app/assets/audios/' + dialog['command'] + '.mp3')
def display_all_customers(): connection = connect() query = f""" SELECT * FROM Customers""" customer_data = execute_query(connection, query) connection.close() return render_template('admin/search-template-results.html', album_data=[], customer_data=customer_data)
def add_track(name, length, album_id): """Adds a new track to DB.""" connection = connect() query = f"""INSERT INTO Tracks (TrackName, TrackLength, AlbumID) VALUES ('{name}', '{length}', '{album_id}')""" execute_non_select_query(connection, query) connection.close()
def get_all_genres(): """ returns list of tuple with genre name and id """ connection = connect() query = "SELECT GenreID, GenreName FROM Genres" genres = execute_query(connection, query) connection.close() return genres
def get_all_artists(): """ returns list of tuple with artist name and id """ connection = connect() query = "SELECT ArtistID, ArtistName FROM Artists" artists = execute_query(connection, query) connection.close() return artists
def create_table(create_table_command, db_file): """ Utility method to create db tables :param create_table_command: command to create table :param db_file: database file :return: None """ conn = connection.connect(db_file) cur = conn.cursor() cur.execute(create_table_command) conn.commit() connection.close(conn)
def get_all_albums(): """ returns list of tuple with album name and id. """ connection = connect() query = "SELECT AlbumID, AlbumName FROM Albums" albums = execute_query(connection, query) connection.close() return albums
def update_multiple(fk, values, table, parameters): connection = connect() query1 = f"DELETE FROM Album_Artists WHERE AlbumID = {fk}" execute_non_select_query(connection, query1) update_values = ",".join( [str((int(fk), int(valueID))) for valueID in values]) fields = ",".join(parameters) query2 = f"Insert Into {table} ({fields}) values {update_values}" execute_non_select_query(connection, query2) connection.close()
def display_all_albums(): connection = connect() query = f"""SELECT Albums.AlbumID, Albums.AlbumName, Albums.Price, Artists.ArtistName, Albums.CopiesInStock, Albums.ReleasedYear FROM Albums INNER JOIN Album_Artists ON Albums.AlbumID = Album_Artists.AlbumID INNER JOIN Artists ON Album_Artists.ArtistID = Artists.ArtistID """ album_data = execute_query(connection, query) connection.close() # return redirect("/admin/search-results") return render_template('admin/search-template-results.html', album_data=album_data)
def insert_to_table(data_list, insert_command, db_file): """ Insert data into a given table :param data_list: data as list of tuples :param insert_command: insert command :param db_file: database file :return: """ con = connection.connect(db_file) cur = con.cursor() cur.executemany(insert_command, data_list) con.commit() connection.close(con)
def add_album_artists(album_id, artist_id): """ Takes an ArtistID and AlbumID and adds a new entry to the Album_Genres table """ connection = connect() query = f"""INSERT INTO Album_Artists (AlbumID, ArtistID) VALUES ({album_id}, {artist_id} )""" # Execute query and get int value of ID. execute_non_select_query(connection, query) connection.close()
def add_artist_genres(artist_id, genre_id): """ Takes an ArtistID and GenreID and adds a new entry to the artist_genre table """ connection = connect() query = f"""INSERT INTO Artist_Genres (ArtistID, GenreID) VALUES ({artist_id}, {genre_id} )""" # Execute query and get int value of ID. execute_non_select_query(connection, query) connection.close()
def get_genre_id_from_name(name): """ Takes the name of an genre as parameter and returns the GenreID. """ connection = connect() query = f""" SELECT Genres.GenreID FROM Genres WHERE Genres.GenreName '{name}'""" # Execute query and get int value of ID. genre_id = execute_query(connection, query)[0][0] connection.close() return genre_id
def get_album_id_from_name_year(name, year): """ Takes the name and release year of an album as parameter and returns the AlbumID. """ connection = connect() query = f""" SELECT Albums.AlbumID FROM Albums WHERE Albums.AlbumName = '{name}' AND Albums.ReleasedYear = '{year}'""" # Execute query and get int value of ID. album_id = execute_query(connection, query)[0][0] connection.close() return album_id
def registr(user_id): connection = connect() try: with connection.cursor() as cursor: result = cursor.execute( f"SELECT * FROM anketa where uid = {user_id}") row = cursor.fetchone() if result == 0: cursor.execute(f"insert into anketa(uid) values ({user_id}) ") connection.commit() else: return row finally: connection.close()
def read_all_data(db_file, sql): """ Utility method to read Read data as list of tuples :param db_file: db file :param sql sql command :return: result :raise sqlite3.OperationalError when db_file is not the correct db file """ "" con = connection.connect(db_file) cur = con.cursor() cur.execute(sql) result = cur.fetchall() con.commit() connection.close(con) return result
def get_all_artists_with_genre(): """ Returns all artist names and IDs along with genres associated with each artist, displayed in ascending order by artist name. """ connection = connect() query = f"""SELECT Artists.ArtistName, Artists.ArtistID, Genres.GenreName FROM Artists LEFT JOIN Artist_Genres ON Artist_Genres.ArtistID = Artists.ArtistID LEFT JOIN Genres ON Genres.GenreID = Artist_Genres.GenreID ORDER BY `Artists`.`ArtistName` ASC """ artist_data = execute_query(connection, query) connection.close() return artist_data
def update(tableName, fields, values, rowID): query = f"UPDATE {tableName} set " for i in range(len(fields)): if i < len(fields) - 1: query += f"{fields[i]} = '{values[i]}', " else: query += f"{fields[i]} = '{values[i]}' " query += f" WHERE {tableName[:-1]}ID = {rowID}" try: connection = connect() execute_non_select_query(connection, query) connection.close() return "success" except Exception as e: print(e) return "fail"
def get_album_id_from_name(name): """ Takes the name of an artist as parameter and returns the ArtistID """ connection = connect() query = f""" SELECT Albums.AlbumID FROM Albums WHERE Albums.AlbumName = '{name}'""" album_id = execute_query(connection, query) connection.close() # Trim ID if it exists to return just int vlaue. if album_id: album_id = album_id[0][0] return album_id
def delete_artist_by_id(id): """ Deletes artist with specified ID from Artists table, Album_Artists table and Artist_Genres table.""" connection = connect() # Delete from Albums table. query = f"""DELETE FROM Artists WHERE Artists.ArtistID = {id}""" execute_non_select_query(connection, query) # Delete from Album_Artists. query = f"""DELETE FROM Album_Artists WHERE Album_Artists.ArtistID = {id}""" execute_non_select_query(connection, query) # Delete from Album_Genres. query = f"""DELETE FROM Artist_Genres WHERE Artist_Genres.ArtistID = {id}""" execute_non_select_query(connection, query) connection.close()