如何创建一个sqlalchemy列，其默认值等于主键？ [英] How can I create a sqlalchemy column whose default is to be equal to the primary key?

问题描述

我有一个看起来像这样的课：

I have a class that looks like this:

class Foo(Base):
    pk = Column(Integer, Sequence('foo'), primary_key=True)

我想创建另一个字段， ref ，对于新创建的对象，默认值等于 pk 。不幸的是，我使用的是Postgres，它的非标准 nextval（）函数，所以我不能简单地将两列都默认设置为Sequence对象。

I want to make another field, ref, that defaults to being equal to pk for newly-created objects. Unfortunately, I'm using Postgres, which has a non-standard nextval() function, so I can't simply make both of the columns default to the Sequence object.

这是我当前的解决方案：

Here's my current solution:

class Foo(Base):
    pk = Column(Integer, Sequence('foo'), primary_key=True)
    ref = Column(Integer, index=True, nullable=False, default=func.currval('foo'))

但是，这取决于两件事：SQLAlchemy生成一个 pk 在<$ c之前的INSERT语句$ c> ref ，和 Postgres从左到右评估表达式（因此，<< c>生成的 nextval（）调用由 pk 总是发生在 ref 生成的当前值调用之前）。

However this depends on two things: SQLAlchemy generating an INSERT statement with pk before ref, and Postgres evaluating expressions from left to right (so the nextval() call generated by pk always happens before the currval call generated by ref).

对于如何更好地执行此操作，我将不胜感激！

I would greatly appreciate any suggestions for how to do this better!

编辑：还有一个更多的解决方案，设置 default = -1 或其他方式，并强制调用方更新 ref 字段。对于SQLA / Postgres语义的更改而言，这样做更安全，更可靠，但对于类的客户端而言却非常不便。

There's also the more solution of setting default=-1 or something and forcing the caller to update the ref field. That's safer and more robust to changes in SQLA/Postgres semantics but pretty inconvenient for clients of the class.

推荐答案

利用SQLAlchemy的ORM after_insert 映射器事件：

It is possible to utilize SQLAlchemy's ORM after_insert mapper event:

from sqlalchemy import event

# Model from OP
class Foo(Base):
    pk = Column(Integer, Sequence('foo'), primary_key=True)
    ref = Column(Integer, index=True, nullable=False, default=-1)

    @staticmethod
    def fill_ref(mapper, connection, target):
        # Note: You cannot use ORM in the event callbacks
        connection.execute(mapper.mapped_table.update()
                                 .values(ref=target.pk))
                                 .where(table.c.pk==target.pk))

event.listen(Foo, 'after_insert', Foo.fill_ref)

这篇关于如何创建一个sqlalchemy列，其默认值等于主键？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！