Extract notes from a journey.cloud sqlite database

journey-extract-notes-from-sqlite.py

'''Extract notes from a journey.cloud sqlite database.

First install journey.cloud on OSX,
locate the database files in folder
~/Library/Containers/com.journey.ios.app/Data/Library/Application\ Support/Journey/Journey.sqlite
and run this script with following arguments:
- the path to the sqlite database
- the directory to write notes into
'''

import sys
import os
import re
import sqlite3
from datetime import datetime, timedelta
from html import unescape
from html.parser import HTMLParser

def inline_html(text):
    text = unescape(text)
    text = text.replace('<p dir="auto">', '')
    text = text.replace('</p>', '\n')
    text = re.sub('</?span>', '', text)
    text = re.sub('<br +/?>', '\n', text)
    text = re.sub(' +\n', '\n', text)
    return text

con = sqlite3.connect(sys.argv[1])
target = sys.argv[2]
os.makedirs(target)

cur = con.cursor()
filenames = set()
for id, timestamp, text, timezone in cur.execute("SELECT Z_PK, ZDATEJOURNAL, ZTEXT, ZTIMEZONE FROM ZJOURNAL ORDER BY ZDATEJOURNAL;"):
    text = inline_html(text)
    dt = datetime.fromtimestamp(timestamp)
    dt = dt.replace(year=dt.year + 31)

    filename = dt.strftime('%y-%m-%d %H-%M')
    if filename in filenames:
        filename += '-bis' # TODO: check that no more than 2 similar dates are existing
    filenames.add(filename)

    with open(f"{target}/{filename}.md", 'w') as f:
        print(f"Write {filename}")
        footer = f'\n(Imported from journey id={id} ts={timestamp} timezone={timezone})\n'
        f.write(text + footer)