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.