onupdate based on another field with sqlalchemy declarative base

Question

I use sqlalchemy with the pyramid framework, and i want to link a person to his geographical department using his postcode. So i try to use the onupdate argument when defining the department_id column define the department_id. see fallowing code:

from datetime import date
from emailing.models import Base, DBSession
from sqlalchemy import Column, Integer, Unicode, Text, DateTime, Sequence, Boolean, Date, UnicodeText, UniqueConstraint, Table, ForeignKey
from sqlalchemy.orm import scoped_session, sessionmaker, column_property, relationship, backref
from sqlalchemy.sql import func

class Person(Base):
    __tablename__ = u'person'
    id = Column(Integer, primary_key=True)

    firstName = Column(Unicode(255))
    lastName = Column(Unicode(255))

    created_at = Column(Date, default=func.now())
    updated_at = Column(Date, onupdate=func.now())

    department_id = Column(Integer(), ForeignKey('department.id'), onupdate=dep_id_from_postcode)
    department = relationship("Department", backref='persons')


    __table_args__ = (UniqueConstraint('firstName', 'lastName'), {})


    def dep_id_from_postcode(self):
        return int(self.postcode[:2]) 

on update for the updated_at field works fine, but for the deparment_id field it tell my:

NameError: name 'dep_id_from_postcode' is not defined

i've found documentation about python executed function here: http://docs.sqlalchemy.org/en/latest/core/schema.html?highlight=trigger#python-executed-functions but nothing that uses another field to use in onupdate argument.

i hope i'm clear enought as i'm not a "natural english speaker" Thank you all

Answer 1

Move the function definition before its usage:

class Person(Base):
    # ...
    def dep_id_from_postcode(self):
        return int(self.postcode[:2])
    # ...
    department_id = Column(Integer(), ForeignKey('department.id'), onupdate=dep_id_from_postcode)
    # ...

Is the postcode really a field directly in Person? Because if it is not, you might need to handle this completely differently. For example, if the postcode is derived from the primary_address relationship, you need to check add/remove of the primary_address relationships and the changes in the related Address object for proper hooking.

Answer 2

SQLAlchemy has special mechanism for using other column value in default (i.e. onupdate) function called: Context-Sensitive Default Functions http://docs.sqlalchemy.org/en/rel_0_7/core/schema.html#context-sensitive-default-functions:

The typical use case for this context with regards to default generation is to have access to the other values being inserted or updated on the row.

As Van pointed out you need to make sure that postcode is a field defined for Person or you'll need to add functionality that will take care about getting postcode associated with Person instance.

What worked for me - regular function, not bound to any object. SQLAlchemy will call it at the time of insert and/or update and pass special argument with "context" - which is not actual object you are updating.

So for your example I would do something like this.

def dep_id_from_postcode(context):
    postcode = context.current_parameters['postcode']
    return int(postcode[:2])

class Person(Base):
    postcode = Column(String)
    # ...

    # ...
    department_id = Column(Integer(), ForeignKey('department.id'), onupdate=dep_id_from_postcode)
    # ...

Be careful with this context argument - I end up with problem when context had None field's value in some cases if I not updating 'postcode' value with the same operation.

Eclipse with pydev with debugger helped me to see what information is passed as context.