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 15
in metadata_type = 15
is for
playlists.
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 files-to-delete.txt
.
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';
The 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.