Jesvin Jose
Jesvin Jose

Reputation: 23088

Executing multiple statements with Postgresql via SQLAlchemy does not persist changes

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!

Upvotes: 7

Views: 5291

Answers (3)

LoMaPh
LoMaPh

Reputation: 1690

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

Upvotes: 0

Ilja Everilä
Ilja Everilä

Reputation: 52939

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.

Upvotes: 8

Marcin Kolenda
Marcin Kolenda

Reputation: 94

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

Upvotes: -1

Related Questions