python - Executing multiple statements with Postgresql via SQLAlchemy does not persist changes

4
votes

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!

3
pythonpostgresqlsqlalchemy
Define "doesn't work"? Exactly. - Craig Ringer
@CraigRinger the account_balance row doesn't update, of course - Jesvin Jose
@IljaEverilä, I get no error since the db executes them, but the update statement has no effect - Jesvin Jose
@IljaEverilä You are right. I forgot to include something to avoid the error. And I use 1.1.12 - Jesvin Jose

3 Answers

6
votes

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.

0
votes

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

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