如何使alembic在after_create上发出自定义DDL？ [英] How do I get alembic to emit custom DDL on after_create?

问题描述

在创建表之后，我有几个要运行的自定义DDL语句：

I've got a couple of custom DDL statements that I want to run after create table:

update_function = DDL("""                                                                                                                                                       
CREATE OR REPLACE FUNCTION update_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = now();
    RETURN NEW;
END;
$$ language 'pgplsql';
""")

update_trigger = DDL("""
CREATE TRIGGER update %(table)s_timestamp BEFORE UPDATE
ON %(table)s FOR EACH ROW EXECUTE PROCEDURE update_timestamp();
""")

我将它们附加为：

event.listen(Session.__table__, 'after_create', update_function)
event.listen(Session.__table__, 'after_create', update_trigger)

当我执行 create_all 时，我得到了期望的SQL：

When I do create_all, I get the SQL I expect:

CREATE OR REPLACE FUNCTION update_timestamp()
RETURNS TRIGGER AS $$ 
BEGIN
    NEW.updated_at = now();
    RETURN NEW;
END;
$$ language 'pgplsql';


CREATE TRIGGER update session_timestamp BEFORE UPDATE
ON session FOR EACH ROW EXECUTE PROCEDURE update_timestamp();

但是当我使用Alembic升级时，不会出现以下语句：

But when I upgrade using Alembic, the statements don't appear:

-- Running upgrade c0d470e5c81 -> 6692fad7378

CREATE TABLE session (
    created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT 'CURRENT_TIMESTAMP', 
    updated_at TIMESTAMP WITHOUT TIME ZONE DEFAULT 'CURRENT_TIMESTAMP', 
    id VARCHAR(32) NOT NULL, 
    owner_id INTEGER, 
    database_id VARCHAR(32), 
    content TEXT, 
    PRIMARY KEY (id), 
    FOREIGN KEY(database_id) REFERENCES database (id), 
    FOREIGN KEY(owner_id) REFERENCES users (id)
);

INSERT INTO alembic_version (version_num) VALUES ('6692fad7378');

是否有一种方法可以使Alembic触发 after_create事件？

Is there a way to get alembic to trigger the 'after_create' events?

推荐答案

将发出表级别的before_create / after_create事件（仅不是元数据级别的事件）。您需要确保env.py脚本中发生的任何事情最终都涉及那些已设置的事件侦听器。

The table level before_create/after_create events are emitted (just not the metadata level ones). you need to make sure whatever happens in your env.py script ultimately involves those event listeners being set up.

此处的代码有点让人怀疑：

The code you have here looks a little suspect:

event.listen(Session.__table__, 'after_create', update_function)
event.listen(Session.__table__, 'after_create', update_trigger)

Session .__ table __ 只是一个 Table 实例，这可能不是您在Alembic脚本中看到的。 Alembic create_table 命令在本地创建 Table 并在其上运行创建，因此您需要收听全局所有表对象：

Session.__table__ here would just be a single Table instance and that's probably not what you'd see in the alembic script. The alembic create_table command creates a Table locally and just runs a create on it, so you'd need to listen to all Table objects globally:

from sqlalchemy import Table
event.listen(Table, 'after_create', update_function)
event.listen(Table, 'after_create', update_trigger)

如果这些事件仅用于但是，在这个特定表中，您将不会使用任何事件，只需将这些触发器的DDL（）直接放在迁移脚本中，就在它调用 create_table（）。

if these events are only for this one specific table though, then you wouldn't use any events, you'd just put the DDL() for those triggers directly in your migration script, right after where it calls create_table().

这篇关于如何使alembic在after_create上发出自定义DDL？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！