Posts in category database

SQLAlchemy's MapperExtension

Problem

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

Solution

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):
        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 instance.name is not None:
            instance.name = instance.name.capitalize()
    def before_insert(self, mapper, connection, instance):
        self._capitalize_name(instance)
    def before_update(self, mapper, connection, instance):
        self._capitalize_name(instance)

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

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

So now, let's see it in action:

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

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.