调用存储函数或过程不会插入并保留更改 [英] Calling stored function or procedure won't insert and persist changes

问题描述

因此，我对使用SQLAlchemy和PostgreSQL的这种奇怪行为感到非常困惑。假设我有一个表：

So I am very confused about this weird behaviour I have with SQLAlchemy and PostgreSQL. Let's say I have a table:

create table staging.my_table( 
  id integer DEFAULT nextval(...),
  name text,
  ...
);

和一个存储函数：

create or replace function staging.test()
  returns void
  language plpgsql
  as $function$
begin    
   insert into staging.my_table (name) values ('yay insert');
end;
$function$;

我现在想要做的是使用SQLAlchemy在Python中调用此函数，如下所示：

What I want to do now is call this function in Python with SQLAlchemy like this:

from sqlalchemy import create_engine

engine = create_engine('postgresql+psycopg2://foo:bar@localhost:5432/baz')
engine.execute('select staging.test()')

---

当我运行此Python代码时，数据库中什么也没插入。这很奇怪，因为当我将函数调用替换为时，选择1 并添加 .fetchall（）即可执行并打印结果时，我在控制台中看到了结果。

---

When I run this Python code nothing get's inserted in my database. That's weird because when I replace the function call with select 1 and add .fetchall() to it it gets executed and I see the result in console when I print it.

假设我两次运行此代码，但没有任何反应，但是代码运行成功且没有错误。
如果我现在切换到数据库并运行，请选择staging.test（）; 并选择my_table，我会得到： id：3;名称：yay insert 。

Let's say I run this code twice and nothing happens but code runs successful without errors. If I switch to the database now and run select staging.test(); and select my_table I get: id: 3; name: yay insert.

所以这意味着当我运行Python文件时，序列实际上正在增加表中是没有数据。

So that means the sequence is actually increasing when I run my Python file but there is no data in my table.

我在做什么错？我想念什么吗？我用Google搜索，但没有找到任何东西。

What am I doing wrong? Am I missing something? I googled but didn't find anything.

推荐答案

在了解自动提交 ：

可以使用 Connection上提供的生成的 Connection.execution_options（）方法来完全控制自动提交行为，引擎，可执行文件，使用 autocommit标志来打开或关闭选定范围的自动提交。例如，表示提交的存储过程的 text（）构造可能会使用它，以便SELECT语句将发出COMMIT：

Full control of the "autocommit" behavior is available using the generative Connection.execution_options() method provided on Connection, Engine, Executable, using the "autocommit" flag which will turn on or off the autocommit for the selected scope. For example, a text() construct representing a stored procedure that commits might use it so that a SELECT statement will issue a COMMIT:

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

SQLAlchemy自动提交检测数据更改操作的方式是，它将语句与模式匹配，查找诸如UPDATE，DELETE和喜欢。它不可能检测到存储的函数/过程是否执行了突变，因此提供了对自动提交的显式控制。

The way SQLAlchemy autocommit detects data changing operations is that it matches the statement against a pattern, looking for things like UPDATE, DELETE, and the like. It is impossible for it to detect if a stored function/procedure performs mutations, and so explicit control over autocommit is provided.

即使失败，该序列也会增加，因为 nextval（）和 setval（）调用永远不会回滚。

The sequence is incremented even on failure because nextval() and setval() calls are never rolled back.

这篇关于调用存储函数或过程不会插入并保留更改的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！