python - Calling stored function or procedure won't insert and persist changes

Question

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,
  ...
);

and a stored function:

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

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()')

---

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.

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.

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.

Answer 1

This particular use case is singled out in "Understanding Autocommit":

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))

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.

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