HandyLibrary data file import

[drzraf]

Is your feature request related to a problem? Please describe.
My existing library was made using HandyLibrary. Import of this CSV file format (or possibly full database which includes cover photos) would be appreciated.

Describe the solution you'd like
Just another CSV importer

Describe alternatives you've considered
CSV conversion tool (just a command-line) : Each app' may deserve a description of their columns in a wiki so that mapping could be made easier and more transparent.

[drzraf]

#!/usr/bin/python3

import sqlite3
import csv
import re
import sys
from datetime import datetime

# HandyLibrary SQLite format
# _id
# ISBN
# ISBN10
# Title
# Author
# Author2
# Author3
# Translator
# Publisher
# Published_Date
# Pages_Number
# Series
# Volume
# Language
# Image_Url
# Icon_Path
# Photo_Path
# Summary
# Hard_Cover
# Ebook
# Width
# Height
# Price
# Category
# Rating
# Rating_Count
# Reviews
# Reviews_Count
# Item_Url
# Item_AffiliateId_Url
# Copy
# Read
# Favorite
# Wish
# Lend_or_Borrow
# Person
# Start_Date
# Due_Date
# Return_Date
# Comment
# Location
# SubTitle
# Original_Title
# Sorted_Title
# Edition
# Original_Language
# Covers
# Internal_Barcode
# Identifiers
# Classifications
# Dewey
# LCC
# Color
# Price_On_Cover
# Currency
# Estimated_Value
# Estimated_Currency
# Media_Link
# Returned_Date
# Purchased_Date
# Status
# Condition
# Deleted_At

DB_PATH = sys.argv[1]
CSV_PATH = open(sys.argv[2], 'w', newline='', encoding='utf-8') if len(sys.argv) > 2 else sys.stdout

conn = sqlite3.connect(DB_PATH)
cursor = conn.cursor()
cursor.execute("SELECT * FROM book_library")
rows = cursor.fetchall()
columns = [description[0] for description in cursor.description]

writer = csv.writer(CSV_PATH)
writer.writerow(['title', 'subtitle', 'author', 'description', 'status', 'favourite', 'deleted', 'rating', 'pages', 'publication_year', 'isbn', 'olid', 'tags', 'my_review', 'notes', 'book_format', 'readings'])

for row in rows:
    data = dict(zip(columns, row))

    title = (data.get('Title') or '').strip()
    subtitle = (data.get('SubTitle') or '').strip()
    authors_raw = [data.get('Author'), data.get('Author2'), data.get('Author3')]
    authors = [(a or '').strip() for a in authors_raw if (a or '').strip()]
    author = ', '.join(authors) if authors else ''
    description = (data.get('Summary') or '').strip()
    status = 'finished' if data.get('Read', 0) == 1 else 'planned'
    favourite = 'true' if data.get('Favorite', 0) == 1 else 'false'
    deleted = 'true' if data.get('Deleted_At') else 'false'
    rating_raw = data.get('Rating')
    rating = (rating_raw or '').strip() if rating_raw is not None else ''
    pages = data.get('Pages_Number') or ''
    pub_date = (data.get('Published_Date') or '').strip()
    publication_year = re.match(r'^\d{4}', pub_date).group(0) if pub_date and re.match(r'^\d{4}', pub_date) else ''
    isbn_raw = data.get('ISBN') or data.get('ISBN10') or ''
    isbn = isbn_raw.strip()
    olid = ''
    tags = '|'.join(list(filter(None, [data.get('Category'), data.get('Location')])))
    my_review = '' # (data.get('Reviews') or '').strip()
    notes = (data.get('Comment') or '').strip()
    hard_cover = data.get('Hard_Cover', 0) == 1
    ebook_raw = data.get('Ebook')
    ebook = (ebook_raw or '').strip().lower() in ['1', 'yes', 'true'] if ebook_raw is not None else False
    book_format = 'hardcover' if hard_cover else 'ebook' if ebook else 'paperback'
    start_date = (data.get('Start_Date') or '').strip()
    return_date = (data.get('Return_Date') or '').strip()
    readings = f"{start_date}|{return_date}|0" if start_date and return_date else ''

    writer.writerow([title, subtitle, author, description, status, favourite, deleted, rating, pages, publication_year, isbn, olid, tags, my_review, notes, book_format, readings])

conn.close()

For reference:

• OpenReads CSV format.
• [BUG] CSV import errors are confusing #834 (will update the above code if I could identify the import errors)