Importing XML into a database with Python and SQLAlchemy

Let’s begin by analyzing the XML we want to import into our database, it consists of a book’s ISBN, title, and author.
<!-- books.xml --> <catalog> <book isbn="1-880985-26-8"> <title>The Consumer</title> <author>M. Gira</author> </book> <book isbn="0-679775-43-9"> <title>The Wind-Up Bird Chronicle</title> <author>Haruki Murakami</author> </book> <!-- imagine more entries here... --> </catalog>

Now we can create the database table.
create table books ( isbn varchar(14) primary key not null, title varchar(50), author varchar(50) );

Our example depends on SQLAlchemy, which is a “Python SQL toolkit and Object Relational Mapper that gives application developers the full power and flexibility of SQL.”

While our example leverages a Postgres database, given SQLAlchemy’s database agnostic approach, it can trivially be modified to access a MySQL database.

This code is derived from a SAX parser originally found in Programming Python 3rd Edition.

# bookhandler.py from sqlalchemy import * from sqlalchemy.orm import * import xml.sax.handler pg_db = create_engine('postgres:///testdb?user=homer') metadata = MetaData(pg_db) books_table = Table('books', metadata, autoload=True) class Book(object): pass mapper(Book, books_table) class BookHandler(xml.sax.handler.ContentHandler): def __init__(self): self.buffer = "" self.inField = 0 self.session = create_session(bind=pg_db) def startElement(self, name, attributes): if name == "book": self.isbn = attributes["isbn"] elif name == "title": self.inField = 1 elif name == "author": self.inField = 1 def characters(self, data): if self.inField: self.buffer += data def endElement(self, name): if name == "book": self.session.begin() self.newbook = Book() self.newbook.isbn = self.isbn self.newbook.title = self.title self.newbook.author = self.author self.session.save(self.newbook) self.session.commit() elif name == "title": self.inField = 0 self.title = self.buffer elif name == "author": self.inField = 0 self.author = self.buffer self.buffer = ""
Now that we’ve set up our sax parser and handler to parse and load the entries from books.xml into the table, lets set up a small script to drive it:
# runit.py import bookhandler import xml.sax parser = xml.sax.make_parser() handler = bookhandler.BookHandler() parser.setContentHandler(handler) parser.parse("books.xml")

Now let’s see if it works:
$ ls bookhandler.py books.xml runit.py $ python ./runit.py $ psql testdb testdb=# select * from books; isbn | title | author ---------------+----------------------------+----------------- 1-880985-26-8 | The Consumer | M. Gira 0-679775-43-9 | The Wind-Up Bird Chronicle | Haruki Murakami (2 rows)