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): 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.
In a function, you want to perform some operations on a database using sqlalchemy's session. If one of the operations fails, you want the session to go back to its state when the function was called.
Use a SAVEPOINT transaction:
from nagare import database def my_func(): try: with database.session.begin(nested=True): # let the job be done in a SAVEPOINT transaction # if an exception is raised in this block, session's state is restored ... return True except: return False