sum-watched-movies.py

#!/usr/bin/env python
# Sum of watched movie length in XBMC database

import sqlite3, glob, os

def minutes_to_str(minutes):
    years = days = hours = 0
    hours, minutes = divmod(minutes, 60)
    if hours > 0:
        days, hours = divmod(hours, 24)
        if days > 0:
            years, days = divmod(days, 365)

    str = ""
    if years > 0:
        str += "%d years, " % years
    if days > 0:
        str += "%d days, " % days
    if hours > 0:
        str += "%d hours, " % hours
    if minutes > 0:
        str += "and %d minutes." % minutes
    return str

pattern = os.path.expanduser("~/Library/Application Support/XBMC/userdata/Database/MyVideos*.db")
files = glob.glob(pattern)

if len(files) > 0:
    file = files[0]
    conn = sqlite3.connect(file)

    c = conn.cursor()

    c.execute("SELECT SUM(c11) FROM MOVIE JOIN files WHERE movie.idFile == files.idFile AND files.playCount >= 1")
    row = c.fetchone()
    print minutes_to_str(row[0])

    conn.close()