Clone wiki

sqlalchemy / UsageRecipes / ValidateOnType

Validate on Column Type

SQLAlchemy normally defers to the DBAPI and database in use to validate that information being passed is as expected. To do so within the Python layer adds performance and complexity overhead, makes assumptions about the calling code, and in most cases is redundant versus what the database already does.

The recipe below shows how to link validation functions to the types of each column, when you do want up-front validation and possibly value coercion, as values are set. The attribute events system is used to intercept column-based attributes as they are instrumented, attaching a listener which can operate upon the value before it's assigned.

from sqlalchemy import Column, Integer, String, DateTime
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import event
import datetime

Base= declarative_base()

def validate_int(value):
    if isinstance(value, basestring):
        value = int(value)
        assert isinstance(value, integer)
    return value

def validate_string(value):
    assert isinstance(value, basestring)
    return value

def validate_datetime(value):
    assert isinstance(value, datetime.datetime)
    return value

validators = {

@event.listens_for(Base, 'attribute_instrument')
def configure_listener(class_, key, inst):
    if not hasattr(, 'columns'):
    @event.listens_for(inst, "set", retval=True)
    def set_(instance, value, oldvalue, initiator):
        validator = validators.get([0].type.__class__)
        if validator:
            return validator(value)
            return value

class MyObject(Base):
    __tablename__ = 'mytable'

    id = Column(Integer, primary_key=True)
    svalue = Column(String)
    ivalue = Column(Integer)
    dvalue = Column(DateTime)

m = MyObject()
m.svalue = "ASdf"

m.ivalue = "45"

m.dvalue = "not a date"