How can I verify Column data types in the SQLAlchemy ORM?

Question

Using the SQLAlchemy ORM, I want to make sure values are the right type for their columns.

For example, say I have an Integer column. I try to insert the value “hello”, which is not a valid integer. SQLAlchemy will allow me to do this. Only later, when I execute session.commit(), does it raise an exception: sqlalchemy.exc.DataError: (DataError) invalid input syntax integer: "hello"….

I am adding batches of records, and I don’t want to commit after every single add(…), for performance reasons.

So how can I:

• Raise the exception as soon as I do session.add(…)
• Or, make sure the value I am inserting can be converted to the target Column datatype, before adding it to the batch?
• Or any other way to prevent one bad record from spoiling an entire commit().

Answer 1

SQLAlchemy doesn't build this in as it defers to the DBAPI/database as the best and most efficient source of validation and coercion of values.

To build your own validation, usually TypeDecorator or ORM-level validation is used. TypeDecorator has the advantage that it operates at the core and can be pretty transparent, though it only occurs when SQL is actually emitted.

To do validation and coercion sooner, this is at the ORM level.

Validation can be ad-hoc, at the ORM layer, via @validates:

http://docs.sqlalchemy.org/en/latest/orm/mapped_attributes.html#simple-validators

The event system that @validates uses is also available directly. You can write a generalized solution that links validators of your choosing to the types being mapped:

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)     else:         assert isinstance(value, int)     return value  def validate_string(value):     assert isinstance(value, basestring)     return value  def validate_datetime(value):     assert isinstance(value, datetime.datetime)     return value  validators = {     Integer:validate_int,     String:validate_string,     DateTime:validate_datetime, }  # this event is called whenever an attribute # on a class is instrumented @event.listens_for(Base, 'attribute_instrument') def configure_listener(class_, key, inst):     if not hasattr(inst.property, 'columns'):         return     # this event is called whenever a "set"      # occurs on that instrumented attribute     @event.listens_for(inst, "set", retval=True)     def set_(instance, value, oldvalue, initiator):         validator = validators.get(inst.property.columns[0].type.__class__)         if validator:             return validator(value)         else:             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" 

Validation and coercion can also be built at the type level using TypeDecorator, though this is only when SQL is being emitted, such as this example which coerces utf-8 strings to unicode:

http://docs.sqlalchemy.org/en/latest/core/custom_types.html#coercing-encoded-strings-to-unicode

Answer 2

Improving on the answer of @zzzeek , I suggest the following solution:

from sqlalchemy import String from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.event import listen_for  Base = declarative_base()  @listens_for(Base, 'attribute_instrument') def configure_listener(table_cls, attr, col_inst):     if not hasattr(col_inst.property, 'columns'):         return     validator = getattr(col_inst.property.columns[0].type, 'validator', None)     if validator:         # Only decorate columns, that need to be decorated         @listens_for(col_inst, "set", retval=True)         def set_(instance, value, oldvalue, initiator):             return validator(value) 

That lets you do things like:

class Name(String):     def validator(self, name):         if isinstance(name, str):             return name.upper()         raise TypeError("name must be a string") 

This has two benefits: Firstly, there is only an event triggered, when there actually is a validator attached to the data field object. It does not waste precious CPU cycles on set events for objects, that have no function for validation defined. Secondly, it allows you to define your own field types and just add a validator method there, so not all things that you want to store as Integer etc run through the same checks, just the ones derived from your new field type.