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

by

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

More Related Contents:

  1. Bulk insert with SQLAlchemy ORM
  2. SQLAlchemy: how to filter date field?
  3. SQL Alchemy ORM returning a single column, how to avoid common post processing
  4. SQLAlchemy: cascade delete
  5. Efficiently updating database using SQLAlchemy ORM
  6. Why is SQLAlchemy insert with sqlite 25 times slower than using sqlite3 directly?
  7. SQLAlchemy ManyToMany secondary table with additional fields
  8. How do you join two tables on a foreign key field using django ORM?
  9. SQLAlchemy: engine, connection and session difference
  10. sqlalchemy: create relations but without foreign key constraint in db?
  11. flask-sqlalchemy or sqlalchemy
  12. Getting random row through SQLAlchemy
  13. Select NULL Values in SQLAlchemy
  14. How to update sqlalchemy orm object by a python dict
  15. Is possible to mapping view with class using mapper in SqlAlchemy?
  16. SQLALchemy dynamic filter_by
  17. using sqlalchemy to load csv file into a database
  18. SQLAlchemy ordering by count on a many to many relationship
  19. How do I get the “id” after INSERT into MySQL database with Python?
  20. How do I execute inserts and updates in an Alembic upgrade script?
  21. SQLAlchemy, get object not bound to a Session
  22. Complex foreign key constraint in SQLAlchemy
  23. What is choice_set in this Django app tutorial?
  24. How to upsert pandas DataFrame to Microsoft SQL Server table?
  25. How do i cast char to integer while querying in django ORM?
  26. Relations on composite keys using sqlalchemy
  27. flake8 complains on boolean comparison “==” in filter clause
  28. SQLAlchemy – Mapping self-referential relationship as one to many (declarative form)
  29. How to get number of affected rows in sqlalchemy?
  30. Flask-SQLAlchemy Constructor

Leave a Comment