See http://www.johannesbader.ch/2014/01/xbmc-library-cleanup-part-i-missing-and-duplicate-movies/

missing_or_duplicate_movies.py

import argparse
import sqlite3 as lite
import os

def _open_db(db):
    try:
        con = lite.connect(db)
        cur = con.cursor()
        return (cur,con)
    except lite.Error as e:
        print("Could not open database %s: %s" % (db,e))
        quit(1)

def find_flaws(db, movie_folder):
    """ find missing or duplicate movies in video library
    
        lookup all movies from a folder in video db
        and find the following flaws:
       
        (1) the movie is not in the libary (won't show up in XBMC)
        (2) the movie has multiple entries (will show up multiple times)
        
        Args:
            db: path to 'MyVideos**.db' (XBMC video library)
            movie_folder: path to directory with movies in separate folders
            
        Returns:
            flaws: dictionary of flaws.
                   key: movie name
                   value: flaw name
    """

    (cur,con) = _open_db(db)
    flaws = dict()
    movies = os.listdir(movie_folder)

    # iterate over all subdirectories of movies, i.e., over all movies on disk
    for i, movie in enumerate(movies):
        # show progress
        print "\r{0:>3}/{1:<3} {2:<100}:".format(i, len(movies), movie),
        path = os.path.join(movie_folder, movie)
                
        # 1) check table 'path' for movies without 'idPath' (movie is missing)
        #    also checks 'VIDEO_TS' subfolder for DVDs
        cur.execute('SELECT idPath FROM path WHERE strPath=? or ' +
                'strPath like ?', (path + os.sep, 
                 os.path.join(path,"VIDEO_TS") + os.sep))                
        res = cur.fetchone()
        
        if not res:
            flaws[movie] = "missing"
            continue
        else:
            idPath = res[0]
            
        # 2) check table 'files' for multiple entry (movie duplicates)        
        cur.execute('SELECT idFile FROM files WHERE idPath=?', (idPath,))
        idsFile = [tmp[0] for tmp in cur.fetchall()]        
        if len(idsFile) > 1:
            flaws[movie] = "{0} duplicate(s)".format(len(idsFile)-1)            
        elif len(idsFile) == 0:
            flaws[movie] = "missing"
        
    # clean progress line
    print "\r" + 100*" " + "\r",
    con.close()
    return flaws
    
if __name__=="__main__":
    parser = argparse.ArgumentParser(description='find flaws in XBMC video database')
    parser.add_argument('db', help='path to myvideos**.db library')
    parser.add_argument('movie_folder', help='path to movie folder')   
    args = parser.parse_args()
    
    flaws = find_flaws(args.db, args.movie_folder)
    for movie, flaw in sorted(flaws.items()):
        print("{0:<15}: {1} ".format(flaw, movie))