Created
October 24, 2023 12:15
-
-
Save da-luggas/1d1e539b55616bdde9a4ee907e93e0d1 to your computer and use it in GitHub Desktop.
Export all highlights and notes from apple books on macos
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| import sqlite3 | |
| import glob | |
| import csv | |
| import os | |
| def get_annotation_db_path(): | |
| pattern = os.path.expanduser("~/Library/Containers/com.apple.iBooksX/Data/Documents/AEAnnotation/AEAnnotation*.sqlite") | |
| return glob.glob(pattern)[0] | |
| def get_library_db_path(): | |
| pattern = os.path.expanduser("~/Library/Containers/com.apple.iBooksX/Data/Documents/BKLibrary/BKLibrary*.sqlite") | |
| return glob.glob(pattern)[0] | |
| def get_highlights_and_notes(): | |
| conn = sqlite3.connect(get_annotation_db_path()) | |
| cursor = conn.cursor() | |
| cursor.execute('''SELECT ZANNOTATIONSELECTEDTEXT, ZANNOTATIONNOTE, ZANNOTATIONASSETID | |
| FROM ZAEANNOTATION | |
| WHERE ZANNOTATIONSELECTEDTEXT IS NOT NULL''') | |
| data = cursor.fetchall() | |
| conn.close() | |
| return data | |
| def get_book_details(asset_id): | |
| conn = sqlite3.connect(get_library_db_path()) | |
| cursor = conn.cursor() | |
| cursor.execute('''SELECT ZSORTTITLE, ZSORTAUTHOR | |
| FROM ZBKLIBRARYASSET | |
| WHERE ZASSETID = ?''', (asset_id,)) | |
| details = cursor.fetchone() | |
| conn.close() | |
| return details if details else ("Unknown Title", "Unknown Author") | |
| def main(): | |
| annotations = get_highlights_and_notes() | |
| with open('apple_books_highlights.csv', 'w', newline='') as csvfile: | |
| writer = csv.writer(csvfile) | |
| writer.writerow(["Book Title", "Author", "Highlight", "Note"]) | |
| for annotation in annotations: | |
| selected_text, note, asset_id = annotation | |
| book_title, author = get_book_details(asset_id) | |
| writer.writerow([book_title, author, selected_text, note or ""]) | |
| if __name__ == "__main__": | |
| main() |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment