Executing multiple statements with Postgresql via SQLAlchemy does not persist changes

Question

This does not work – the update has no effect:

command = "select content from blog where slug = 'meow'; update account_balance set balance=200 where id=1; select 1 from blog;"
content = db.engine.scalar(command)

Switching the statements performs the update and select successfully:

command = "update account_balance set balance=200 where id=1; select content from blog where slug = 'meow';"
content = db.engine.scalar(command)

Why does the first not work? It works in Pgadmin. I enabled autocommit with Flask-Sqlalchemy.

I am doing a workshop on SQL injection, so please dont rewrite the solution!

@CraigRinger the account_balance row doesn't update, of course — Jesvin Jose
– Jesvin Jose, Commented Jul 27, 2017 at 10:14
@IljaEverilä, I get no error since the db executes them, but the update statement has no effect — Jesvin Jose
– Jesvin Jose, Commented Jul 27, 2017 at 10:17
@IljaEverilä You are right. I forgot to include something to avoid the error. And I use 1.1.12 — Jesvin Jose
– Jesvin Jose, Commented Jul 27, 2017 at 10:25

Ilja Everilä · Accepted Answer · 2017-07-27 10:59:39Z

8

The way SQLAlchemy's autocommit works is that it inspects the issued statements, trying to detect whether or not data is modified:

..., SQLAlchemy implements its own “autocommit” feature which works completely consistently across all backends. This is achieved by detecting statements which represent data-changing operations, i.e. INSERT, UPDATE, DELETE, as well as data definition language (DDL) statements such as CREATE TABLE, ALTER TABLE, and then issuing a COMMIT automatically if no transaction is in progress. The detection is based on the presence of the autocommit=True execution option on the statement. If the statement is a text-only statement and the flag is not set, a regular expression is used to detect INSERT, UPDATE, DELETE, as well as a variety of other commands for a particular backend

Since multiple result sets are not supported at SQLAlchemy level, in your first example the detection simply omits issuing a COMMIT because the first statement is a SELECT, where as in your second example it is an UPDATE. No attempt to detect data modifying statements from multiple statements takes place.

If you look at PGExecutionContext.should_autocommit_text(), you'll see that it does a regex match against AUTOCOMMIT_REGEXP. In other words it matches only at the beginning of the text.

edited Jul 27, 2017 at 10:59

answered Jul 27, 2017 at 10:34

Ilja Everilä

53.4k9 gold badges137 silver badges141 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Jesvin Jose Over a year ago

I appended a commit and now it works. SQLAlchemy must have forgotten to issue it by itself. Is your speculation that SQLA checks the last statement for whether it should issue a commit?

Ilja Everilä Over a year ago

Sorry, I fixed the answer. It is the first statement that matters (like in your second example, where the first statement is the UPDATE). Also added some context as to why.

Craig Ringer Over a year ago

Wow seriously? That's an insane way to implement that. Don't use that feature!

Ilja Everilä Over a year ago

@craig-ringer It works ok for single textual SQL statements, and if you really want both multi-statement SQL strings (which SQLA does not seem to officially support, but leaves for DBAPI level) and autocommit, there's text(...).execution_options(autocommit=True), as documented in "Understanding Autocommit", though I guess that's more commonly used for signalling that a function or SP mutates data and should commit. On the other hand personally I'm not a fan of autocommit in general.

Ilja Everilä Over a year ago

@CraigRinger Absolutely. That's why the docs have the example engine.execute(text("SELECT my_mutating_procedure()").execution_options(autocommit=True)). I actually ran in to similar issues before support for REFRESH MATERIALIZED VIEW was added. Had to manually mark the session as dirty so that the transaction context handler would issue a COMMIT.

|

LoMaPh · Accepted Answer · 2020-12-09 02:29:22Z

0

If you want to create a table using SELECT INTO:

As explained above engine.execute('select * into a from b') doesn't work. Instead you can do as follows:

conn = engine.raw_connection()
cursor = conn.cursor()

cursor.execute('select * into a from b')
conn.commit()

answered Dec 9, 2020 at 2:29

LoMaPh

1,7203 gold badges24 silver badges37 bronze badges

Comments

Marcin Kolenda · Accepted Answer · 2017-07-27 10:11:14Z

-1

You should use db.engine.execute(...).first() if you want to execute everything and get only first row.

answered Jul 27, 2017 at 10:11

Marcin Kolenda

942 bronze badges

Collectives™ on Stack Overflow

Executing multiple statements with Postgresql via SQLAlchemy does not persist changes

3 Answers 3

9 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

9 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related