Comparative Databases Demo Home  >> Python

Page updated 12-Sep-2007

This is a comparative demo of Python database APIs. Currently there are parallel demos of SQLAlchemy, a front end to several databases (MySQL, PostgreSQL, SQLite, MS-SQL, Oracle, etc), and the object database Durus. Each demo creates a table of characters from the Flintstones, makes queries, and modifies data in the same way. Here's the output and the databases created:

• output.txt
• flintstones.sqlite
• flintstones.durus

Source code

To run the demos you'll need these files:

• demo_sqlalchemy.py
• demo_durus.py
• common.py

You'll also need SQLAlchemy 0.4.0beta3 or newer (not 0.3), SQLite 3 and pysqlite, Durus, and Python. The programs create the database in the current working directory. You can use a different database engine by changing the DSN constant at the top of demo_sqlalchemy.py.

Make sure the current directory is in your Python path (e.g., PYTHONPATH=.) so that the top-level programs can find the common.py module.

Discussion

Durus

Durus is an object database, meaning it has a root object that can recursively contain other objects. Durus's root object mimics a Python dictionary which can contain any pickleable objects. You access the database as an ordinary Python data structure, and Durus takes care of loading the parts into memory as they're needed. By default the entire database is saved as a single "part" (a single pickle), but you can use subclasses of Persistent to fine tune this. Any "persistent" object is stored as a separate pickle and loaded individually, saving memory and processing time if you access a small part of a large collection. Durus provides PersistentDict and PersistentList classes which mimic Python dics and lists. There's also a BTree class which acts like a dict but stores the values in groups of 16.

Note: Some database systems use the term "nonpersistent" to refer to a temporary attribute that is not saved to the database. Durus does not have this concept. "Persistent" in Durus refers to how something is saved, not whether it is saved. However, a class can use Python's pickle protocol to prevent certain attributes from being saved.

The demo uses a PersistentDict of PFlintstone instances to represent a table. PFlintstone subclasses Persistent. You can also use a dict of dicts (or their persistent varieties) to avoid depending on classes that aren't built into Python or Durus. If your table has no meaningful primary key (e.g., log messages), you can use a list of instances or a list of dicts instead.

The Zope Object Database (ZODB) is similar to Durus.

SQLAlchemy

SQLAlchemy can access relational databases in three ways: via raw SQL strings, SQL statements built from Python methods, or the object-relational mapper (ORM). The demo uses the latter two. The ORM tries to mimic an object database by implicitly coverting instances of a user-specified class into table records and vice-versa. However, you have to configure the tables and relations yourself, along with things called "metadata" and "session" objects, and call several other methods, often with table-column arguments. This makes SQL database usage significantly more verbose than object databases, even with the ORM.

On the other hand, you can often pack an entire aggregate query or bulk update into a single SQL query (method call), which would require a loop and counter variables in Durus. SQL database have been heavily optimized for aggregate queries, relation queries, and bulk updates, and some use less memory than object databases. And SQL databases can be accessed from other programming languages and off-the-shelf tools.

In an object database, you can embed a collection in an attribute; e.g., a person has several addresses, implemented as a list of address objects on the .addresses attribute. In a relational database this must be implemented as a separate address table with a foreign key back to the person. But the ORM can make it look like an .addresses attribute with a list if you set up the relation.

SQLAlchemy has a PickleType which can transparently serialize a Python object into a database value even without the ORM. This can be used to store a web framework's session object, for instance, which may have a structure unknown to the writer of the database model, or even a different structure in each record. This could be used to embed a list of objects in one table record (such as a list of addresses), thus avoiding a second table and a relationship. The tradeoff is the database engine can't see inside the pickles, so you can't query their subcomponents in an optimized SQL query.

Sluggo is Mike Orr, a helluva friendly guy in Seattle. Email me if you have feedback.