How to get a list of filenames from a Plex playlist
I recently wanted to delete some files from my Plex server. Plex has a feature to allow deleting of files through the web interface, but it makes me feel slightly uneasy having that feature turned on due to security concerns, so I chose to keep it off, and for this one-off task I decided to delete the files manually.
I needed a list of filenames so I started by adding all the files I wanted to delete to a playlist called "To Delete". Plex uses a SQLite database to store metadata. This is what I used to get the information from the playlist.
Connect to the SQLite database like this:
1 sqlite3 '/var/lib/plexmediaserver/Library/Application Support/Plex Media Server/Plug-in Support/Databases/com.plexapp.plugins.library.db'
We can then list all playlists. The
metadata_type = 15 is for
1 sqlite> select title from metadata_items where metadata_type = 15;
You can see that the output includes the "To Delete" playlist.
1 2 3 4 5 Christmas Pixar Comedy classics Star Wars To Delete
Ideally, we'd like a list of filenames in a text file. This makes it easy to
process later. The
sqlite client has a
.output command that allows you to
redirect output from queries to an external file.
1 sqlite> .output files-to-delete.txt
When we run subsequent queries, the output will end up in
Now, we can run a query to get a list of filenames. I won't pretend to
understand the whole structure of this query, but it works for me. Thanks to
this Reddit thread. Note the
where metadata_items.title = 'To
Delete' clause where we specify the playlist name.
1 2 3 4 5 6 sqlite> select file from media_parts left outer join media_items on media_items.id = media_parts.media_item_id left outer join play_queue_generators on play_queue_generators.metadata_item_id = media_items.metadata_item_id left outer join metadata_items on metadata_items.id = play_queue_generators.playlist_id where metadata_items.title = 'To Delete';
files-to-delete.txt file now contains a list of filenames for the movies
on the "To Delete" playlist. We can now use that list to delete the files. In my
case, I used a loop in fish shell.
1 2 3 for f in (cat files-to-delete.txt) rm -i -v $f end
There are certainly many ways to do this, but this is how I did it.