SQLAlchemy - maximum column length

Question

is it possible in SQLAlchemy to provide the maximum row length of the value assigned to the mapped column? All I need to do is throw an exception if the assigned string value is greater than the length of the corresponding column of the table of type STRING.

thanks

Answer 1

The easiest way is to simply rename the associated column and proxy 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 decompose the creation of a property and even use a common validation structure such as formencode.

---

If you need a more specific SQLAlchemy solution and don't mind using specific interfaces, then SQLAlchemy has an extension mechanism for collecting events by attribute. A validator using this will 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 

Then you should use this extension by setting __sa_instrumentation_manager__ = InstallValidatorListeners to any class that you want to test. You can also just set it in the Base class if you want it to apply to all classes derived from it.

Answer 2

Here is the updated version that matches the event system of the new 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