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

本文是小编为大家收集整理的关于如何验证SQLAlchemy ORM中的列数据类型?的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

使用sqlalchemy orm,我想确保值是其列的正确类型.

例如,说我有一个整数列.我尝试插入值" Hello"的值,这不是一个有效的整数. Sqlalchemy将允许我这样做.只有稍后,当我执行session.commit()时,它是否会引起异常:sqlalchemy.exc.DataError: (DataError) invalid input syntax integer: "hello"….

我正在添加批次记录,出于绩效原因,我不想在每次add(…)之后提交.

所以我该怎么:

  • 我做session.add(…)
  • 立即提出例外
  • 或确保我插入的值可以转换为目标列数据类型, 将其添加到批处理?
  • 或任何其他方法来防止一个不好的记录破坏整个commit().

推荐答案

sqlalchemy不会在dbapi/数据库中作为最佳,最有效的验证源和值的胁迫来构建它.

.

要构建您自己的验证,通常使用打字机或ORM级验证. TypeDecorator的优势是它在核心处运行,并且可以非常透明,尽管仅在SQL实际发射时才发生.

要尽快进行验证和胁迫,这是在ORM级别.

通过@validates:

在ORM层处验证可以是临时的.

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

@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"

验证和胁迫也可以使用TypeDecorator在类型级别构建,尽管这只有在发射SQL时,例如此示例将UTF-8字符串迫使Unicode:

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

其他推荐答案

改进@zzzeek的答案,我建议以下解决方案:

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)

让您可以做以下操作:

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

这有两个好处:首先,只有一个事件触发,而实际上有一个附加到数据字段对象的验证器.它不会在set事件上浪费宝贵的CPU周期,这些事件无需定义验证功能.其次,它允许您定义自己的字段类型,并在此处添加验证方法,因此,并非所有要存储为Integer etc的东西都通过相同的检查运行,而只是从您的新字段类型中得出的内容.<<<<<<<<<

本文地址:https://www.itbaoku.cn/post/597721.html

问题描述

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

推荐答案

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

其他推荐答案

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.