Posts for the month of February 2010

SQLAlchemy's MapperExtension


You use SQLAlchemy and you want to compute some models' attributes just before they are flushed to the database by SQLAlchemy (an SQL trigger, but on Python's side).


SqlAlchemy provides the MapperExtension interface with several methods to be implemented (eg.: before_insert, before_update, after_insert, after_update). You can see a full list of possible hooks in SqlAlchemy's documentation.

Let's see how to always capitalize the name of our users:

class User(object):
    def __init__(self, name): = name
from sqlalchemy import Table, Column, Integer, Text
users_table = Table('users',
                     Column('id', Integer, primary_key=True),
                     Column('name', Text))

We want to modify the name before every SQL insert and every SQL update statement. The corresponding MapperExtension could be:

from sqlalchemy.orm import MapperExtension
class CapitalizeNameMapperExtension(MapperExtension):
    def _capitalize_name(self, instance):
        if is not None:
    def before_insert(self, mapper, connection, instance):
    def before_update(self, mapper, connection, instance):

We can now map the model and the table using this extension:

from sqlalchemy.orm import mapper
user_mapper = mapper(User, users_table,

So now, let's see it in action:

>>> user1 = User("mynameis")
>>> print
>>> from nagare import database
>>> database.session.add(user1)
>>> print
>>> database.session.flush()
>>> print

Note: this works on column-based attributes. At the call of the MapperExtension's functions, the mapper has already made the graph of objects to be updated in the database. This implies that if you make any change on collection attribute, it will not be stored in the database before the next flush call.