Pylons provides a model package to put your database code in but does not offer a database engine or API. Instead there are several third-party APIs to choose from.

SQL databases

SQLAlchemy

SQLAlchemy is by far the most common approach for Pylons databases. It provides a connection pool, a SQL statement builder, an object-relational mapper (ORM), and transaction support. SQLAlchemy works with several database engines (MySQL, PostgreSQL, SQLite, Oracle, Firebird, MS-SQL, Access via ODBC, etc) and understands the peculiar SQL dialect of each, making it possible to port a program from one engine to another by simply changing the connection string. Although its API is still changing gradually, SQLAlchemy is well tested, widely deployed, has good documentation, and its mailing list is quick with answers. Using SQLAlchemy with Pylons describes the recommended way to configure a Pylons application for SQLAlchemy.

SQLAlchemy add-ons

Most of these provide a higher-level ORM, either by combining the table definition and ORM class definition into one step, or supporting an "active record" style of access. Please take the time to learn how to do things "the regular way" before using these shortcuts in a production application. Understanding what these add-ons do behind the scenes will help if you have to troubleshoot a database error or work around a limitation in the add-on later.

SQLSoup, an extension to SQLAlchemy, provides a quick way to generate ORM classes based on existing database tables.

Tesla and Elixir: see the links in [Using SQLAlchemy with Pylons.

Non-SQLAlchemy libraries

Most of these expose only the object-relational mapper; their SQL builder and connection pool are not meant to be used directly.

Storm

DB-API

All the SQL libraries above are built on top of Python's DB-API, which provides a common low-level interface for interacting with several database engines: MySQL, PostgreSQL, SQLite, Oracle, Firebird, MS-SQL, Access via ODBC, etc. Most programmers do not use DB-API directly because its API is low-level and repetitive and does not provide a connection pool. There's no "DB-API package" to install because it's an abstract interface rather than software. Instead, install the Python package for the particular engine you're interested in. Python's Database Topic Guide describes the DB-API and lists the package required for each engine. The sqlite3 package for SQLite is included in Python 2.5.

Object databases

Object databases store Python dicts, lists, and classes in pickles, allowing you to access hierarchical data using normal Python statements rather than having to map them to tables, relations, and a foreign language (SQL).

ZODB

ZODB is a popular object database for the Zope framework, but can also be used standalone. However, to use it with pylons, one need to solve some ZODB related issues like :

• managing write and read conflicts in a multithreaded environement
• managing transactions
• open and close connections to the zodb database on each request

Also note that ZODB dosen't do any indexing on its own, so you have to rely on some "cataloging code to index objects" most of the time (I don't know what that means ! look in the ZODB documentation maybe).

You can try to get repoze components do the work for you :

• repoze.retry middleware to resolve conflict errors
• repoze.tm2 middleware for transaction management
• repoze.zodbconn opens and closes the connection on each request
• repoze.folder which stores objects efficiently

There's a repoze tutorial on how to use ZODB with repoze, which may be applicable to pylons.

Other object databases

Durus

Document driven databases

CouchDb is a document driven database . It features a Python API.

Other databases

Pylons can also work with other database systems, such as the following:

Schevo uses Durus to combine some features of relational and object databases. It is written in Python.

Mongodb combines the best of document oriented databases, key/value stores and traditional relation databases. It stores your data as key:value pairs in a json-like syntax. Its shell lets you use JavaScript as a query language, which can be very powerful. Ben Bangert wrote a blog engine for pylons called minger which uses mongodb as a backend.