Python & SQLite versions: Is it possible to import sqlite3 from PlexScriptHost interpreter to av

[SHoTT]

So I am not sure which way to approach this issue so I figured I'd ask for some suggestions here on the forums and get some expert opinions. Ideally I am looking for a non-hackish way to access the Plex database using Python, which does not force me to replace Python's included sqlite3.dll file with the one included with Plex, or a fresh copy from the SQLite website.

 

I currently have Python 2.7.6 installed on my Windows 8.1 x64 machine and when I recently attempted to run a query to update my titles to append the year at the end (like "The Usual Suspects (1995)"), I keep encountering the same error each time:

>>> import sqlite3
>>> conn = sqlite3.connect("C:/plex_temp/com.plexapp.plugins.library.db") # copied db
>>> cursor = conn.cursor()
>>> cursor.execute("""SELECT Title from metadata_items;""")
Traceback (most recent call last):
  File "", line 1, in 
sqlite3.DatabaseError: file is encrypted or is not a database

The copy of the database is not corrupt, there is no encryption, and it is indeed a database as the copy is accessible using SQLite GUI apps without a hitch! Apparently my installed Python 2.7.6 is what's causing the issue...

 

I did some googling and was able to conclude that the problem is with the default version of sqlite3.dll (v3.6.21) included with Python 2.7.6, which is far below the current version found on the SQLite download page (3.8.3.1), as well as the version included with Plex (3.8.0.2). Now after even more googling I was able to solve the problem and was able to successfully run a query by replacing the sqlite3.dll with the current version, or with the one included with Plex (a better idea, ensures compatibility), but to be honest this approach just doesn't seem right? I really don't know if this will cause any future compatibility issues down the road, and as afaik the accompanying _sqlite3.pyd was not designed for the updated version(s). Bottom line, this just seems like a really hackish way to deal with it. Having to replace/restore the sqlite3.dll each time just to access the Plex database? Is there really no other way to do this?

 

I figured Plex must include it's own interpreter so after digging through the Plex program folder I was able to start Plex's interpreter by running PlexScriptHost.exe from a cmd prompt. I thought this would solve the issue for sure and include Plex's sqlite3.dll version without the hackish approach, but when I attempt to import sqlite3 I receive an error stating:

>>> import sqlite3
Traceback (most recent call last):
  File "", line 1, in 
  File "C:\Program Files (x86)\Plex\Plex Media Server\python27.zip\sqlite3\__init__.py", line 24, in 
  File "C:\Program Files (x86)\Plex\Plex Media Server\python27.zip\sqlite3\dbapi2.py", line 27, in 
ImportError: No module named _sqlite3

ARRRGH!!! This would have been sooooooo much easier! :P

 

I tried a few more things like running the same command line used when PlexScriptHost.exe is launched at server startup, but naturally that produced it's own errors. I also attempted to copy Python's default _sqlite3.pyd file to Plex's program folder (as suggested in other posts for loading sqlite from an alternate location) but it also generates similar errors...and in the long run that really is no different then the original solution of copying Plex's sqlite3.dll to Python's DLLs folder.

 

I guess at this point I really just have two questions:

 

1) Is there a way to initiate PlexScriptHost.exe to allow me to import Plex's included sqlite3 version for querying..?

2) If the first approach is not possible, does anyone know of a way to force my installed python interpreter to import a different sqlite3.dll by specifying a custom location to the file, like the Plex Media Server folder?

 

Any help/feedback with this issue would be greatly appreciated!

 

P.S. Incase anyone wants to play with the SQL line I am using to update my titles, I've included it below. Note that SQL is not my strength so if anyone has suggestions on how to improve these queries, I'm all ears)

 

(Edit: Updated SQL query, added " (YEAR)" removal query)

-- Update Title with year "The Title (1987)" --
UPDATE metadata_items SET Title=CASE WHEN Year IS NULL OR Title LIKE ("% (" || "Year" || ")") THEN Title ELSE ("Title" || " (" || "Year" || ")") END;
– Remove Year from Title –

UPDATE metadata_items SET Title=SUBSTR(Title, 1, LENGTH(Title)-7) WHERE Title LIKE ("% (" || “Year” || “)”);

[jackdintruff]

A few weeks ago I wanted to be able to query my Plex Server's DB to see if a particular episode of a show was already in my library (the actual use case doesn't matter).  I tried to do something similar to you but didn't encounter the .dll issues because I'm running PMS on Ubuntu Server 14.04.  Instead, I encountered some issues when trying to read the DB directly so I instead copied it to another directory and read it from there. Bam.  Worked like a charm.  The actual process for determining if an episode was already there was a little more difficult because the DB schema that Plex uses is kind of hard to navigate, but if you open your DB file in a database browser like http://sourceforge.net/projects/sqlitebrowser/ you'll be able to see some patterns.  Basically, you need to query for the index of a show (by name), then look for seasons of shows where their parent index is the same as the show's index. Then you get the season index and look for episodes where the parent index is the same as the season index you just got.  I think a similar (but simpler) process could be used to query for movies, but my particular use case didn't call for it.  Here's a truncated version of the code I wrote to perform this task:

#!/usr/bin/python
import sqlite3, shutil
# Copy the db to another place so we can read it safely
shutil.copy('/var/lib/plexmediaserver/Library/Application Support/Plex Media Server/Plug-in Support/Databases/com.plexapp.plugins.library.db', '/tmp/')
conn = sqlite3.connect('/tmp/com.plexapp.plugins.library.db')
curs = conn.cursor()
show = “Workaholics”

season = 1

episode = 1
Okay this next line needs some explaining:
metadata_items: this is the table that contains the metadata for your library
id: the index we’re looking for to plug into the parent index in the next query
library_section_id=1: In my case 1 is for tv shows, 2 is for movies
metadata_type=2: this indicates we’re looking for a tv show, movies are type 1
firstquery = curs.execute(‘select id from metadata_items where library_section_id=1 and metadata_type=2 and title="’ + str(show) + ‘"’)

firstresult = firstquery.fetchone()
if not firstresult:

print "[PLEX] Couldn’t find the show: " + str(show)

raise Exception(“Show " + show + " not found in Plex”)
Incrementing metadata_type to 3 for seasons of tv shows
You can also see we’re passing in the firstresult so we can find seasons who belong to the show we’re looking for
secondquery = curs.execute(‘select id from metadata_items where library_section_id=1 and metadata_type=3 and index=’ + str(season) + ’ and parent_id=’ + str(firstresult[0]))

secondresult = secondquery.fetchone()
if not secondresult:

print "[PLEX] Couldn’t find that season of " + str(show)

raise Exception(“Season not found in Plex”)
metadata_type=4: looking for episodes only
thirdquery = curs.execute(‘select id from metadata_items where library_section_id=1 and metadata_type=4 and index=’ + str(episode) + ’ and parent_id=’ + str(secondresult[0]))

thirdresult = thirdquery.fetchone()

if thirdresult:

print "[INFO] Plex already has " + str(show) + " season " + str(season) + ", episode " + str(episode)

else:

print "[PLEX] Couldn’t find that episode of " + str(show)

raise Exception(“Episode not found in Plex”)