使用SQLAlchemy在PostgreSQL中创建函数和触发器 [英] Creating functions and triggers in PostgreSQL with SQLAlchemy

问题描述

我使用SQLAlchemy Engine创建一些函数和触发器，但是我不想混合使用Python和SQL，因此我为SQL语句创建了一个单独的文件，我读取了内容并将其传递给 engine.execute（）。它不会引发任何错误，但是不会在数据库中创建函数，但是如果我通过pgAdmin运行相同的SQL文件，则一切正常。

I use SQLAlchemy Engine to create some functions and triggers, but I did not want to mix Python and SQL, so I have created a separate file for my SQL statements, I read the content and pass it to engine.execute(). It throws no errors, however the functions are not created in the database, but if I run the same SQL file through pgAdmin, everything works fine.

我的SQL文件：

DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_extension WHERE extname = 'plpython3u') THEN
    CREATE EXTENSION plpython3u;
  END IF;
END;
$$;

DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_proc WHERE proname = 'my_func') THEN
    CREATE FUNCTION public.my_func() RETURNS TRIGGER LANGUAGE 'plpython3u' NOT LEAKPROOF AS $BODY$
    -- definition
    $BODY$;

    GRANT EXECUTE ON FUNCTION my_func() TO public;
  END IF;
END;
$$;

DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_proc WHERE proname = 'my_func2') THEN
    CREATE FUNCTION public.my_func2() RETURNS TRIGGER LANGUAGE 'plpython3u' NOT LEAKPROOF AS $BODY$
    -- definition
    $BODY$;

    GRANT EXECUTE ON FUNCTION my_func2() TO public;
  END IF;
END;
$$;

我按如下方式运行：

def execute_sql_file(engine, path):
    try:
        with open(path) as file:
            engine.execute(file.read())
    except ProgrammingError:
        raise MyCustomError
    except FileNotFoundError:
        raise MyCustomError

如果我在没有超级用户特权的情况下运行此程序，则将按预期方式引发 ProgrammingError 。以我的理解， END; 提交事务，因此该代码真正运行了，这些功能应该对公众可用，但是甚至没有创建。欢迎提出任何想法，谢谢！

If I run this without superuser privilege, it throws ProgrammingError, as expected. In my understanding END; commits the transaction, so it this code is really run, the functions should be available for the public, however they are not even created. Any ideas are welcome, thanks!

推荐答案

我相信您可能将 BEGIN SQL命令（Postgresql扩展名）和 PL / pgSQL块。 SQL命令 DO 执行一个匿名代码块，就像它是一个没有参数的匿名函数并返回 void 一样。换句话说，

I believe you may have mixed the BEGIN SQL command (a Postgresql extension) and a PL/pgSQL block. The SQL command DO executes an anonymous code block, as if it were an anonymous function with no parameters and returning void. In other words in

DO $$
BEGIN
    ...
END;
$$;

BEGIN / END; 对表示代码块，而不是事务。值得注意的是，从Postgresql版本11 开始，可以在 DO 块，因为它是未在事务块中执行，但其命令为 COMMIT 和 ROLLBACK ，而不是关键字 END 。

the BEGIN / END; pair denotes the code block, not a transaction. It is worth noting that starting from Postgresql version 11 it is possible to manage transactions in a DO block, given that it is not executed in a transaction block, but the commands for that are COMMIT and ROLLBACK, not the keyword END.

那么问题是尽管可以清楚地执行您的命令，但未提交更改-如错误所证明的（如果未以适当的特权运行）。此问题是由 SQLAlchemy自动提交功能的工作原理引起的。简而言之，它将检查您的语句/命令，并尝试确定它是数据更改操作还是DDL语句。这适用于基本操作，例如 INSERT ， DELETE ， UPDATE 之类，但并不完美。实际上，不可能始终正确地确定语句是否更改了数据。例如 SELECT my_mutating_procedure（）就是这样的语句。因此，如果执行更复杂的操作，则需要一些帮助。一种方法是通过将SQL字符串包装在 text（） 构造和使用 execution_options（） ：

The problem then is that your changes are not committed, though your commands clearly are executed – as proven by the error, if not running with suitable privileges. This issue is caused by how SQLAlchemy autocommit feature works. In short, it inspects your statement / command and tries to determine if it is a data changing operation, or a DDL statement. This works for the basic operations such as INSERT, DELETE, UPDATE, and the like, but is not perfect. In fact it is impossible for it to always correctly determine if a statement changes data; for example SELECT my_mutating_procedure() is such a statement. So it needs some help, if doing more complex operations. One way is to instruct the autocommit machinery that it should commit by wrapping the SQL string in a text() construct and using execution_options():

engine.execute(text("SELECT my_mutating_procedure()").
               execution_options(autocommit=True))

也可以使用 DDL 构造：

It is also possible to explicitly instruct SQLAlchemy that the command is a literal DDL statement using the DDL construct:

from sqlalchemy.schema import DDL

def execute_sql_file(engine, path):
    try:
        with open(path) as file:
            stmt = file.read()

        # Not strictly DDL, but a series of DO commands that execute DDL
        ddl_stmt = DDL(stmt)
        engine.execute(ddl_stmt)

    except ProgrammingError:
        raise MyCustomError

    except FileNotFoundError:
        raise MyCustomError

关于它与pgAdmin一起使用的原因，如果没有引发错误，则默认情况下可能会提交。

As to why it works with pgAdmin, it probably by default commits, if no error was raised.

这篇关于使用SQLAlchemy在PostgreSQL中创建函数和触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！