SQL databases¶

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).

Durus

ZODB

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.

CouchDb is a document-based database. It features a Python API.

Install SQLAlchemy¶

We’ll assume you’ve already installed Pylons and have the easy_install command. At the command line, run:

easy_install SQLAlchemy

Next you’ll have to install a database engine and its Python bindings. If you don’t know which one to choose, SQLite is a good one to start with. It’s small and easy to install, and Python 2.5 includes bindings for it. Installing the database engine is beyond the scope of this article, but here are the Python bindings you’ll need for the most popular engines:

easy_install pysqlite # If you use SQLite and Python 2.4 (not needed for Python 2.5)
easy_install MySQL-python # If you use MySQL
easy_install psycopg2 # If you use PostgreSQL

See the Python Package Index (formerly the Cheeseshop) for other database drivers.

>>> import sqlalchemy
>>> sqlalchemy.__version__
0.4.3

Model¶

"""SQLAlchemy Metadata and Session object"""
from sqlalchemy import MetaData

__all__ = ['engine', 'metadata', 'Session']

# SQLAlchemy database engine. Updated by model.init_model().
engine = None

# SQLAlchemy session manager. Updated by model.init_model().
Session = None

# Global metadata. If you have multiple databases with overlapping table
# names, you'll need a metadata for each database.
metadata = MetaData()

import sqlalchemy as sa
from sqlalchemy import orm

from myapp.model import meta

def init_model(engine):
    """Call me before using any of the tables or classes in the model."""

    sm = orm.sessionmaker(autoflush=True, transactional=True, bind=engine)

    meta.engine = engine
    meta.Session = orm.scoped_session(sm)

import sqlalchemy as sa
from sqlalchemy import orm

from myapp.model import meta

t_dictionary = sa.Table("Dictionary", meta.metadata,
    sa.Column("id", sa.types.Integer, primary_key=True),
    sa.Column("term", sa.types.String(100), nullable=False),
    sa.Column("definition", sa.types.String, nullable=False),
    )

class Dictionary(object):
    pass

orm.mapper(Dictionary, t_dictionary)

from myapp.model import dictionary
from myapp.model.dictionary import Dictionary

import sqlalchemy as sa
from sqlalchemy import orm

from myapp.model import meta

t_people = sa.Table('people', meta.metadata,
    sa.Column('id', sa.types.Integer, primary_key=True),
    sa.Column('name', sa.types.String(100)),
    sa.Column('email', sa.types.String(100))
    )

t_addresses_people = sa.Table('addresses_people', meta.metadata,
    sa.Column('id', sa.types.Integer, primary_key=True),
    sa.Column('person_id', sa.types.Integer, sa.ForeignKey('people.id')),
    sa.Column('address_id', sa.types.Integer, sa.ForeignKey('addresses.id'))
    )

t_addresses = sa.Table('addresses', meta.metadata,
    sa.Column('id', sa.types.Integer, primary_key=True),
    sa.Column('address', sa.types.String(100))
    )

class Person(object):
    pass

class Address(object):
    pass

orm.mapper(Address, t_addresses)
orm.mapper(Person, t_people, properties = {
    'my_addresses' : orm.relation(Address, secondary = t_addresses_people),
    })

import sqlalchemy as sa
from sqlalchemy import orm

from myapp.model import meta
from myapp.model import records

def init_model(engine):
    """Call me before using any of the tables or classes in the model."""

    sm = orm.sessionmaker(autoflush=True, transactional=True, bind=engine)

    meta.engine = engine
    meta.Session = orm.scoped_session(sm)

records.t_record = sa.Table("Record", meta.metadata,
                            autoload=True, autoload_with=engine)
orm.mapper(records.Record, records.t_record)

% python
Python 2.5.1 (r251:54863, Oct 5 2007, 13:36:32)
[GCC 4.1.3 20070929 (prerelease) (Ubuntu 4.1.2-16ubuntu2)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlalchemy as sa
>>> engine = sa.create_engine("sqlite:///test.sqlite")
>>> from myapp import model
>>> model.init_model(engine)

The config file¶

When your Pylons application runs, it needs to know which database to connect to. Normally you put this information in development.ini and activate the model in environment.py. Put the following in development.ini in the [app:main] section, depending on your database,

sqlalchemy.url = sqlite:///%(here)s/mydatabasefilename.sqlite

sqlalchemy.url = mysql://username:password@host:port/database
sqlalchemy.pool_recycle = 3600

sqlalchemy.url = postgres://username:password@host:port/database

The engine¶

Put this at the top of myapp/config/environment.py:

from sqlalchemy import engine_from_config
from myapp.model import init_model

And this in the load_environment function:

engine = engine_from_config(config, 'sqlalchemy.')
init_model(engine)

The second argument is the prefix to look for. If you named your keys “sqlalchemy.default.url”, you would put “sqlalchemy.default.” here. The prefix may be anything, as long as it’s consistent between the config file and this function call.

Controller¶

Add the following to the top of myapp/lib/base.py (the base controller):

from myapp.model import meta

And change the .__call__ method to: .. code-block:: python

def __call__(self, environ, start_response):

try:

return WSGIController.__call__(self, environ, start_response)

finally:

meta.Session.remove()

The .remove() method is very important! It discards any leftover ORM data in the current web request. Otherwise the stray data will leak into the next request handled by this thread, potentially causing errors or data corruption.

Any per-request behaviors can be configured at this stage. For example, to use just a single database connection per request, which removes all connection pool checkin/checkout overhead, the per-request Session can be configured with a Connection:

def __call__(self, environ, start_response):
    conn = meta.engine.connect()
    meta.Session.configure(bind=conn)
    try:
        return WSGIController.__call__(self, environ, start_response)
    finally:
        meta.Session.remove()
        conn.close()

Note that when using a session with transactional=True, the session holds onto a single connection through the lifespan of each transaction so the above optimization is not as significant.

Building the database¶

To actually create the tables in the database, you call the metadata’s .create_all() method. You can do this interactively or use paster‘s application initialization feature. To do this, put the code in myapp/websetup.py. After the load_environment() call, put:

from myapp.model import meta
log.info("Creating tables")
meta.metadata.create_all(bind=meta.engine)
log.info("Successfully setup")

Then run the following on the command line:

paster setup-app development.ini

Data queries and modifications¶

Here’s how to enter new data into the database:

mr_jones = Person()
mr_jones.name = 'Mr Jones'
meta.Session.save(mr_jones)
meta.Session.commit()

mr_jones here is an instance of Person. Its properties correspond to the column titles of t_people and contain the data from the selected row. A more sophisticated application would have a Person.__init__ method that automatically sets attributes based on its arguments.

An example of loading a database entry in a controller method, performing a sex change, and saving it:

person_q = meta.Session.query(Person) # An ORM Query object for accessing the Person table
mr_jones = person_q.filter(Person.name=='Mr Jones').one()
print mr_jones.name # prints 'Mr Jones'
mr_jones.name = 'Mrs Jones' # only the object instance is changed here ...
meta.Session.commit() # ... only now is the database updated

To return a list of entries use:

all_mr_joneses = person_q.filter(Person.name=='Mr Jones').all()

To get all list of all the people in the table use:

everyone = person_q.all()

To retrieve by id:

someuser = person_q.get(5)

You can iterate over every person even more simply:

print "All people"
for p in person_q:
print p.name
print
print "All Mr Joneses:"
for p in person_q.filter(Person.name=='Mr Jones'):
print p.name

To delete an entry use the following:

mr_jones = person_q.filter(Person.name=='Mr Jones').one()
meta.Session.delete(mr_jones)
meta.Session.commit()

print mr_jones.my_addresses[0].address # prints first address

address_q = meta.Session.query(Address)

# Retrieve an existing address
address = address_q.filter(Address.address=='33 Pine Marten Lane, Pleasantville').one()

# Add to the list
mr_jones.my_addresses.append(new_address)

# issue updates to the join table
meta.Session.commit()

new_address = Address() # Construct an empty address object
new_address.address = '33 Pine Marten Lane, Pleasantville'
mr_jones.my_addresses.append(new_address) # Add to the list
meta.Session.commit() # Commit changes to the database

search_address = Address()
search_address.address = '33 Pine Marten Lane, Pleasantville'
residents_at_33_pine_marten_lane = \
    person_q.filter(Person.my_addresses.contains(search_address)).all()

residents_at_33_pine_marten_lane = \
 person_q.join('my_addresses').filter(
    Address.address=='33 Pine Marten Lane, Pleasantville').all()

residents_at_33_pine_marten_lane = \
 person_q.filter(Person.my_addresses.any(
    Address.address=='33 Pine Marten Lane, Pleasantville')).all()

del mr_jones.my_addresses[0] # Delete the reference to the address
meta.Session.commit()

meta.Session.delete(mr_jones.my_addresses[0]) # Delete the Address object
del mr_jones.my_addresses[0]
meta.Session.commit() # Commit both operations to the database

orm.mapper(Address, t_addresses)
orm.mapper(Person, t_people, properties = {
'my_addresses' : orm.relation(
        Address, secondary=t_addresses_people, cascade="all,delete-orphan"),
})

del mr_jones.my_addresses[0] # Delete the reference to the address,
                             # also deletes the Address
meta.Session.commit()

q = sa.select([table1.c.id, table1.c.name], order_by=[table1.c.name])
records = meta.Session.execute(q).fetchall()

# Example of a bulk SQL UPDATE.
update = table1.update(table1.c.name=="Jack")
meta.Session.execute(update, name="Ed")
meta.Session.commit()

# Example of updating all matching records using an expression.
update = table1.update(values={table1.c.entry_id: table1.c.entry_id + 1000})
meta.Session.exececute(update)
meta.Session.commit()

# Example of a bulk SQL DELETE.
delete = table1.delete(table1.c.name.like("M%"))
meta.Session.execute(delete)
meta.Session.commit()

Testing Your Models¶

Normal model usage works fine in model tests, however to use the metadata you must specify an engine connection for it. To have your tables created for every unit test in your project, use a test_models.py such as:

from myapp.tests import *
from myapp import model
from myapp.model import meta

class TestModels(TestController):
    def setUp(self):
        meta.Session.remove()
        meta.metadata.create_all(meta.engine)

    def test_index(self):
        # test your models

“nosetests –with-pylons=/path/to/test.ini ...” is another way to ensure that your model is properly initialized before the tests are run. This can be used when running non-controller tests.

Multiple engines¶

Some applications need to connect to multiple databases (engines). Some always bind certain tables to the same engines (e.g., a general database and a logging database); this is called “horizontal partitioning”. Other applications have several databases with the same structure, and choose one or another depending on the current request. A blogging app with a separate database for each blog, for instance. A few large applications store different records from the same logical table in different databases to prevent the database size from getting too large; this is called “vertical partitioning” or “sharding”. The pattern above can accommodate any of these schemes with a few minor changes.

First, you can define multiple engines in your config file like this:

sqlalchemy.default.url = "mysql://..."
sqlalchemy.default.pool_recycle = 3600
sqlalchemy.log.url = "sqlite://..."

This defines two engines, “default” and “log”, each with its own set of options. Now you have to instantiate every engine you want to use.

default_engine = engine_from_config(config, 'sqlalchemy.default.')
log_engine = engine_from_config(config, 'sqlalchemy.log.')
init_model(default_engine, log_engine)

Of course you’ll have to modify init_model() to accept both arguments and create two engines.

To bind different tables to different databases, but always with a particular table going to the same engine, use the binds argument to sessionmaker rather than bind:

binds={"table1": engine1, "table2": engine2}
Session = scoped_session(sessionmaker(
                transactional=True, autoflush=True, binds=binds)

To choose the bindings on a per-request basis, skip the sessionmaker bind(s) argument, and instead put this in your base controller’s __call__ method before the superclass call, or directly in a specific action method:

meta.Session.configure(bind=meta.engine)

binds= works the same way here too.

Discussion on coding style, the Session object, and bound metadata¶

All ORM operations require a Session and an engine. All non-ORM SQL operations require an engine. (Strictly speaking, they can use a connection instead, but that’s beyond the scope of this tutorial.) You can either pass the engine as the bind= argument to every SQLAlchemy method that does an actual database query, or bind the engine to a session or metadata. This tutorial recommends binding the session because that is the most flexible, as shown in the “Multiple Engines” section above.

It’s also possible to bind a metadata to an engine using the MetaData(engine) syntax, or to change its binding with metadata.bind = engine. This would allow you to do autoloading without the autoload_with argument, and certain SQL operations without specifying an engine or session. Bound metadata was common in earlier versions of SQLAlchemy but is no longer recommended for beginners because it can cause unexpected behavior when ORM and non-ORM operations are mixed.

Don’t confuse SQLAlchemy sessions and Pylons sessions; they’re two different things! The session object used in controllers (pylons.session) is an industry standard used in web applications to maintain state between web requests by the same user. SQLAlchemy’s session is an object that synchronizes ORM objects in memory with their corresponding records in the database.

The Session variable in this chapter is _not_ a SQLAlchemy session object; it’s a “contextual session” class. Calling it returns the (new or existing) session object appropriate for this web request, taking into account threading and middleware issues. Calling its class methods (Session.commit(), Session.query(...), etc) implicitly calls the corresponding method on the appropriate session. You can normally just call the Session class methods and ignore the internal session objects entirely. See “Contextual/Thread-local Sessions” in the SQLAlchemy manual for more information. This is equivalent to SQLAlchemy 0.3’s SessionContext but with a different API.

“Transactional” sessions are a new feature in SQLAlchemy 0.4; this is why we’re using Session.commit() instead of Session.flush(). The transactional and autoflush args to sessionmaker enable this, and should normally be used together.

# Instead of the regular mapper calls.
meta.Session.mapper(MyClass, table1)

Fancy classes¶

Here’s an ORM class with some extra features:

class Person(object):
    def __init__(self, firstname, lastname, sex):
        if not firstname: raise ValueError("arg 'firstname' cannot be blank")
        if not lastname: raise ValueError("arg 'lastname' cannot be blank")
        if sex not in ["M", "F"]: raise ValueError("sex must be 'M' or 'F'")
        self.firstname = firstname
        self.lastname = lastname
        self.sex = sex

    def __repr__(self):
        myclass = self.__class__.__name__
        return "<%s %s %s>" % (myclass, self.firstname, self.lastname)
        #return "%s(%r, %r)" % (myclass, self.firstname, self.lastname, self.sex)
        #return "<%s %s>" % (self.firstname, self.lastname)

    @property
    def name(self):
        return "%s %s" % (self.firstname, self.lastname)

    @classmethod
    def all(class_, order=None, sex=None):
        """Return a Query of all Persons. The caller can iterate this,
        do q.count(), add additional conditions, etc.
        """
        q = meta.Session.query(Person)
        if order and order.lower().startswith("d"):
            q = q.order_by([Person.birthdate.desc()])
        else:
            q = q.order_by([Person.lastname, Person.firstname])
        return q

    @classmethod
    def recent(self, cutoff_days=30):
        cutoff = datetime.date.today() - datetime.timedelta(days=cutoff_days)
        q = meta.Session.query(Person).order_by(
                [Person.last_transaction_date.desc()])
        q = q.filter(Person.last_transaction_date >= cutoff)
        return q

With this class you can create new records with constructor args. This is not only convenient but ensures the record starts off with valid data (no required field empty). .__init__ is not called when loading an existing record from the database, so it doesn’t interfere with that. Instances can print themselves in a friendly way, and a read-only property is calculated from multiple fields.

Class methods return high-level queries for the controllers. If you don’t like the class methods you can have a separate PersonSearch class for them. The methods get the session from the myapp.model.meta module where we’ve stored it. Note that this module imported the meta module, not the Session object directly. That’s because init_model() replaces the Session object, so if we’d imported the Session object directly we’d get its original value rather than its current value.

You can do many more things in SQLAlchemy, such as a read-write property on a hidden column, or specify relations or default ordering in the orm.mapper call. You can make a composite property like person.location.latitude and person.location.longitude where latitude and longitude come from different table columns. You can have a class that mimics a list or dict but is associated with a certain table. Some of these properties you’ll make with Pylons normal property mechanism; others you’ll do with the property argument to orm.mapper. And you can have relations up the gazoo, which can be lazily loaded if you don’t use one side of the relation much of the time, or eagerly loaded to minimize the number of queries. (Only the latter use SQL joins.) You can have certain columns in your class lazily loaded too, although SQLAlchemy calls this “deferred” rather than “lazy”. SQLAlchemy will automatically load the columns or related table when they’re accessed.

If you have any more clever ideas for fancy classes, please add a comment to this article.

Logging¶

SQLAlchemy has several loggers that chat about the various aspects of its operation. To log all SQL statements executed along with their parameter values, put the following in development.ini:

[logger_sqlalchemy]
level = INFO
handlers =
qualname = sqlalchemy.engine

Then modify the “[loggers]” section to enable your new logger:

[loggers]
keys = root, myapp, sqlalchemy

To log the results along with the SQL statements, set the level to DEBUG. This can cause a lot of output! To stop logging the SQL, set the level to WARN or ERROR.

SQLAlchemy has several other loggers you can configure in the same way. “sqlalchemy.pool” level INFO tells when connections are checked out from the engine’s connection pool and when they’re returned. “sqlalchemy.orm” and buddies log various ORM operations. See “Configuring Logging” in the SQLAlchemy manual.

Multiple application instances¶

If you’re running multiple instances of the _same_ Pylons application in the same WSGI process (e.g., with Paste HTTPServer’s “composite” application), you may run into concurrency issues. The problem is that Session is thread local but not application-instance local. We’re not sure how much this is really an issue if Session.remove() is properly called in the base controller, but just in case it becomes an issue, here are possible remedies:

1. Attach the engine(s) to pylons.g (aka. config["pylons.g"]) rather than to the meta module. The globals object is not shared between application instances.
2. Add a scoping function. This prevents the application instances from sharing the same session objects. Add the following function to your model, and pass it as the second argument to scoped_session:

def pylons_scope():
    import thread
    from pylons import config
    return "Pylons|%s|%s" % (thread.get_ident(), config._current_obj())

Session = scoped_session(sessionmaker(...), pylons_scope)

If you’re affected by this, or think you might be, please bring it up on the pylons-discuss mailing list. We need feedback from actual users in this situation to verify that our advice is correct.