And now it’s all this

I just said what I said and it was wrong
Or was taken wrong

Next post Previous post

Adding books to my library database

April 12, 2025 at 8:54 AM by Dr. Drang

To follow up on the two posts I wrote a couple of weeks ago, I thought I’d explain how I use the Library of Congress catalog to add entries to the SQLite database of my technical books.

As a reminder, the database consists of three tables, book, author, and book_author. The fields for each table are listed below:

book	author	book_author
id	id	id
title	name	book_id
subtitle		author_id
volume
edition
publisher
published
lccn
loc
added

In each table, the id field is a sequential integer that’s automatically generated when a new record is added. The book_id and author_id fields in the book_author table are references to the id values in the other two tables. They tie book and author together and handle the many-to-many relationship between the two.

The author table is about as simple as it can be. I don’t care about breaking names into given and surname, nor do I care about their birth/death dates. The names are saved in last, first [middle] format, i.e.,

Ang, Alfredo Hua-Sing
Gere, James M.
King, Wilton W.
McGill, David J.
Tang, Wilson H.
Timoshenko, Stephen

Most of the book fields are self-explanatory. The published field is the publication date (just a year), and added is the date I added the book to the database, which helps when I want to print out information about recently added books. SQLite doesn’t have a date datatype, so added is a string in yyyy-mm-dd format. The loc is the Library of Congress Classification, an alphanumeric identifier similar to the Dewey Decimal system. The lccn is the Library of Congress Control Number, which is basically a serial number that’s prefixed by the year. It has nothing to do with classification by topic or shelving, but it’s the key to quickly collecting all the other data on books in the Library of Congress catalog.

I shelve my books according to the Library of Congress Classification. All other things being equal, I’d prefer to use the Dewey Decimal system because that’s the system used by most of the libraries I’ve patronized.¹ But all other things are not equal.

1. Virtually all of my technical books are in the Library of Congress.
2. The LoC catalog is freely available online.
3. The LoC’s records can be downloaded in a convenient format.
4. Unfortunately, many of the LoC records do not include a Dewey Decimal number.

The advantages of using the LoC Classification far outweigh my short-lived comfort. I’m getting used to my structural engineering books being in the TA600 series instead of the 624.1 series.

The Library of Congress keeps its catalog records in a few different formats. There’s the venerable MARC format, which uses numbers to identify fields and letters to identify subfields. There’s MARCXML, which is a more or less direct translation of MARC into XML. Neither of these were appealing to me. But there’s also the MODS format, which uses reasonable names for the various elements. For example, here’s the MODS record for An Introduction to Dynamics by McGill & King:

xml:
<?xml version="1.0" encoding="UTF-8"?><mods xmlns="http://www.loc.gov/mods/v3" xmlns:zs="http://docs.oasis-open.org/ns/search-ws/sruResponse" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" version="3.8" xsi:schemaLocation="http://www.loc.gov/mods/v3 http://www.loc.gov/standards/mods/v3/mods-3-8.xsd">
  <titleInfo>
    <nonSort xml:space="preserve">An </nonSort>
    <title>introduction to dynamics</title>
  </titleInfo>
  <titleInfo type="alternative">
    <title>Engineering mechanics</title>
  </titleInfo>
  <name type="personal" usage="primary">
    <namePart>McGill, David J.,</namePart>
    <namePart type="date">1939-</namePart>
  </name>
  <name type="personal">
    <namePart>King, Wilton W.,</namePart>
    <namePart type="date">1937-</namePart>
  </name>
  <typeOfResource>text</typeOfResource>
  <originInfo>
    <place>
      <placeTerm authority="marccountry" type="code">cau</placeTerm>
    </place>
    <dateIssued encoding="marc">1984</dateIssued>
    <issuance>monographic</issuance>
    <place>
      <placeTerm type="text">Monterey, Calif</placeTerm>
    </place>
    <agent>
      <namePart>Brooks/Cole Engineering Division</namePart>
    </agent>
    <dateIssued>c1984</dateIssued>
  </originInfo>
  <language>
    <languageTerm authority="iso639-2b" type="code">eng</languageTerm>
  </language>
  <physicalDescription>
    <form authority="marcform">print</form>
    <extent>xv, 608 p. : ill. (some col.) ; 25 cm.</extent>
  </physicalDescription>
  <note type="statement of responsibility">David J. McGill and Wilton W. King.</note>
  <note>Cover title: Engineering mechanics.</note>
  <note>Includes index.</note>
  <subject authority="lcsh">
    <topic>Dynamics</topic>
  </subject>
  <classification authority="lcc">TA352 .M385 1984</classification>
  <classification authority="ddc" edition="19">620.1/04</classification>
  <identifier type="isbn">0534029337</identifier>
  <identifier type="lccn">83025283</identifier>
  <recordInfo>
    <descriptionStandard>aacr</descriptionStandard>
    <recordContentSource authority="marcorg">DLC</recordContentSource>
    <recordCreationDate encoding="marc">831128</recordCreationDate>
    <recordChangeDate encoding="iso8601">19840409000000.0</recordChangeDate>
    <recordIdentifier>4242715</recordIdentifier>
    <recordOrigin>Converted from MARCXML to MODS version 3.8 using MARC21slim2MODS3-8_XSLT1-0.xsl
                (Revision 1.172 20230208)</recordOrigin>
  </recordInfo>
</mods>

OK, XML isn’t as nice as JSON, but there are Python modules for parsing it, and it’s relatively easy to pick out the elements I want to put in my database.

And if you know a book’s LCCN, you can get its MODS record using a simple URL. The LCCN of McGill & King’s book is 83025283, and the URL to download it is

https://lccn.loc.gov/83025283/mods

That’s very convenient.

It does raise the question, though, of how you get the LCCN of a book. In many of my books, especially the older ones, the LCCN is printed on the copyright page. Here’s a photo of the copyright page of Timoshenko & Gere’s Theory of Elastic Stability:

The name of the LCCN has changed over the years, and you’ll sometimes see it like this with a dash between the year and the serial number, but it’s easy to convert it to the current canonical form.

If the LCCN isn’t in the book, I use the LoC’s Advanced Search form to find it. This allows searches by title, author, ISBN (my older books don’t have ISBNs, but all the newer books do), or combinations. The record that comes up will always have the LCCN.

However I manage to get the LCCN, I then run the lccn2library command with the LCCN as its argument. That adds the necessary entries to the book, author, and book_author tables and returns the loc catalog value. For McGill & King’s book, it would work like this:

lccn2library 83025283

which returns

TA352 .M385 1984

This typically gets printed on a label that I stick on the spine of the book before shelving it.

Here’s the code for lccn2library. It’s longer than the scripts I usually post here, but that’s because there are a lot of details that have to be handled.

python:
  1:  #!/usr/bin/env python3
  2:  
  3:  import sys
  4:  import re
  5:  import sqlite3
  6:  import requests
  7:  from unicodedata import normalize
  8:  import xml.etree.ElementTree as et
  9:  from datetime import date
 10:  import time
 11:  
 12:  ########## Functions ##########
 13:  
 14:  def canonicalLCCN(lccn):
 15:    """Return an LCCN with no hyphens and the correct number of digits.
 16:  
 17:    20th century LCCNs have a 2-digit year. 21st century LCCNs have a
 18:    4-digit year. The serial number needs to be 6 digits. Pad with
 19:    zeros if necessary."""
 20:  
 21:    # All digits
 22:    if re.search(r'^\d+$', lccn):
 23:      if len(lccn) == 8 or len(lccn) == 10:
 24:        return lccn
 25:      else:
 26:        return lccn[:2] + f'{int(lccn[2:]):06d}'
 27:    # 1-3 lowercase letters followed by digits
 28:    elif m := re.search(r'^([a-z]{1,3})(\d+)$', lccn):
 29:      if len(m.group(2)) == 8 or len(m.group(2)) == 10:
 30:        return lccn
 31:      else:
 32:        return m.group(1) + m.group(2)[:2] + f'{int(m.group(2)[2:]):06d}'
 33:    # 20th century books are sometimes given with a hyphen after the year
 34:    elif m := re.search(r'^(\d\d)-(\d+)$', lccn):
 35:      return m.group(1) + f'{int(m.group(2)):06d}'
 36:    else:
 37:      raise ValueError(f'{lccn} is in an unknown form')
 38:  
 39:  def correctName(name):
 40:    """Return the author name without spurious trailing commas,
 41:    space, or periods."""
 42:  
 43:    # Regex for finding trailing periods that are not from initials
 44:    trailingPeriod = re.compile(r'([^A-Z])\.$')
 45:  
 46:    name = name.rstrip(', ')
 47:    name = trailingPeriod.sub(r'\1', name)
 48:    return(name)
 49:  
 50:  def dbAuthors(cur):
 51:    """Return a dictionary of all the authors in the database.
 52:    Keys are names and values are IDs."""
 53:  
 54:    res = cur.execute('select name, id from author')
 55:    authorList = res.fetchall()
 56:    return dict(authorList)
 57:  
 58:  def addAuthor(cur, name):
 59:    """Add a new author to the database and return the ID."""
 60:  
 61:    params = [name]
 62:    insertCmd = 'insert into author(name) values(?)'
 63:    res = cur.execute(insertCmd, params)
 64:    params = [name]
 65:    idCmd = 'select id from author where name = ?'
 66:    res = cur.execute(idCmd, params)
 67:    return res.fetchone()[0]
 68:  
 69:  def bookData(root):
 70:    """Return a dictionary of information about the book.
 71:  
 72:    Keys are field names and values are from the book.
 73:    If a field name is missing, it's given the value None."""
 74:  
 75:    # Initialize the dictionary
 76:    book = dict()
 77:  
 78:    # Collect the book information from the MODS XML record
 79:    # Use the order in the database: title, subtitle, volume,
 80:    # edition, publisher, published, lccn, loc
 81:  
 82:    # The default namespace for mods in XPath searches
 83:    ns = {'m': 'http://www.loc.gov/mods/v3'}
 84:  
 85:    # Get the title, subtitle, and part/volume
 86:    for t in root.findall('m:titleInfo', ns):
 87:      if len(t.attrib.keys()) == 0:
 88:        # Title
 89:        try:
 90:          starter = t.find('m:nonSort', ns).text
 91:        except AttributeError:
 92:          starter = ''
 93:        book['title'] = starter + t.find('m:title', ns).text.rstrip(', ')
 94:  
 95:        # Subtitle
 96:        try:
 97:          book['subtitle'] = t.find('m:subTitle', ns).text.rstrip(', ')
 98:        except AttributeError:
 99:          book['subtitle'] = None
100:  
101:        # Part/volume
102:        try:
103:          book['volume'] = t.find('m:partName', ns).find.rstrip(', ')
104:        except AttributeError:
105:          book['volume'] = None
106:  
107:    # Get the origin/publishing information
108:    # Edition
109:    try:
110:      book['edition'] = root.find('m:originInfo/m:edition', ns).text
111:    except AttributeError:
112:      book['edition'] = None
113:  
114:    # Publisher
115:    try:
116:      book['publisher'] = root.find('m:originInfo/m:agent/m:namePart', ns).text
117:    except AttributeError:
118:      book['publisher'] = None
119:  
120:    # Date published
121:    try:
122:      book['published'] = root.find('m:originInfo/m:dateIssued', ns).text
123:    except AttributeError:
124:      book['published'] = None
125:  
126:    # ID numbers
127:    # LCCN (must be present)
128:    book['lccn'] = root.find('m:identifier[@type="lccn"]', ns).text
129:  
130:    # LOC classification number (must be present)
131:    book['loc'] = root.find('m:classification[@authority="lcc"]', ns).text
132:  
133:    # Date added to database is today
134:    book['added'] = date.today().strftime('%Y-%m-%d')
135:  
136:    return book
137:  
138:  def authorData(cur, root):
139:    """Return a dictionary of authors of the book, primary first.
140:  
141:    The keys are the author names and values are their IDs.
142:    Authors not already in the database are added to it."""
143:  
144:    # The default namespace for mods in XPath searches
145:    ns = {'m': 'http://www.loc.gov/mods/v3'}
146:  
147:    # Get all the authors (primary and secondary) of the book
148:    # The primary author goes first in the authors list
149:    authors = []
150:    names = root.findall('m:name', ns)
151:    pnames = root.findall('m:name[@usage="primary"]', ns)
152:    snames = list(set(names) - set(pnames))
153:    pnames = [ correctName(n.find('m:namePart', ns).text) for n in pnames ]
154:    snames = [ correctName(n.find('m:namePart', ns).text) for n in snames ]
155:  
156:    # Get the authors already in the database
157:    existingAuthors = dbAuthors(cur)
158:  
159:    # Determine which authors are new to the database and add them.
160:    # The primary author comes first.
161:    authors = dict()
162:    for n in pnames:
163:      if n in existingAuthors.keys():
164:        authors[n] = existingAuthors[n]
165:      else:
166:        newID = addAuthor(cur, n)
167:        authors[n] = newID
168:    for n in snames:
169:      if n in existingAuthors.keys():
170:        authors[n] = existingAuthors[n]
171:      else:
172:        newID = addAuthor(cur, n)
173:        authors[n] = newID
174:  
175:    return authors
176:  
177:  
178:  ########## Main program ##########
179:  
180:  # Connect to the database
181:  con = sqlite3.connect('library.db')
182:  cur = con.cursor()
183:  
184:  # Get the LCCN from the argument
185:  lccn = canonicalLCCN(sys.argv[1])
186:  
187:  # Get and parse the MODS data for the LCCN
188:  r = requests.get(f'https://lccn.loc.gov/{lccn}/mods')
189:  mods = normalize('NFC', r.content.decode())
190:  root = et.fromstring(mods)
191:  
192:  # Collect the book data and add it to the book table
193:  book = bookData(root)
194:  params = list(book.values())
195:  insertCmd = 'insert into book(title, subtitle, volume, edition, publisher, published, lccn, loc, added) values(?, ?, ?, ?, ?, ?, ?, ?, ?);'
196:  res = cur.execute(insertCmd, params)
197:  params = [book["lccn"]]
198:  idCmd = f'select id from book where lccn = ?'
199:  res = cur.execute(idCmd, params)
200:  bookID = res.fetchone()[0]
201:  
202:  # Collect the authors, adding the new ones to the author table
203:  authors = authorData(cur, root)
204:  
205:  # Add entries to the book_author table
206:  for authorID in authors.values():
207:    params = [bookID, authorID]
208:    insertCmd = f'insert into book_author(book_id, author_id) values(?, ?)'
209:    res = cur.execute(insertCmd, params)
210:  
211:  # Commit and close the database
212:  con.commit()
213:  con.close()
214:  
215:  # Print the LOC classification number
216:  print(book['loc'])

The script starts with a couple of utility functions, canonicalLCCN and correctName. The former (Lines 12–37) takes an LCCN as its argument and returns it in the form needed in the URL we talked about above. For 20th century books, that form is a two-digit year followed by a six-digit serial number. For 21st century books, it’s a four-digit year followed by a six-digit serial number. In both cases, the serial number part is padded with zeros to make it six digits long. Hyphens are removed. Oh, and there can sometimes be 1–3 lowercase letters in front of the digits.

correctName (Lines 39–48) is necessary because I noticed that sometimes the authors’ names are followed by spurious commas or periods. You can see trailing commas in both authors’ names in the MODS file shown above. I think these extra bits of punctuation made some sense in the MARC format, but I don’t want them in my database.

Often, the book I’m adding to the database has one or more authors that are already entered in the author table. I don’t want them entered again, so I use the dbAuthors function (Lines 50–56) to query the database for all the authors and put them in a dictionary. The dictionary may seem backwards—its keys are the names and the values are the IDs—but that makes it easy to look up an author’s ID by their name.

The addAuthor function (Lines 58–67) does what you’d expect: it executes an SQL command to add a new author to the author table. The return value is the author’s ID.

The bookData function (Lines 69–136) is by far the longest function. It starts at the root of the XML element tree and pulls out all of the elements needed for the book table entry. It returns a dictionary in which the keys are the book field names (other than id, which will be automatically generated), and the values are the corresponding entries in the MODS file. If there is no entry for, say, a subtitle or volume number, the dictionary is given a None value for that key.

I’m using the ElementTree module for parsing and searching the MODS, and its find and findall functions want the namespace of the elements they’re looking for when the XML data has more than one namespace. As you can see in the first line of the example above, there are three namespaces in MODS, the first of which is

http://www.loc.gov/mods/v3

That’s the reason for the ns dictionary defined on Line 83 and the m: prefix in all the element names.

Searching for the book fields takes up many lines of code, partly because MODS has nested data and partly because some of the fields may not be present. That’s why there are several try/except blocks.

One last thing: the added field (Line 128) comes from the date on which lccn2library is run. It has nothing to do with the MODS data.

The last function is authorData (Lines 138–175). It pulls the names of the authors from the MODS data, distinguishing between the primary author and the others. It then uses dbAuthors (Line 151) to figure out which of this book’s authors are already in the database. Those that aren’t in the database are added to it using the addAuthor function described above. A dictionary of all the book’s authors is returned. As with dbAuthors, the keys are the author names and the values are the author IDs. The primary author comes first in the dictionary.²

The main program starts on Line 181 by connecting to the database and setting up a “cursor” for executing commands. The LCCN argument is put in canonical form (Line 185), which is then used with the requests library to download the MODS data.

Before parsing the XML, I normalize the Unicode data into the NFC format on Line 189. This means that something like é is made into a single character, rather than an e followed by a combining acute accent character. I did this because some utilities I use to format the results of database queries don’t like combining characters.

With the root of the MODS element tree defined in Line 190, the script then calls bookData to get the dictionary of book info. That is then inserted into the book table on Lines 194–196 using SQL command execution with parameters. The ID of the newly added book—which was automatically generated by the insert command—is then gathered from the database and put in the bookID variable in Lines 197–200.

The authors are added to the author table by calling authorData on Line 203. The dictionary of author names and IDs is saved in the authors variable.

The entries in the book_author table are inserted in Lines 206–209 using the bookID and authors values.

With all the table insertions done, the changes are committed and the database connection closed in Lines 212-213. The loc field for the book is printed out by Line 216.

Phew!

As you might imagine, I didn’t run lccn2library by hand hundreds of times when I was initially populating the database. No, I made files with lists of LCCNs, one per line, and ran commands like

xargs -n 1 lccn2library < lccn-list.txt

Giving the -n 1 option to xargs insures that lccn2library will consume only one argument each time it’s run.

I typically did this in chunks of 20-50 LCCNs, mainly because every time I thought I finally had lccn2library debugged, some new MODS input would reveal an error. I feel certain there are still bugs in it, but none have turned up in a while.

There are currently about 500 books in the database, and I think I’ve cleaned out every nook and cranny in the house where books might be hiding. Of course, books still somehow show up when I visit used book stores or (this is the most dangerous) AbeBooks.

Next post Previous post

Site search

Meta

• drdrang at leancrew
• Blog archive
• RSS feed
• JSON feed
• Mastodon
• GitHub repositories

Recent posts

Credits

This work is licensed under a Creative Commons Attribution-Share Alike 3.0 Unported License.

© 2005–2025, Dr. Drang