check whether all notes in evernote3 are migrated to 3 to 4

gistfile1.py

# coding: utf-8

# check whether all notes in evernote3 are migrated to 3 to 4

# 1. open an evernote 4.x database (sqlite)
# 2. open an evernote 3.x export (.enex) 
# 3. check for each note in enex, if not in evernote 4 database, then print out the title

import sqlite3
from lxml import etree
from datetime import date

def main(enex_file_name, exb_file_name):
    f = file(enex_file_name, "r")
    enex = etree.parse(f)
    notes = enex.xpath("//note")

    conn = sqlite3.connect(exb_file_name)
    cursor = conn.cursor()
    sql = """SELECT title, date_created, date_updated, source_url
FROM note_attr WHERE is_deleted is null AND title=? COLLATE NOCASE""" 
    
    for note in notes:
        title = url = ""
        etitle = note.xpath("title")
        if len(etitle)>0:
            title = etitle[0].text
        eurl = note.xpath("note-attributes/source-url")
        if len(eurl)>0:
            url = eurl[0].text
        #print("  - [[%(url)s][%(title)s]]" % locals())
        
        rs = cursor.execute(sql, [(title)])
        if not rs.fetchone():
            print("  - [[%(url)s][%(title)s]]" % locals())                


def reverse_check(exb_file_name, enex_file_name, cutoff_date=None):
    """Check where all notes in exb are also exist in enex"""    

    f = file(enex_file_name, "r")
    enex = etree.parse(f)
    notes = enex.xpath("//note")
    
    notes_dict = {}  # hash: title -> xml element
    for note in notes:
        title = url = ""
        etitle = note.xpath("title")
        if len(etitle)>0:
            title = etitle[0].text
        notes_dict[title] = etitle
        
    cutoff = date(2012, 05, 20).toordinal()
    conn = sqlite3.connect(exb_file_name)
    cursor = conn.cursor()
    sql = """SELECT title, date_created, date_updated, source_url
FROM note_attr WHERE is_deleted is null
and date_updated > %d """ % cutoff

    rs = cursor.execute(sql)
    for record in rs:
        if not record:
            break
        title = record[0]
        if not notes_dict.has_key(title):
            print title
    

if __name__ == '__main__':
    #main('evernote3.enex', 'bamanzi.exb')
    reverse_check('bamanzi.exb', 'evernote3.enex')