sqlite python程序比较文本文件与曲目从播放列表数据库显示所有曲目

u4dcyp6a  于 2023-01-13  发布在  SQLite
关注(0)|答案(1)|浏览(128)

对于一个任务,我需要将播放列表(文本文件)导入到数据库(chinook.db)中。
波希米亚狂想曲
你是
颤栗
然而,可能有几首曲目以文件中给定的关键字开头。在这种情况下,应用程序应该显示一个小的选择菜单,其中包含不同的选项(艺术家名称+曲目名称)。
当我运行代码并输入文件名和播放列表名时,我会得到一个数据库中所有歌曲的列表。在此之后,你会被要求做出选择(这一步实际上应该只在曲目被过滤后或关键词在文本文件中)。在给出选择后,我得到了以"You're"开头的歌曲列表。
到目前为止,我所做的是一个if语句,当搜索结果〉1时,它会询问如果找到多个关键词要添加哪首歌。然而,它只会在我填写了一次选择后才显示这些歌曲。我需要对搜索结果做些什么,但我无法完成。过去几天我一直在挣扎几个小时。代码如下:

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)

我不希望所有这些轨道和第一个"你的选择"打印..我想要这个结果:DesiredOutcome

wqsoz72f

wqsoz72f1#

当输入文件中有空行(或白色)时,条带会将其缩减为空字符串。然后SQL查询缩减为“... WHERE LIKE“%”并匹配每一行。要解决此问题,请检查空字符串并跳到下一行。

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

相关问题