Executing multiple statements with Postgresql via SQLAlchemy does not persist changes

by

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.

More Related Contents:

  1. How to write DataFrame to postgres table
  2. CS50: LIKE operator, variable substitution with % expansion
  3. SQLAlchemy create_all() does not create tables
  4. How to create an SQL View with SQLAlchemy?
  5. Generate sql with subquery as a column in select statement using SQLAlchemy
  6. Updates to JSON field don’t persist to DB
  7. Complex foreign key constraint in SQLAlchemy
  8. Connection problems with SQLAlchemy and multiple processes
  9. How to upsert pandas DataFrame to PostgreSQL table?
  10. SQLAlchemy multiple foreign keys in one mapped class to the same primary key
  11. sqlalchemy.exc.NoSuchModuleError: Can’t load plugin: sqlalchemy.dialects:postgres
  12. SQLAlchemy – performing a bulk upsert (if exists, update, else insert) in postgresql
  13. Strange SQLAlchemy error message: TypeError: ‘dict’ object does not support indexing
  14. Creating partial unique index with sqlalchemy on Postgres
  15. SQLAlchemy func.count on boolean column
  16. How to set connection timeout in SQLAlchemy
  17. SQLAlchemy: filtering on values stored in nested list of the JSONB field
  18. How to prevent UUID primary key for new SQLAlchemy objects being created with the same value
  19. How to give column name dynamically from string variable in sql alchemy filter?
  20. How do I get a raw, compiled SQL query from a SQLAlchemy expression?
  21. django.db.utils.OperationalError Could not connect to server
  22. Python call sql-server stored procedure with table valued parameter
  23. How can I verify Column data types in the SQLAlchemy ORM?
  24. How to upsert pandas DataFrame to Microsoft SQL Server table?
  25. Pyodbc – “Data source name not found, and no default driver specified”
  26. Relations on composite keys using sqlalchemy
  27. sqlalchemy existing database query
  28. Flask-SQLAlchemy Constructor
  29. How does COPY work and why is it so much faster than INSERT?
  30. SQLAlchemy ordering by count on a many to many relationship

Leave a Comment