Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Tool to populate Zotero's date-added and date-modified values from the "extra" field resulting from importing from BibDesk
#!/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:]))
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment