repository·main·Indexed 42 days ago
https://github.com/librarieshacked/openlibrary-searchA pipeline to download, clean, and import Open Library bibliographic data dumps into a local PostgreSQL database. Includes scripts for processing raw text files via Python 3 and SQL scripts for importing data into a schema optimized for ISBN lookups and advanced SQL querying.
Once the data is processed and located in the data/processed folder, you can import it into PostgreSQL using the provided SQL scripts. This process is resource-intensive and may take several hours.
Use the psql command-line tool to execute the openlibrary-db.sql script. Replace username with your actual PostgreSQL username.
psql --set=sslmode=require -f openlibrary-db.sql -h localhost -p 5432 -U username postgresDownload the latest Editions, Works, and Authors data dumps from the Open Library data dumps page.
Follow these steps to download, organize, and uncompress the files:
wget into a downloads folder../data/unprocessed/ directory in this project..gz files using gzip.Note: Ensure you have sufficient disk space before uncompressing, as the files expand significantly.
# 1. Download
wget https://openlibrary.org/data/ol_dump_editions_latest.txt.gz -P ~/downloads
wget https://openlibrary.org/data/ol_dump_works_latest.txt.gz -P ~/downloads
wget https://openlibrary.org/data/ol_dump_authors_latest.txt.gz -P ~/downloads
# 2. Move to project directory
mv ~/downloads/ol_dump_authors_*txt.gz ./data/unprocessed/ol_dump_authors.txt.gz
mv ~/downloads/ol_dump_works_*txt.gz ./data/unprocessed/ol_dump_works.txt.gz
mv ~/downloads/ol_dump_editions_*txt.gz ./data/unprocessed/ol_dump_editions.txt.gz
# 3. Uncompress
gzip -d -c data/unprocessed/ol_dump_editions.txt.gz > data/unprocessed/ol_dump_editions.txt
gzip -d -c data/unprocessed/ol_dump_works.txt.gz > data/unprocessed/ol_dump_works.txt
gzip -d -c data/unprocessed/ol_dump_authors.txt.gz > data/unprocessed/ol_dump_authors.txtBefore setting up the Open Library database, ensure your environment meets the following requirements:
The raw Open Library text files contain a varying number of columns which causes errors during direct PostgreSQL import. Use the openlibrary_data_process.py script to clean the data.
What the script does:
Configuration:
LINES_PER_FILE setting within the script (default is 2,000,000 lines).Cleanup Tip:
Once the script generates files in data/processed, you can delete the large .txt files in data/unprocessed to reclaim disk space (required if you are close to the 250GB limit).
python openlibrary_data_process.pyYou can perform complex joins across the editions, edition_isbns, works, author_works, and authors tables to retrieve comprehensive bibliographic information. The following example retrieves titles, subtitles, subjects, and descriptions for a specific ISBN13.
select
e.data->>'title' "EditionTitle",
w.data->>'title' "WorkTitle",
a.data->>'name' "Name",
e.data->>'subtitle' "EditionSubtitle",
w.data->>'subtitle' "WorkSubtitle",
e.data->>'subjects' "Subjects",
e.data->'description'->>'value' "EditionDescription",
w.data->'description'->>'value' "WorkDescription",
e.data->'notes'->>'value' "EditionNotes",
w.data->'notes'->>'value' "WorkNotes"
from editions e
join edition_isbns ei
on ei.edition_key = e.key
join works w
on w.key = e.work_key
join author_works a_w
on a_w.work_key = w.key
join authors a
on a_w.author_key = a.key
where ei.isbn = '9781551922461'The imported database consists of 5 primary tables designed for querying bibliographic data, specifically optimized for ISBN lookups.
| Data | Description |
| :------------ | :-------------------------------------------------------------- |
| Authors | Authors are the individuals who write the works |
| Works | The works as created by the authors, with titles, and subtitles |
| Author Works | A table linking the works with authors |
| Editions | The particular editions of the works, including ISBNs |
| Edition ISBNs | The ISBNs for the editions |