💾 Archived View for text.adventuregameclub.com › tech › 2021-06-21-kindle-to-sqlite3.gmi captured on 2022-07-16 at 14:38:12. Gemini links have been rewritten to link to archived content

-=-=-=-=-=-=-

Exporting Kindle Clippings to an Sqlite3 Database

I use a little python script to grab clippings out of my (early model) kindle's `My Clippings.txt` file and import them into an sqlite3 database. The clips and notes are in a consistant format, so it's possible to parse the file with some regular expressions, and a little logic. I found a script that did most of the work already, and converted it to send the clips to a database.

The sqlite3 database has a table for holding these clips. This is the basic structure of the table:

CREATE TABLE IF NOT EXISTS "clips" (
    `clipID`     INTEGER PRIMARY KEY AUTOINCREMENT,
    `bid`        INTEGER,
    `type`       TEXT,
    `location`   INTEGER NOT NULL,
    `text`       TEXT,
    `datestring` TEXT
);

For a little added efficiency, the import script will check for the latest date in the table and ignore any clips in `My Clippings.txt` from before that date.

This works well until my kindle runs out of charge completely and resets the date back to 1970. Then, because the kindle doesn't really show the date anywhere, I don't notice the problem for a while. (You'll see there's a little hack in the code that I use to grab those clips.)

There's also a table for books. Here's the basic structure:

CREATE TABLE IF NOT EXISTS "books" (
    `id`	INTEGER,
    `book`	TEXT,
    `Title`	TEXT,
    `SubTitle`	TEXT,
    `Author`	TEXT,
    PRIMARY KEY(`id`)
);

I think those are all the required fields. My version has some other fields for extra information.

grabClippings.py

requires zc.lockfile

#!/usr/bin/env python3
# -*- coding: utf-8 -*-

import os
import re
from datetime import datetime
import sqlite3
from shutil import copyfile
import zc.lockfile

# EDIT THESE TWO FILEPATHS
DATABASE = u"/home/xxxx/Sync/SRS/kindleClips.sqlite3"
MYCLIPPINGS = u"/media/xxxx/Kindle/documents/My Clippings.txt"

BOUNDARY = u"==========\r\n"
TEMPFILE = u"/tmp/kindleClippings.txt"
book_ids = {}

def get_sections(filename):
    with open(filename, 'rb') as f:
        content = f.read().decode('utf-8')
    content = content.replace(u'\ufeff', u'')
    return content.split(BOUNDARY)


def get_clip(section):
    clip = {}

    lines = [l for l in section.split(u'\r\n') if l]
    if len(lines) != 3:
        return

    clip['book'] = lines[0]
    match = re.search(r'(\d+)-\d+', lines[1]) #Matches only highlights
    if not match:
        match = re.search(r'(\d+)', lines[1])
        if not match:
            return
    position = match.group(1)

    #Grab Date String
    dmatch = re.search(r'Added on (.*)


, lines[1])
    if dmatch:
        date = datetime.strptime(dmatch.group(1), '%A, %B %d, %Y, %I:%M %p')
    else:
        date = None

    if "- Highlight Loc." in lines[1]:
        clipType = "highlight"
    elif "- Note Loc." in lines[1]:
        clipType = "note"
    else:
        clipType = "bookmark"

    clip['position'] = int(position)
    clip['content'] = lines[2]
    clip['type'] = clipType
    clip['date'] = date
    clip['bid'] = get_book_id(clip['book'])

    return clip


def sql_file(clip):
    """
    Export clip to sql file if unique
    """
    #print("adding clip to database: %s" % clip)
    #Add to sql file
    if clip['content']:
        # check if exists
        c.execute("select count(clipID) from clips where bid = ? and type = ? and location = ? and text = ? and datestring = ?", (clip['bid'],clip['type'],clip['position'],clip['content'], str(clip['date'])))
        ret = c.fetchone()
        if not ret[0]:
            # insert
            c.execute("insert into clips (bid, type, location, text, datestring) values (?,?,?,?,?)", (clip['bid'], clip['type'], clip['position'], clip['content'], str(clip['date'])))
    else:
        # check if it exists
        c.execute("select count(clipID) from clips where bid = ? and type = ? and location = ? and  datestring = ?)", (clip['bid'], clip['type'], clip['position'], str(clip['date'])))
        ret = c.fetchone()
        if not ret[0]:
            # insert
            c.execute("insert into clips (bid, type, location, datestring) values (?,?,?,?)", (clip['bid'], clip['type'], clip['position'], str(clip['date'])))

def find_most_recent():
    c.execute("select datestring from clips order by datetime(datestring) DESC limit 1")
    result = c.fetchone()
    if result:
        lastdate = datetime.strptime(result[0], '%Y-%m-%d %H:%M:%S')
    else:
        lastdate = datetime.strptime('1900-01-01 00:00:00', '%Y-%m-%d %H:%M:%S')
    return lastdate

def add_book(book):
    #find title, subtitle, and author
    title = None
    subtitle = None
    author = None
    if '(' in book:
        author = book.split('(')[1].split(')')[0].strip()
        title = book.split('(')[0].strip()
    if title and '-' in title:
        subtitle = title.split('-')[1].strip()
        title = title.split('-')[0].strip()
    if not subtitle and title and ':' in title:
        subtitle = title.split(':')[1].strip()
        title = title.split(':')[0].strip()
    #insert
    c.execute("insert into books (book, Title, SubTitle, Author) values (?, ?, ?, ?)", (book, title, subtitle, author))
    conn.commit()

def get_book_id(book):
    global book_ids
    bid = None
    if book in book_ids and book_ids[book]:
        return book_ids[book]
    while not bid:
        c.execute("select id from books where book = ?", (book,))
        result = c.fetchone()
        if result:
            bid = result[0]
            book_ids[book] = bid
            return bid
        else:
            print('We need to add the book')
            add_book(book)

def main():
    global book_ids
    # extract clips
    mostRecent = find_most_recent()
    #mostRecent = datetime.strptime('1971-01-01 00:00:00', '%Y-%m-%d %H:%M:%S') #hack
    # Hack above: when date gets screwed up on kindle, year reverts to 1970, so 
    #             highlights should all be before 1971. Change the > sign in date
    #             comparison 'if' statement below to < to use.
    print("Most Recent in Database: %s" % mostRecent)
    sections = get_sections(TEMPFILE)
    for section in sections:
        clip = get_clip(section)
        if clip:
            #print(clip['date'])
            if clip['date'] and clip['date'] > mostRecent:
                sql_file(clip)

if __name__ == '__main__':
    try:
        lock = zc.lockfile.LockFile('/tmp/grabClippings.lock')
    except zc.lockfile.LockError:
        print("Can't create lock file")
        exit()
    #copy clippings file
    if os.path.isfile(MYCLIPPINGS):
        copyfile(MYCLIPPINGS, TEMPFILE)
    if not os.path.isfile(TEMPFILE):
        print("Temp file does not exist")
        exit()
    conn = sqlite3.connect(DATABASE)
    c = conn.cursor()
    main()

    conn.commit()
    conn.close()
    lock.close()

 _______________
< $ fortune -os >
 ---------------
   \
    \
        .--.
       |o_o |
       |:_/ |
      //   \ \
     (|     | )
    /'\_   _/`\
    \___)=(___/

✍️ Last Updated: 2021-11-18

👈️ Back to: Tech