Use case
I wanted to run new Plex Media Server in Docker container for easier management and migration in the future. New server did not migrate old Plex files but scanned and re-index everything all over again. Plex was nice enough to sync all watch stats, but my music ratings was not synced. For this I needed a way to migrate all track ratings to the new plex server.
Solution
In a nutshell here is what needs to happen:
- Turn off old Plex server
- Copy the library database locally (or if it’s already local - for safety reasons). On my Synology, the location is:
/volume1/PlexMediaServer/AppData/Plex Media Server/Plug-in Support/Database/com.plexapp.plugins.library.db. - Extract the rating data for each music track into
csvfile - Use a python script to rate all matching music tracks in the new Plex server
Things to note:
- New plex server uses exactly the same data folder for my music as the old server (file names are the same)
- The music folder path was changed on the new server, therefore filesystem paths to music tracks are not the same
What’s needed:
- sqlite3 client
- python3
- plexapi (python library -
pip install plexapi)
Step-by-step
An SQL query to retrieve rating data from old Plex library database (edit upon need):
select
-- Rounding the half-star ratings
CAST(IIF(mis.rating % 2 == 0, mis.rating, mis.rating + 1) AS INT) as 'Rating',
-- Rewriting track file path to new Plex mount point
replace(mp.file, '/old/plex/music-collection', '/new/plex/music/location') as 'File'
from media_items mi
join media_parts mp on mi.id = mp.media_item_id
join metadata_items mda on mi.metadata_item_id = mda.id
join metadata_item_settings mis on mis.guid = mda.guid
join accounts a on mis.account_id = a.id
join library_sections ls on mda.library_section_id = ls.id
where
a.name = 'username' -- Which user ratings to get
and ls.name = 'Music' -- Which Plex library to query
and mis.rating is not null -- Get only tracks with rating
;
After edit the query to suit your need, remove the comments and make it a one liner command, so we could pass it in the sqlite3 CLI client easily:
sqlite3 -csv <path-to-library.db> "select CAST(IIF(mis.rating % 2 == 0, mis.rating, mis.rating + 1) AS INT) as 'Rating',replace(mp.file, '/old/plex/music-collection', '/new/plex/music/location') as 'File' from media_items mi join media_parts mp on mi.id = mp.media_item_id join metadata_items mda on mi.metadata_item_id = mda.id join metadata_item_settings mis on mis.guid = mda.guid join accounts a on mis.account_id = a.id join library_sections ls on mda.library_section_id = ls.id where a.name = 'username' and ls.name = 'Music' and mis.rating is not null;" > ratings.csv
Having the ratings.csv file, we already got all the data from the old Plex we need. It’s time to rate the tracks in the new Plex server. Here is a python script which will read the ratings.csv and rate each track in Plex which matches files in ratings.csv. Change the configuration variables as needed.
from plexapi.server import PlexServer
import csv
# Configuration variables
baseurl = 'http://localhost:32400'
token = 'PLEX_TOKEN'
ratingsPath = 'ratings.csv'
# Implementation details
db = {}
with open(ratingsPath) as csvfile:
reader = csv.reader(csvfile, delimiter=',')
for row in reader:
rating = row[0]
file = row[1]
db[file] = rating
plextracks = PlexServer(baseurl, token).library.section('Music').searchTracks()
for track in plextracks:
# Uncomment to clear all tracks rating before rate them.
# track.rate(None)
file = track.media[0].parts[0].file
if file in db:
track.rate(int(db[file]))
else:
print(file)
Execute via (assuming the script is named rating-migration.py and python executable is named python):
python rating-migration.py > not-rated.out
The script will print on stdout all unmatched tracks from the new Plex server.
Note: To obtain Plex Token check this guide.
That’s all - all tracks should be rated now.
Cheers!
