Executing Multiple Statements with Postgresql via SQLalchemy Does Not Persist Changes

Executing multiple statements with Postgresql via SQLAlchemy does not persist changes

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.

Enable executing multiple statements while execution via sqlalchemy

multi=True is a requirement for MySql connector. You can not set this flag passing it to SQLAlchemy methods. Do this:

conn  = session.connection().connection
cursor = conn.cursor()  # get mysql db-api cursor
cursor.execute(sql, multi=True)

More info here: http://www.mail-archive.com/sqlalchemy@googlegroups.com/msg30129.html

SQL vs sqlalchemy.execute

I have resolved the problem by turning on autocommit. I am not sure why this convoluted thing works, but it does the trick.

Fixing the unexpected behaviour

import sqlalchemy
import text from sqlalchemy

# Creating text query with autocommit on
my_query = text("IF NOT EXISTS (select * from information_schema.tables where table_name='my_table') CREATE TABLE my_table (col1 float(53), col2 varchar(100), col3 varchar(10))").execution_options(autocommit=True)

# Executing query
db_engine.engine.execute(my_query)

sqlalchemy not executing Comment on schema

Not entirely sure why, but it appears to work once you create a connection and run COMMIT:

In [8]: conn = engine.connect()                                                                                                                                                                              

In [9]: conn.execute("COMMENT ON SCHEMA myschema IS 'Seemly Random Description'")                                                                                                                            
Out[9]: <sqlalchemy.engine.result.ResultProxy at 0x7fd109991cd0>

In [10]: conn.execute("COMMIT;")                                                                                                                                                                             
Out[10]: <sqlalchemy.engine.result.ResultProxy at 0x7fd0ff3b7d10>

postgres=# \dn+
                            List of schemas
   Name   |  Owner   |  Access privileges   |        Description        
----------+----------+----------------------+---------------------------
 myschema | postgres |                      | Seemly Random Description
 public   | postgres | postgres=UC/postgres+| standard public schema
          |          | =UC/postgres         |

Using python sqlalchemy to execute raw queries with WITH statement

My question got answered on github.

The solution is to wrap the execute in a transaction context:

with engine.begin() as conn:
   conn.execute("whatever")

---

Related Topics