Query the Strawberry database to get filenames for a given playlists?

[thenoblesunfish]

A somewhat whimsical thought, but: before discovering Strawberry I was using a pretty low-level approach of mpd + ncmpcpp and bunch of homebrewed scripts to do things like transfer music to my phone (where I play it with PowerAmp). Maybe there's already a good way to transfer music to my phone (from OS X to an Android (Fairphone) phone) with Strawberry - all ears if so! But assuming there isn't, it seems like maybe I could just do something like

• Put a list of the playlists I want to sync in a text file
• Run a query directly on the Strawberry db to dump out the filenames (in order) of files in playlists with matching names
• Use that to generate .m3u files
• Use my existing scripts to move the files and .m3u playlists to the phone

Is the step about the query reasonable? I'd just want to be able to query for all the songs in a playlist with a given name. Perhaps I could even crib from the source code to export a playlist.

[thenoblesunfish]

This works great! So, so nice to just be able to directly work with the database.

Here is the key new script, that dumps out all the playlists to plain m3us (just relative paths) in the same directory.

I am also very happy to have this script as it means there is now little risk in using Strawberry - I can make my playlists there and if needbe, dump them back out to the plain m3us I use with ncmpcpp.

#!/usr/bin/env python
# Tested with
#   Strawberry 1.0.17
#   macOS 12.2 (Monterey)
#   Python 3.11.2
# Inspired by code from @TheBlackKoala posted at
# https://github.com/clementine-player/Clementine/issues/6214

import os
import sqlite3
import urllib.parse

# Update with the path to your database (or a copy of it)
DATABASE_PATH = os.path.join(os.environ["HOME"], "Library",
                             "Application Support", "Strawberry", "Strawberry",
                             "strawberry.db")

# Update with a root to strip from absolute paths
LIBRARY_ROOT = os.path.join(os.sep, "Volumes", "BigDrive", "music_library")


def _name_for_playlist(c, playlist_id):
    c.execute("SELECT name FROM playlists WHERE rowid=?", playlist_id)
    return c.fetchone()[0]


def _url_to_path(url):
    return urllib.parse.unquote(
        url.removeprefix("file://")).removeprefix(LIBRARY_ROOT).removeprefix(
            os.sep)


def _paths_for_playlist(c, playlist_id):
    c.execute(
        "SELECT s.url FROM playlist_items AS p JOIN songs AS s ON p.collection_id = s.rowid WHERE playlist = ?",
        playlist_id)
    return [_url_to_path(item[0]) for item in c.fetchall()]


def _write_plain_m3u_from_paths(name, paths):
    with open(f"{name}.m3u", "w") as m3u_file:
        for path in paths:
            m3u_file.write(f"{path}\n")


def main():
    connection = sqlite3.connect(DATABASE_PATH)
    cursor = connection.cursor()
    cursor.execute("SELECT DISTINCT playlist FROM playlist_items")
    for playlist_id in cursor.fetchall():
        name = _name_for_playlist(cursor, playlist_id)
        paths = _paths_for_playlist(cursor, playlist_id)
        _write_plain_m3u_from_paths(name, paths)


if __name__ == "__main__":
    main()