For an assignment I need to import playlist (text file) into a database (chinook.db). An example of this text file is:

Bohemian Rhapsody

You're

Thriller

However, there may be several tracks that start with the given keywords from the file. In that case, the application should display a small selection menu with the different alternatives (artist name track name).

When I run my code and have entered a file name and playlist name, I get a list of all the songs in the database. After this you will be asked to make a choice (this step should actually only come later after the tracks have been filtered on the tracks or keywords that are in the text file). After giving the choice, I do get the desired list of songs that start with "You're".

What I've done so far is an if statement that when results > 1 asks which song to add if multiple keywords are found. However, it only displays these songs after I have filled in a choice once. I need to do something with results but I can't get it done. Been struggling for hours the past couple days.This is the code:

import sqlite3
import os

def import_playlist(file_name, playlist_name):
    # Connect to the Chinook database
    conn = sqlite3.connect("chinook.db")
    c = conn.cursor()

    # Check if the file exists
    if not os.path.isfile(file_name):
        print("Error: the file does not exist.")
        return

    # Check if a playlist with the same name already exists in the database
    c.execute("SELECT COUNT(*) FROM playlists WHERE Name=?", (playlist_name,))
    if c.fetchone()[0] > 0:
        print("Error: a playlist with the same name already exists in the database.")
        return

    # Create a new playlist in the database
    c.execute("INSERT INTO playlists (Name) VALUES (?)", (playlist_name,))
    playlist_id = c.lastrowid

    # Read tracks from the file
    with open(file_name, "r") as file:
        for line in file:
            track_name = line.strip()

            # Find tracks that match the search keywords
            c.execute("SELECT * FROM tracks WHERE Name LIKE ?", (track_name   "%",))
            results = c.fetchall()

            # Show the alternatives if multiple tracks are found
            if len(results) > 1:
                track_id = None
                while track_id is None:
                    print("Make a choice from the following tracks:")
                    for i, track in enumerate(results):
                        print("{}   {}   {}".format(i 1, track[1], track[2]))
                    choice = input("Your choice:")
                    if choice.isnumeric() and int(choice) <= len(results):
                        track_id = results[int(choice)-1][0]
                    else:
                        print("Invalid Input")
            elif len(results) == 1:
                track_id = results[0][0]
            else:
                continue

            # Add the selected track to the playlist
            c.execute("INSERT INTO playlist_track (PlaylistId, TrackId) VALUES (?, ?)", (playlist_id, track_id))

    # Save the changes and close the database connection
    conn.close()

    print("The playlist has been imported.")

file_name = input("Enter the name of the file:")
playlist_name = input("Enter the name of the playlist:")
import_playlist(file_name, playlist_name)

I dont want all those tracks and the first "You're choice" printen.. I want this outcome: DesiredOutcome

CodePudding user response：

When there is a blank (or white space only) line in the input file, the strip will reduce it to an empty string. The SQL query then reduces to "... WHERE LIKE "%" and matches every row. To resolve this check for an empty string and skip to the next line.

for line in file:
    track_name = line.strip()
    if len(track_name) == 0:
        continue;

    # Find tracks that match the search keywords
    c.execute("SELECT * FROM tracks WHERE Name LIKE ?", (track_name   "%",))
    results = c.fetchall()