KODI SQL

kodi_sql.py

https://kodi.wiki/view/List_of_built-in_functions
  
https://kodi.wiki/view/Databases/MyVideos
 
https://www.reddit.com/r/Addons4Kodi/comments/c4szk4/next_playlist_next_up_episodes_playlist/
  
https://henryjfry.github.io/repository.nextup/repo/

import sqlite3
con = sqlite3.connect('/home/osmc/.kodi/userdata/Database/MyVideos116.db')
cur = con.cursor()

#last played episode
print(cur.execute("SELECT * from files order by lastplayed desc limit 1").fetchall())

#episode details for last played episode
print(cur.execute('SELECT * from episode where idfile=(SELECT idfile from files where playcount > 0 order by lastplayed desc limit 1)').fetchall())

#get episodeid for last watched episode
print(cur.execute("SELECT idepisode from episode where idfile=(SELECT idfile from files where playcount > 0 order by lastplayed desc limit 1)").fetchall())


print(cur.execute("SELECT * from tvshowview;").fetchall())


print(cur.execute("SELECT * from files order by lastplayed desc limit 1").fetchall())

print(cur.execute("SELECT  OBJECT_SCHEMA_NAME(v.object_id) schema_name, v.name FROM  sys.views as v;").fetchall())

print(cur.execute("select * inprogress").fetchall())


print(cur.execute("SELECT  episode.*, files.strFileName AS strFileName, path.strPath AS strPath, files.playCount AS playCount, files.lastPlayed AS lastPlayed,  files.dateAdded AS dateAdded, tvshow.c00 AS strTitle, tvshow.c14 AS strStudio, tvshow.c05 AS premiered, tvshow.c13 AS mpaa, bookmark.timeInSeconds AS resumeTimeInSeconds, bookmark.totalTimeInSeconds AS totalTimeInSeconds, seasons.idSeason AS idSeason FROM episode JOIN files ON files.idFile=episode.idFile  JOIN tvshow ON  tvshow.idShow=episode.idShow LEFT JOIN seasons ON seasons.idShow=episode.idShow AND seasons.season=episode.c12  JOIN path ON  files.idPath=path.idPath LEFT JOIN bookmark ON   bookmark.idFile=episode.idFile AND bookmark.type=1;").fetchall())