Tool to populate Zotero's date-added and date-modified values from the "extra" field resulting from importing from BibDesk

import_bibdesk_dates.py

#!/usr/bin/env python3

# Usage: python3 import_bibdesk_dates.py path/to/zotero.sqlite
# Will only touch records that have not been synced (i.e. "version" == 0)

import sqlite3
import re
import datetime
import pytz
import dateutil.parser
import sys
import os

default_dbfile = "~/Zotero/zotero.sqlite"

def main(dbfile=default_dbfile):
    try:
        con = sqlite3.connect(os.path.expanduser(dbfile),
                              isolation_level="DEFERRED")
        extras = getExtras(con)
        datesAdded = [i for i in [(extractDateAdded(value), itemID)
                      for (value, itemID) in extras] if i[0] is not None]
        print("{} items found with tex.date-added\n".format(
            len(datesAdded)))
        datesModified = [i for i in [(extractDateModified(value), itemID)
                         for (value, itemID) in extras] if i[0] is not None]
        print("{} items found with tex.date-modified\n".format(
           len(datesModified)))
        result = updateDatesAdded(con, datesAdded)
        result = updateDatesModified(con, datesModified)
        con.commit()
        print("Records updated\n")
    except Exception:
        con.rollback()
        raise
    finally:
        con.close()

def getExtras(con):
    cur = con.execute("""SELECT
            itemDataValues.value,
            items.itemID
        FROM items
            INNER JOIN itemData
                ON items.itemID == itemData.itemID
            INNER JOIN itemDataValues
                ON itemData.valueID == itemDataValues.valueID
            INNER JOIN fieldsCombined
                ON itemData.fieldID == fieldsCombined.fieldID
        WHERE fieldsCombined.fieldName == 'extra'
        AND items.version == 0
        AND itemDataValues.value LIKE '%tex.date-%';""")
    results = cur.fetchall()
    return results

def extractDateAdded(string):
    datestring = re.search(r'date-added: *(.*)', string).group(1)
    if datestring is not None:
        return makeUTC(dateutil.parser.parse(datestring))

def extractDateModified(string):
    datestring = re.search(r'date-modified: *(.*)', string).group(1)
    if datestring is not None:
        return makeUTC(dateutil.parser.parse(datestring))

def updateDatesAdded(con, newdates):
    con.executemany("UPDATE items set dateAdded = ? where itemID = ?", newdates)

def updateDatesModified(con, newdates):
    con.executemany("UPDATE items set dateModified = ? where itemID = ?", newdates)

def makeUTC(date):
    return date.astimezone(pytz.utc).replace(tzinfo=None)

if (__name__ == "__main__"):
   main(*(sys.argv[1:]))