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