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 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_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 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 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 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 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 add_artist(name, genre_id=None):
"""
Adds a new artist to the DB with the given name, and if genre is provided, will also add row to Artist_Genres table.
"""
connection = connect()
query = f"""INSERT INTO Artists (ArtistName)
VALUES ('{name}')"""
execute_non_select_query(connection, query)
connection.close()
# Get the newly generated ArtistID.
artist_id = get_artist_id_from_name(name)
if genre_id:
# Update Artist_Genres table.
add_artist_genres(artist_id, genre_id)
print("done")
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()