如何在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.

例如，我有一个Integer列。我尝试插入值hello，这不是一个有效的整数。 SQLAlchemy将允许我这样做。只有稍后，当我执行 session.commit（），它引发一个异常： sqlalchemy.exc.DataError：（DataError）无效的输入语法整数：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"….

我添加了批记录，我不想在每个<$ c $

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层，通过@validates：

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

http://docs.sqlalchemy.org/en/latest /orm/mapper_config.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"

$ b b

验证和强制也可以使用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/types.html#coercing-encoded-strings-to-unicode

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