如何用Python+SQLite实现用户从歌曲库创建并保存播放列表

Hey there! Let's break down how to implement the playlist creation feature—including support for empty playlists—using your existing SQLite setup. First, let's make sure our database schema is set up correctly to handle the relationships between users, songs, and playlists.

1. 完善数据库表结构 #

Since a playlist can have multiple songs and a song can live in multiple playlists, we need a join table (to handle the many-to-many relationship) linking playlists and songs. If you don't have this table yet, add it with this code:

# 在数据库连接后执行这段代码（确保表存在）
cursor = db.cursor()

cursor.execute('''
CREATE TABLE IF NOT EXISTS playlist_songs (
    playlist_id INTEGER,
    song_id INTEGER,
    PRIMARY KEY (playlist_id, song_id),
    FOREIGN KEY (playlist_id) REFERENCES playlist(playlist_id),
    FOREIGN KEY (song_id) REFERENCES library(song_id)
)
''')

db.commit()

We'll assume your existing tables have these key fields:

• users: user_id (primary key), username, password
• library: song_id (primary key), title, artist, genre
• playlist: playlist_id (primary key), user_id (foreign key to users), playlist_name

2. 核心功能实现 #

Let's write reusable functions to handle creating empty playlists, adding songs to playlists, and fetching playlist data.

创建空播放列表 #

This lets a logged-in user create a playlist without adding any songs right away:

def create_empty_playlist(user_id, playlist_name):
    cursor = db.cursor()
    # 插入空播放列表记录
    cursor.execute('''
    INSERT INTO playlist (user_id, playlist_name)
    VALUES (?, ?)
    ''', (user_id, playlist_name))
    db.commit()
    # 返回新播放列表的ID
    return cursor.lastrowid

从歌曲库添加歌曲到播放列表 #

Use this to add existing library songs to a playlist (with a check to avoid duplicates):

def add_song_to_playlist(playlist_id, song_id):
    cursor = db.cursor()
    # 检查歌曲是否已在播放列表中
    cursor.execute('''
    SELECT 1 FROM playlist_songs WHERE playlist_id = ? AND song_id = ?
    ''', (playlist_id, song_id))
    if not cursor.fetchone():
        cursor.execute('''
        INSERT INTO playlist_songs (playlist_id, song_id)
        VALUES (?, ?)
        ''', (playlist_id, song_id))
        db.commit()
        print("Song added successfully!")
    else:
        print("This song is already in the playlist.")

查询用户的所有播放列表（包括空列表） #

Fetch all playlists for a user, including their songs (or note if they're empty):

def get_user_playlists(user_id):
    cursor = db.cursor()
    # 获取用户所有播放列表
    cursor.execute('''
    SELECT playlist_id, playlist_name FROM playlist WHERE user_id = ?
    ''', (user_id,))
    playlists = cursor.fetchall()
    
    # 为每个播放列表匹配歌曲
    user_playlists = []
    for playlist_id, name in playlists:
        cursor.execute('''
        SELECT l.song_id, l.title, l.artist, l.genre
        FROM library l
        JOIN playlist_songs ps ON l.song_id = ps.song_id
        WHERE ps.playlist_id = ?
        ''', (playlist_id,))
        songs = cursor.fetchall()
        user_playlists.append({
            "playlist_id": playlist_id,
            "name": name,
            "songs": songs
        })
    return user_playlists

3. 结合现有代码补全登录后的流程 #

Now let's integrate these functions into your existing login flow. Here's the full extended code:

import sqlite3
import os
from shutil import copyfile

db = sqlite3.connect('example.db')
cursor = db.cursor()

# 初始化所有必要的表
cursor.execute('''
CREATE TABLE IF NOT EXISTS users (
    user_id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT UNIQUE NOT NULL,
    password TEXT NOT NULL
)
''')

cursor.execute('''
CREATE TABLE IF NOT EXISTS library (
    song_id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    artist TEXT NOT NULL,
    genre TEXT NOT NULL
)
''')

cursor.execute('''
CREATE TABLE IF NOT EXISTS playlist (
    playlist_id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    playlist_name TEXT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
)
''')

cursor.execute('''
CREATE TABLE IF NOT EXISTS playlist_songs (
    playlist_id INTEGER,
    song_id INTEGER,
    PRIMARY KEY (playlist_id, song_id),
    FOREIGN KEY (playlist_id) REFERENCES playlist(playlist_id),
    FOREIGN KEY (song_id) REFERENCES library(song_id)
)
''')

db.commit()

# 欢迎信息
print("WELCOME TO BlaBlah !!!")
print("We have three genre types available.\nPop\nHip Hop\nClassic\nWe also have five available artists.\nDean\nIsbean\nNoazy\nKepen\nDrip\n")

answer = input("Do you already have an account ?\nIf yes type 'y' if no type 'n'! ")
loggedIn = False
current_user_id = None

if answer == "y":
    # 登录逻辑
    username = input("Enter your username: ")
    password = input("Enter your password: ")
    cursor.execute('''
    SELECT user_id FROM users WHERE username = ? AND password = ?
    ''', (username, password))
    user = cursor.fetchone()
    if user:
        loggedIn = True
        current_user_id = user[0]
        print(f"Welcome back, {username}!")
    else:
        print("Invalid username or password.")
elif answer == "n":
    # 注册逻辑
    username = input("Choose a username: ")
    password = input("Choose a password: ")
    try:
        cursor.execute('''
        INSERT INTO users (username, password) VALUES (?, ?)
        ''', (username, password))
        db.commit()
        current_user_id = cursor.lastrowid
        loggedIn = True
        print(f"Account created successfully! Welcome, {username}!")
    except sqlite3.IntegrityError:
        print("Username already exists. Please choose another.")

# 登录后的功能菜单
if loggedIn:
    while True:
        print("\n--- Playlist Menu ---")
        print("1. Create an empty playlist")
        print("2. Add a song to a playlist")
        print("3. View all your playlists")
        print("4. Exit")
        choice = input("Enter your choice (1-4): ")
        
        if choice == "1":
            playlist_name = input("Enter the name of your new playlist: ")
            playlist_id = create_empty_playlist(current_user_id, playlist_name)
            print(f"Empty playlist '{playlist_name}' created with ID {playlist_id}!")
        elif choice == "2":
            # 显示用户的播放列表
            playlists = get_user_playlists(current_user_id)
            if not playlists:
                print("You don't have any playlists yet. Create one first!")
                continue
            print("\nYour playlists:")
            for idx, pl in enumerate(playlists, 1):
                print(f"{idx}. {pl['name']} (ID: {pl['playlist_id']})")
            pl_idx = int(input("Enter the number of the playlist to add to: ")) - 1
            if pl_idx < 0 or pl_idx >= len(playlists):
                print("Invalid choice.")
                continue
            target_pl_id = playlists[pl_idx]['playlist_id']
            
            # 显示歌曲库
            cursor.execute('''SELECT song_id, title, artist FROM library''')
            songs = cursor.fetchall()
            print("\nAvailable songs:")
            for idx, song in enumerate(songs, 1):
                print(f"{idx}. {song[1]} by {song[2]} (ID: {song[0]})")
            song_idx = int(input("Enter the number of the song to add: ")) - 1
            if song_idx < 0 or song_idx >= len(songs):
                print("Invalid choice.")
                continue
            target_song_id = songs[song_idx][0]
            
            add_song_to_playlist(target_pl_id, target_song_id)
        elif choice == "3":
            playlists = get_user_playlists(current_user_id)
            if not playlists:
                print("You don't have any playlists yet.")
                continue
            print("\nYour Playlists:")
            for pl in playlists:
                print(f"\nPlaylist: {pl['name']}")
                if pl['songs']:
                    print("Songs:")
                    for song in pl['songs']:
                        print(f"- {song[1]} by {song[2]} ({song[3]})")
                else:
                    print("(Empty playlist)")
        elif choice == "4":
            print("Goodbye!")
            break
        else:
            print("Invalid choice. Please enter a number between 1 and 4.")

db.close()

关键说明 #

• 空播放列表支持: When you create a playlist with create_empty_playlist, it only adds a record to the playlist table—no entries go into playlist_songs, so it's recognized as empty by the get_user_playlists function.
• 数据完整性: Foreign keys and primary keys prevent invalid operations (like adding a non-existent song to a playlist, or creating a playlist for a user that doesn't exist).
• 可扩展性: This setup makes it easy to add more features later, like removing songs from playlists or renaming playlists.

内容的提问来源于stack exchange，提问作者Justas Vaivada