Read lines from .txt file into sql query-CodePudding

I want to run a .txt file line for line through an SQL query. The .txt file consists of songtitles that may or may not exist in the database. If there is more than one option that fits the songtitle in the database a selection menu should appear. If there is only one option no further action is needed. If the line in the .txt file is not in the database a print statment shoud appear saying the song is not found.

To test this I made a .txt file with each of the three options described above:

Your (this gives 7 hits)
Bohemian (this gives 1 hit)
Thriller (this gives 0 hits)

I created the .txt file in another .py file, like this:

with open('MijnMuziek.txt', 'w') as f:
    f.writelines("""
                        your
                        bohemian
                        thriller""")
    f.close()

But if I run the code below in a separate .py file it only prints 'Choose from the following options: ' and than gives an error message saying index is out of range.

import sqlite3

music_database = sqlite3.connect("C:\\Users\marlo\Downloads\chinook_LOI.db")
cursor = music_database.cursor()

def read_file(filename):
    with open(filename) as f:
        for track in f:
            cursor.execute(f"""SELECT DISTINCT t.TrackId, t.Name, art.Name
                               FROM tracks t
                               JOIN albums alb ON t.AlbumId = alb.AlbumId
                               JOIN artists art ON alb.ArtistId = art.ArtistId
                               WHERE t.Name LIKE '{track}%'""")
def selection_menu():
    for position, song in enumerate(tracks_available):
        print(str(position   1), *song[1:3], sep='\t')
    choice = int(input('Choose from the following options: '))
    print('You chose:', *tracks_available[choice - 1], sep='\t')

read_file('MijnMuziek.txt')
tracks_available = cursor.fetchall()
selection_menu()
music_database.close()

When I put only one option in the .txt file (f.writelines('your')) the code does work and I get a selection menu.But with more than one line in the .txt file it does not work.

How do I solve this?

CodePudding user response：

I don't have your database to test this, but this is a way to do it.

It makes sense to open & close the database in the read function. It also is a good idea to avoid global variable use and instead pass them into functions.

I included protection against blank lines in your text file.

I didn't fix the SQL injection for you because I'd need to google how it works with the LIKE % you use...

import sqlite3

DATABASE_FILE = r"C:\\Users\marlo\Downloads\chinook_LOI.db"

def read_tracks_from_file(filename, database_file):
    music_database = sqlite3.connect(database_file)
    cursor = music_database.cursor()

    tracks_available = []
    with open(filename) as f:
        for track in f:
            if track:
                cursor.execute(f"""SELECT DISTINCT t.TrackId, t.Name, art.Name
                                   FROM tracks t
                                   JOIN albums alb ON t.AlbumId = alb.AlbumId
                                   JOIN artists art ON alb.ArtistId = art.ArtistId
                                   WHERE t.Name LIKE '{track}%'""")
                for track in cursor.fetchall():
                    tracks_available.append(track)

    music_database.close()
    return tracks_available

def selection_menu(track_selection):
    for position, song in enumerate(track_selection, start=1):
        print(str(position), *song[1:3], sep='\t')
    choice = int(input('Choose from the following options: '))
    print('You chose:', *track_selection[choice - 1], sep='\t')

tracks_available = read_tracks_from_file(filename='MijnMuziek.txt',
                                         database_file=DATABASE_FILE)
selection_menu(track_selection=tracks_available)