Kobo shelves building

import sqlite3

conn = sqlite3.connect('KoboReader.sqlite')

c = conn.cursor()

c.execute('''SELECT DISTINCT BookID FROM content''')

import re
re_path = re.compile(r'^file:///mnt/(?P<dev>[^/]*)/(?P<path>.*)/(?P<filename>[^/]*)$')

def splitpath(p):
    m = re_path.match(p)
    return (m.group('dev'), m.group('path'), m.group('filename'))

database = {}
for v in c.fetchall():
    p = v[0]
    if p and p.startswith('file:///mnt/'):
        dev, dirpath, filename = splitpath(p)
        if (dev,dirpath) not in database:
            database[(dev,dirpath)] = []
        database[(dev,dirpath)].append(filename)

for dev,dirpath in database:
    shelf = dirpath.replace('/','_')
    c.execute("SELECT * FROM Shelf WHERE InternalName='%s'" % shelf)
    if c.fetchone() is None :
        c.execute("INSERT INTO Shelf VALUES ('2012-07-25T19:59:29Z', NULL, '%s', '2012-07-25T19:59:29Z', '%s', NULL, 'false', 'true', 'false')" % (shelf,shelf))
        c.execute("SELECT * FROM Shelf WHERE InternalName='%s'" % shelf)
    for f in database[(dev,dirpath)]:
        fullname = u'file:///mnt/%s/%s/%s' % (dev,dirpath,f)
        fullname = fullname.replace("'","''")
        c.execute("SELECT * FROM ShelfContent WHERE ShelfName='%s' AND ContentId='%s'" % (shelf, fullname))
        r = c.fetchone()
        if r is None:
            c.execute("INSERT INTO ShelfContent VALUES ('%s','%s','2012-07-25T19:59:29Z','false','false')" % (shelf,fullname))

conn.commit()
c.close()