Playing around with library software

I actually converted my gcstar library to zotero using this script:

#!/usr/bin/python

# first, convert your library to a bibtex export
#
# this can be done by:

# 1. converting to tellico (or importing straight from tellico)
# 2. conver your tellico library to a bibliography
# 3. export to a bibtex file
# 4. import the bibtex file into Zotero

# the "accessDate" field will be empty - this is what this script
# tries to fix

from __future__ import print_function
import time
import xml.etree.ElementTree as ET
import sqlite3
import sys
import glob

conn = sqlite3.connect(glob.glob('/home/anarcat/.zotero/zotero/*.default/zotero/zotero.sqlite'))
sql = conn.cursor()

sqlcnt = 0
sqlentries = []
for row in sql.execute('SELECT * FROM items i INNER JOIN itemData id ON id.itemID = i.itemID INNER JOIN itemDataValues idv ON idv.valueID = id.valueID WHERE i.itemTypeID = 2 AND (fieldID=110);'):
    #print row
    sqlentries.append(row[-1])
    sqlcnt += 1
sqlentries.sort()

tree = ET.parse('biblio.gcs')
root = tree.getroot()

biblioentries = []
bibliodates = []
bibliocnt = 0
for item in root:
    try:
        biblioentries.append(item.attrib['title'])
        #print item.attrib['title'], time.strftime('%Y/%m/%d', time.strptime(item.attrib['added'], '%d/%m/%Y'))
        bibliodates.append((item.attrib['title'], time.strftime('%Y-%m-%d', time.strptime(item.attrib['added'], '%d/%m/%Y'))))
        bibliocnt += 1
    except ValueError:
        #print ''
        bibliocnt += 1
    except KeyError:
        pass
biblioentries.sort()

#print bibliodates
if bibliocnt == sqlcnt:
    print("count is good", file=sys.stderr)
else:
    print("count differs, sqlcnt: %d, bibliocnt: %d" % (sqlcnt, bibliocnt), file=sys.stderr)

for entry in bibliodates:
    # XXX: innefficient, O(n^2)
    if entry[0] in sqlentries:
        print(('INSERT INTO itemDataValues (value) VALUES ("%s");' % entry[1]).encode('utf8'))
        print(('INSERT INTO itemData (itemID, fieldID, valueID) VALUES ((SELECT i.itemID FROM items i INNER JOIN itemData id ON id.itemID = i.itemID INNER JOIN itemDataValues idv ON idv.valueID = id.valueID WHERE i.itemTypeID=2 AND id.fieldID=110 AND idv.value="%s"), 27, LAST_INSERT_ROWID());' % entry[0]).encode('utf8'))

        try:
            sql.execute('INSERT INTO itemDataValues (value) VALUES (?);', (entry[1],))
            valueID = 'LAST_INSERT_ROWID()'
        except sqlite3.IntegrityError: # column value is not unique
            sql.execute('SELECT valueID FROM itemDataValues WHERE value = ?', (entry[1],))
            valueID = sql.fetchone()[0]
        try:
            sql.execute('INSERT INTO itemData (itemID, fieldID, valueID) VALUES ((SELECT i.itemID FROM items i INNER JOIN itemData id ON id.itemID = i.itemID INNER JOIN itemDataValues idv ON idv.valueID = id.valueID WHERE i.itemTypeID=2 AND id.fieldID=110 AND idv.value=?), 27, %s);' % valueID, (entry[0],))
        except sqlite3.IntegrityError:
            print((u"book %s already had a date field" % entry[0]).encode('utf-8'), file=sys.stderr)
            # cleanup the duplicate, if it was ours
            try:
                sql.execute('DELETE FROM itemDataValues WHERE valueID=LAST_INSERT_ROWID();')
            except sqlite3.IntegrityError:
                pass
    else:
        print((u"biblio entry %s missing from sql" % entry[0]).encode('utf-8'), file=sys.stderr)

conn.commit()
conn.close()

# XXX: innefficient, O(n^2)
for sqlentry in sqlentries:
    if sqlentry not in biblioentries:
        print((u"sql entry %s missing from biblio" % sqlentry).encode('utf-8'), file=sys.stderr)