OT. Python and SQL against the database

[dane22]

Banging my head against the wall here, so hoping for some insight.

 

 

Trying to run the following query in python:

mySQL = 'SELECT metadata_items.title, M.file, M.extra_data, M.hash FROM media_parts M INNER JOIN (media_items INNER JOIN metadata_items ON media_items.metadata_item_id = metadata_items.id) ON M.media_item_id = media_items.id WHERE (((metadata_items.library_section_id)=' + str (answer) + '))'
myMedias = conn.cursor().execute(mySQL).fetchall()

When running, it keeps telling me, the following:

 

sqlite3.OperationalError: no such column: metadata_items.title

 

So it seems like my SQLite skills are a show stopper from within python.

 

The query does however runs okay in SQLite Manager, so scratching my head here....

 

Any takers?

 

/T

[ljunkie]

Banging my head against the wall here, so hoping for some insight.

Trying to run the following query in python:

mySQL = 'SELECT metadata_items.title, M.file, M.extra_data, M.hash FROM media_parts M INNER JOIN (media_items INNER JOIN metadata_items ON media_items.metadata_item_id = metadata_items.id) ON M.media_item_id = media_items.id WHERE (((metadata_items.library_section_id)=' + str (answer) + '))'
myMedias = conn.cursor().execute(mySQL).fetchall()

When running, it keeps telling me, the following:

sqlite3.OperationalError: no such column: metadata_items.title

So it seems like my SQLite skills are a show stopper from within python.

The query does however runs okay in SQLite Manager, so scratching my head here....

Any takers?

/T

Here is a fix, even though the sql is oddly formatted.

SELECT mi.title, M.file, M.extra_data, M.hash FROM media_parts M INNER JOIN (media_items INNER JOIN metadata_items ON media_items.metadata_item_id = metadata_items.id) mi ON M.media_item_id = mi.id WHERE (((mi.library_section_id)=6))

[ljunkie]

Here is another way. I can read it better :)

SELECT mdi.title, mp.file, mp.extra_data, mp.hash 
 FROM metadata_items mdi  
 inner join media_items mi on mdi.id = mi.metadata_item_id  
 inner join media_parts mp on mi.id = mp.media_item_id
 where mdi.library_section_id = 6

Banging my head against the wall here, so hoping for some insight.

Trying to run the following query in python:

mySQL = 'SELECT metadata_items.title, M.file, M.extra_data, M.hash FROM media_parts M INNER JOIN (media_items INNER JOIN metadata_items ON media_items.metadata_item_id = metadata_items.id) ON M.media_item_id = media_items.id WHERE (((metadata_items.library_section_id)=' + str (answer) + '))'
myMedias = conn.cursor().execute(mySQL).fetchall()

When running, it keeps telling me, the following:

sqlite3.OperationalError: no such column: metadata_items.title

So it seems like my SQLite skills are a show stopper from within python.

The query does however runs okay in SQLite Manager, so scratching my head here....

Any takers?

/T

Here is a fix, even though the sql is oddly formatted.

SELECT mi.title, M.file, M.extra_data, M.hash FROM media_parts M INNER JOIN (media_items INNER JOIN metadata_items ON media_items.metadata_item_id = metadata_items.id) mi ON M.media_item_id = mi.id WHERE (((mi.library_section_id)=6))

[dane22]

Sir.....Your insights, fast response time as well as knowledge about both Python/SQLite and Plex and is breathtaken

I salute you for this, and even though my wife will hate me for it, my next born will be named ljunkie :wub:

/Tommy

[dane22]

And if you are wondering about what I use it for, take a peek here:

https://github.com/ukdtom/Plex_Offline_Indexer

And yes, since been a newbie to both Python as well as SQLite, I can use all the help I can get here, so if you, or others, got some spare-time, then please fork away, and improve my pathetic code, cuz I kinda think this project is cool

Best Regards

Tommy