SQLAlchemy - maximum column length

is it possible in SQLAlchemy to enforce maximum string length of val开发者_运维知识库ue assigned to mapped column? All I want is to raise an exception if an assigned string value is longer then the length of the corresponding table column of type STRING.

Thank you

It's easiest to just rename the mapped column and proxy it through a property:

class Something(Base):
    ...
    _foo = Column('foo', String(123))

    @property
    def foo(self):
        return self._foo

    @foo.setter
    def foo(self, value):
        if len(value) > _foo.type.length:
            raise Exception("Value too long")
        self._foo = value 

You can easily factor out the property creation, and even use a generic validation framework like formencode.

If you need a more SQLAlchemy specific solution and don't mind using specific interfaces, then SQLAlchemy has an extension mechanism for capturing events on attributes. A validator using that would look something like this:

from sqlalchemy.orm.interfaces import AttributeExtension, InstrumentationManager
from sqlalchemy.orm import ColumnProperty

class InstallValidatorListeners(InstrumentationManager):
    def post_configure_attribute(self, class_, key, inst):
        """Add validators for any attributes that can be validated."""
        prop = inst.prop
        # Only interested in simple columns, not relations
        if isinstance(prop, ColumnProperty) and len(prop.columns) == 1:
            col = prop.columns[0]
            # if we have string column with a length, install a length validator
            if isinstance(col.type, String) and col.type.length:
                inst.impl.extensions.insert(0, LengthValidator(col.type.length))

class ValidationError(Exception):
    pass

class LengthValidator(AttributeExtension):
    def __init__(self, max_length):
        self.max_length = max_length

    def set(self, state, value, oldvalue, initiator):
        if len(value) > self.max_length:
            raise ValidationError("Length %d exceeds allowed %d" %
                                (len(value), self.max_length))
        return value

You would then use this extension by setting __sa_instrumentation_manager__ = InstallValidatorListeners on any class you want validated. You can also just set it on the Base class if you want it to apply to all classes derived from it.

Here is an updated version that fits the event system of newer sqlalchemy versions:

class InstallValidatorListeners(InstrumentationManager):
    def post_configure_attribute(self, class_, key, inst):
        """Add validators for any attributes that can be validated."""
        prop = inst.prop
        # Only interested in simple columns, not relations
        if isinstance(prop, ColumnProperty) and len(prop.columns) == 1:
            col = prop.columns[0]
            if isinstance(col.type, String) and col.type.length:
                sqlalchemy.event.listen(
                    getattr(class_, key), 'set', LengthValidator(col.type.length), retval=True)


class ValidationError(Exception):
    pass


class LengthValidator(AttributeExtension):
    def __init__(self, max_length):
        self.max_length = max_length

    def __call__(self, state, value, oldvalue, initiator):
        if len(value) > self.max_length:
            raise ValidationError(
                "Length %d exceeds allowed %d" % (len(value), self.max_length))
        return value