Please read the notes in the code for details!
**this is just my first run of code, I still have some cleanup and better error handling to do, as well as command line access, and a few more tools yet to be added**
# -*- coding: utf-8 -*-
import sqlite3 as sql
import os, re, sys
import traceback as tb
Folders2Genres
For years I have been organizing my media within genre folders but since I starting using Plex
I was forced to deal with multiple genres per movie…which made my standard searching quite
annoying! Example, if I search for Sci-Fi movies only, I will also get movies listed under
Action, Crime, Animation, etc… With a large collection, manually editing each flick was just
ridiculous so I had to find a new way to batch process the job…
And here it is…
how it works:
Imagine a folder structure like so…
Movies >>
- Action
- Adventure
- Animation
- Comedy
(and so on…)
Within each genre folder are the movies, single layered (atm, it doesn’t compensate for sub folders).
Currently the script is set to scan the paths of each file, then parse the genre folder, create a tag
for it (if not already present), remove all other associated tags, then set the new genre folder as the
tag and lock the field. Also note that this will only add Genres to the lock, all previously locked
fields should be retained.
NOTE Please use forward slashes for your paths, even on Windows! NOTE
Unfortunately as not a single Plex developer would answer any of my questions, I would like to thank only myself
for my hard work. Also note that do to the lack of response from the Plex devs, if there are any logic errors
or issues I have overlooked, please let me know… Otherwise take it up with the Plex dev’s because I simply
can’t verify a single thing until one of them bothers to reply!! With that said, please note that this script has
run flawlessly on my own systems so far, including through networked drives, so it should be safe. Also
note that I have only tested this script on Windows so if anyone has suggestions to improve compatibility,
I am all ears…err eyes!!
DISCLAIMER This code is provided on a “As-Is” basis. I take no responsibility over any issues caused by
this script so please ensure you backup your database prior to running this code!! DISCLAIMER
##################################### SET VARIABLES #####################################
path to your Plex database (It is highly recommended that you use a copy of your db instead of the live db)
database_folder = “C:/path/to/my/backed/up/databse/com.plexapp.plugins.library.db”
The root folder where your genre folders are located (networked paths accepted (SMB))
movies_root_folder = “C:/path/to/my/movie/genre/folders”
Ignore any Genre folders within the ‘movies_root_folder’ by adding the folder name to the exempt list.
exempt_folders = [""]
###################################### CODE BEGINS ######################################
verify path exists and add genre folders to a list (need more error handling)
genre_folders = [item for item in os.listdir(movies_root_folder) if os.path.isdir("%s/%s" % (movies_root_folder, item))]
Sort genres alphabetically
genre_folders.sort()
try:
tags = {}
# Establish connection, create cursor
conn = sql.connect(database_folder)
cursor = conn.cursor()
for folder_name in genre_folders:
try:
# Get list of tags, compare folder names to existing tags, skip if found
cursor.execute("""SELECT id, tag_type FROM tags WHERE tag='%s'""" % (folder_name))
fetched = cursor.fetchall()
# if folder_name not in table 'tags', insert it...
if fetched == []:
# creating new tags, where needed
insert = """INSERT INTO tags(tag, tag_type, user_thumb_url, user_art_url, user_music_url, created_at, updated_at) """
insert += """VALUES ("%s", 1, "", "", "", CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);""" % (folder_name)
cursor.execute(insert)
conn.commit()
# fetch the appended 'tags'
cursor.execute("""SELECT id, tag_type FROM tags WHERE tag='%s'""" % (folder_name))
fetched = cursor.fetchall()
#create map of custom tags for later use
tags[folder_name] = [fetched[0][0], fetched[0][1]]
except sql.Error, error:
if conn:
conn.rollback()
print tb.format_exc()
sys.exit(1)
# Retrieve media information from table media_parts, exclude id's that no longer exist in 'metadata_items' table
# not sure why deleted content is left in media_items table to begin with?
cursor.execute("""SELECT media_item_id, file FROM media_parts WHERE media_item_id IN (SELECT id FROM metadata_items);""")
for row in cursor.fetchall():
m_id, m_file = row
# parse folder name from the 'file' path to use as the 'genre'
folder_name = re.findall("%s/(.*?)/" % (movies_root_folder), m_file.replace("\\", "/"))[0]
print m_id, tags[folder_name][0]
# check if custom tag already exists (Action, Crime, etc. are pretty standard and are usually added automatically...)
cursor.execute("""SELECT id FROM taggings WHERE metadata_item_id=%s AND tag_id=%s;""" % (m_id, tags[folder_name][0]))
if cursor.fetchall() == []:
try:
# If the custom tag does not exist, insert it...
insert = """INSERT INTO taggings(metadata_item_id, tag_id, `index`, text, thumb_url, created_at) """
insert += """VALUES('%s', '%s', 0, '', '', CURRENT_TIMESTAMP);""" % (m_id, tags[folder_name][0])
cursor.execute(insert)
conn.commit()
except sql.Error, error:
if conn:
conn.rollback()
print tb.format_exc()
sys.exit(1)
# check if custom tag was added successfully...
cursor.execute("""SELECT id FROM taggings WHERE metadata_item_id=%s AND tag_id=%s;""" % (m_id, tags[folder_name][0]))
if cursor.fetchall() == []:
raise Exception, "BlameThePlexDevsForNotAnsweringMe!---Error"
else:
print "verified!"
try:
# Once new tags are added, delete all current tags that do not match the folder name *NOTE* tag_type=1 IS IMPORTANT! *NOTE*
# Adding 'tag_type=1' filters out all associated tags that are not considered a genre, PRIOR to tag deletion!
cursor.execute("""DELETE FROM taggings WHERE metadata_item_id=%s AND tag_id IN (SELECT id FROM tags WHERE tag!='%s' AND tag_type=1);""" % (m_id, folder_name))
# Update 'tags_genre' with the folder name containing the current file
cursor.execute("""UPDATE metadata_items SET tags_genre='%s' WHERE id=%s;""" % (folder_name, m_id))
# Fetch current 'user_fields' value to obtain the 'lockedFields' data
cursor.execute("""SELECT user_fields FROM metadata_items WHERE id=%s;""" % (m_id))
fetched = cursor.fetchall()
user_fields = fetched[0][0]
print [user_fields]
# Search 'lockedFields' data for id# 15, which represents the 'genre' field...
if not "15" in user_fields:
# If 'user_fields' is empty, only add id# 15
if user_fields == "":
# If id# 15 is not found...
locked = ["15"]
else:
# If 'user_fields' is not empty, preserve the list of currently locked id's
locked = user_fields.split("=")[1].split("|")
# append id# 15 to current locked list
locked.append("15")
# Sort list to organize id's in asc order.
locked.sort(key=float)
# compile usable string with locked data to inject back into 'user_fields'
new = "lockedFields=%s" % ("|".join(locked))
cursor.execute("""UPDATE metadata_items SET user_fields='%s' WHERE id=%s""" % (new, m_id))
conn.commit()
#sys.exit() #uncomment to limit run to first entry, for testing...
except sql.Error, error:
if conn:
conn.rollback()
print tb.format_exc()
sys.exit(1)
except sql.Error, error:
if conn:
conn.rollback()
print tb.format_exc()
sys.exit(1)
finally:
if conn:
print “Session ended, closing connection…”
conn.close()
