| |
| import sqlite3 |
| from pathlib import Path |
|
|
| DB_PATH = Path(__file__).parent / "music_player.db" |
|
|
|
|
| def init_db() -> None: |
| """Create the songs table if it doesn't exist.""" |
| with sqlite3.connect(DB_PATH) as conn: |
| conn.execute(""" |
| CREATE TABLE IF NOT EXISTS songs ( |
| id INTEGER PRIMARY KEY AUTOINCREMENT, |
| title TEXT NOT NULL, |
| artist TEXT NOT NULL DEFAULT 'Unknown Artist', |
| file TEXT NOT NULL, |
| cover TEXT, |
| lyrics TEXT, |
| uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP |
| ) |
| """) |
| conn.commit() |
|
|
|
|
| def fetch_songs() -> list[dict]: |
| """Return all songs ordered by upload date (newest first).""" |
| with sqlite3.connect(DB_PATH) as conn: |
| conn.row_factory = sqlite3.Row |
| rows = conn.execute( |
| "SELECT id, title, artist, file, cover, lyrics " |
| "FROM songs ORDER BY uploaded_at DESC" |
| ).fetchall() |
| return [dict(r) for r in rows] |
|
|
|
|
| def insert_song( |
| title: str, |
| artist: str, |
| file_path: str, |
| cover_path: str | None, |
| lyrics: str, |
| ) -> dict: |
| """Insert a new song row and return the full inserted record.""" |
| with sqlite3.connect(DB_PATH) as conn: |
| cur = conn.execute( |
| "INSERT INTO songs (title, artist, file, cover, lyrics) " |
| "VALUES (?, ?, ?, ?, ?)", |
| (title, artist, file_path, cover_path, lyrics), |
| ) |
| new_id = cur.lastrowid |
| conn.commit() |
| row = conn.execute( |
| "SELECT id, title, artist, file, cover, lyrics " |
| "FROM songs WHERE id = ?", |
| (new_id,), |
| ).fetchone() |
| return dict(row) if row else {} |
