基于具有 sqlalchemy 声明性基础的另一个字段的 onupdate [英] onupdate based on another field with sqlalchemy declarative base

问题描述

我将 sqlalchemy 与金字塔框架一起使用，我想使用他的邮政编码将一个人链接到他的地理部门.所以我在定义department_id 列定义department_id 时尝试使用onupdate 参数.请参阅以下代码:

I use sqlalchemy with the pyramid framework, and i want to link a person to his geographical department using his postcode. So i try to use the onupdate argument when defining the department_id column define the department_id. see fallowing code:

from datetime import date
from emailing.models import Base, DBSession
from sqlalchemy import Column, Integer, Unicode, Text, DateTime, Sequence, Boolean, Date, UnicodeText, UniqueConstraint, Table, ForeignKey
from sqlalchemy.orm import scoped_session, sessionmaker, column_property, relationship, backref
from sqlalchemy.sql import func

class Person(Base):
    __tablename__ = u'person'
    id = Column(Integer, primary_key=True)

    firstName = Column(Unicode(255))
    lastName = Column(Unicode(255))

    created_at = Column(Date, default=func.now())
    updated_at = Column(Date, onupdate=func.now())

    department_id = Column(Integer(), ForeignKey('department.id'), onupdate=dep_id_from_postcode)
    department = relationship("Department", backref='persons')


    __table_args__ = (UniqueConstraint('firstName', 'lastName'), {})


    def dep_id_from_postcode(self):
        return int(self.postcode[:2]) 

on update for the updated_at 字段工作正常，但对于 deparment_id 字段，它告诉我:

on update for the updated_at field works fine, but for the deparment_id field it tell my:

NameError: name 'dep_id_from_postcode' 未定义

NameError: name 'dep_id_from_postcode' is not defined

我在这里找到了关于 python 执行函数的文档:http://docs.sqlalchemy.org/en/latest/core/schema.html?highlight=trigger#python-executed-functions但没有使用另一个字段在 onupdate 参数中使用.

i've found documentation about python executed function here: http://docs.sqlalchemy.org/en/latest/core/schema.html?highlight=trigger#python-executed-functions but nothing that uses another field to use in onupdate argument.

我希望我足够清楚，因为我不是天生会说英语的人"谢谢大家

i hope i'm clear enought as i'm not a "natural english speaker" Thank you all

推荐答案

SQLAlchemy 有特殊的机制来使用默认(即 onupdate)函数中的其他列值，称为:Context-Sensitive Default Functionshttp://docs.sqlalchemy.org/en/rel_0_7/core/schema.html#context-sensitive-default-functions:

SQLAlchemy has special mechanism for using other column value in default (i.e. onupdate) function called: Context-Sensitive Default Functions http://docs.sqlalchemy.org/en/rel_0_7/core/schema.html#context-sensitive-default-functions:

此上下文的典型用例关于默认生成是可以访问正在插入的其他值或在行上更新.

The typical use case for this context with regards to default generation is to have access to the other values being inserted or updated on the row.

正如 Van 指出的，您需要确保邮政编码是为 Person 定义的字段，否则您需要添加功能来处理与 Person 实例关联的邮政编码.

As Van pointed out you need to make sure that postcode is a field defined for Person or you'll need to add functionality that will take care about getting postcode associated with Person instance.

什么对我有用 - 常规函数，不绑定到任何对象.SQLAlchemy 将在插入和/或更新时调用它并传递带有上下文"的特殊参数 - 这不是您正在更新的实际对象.

What worked for me - regular function, not bound to any object. SQLAlchemy will call it at the time of insert and/or update and pass special argument with "context" - which is not actual object you are updating.

所以对于你的例子，我会做这样的事情.

So for your example I would do something like this.

def dep_id_from_postcode(context):
    postcode = context.current_parameters['postcode']
    return int(postcode[:2])

class Person(Base):
    postcode = Column(String)
    # ...

    # ...
    department_id = Column(Integer(), ForeignKey('department.id'), onupdate=dep_id_from_postcode)
    # ...

小心这个上下文参数 - 在某些情况下，如果我不使用相同的操作更新邮政编码"值，当上下文具有 None 字段的值时，我最终会遇到问题.

Be careful with this context argument - I end up with problem when context had None field's value in some cases if I not updating 'postcode' value with the same operation.

带有调试器的 pydev 的 Eclipse 帮助我查看了哪些信息作为上下文传递.

Eclipse with pydev with debugger helped me to see what information is passed as context.

这篇关于基于具有 sqlalchemy 声明性基础的另一个字段的 onupdate的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！