amarok2-migrate-stats.py

#!/usr/bin/env python
# copy-ratings-to-mysqle.py
# @Author:      Diwaker Gupta (diwaker@floatingsun.net)
# @Last Change: 2009-01-04

import os
import sys
import optparse

import re

from pysqlite2 import dbapi2 as sqlite
import MySQLdb as mysql

parser = optparse.OptionParser("%prog [options] old-collection new-collection")
parser.add_option("--verbose", "-v", action = "store_true", dest = "verbose")

(options, args) = parser.parse_args()

if len(args) < 1:
    parser.print_help()
    sys.exit(1)

old_conn = sqlite.connect(args[0])
old_conn.text_factory = str
old = old_conn.cursor()

new_conn = mysql.connect(db="amarok", port=12345, unix_socket="/home/XXX/.kde/share/apps/amarok/sock")
new = new_conn.cursor()

new.execute("select id,uniqueid,rpath from urls")
songs = new.fetchall()
for (urlid,uniqueid,rpath) in songs:
  rpath = re.sub("'", "''", rpath)
  query = "select * from statistics where url='%s'" % rpath
  print query
  old.execute(query)
  vals = old.fetchone()
  if vals != None:
    oldurl, olddeviceid, createdate, accessdate, percentage, rating, playcounter, olduniqueid, deleted = vals
    query = """insert into statistics (url, createdate, accessdate, score, rating, playcount, deleted) values (%d, %d, %d, %.2f, %d, %d, %d)"""% (urlid, createdate, accessdate, percentage, rating, playcounter, deleted)
    try:
      new.execute(query)
    except:
      continue

new_conn.commit()