如何验证SQLAlchemy ORM中的列数据类型？

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

使用SQLAlchemy ORM，我想确保值是其列的正确类型。

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"….

例如，假设我有一个Integer列。我尝试插入值“hello”，这不是一个有效的整数。 SQLAlchemy允许我这样做。仅在稍后，当我执行session.commit（）时，它是否引发异常：sqlalchemy.exc.DataError：（DataError）无效的输入语法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(…)
我做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().
或者任何其他方法来防止一个坏记录破坏整个commit（）。

1 个解决方案

#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.

SQLAlchemy没有构建它，因为它将DBAPI /数据库作为最佳和最有效的验证和强制来源。

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.

要构建自己的验证，通常使用TypeDecorator或ORM级验证。 TypeDecorator的优势在于它在核心运行并且可以非常透明，但它只在实际发出SQL时才会发生。

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

为了更快地进行验证和强制，这是在ORM级别。

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

验证可以在ORM层通过@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:

@validates使用的事件系统也可以直接使用。您可以编写一个通用解决方案，将您选择的验证器链接到要映射的类型：

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:

验证和强制也可以使用TypeDecorator在类型级别构建，但这仅在发出SQL时，例如将utf-8字符串强制转换为unicode的示例：

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

#1