调用存储的函数或过程不会插入和保留更改 [英] 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 代码时，我的数据库中没有插入任何内容.这很奇怪，因为当我用 select 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.

假设我运行此代码两次，但没有任何反应，但代码运行成功且没有错误.如果我现在切换到数据库并运行 select staging.test(); 并选择 my_table 我得到: id: 3;名称:是插入.

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.

我做错了什么?我错过了什么吗?我用谷歌搜索但没有找到任何东西.

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

推荐答案

这个特殊用例在 "理解自动提交":

使用 Connection、Engine、Connection.execution_options() 提供的生成式 Connection.execution_options() 方法可以完全控制自动提交"行为code>Executable，使用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屋！