如何验证 SQLAlchemy ORM 中的列数据类型? [英] How can I verify Column data types in the SQLAlchemy ORM?

问题描述

使用 SQLAlchemy ORM，我想确保值对于它们的列是正确的类型.

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

例如，假设我有一个整数列.我尝试插入值hello"，它不是一个有效的整数.SQLAlchemy 将允许我这样做.只是后来，当我执行 session.commit() 时，它是否引发异常:sqlalchemy.exc.DataError: (DataError) invalid input syntax integer: "hello"....

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

我正在添加一批记录，出于性能原因，我不想在每个 add(...) 之后提交.

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

那我该怎么办:

• 我一做就引发异常session.add(…)
• 或者，确保我插入的值可以转换为目标列数据类型，在将它添加到批处理之前?
• 或者任何其他方法来防止一个不良记录破坏整个 commit().

• 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().

推荐答案

SQLAlchemy 没有内置此功能，因为它认为 DBAPI/数据库是验证和强制值的最佳和最有效来源.

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.

要构建自己的验证，通常使用 TypeDecorator 或 ORM 级别的验证.TypeDecorator 的优点是它在核心运行并且可以非常透明，尽管它只在实际发出 SQL 时发生.

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.

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

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

验证可以是临时的，在 ORM 层，通过 @validates:

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

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

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

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"

也可以使用 TypeDecorator 在类型级别构建验证和强制，尽管这仅在发出 SQL 时进行，例​​如将 utf-8 字符串强制为 unicode 的示例:

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

这篇关于如何验证 SQLAlchemy ORM 中的列数据类型?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！