Python PocketBook 3 quote script

pocketbook3-quote-upload.py

# 1. Scan devices and let me choose
# 2. Scan books and let me choose or ALL
# 3. Store quotes in fille /quotes/BookName-DATE.odt append bookmarked page numbers at the end (v1)

import sqlite3, os, json, re
from ezodf2 import newdoc, opendoc, Paragraph, Heading, Sheet
from datetime import date
from operator import itemgetter
import random

DB_FILE = '/media/justinas/Basic 3/system/config/books.db';
#DB_FILE = os.path.dirname(__file__) + '/books.db'
STORAGE_DIR = os.path.dirname(os.path.abspath(__file__)) + '/quotes'

print(DB_FILE)


def getBooks(conn, limit = 10):
    cursor = conn.execute('SELECT * FROM `Books` ORDER BY TimeAdd DESC LIMIT %d' % limit)

    books = []

    for book in cursor:
        books.append(book)

    return books

def getHighlights(conn, book_id):
    cursor = conn.execute('SELECT Tags.OID, Tags.Val, Tags.TimeEdt, Items.ParentID as BookID FROM Tags JOIN Items ON Tags.ItemID=Items.OID WHERE Tags.TagID in (SELECT TagNames.OID FROM TagNames WHERE TagNames.TagName=\'bm.quotation\') AND Items.ParentID = %s ORDER BY Tags.TimeEdt DESC' % book_id)
    
    highlights = []

    for highlight in cursor:
        highlights.append(highlight)

    return highlights

def getAvailableFileName(book, mode = 'E'):
    book_file = '%s/%s-%s_%s.odt' % (STORAGE_DIR, mode, book['title'], date.today().strftime('%Y-%m-%d'))

    safety_index = 1

    while (os.path.exists(book_file)):
        book_file = '%s/%s-%s_%s-%d.odt' % (STORAGE_DIR, mode, book['title'], date.today().strftime('%Y-%m-%d'), safety_index)
        safety_index += 1

    return book_file

def storeHighlights(book, highlights, book_file):   
    odt = newdoc(doctype='odt', filename=book_file)

    odt.body += Heading('"%s" by %s' % (book['title'], book['author']))

    for highlight in highlights:
        odt.body += Paragraph("") #some space for upcoming highlight

        if highlight['page_starts'] is not None and highlight['page_ends'] is not None:
            if highlight['page_starts'] != highlight['page_ends']:
                odt.body += Paragraph('"%s" (p%s-%s)' % (highlight['quote'], highlight['page_starts'], highlight['page_ends']))
            else:
                odt.body += Paragraph('"%s" (p%s)' % (highlight['quote'], highlight['page_starts']))
        else:
            odt.body += Paragraph('"%s"' % highlight['quote'])

    try:
        odt.save()
    except Exception as e:
        print("Failed writing ODT: %s" % e)

if __name__ == '__main__':
    conn = sqlite3.connect(DB_FILE)

    books = getBooks(conn)

    total_books = len(books)

    if total_books:
        print("\n")

        for i in range(total_books):
            book = books[i]
            print('#%d - "%s" by %s' % (i, book[1], book[2]))

        n = int(input("\nChoose book to import: "))

        print('Importing highlights from "%s"...' % books[n][1])

        if books[n]:
            highlights_raw = getHighlights(conn, books[n][0])
            
            highlights = []

            page_regex = r"page=(\d+)"

            for highlight in highlights_raw:
                highlight_info = json.loads(highlight[1])

                obj_keys = highlight_info.keys()

                if 'text' in obj_keys and highlight_info['text'] != 'Bookmark':
                    if 'begin' in obj_keys:
                        matches_begin = re.search(page_regex, highlight_info['begin'])

                        if matches_begin and matches_begin.groups():
                            page_starts = int(matches_begin.group(1))
                        else:
                            page_starts = 0
                    else:
                        page_starts = 0

                    if 'end' in obj_keys:
                        matches_end = re.search(page_regex, highlight_info['end'])

                        if matches_end and matches_end.groups():
                            page_ends = int(matches_end.group(1))
                        else:
                            page_ends = 0
                    else:
                        page_ends = 0

                    quote = highlight_info['text']

                    highlight_formated = { 'quote': quote, 'page_starts': page_starts, 'page_ends': page_ends }

                    highlights.append(highlight_formated)

             
            highlights_random = highlights

            random.shuffle(highlights_random)

            highlights_ordered = sorted(highlights, key=itemgetter('page_starts'))

            book_info = { 'id': books[n][0], 'title': books[n][1], 'author': books[n][2]}

            storeHighlights(book_info, highlights_ordered, getAvailableFileName(book_info, 'N'))
            storeHighlights(book_info, highlights_random, getAvailableFileName(book_info, 'E'))
        else:
            print("There is no such option.")
    else:
        print("You have no books!")

    conn.close()

    print("Done.")