fictive.sqlalchemy.ext.flask package

Subpackages

fictive.sqlalchemy.ext.flask.builtins	import these to create new resources (usually not what you want to do)
fictive.sqlalchemy.ext.flask.shared	import these to use the framework’s shared resources (usually what you want to do)

Module contents

enhancements for working with Flask-SQLAlchemy

class fictive.sqlalchemy.ext.flask.FilterByOrCreateMixin

Bases: object

Apply this mixin to the SQLAlchemy extension class

make_declarative_base(model, metadata=None)

attach a filter_by_or_create descriptor to the declarative base

class fictive.sqlalchemy.ext.flask.OptInTransactionMixin

Bases: object

enable auto-commit with optional transactions

Python database modules (including SQLAlchemy) are encouraged to follow PEP249 (see https://www.python.org/dev/peps/pep-0249/). PEP 249 does not contain an interface to explicitly BEGIN a transaction; it is implied that the database connection begins a new transaction automatically when it is initialized and after each COMMIT. This is fine for many applications, but can be ill suited to HTTP servers. See e.g. http://oddbird.net/2014/06/14/sqlalchemy-postgres-autocommit/

HTTP requests are ‘read only’ with respect to the database. For these requests, where the extra overhead of starting and rolling back a transaction provides no benefit. Furthermore, when making API rquests that do mutate the database, (e.g., a POST or PATCH that performs multiple related CREATE or UPDATE statements), it is often beneficial to have explicit control over when transactions begin and end.

For many HTTP servers, what is desired is:

• default to something like PostgreSQL’s AUTOCOMMIT mode. I.e., statements issued outside of a a transaction are automcatically committed (with no implicit transaction(s) being created)

• have the ability to explicitly begin and end transactions when they are desired

E.g.:

@app.route('/read_object/<object_id>', methods=['GET'])
def read_object(object_id):
    # no transaction
    object = ObjectModel.query.get(object_id)
    if not object:
        abort(404)
    return jsonify(object)

@app.route('/update_object/<object_id>', methods=['PATCH'])
def update_object(object_id):
    # not in a transaction
    object = ObjectModel.query.get(object_id)
    if not object:
        abort(404)
    # first atomic operation
    with db.session.begin():
        sb.session.add(object)
        object.update(request.form)
        object.child.update(request.args)
    # second atomic operation
    with db.session.begin():
        object.changed = datetime.datetime.now()
        child.chaned = datetime.datetime.now()

SQLALchemy and Flask-SQLAlchemy don’t provide an ‘out-of-the-box’ means for this type of control. SQLAlchemy’s ‘autocommit’ mode is more akin to ‘one mutating statement per transaction’. I.e., it implicitly creates a new transaction, and implicitly issues a COMMIT whenever a mutating statement is issued. This would seem to be a “worst of both worlds” solution to the above states concerns (i.e., all of the overhead of unnecessary transactions with none of the atomicity control).

One can achieve the desired pattern with some configuration and customization (assuming a PostgreSQL database):

First, start the psycopg2 engine with isolation_level="AUTOCOMMIT". This puts SQLAlchemy/psycopg2 in PostgreSQL’s autocommit mode where statements are instanty executed without issuing any BEGIN/COMMIT statements.

This turns off all transactions, which is ideal for read-only requests. But mutating requests need to be able to use transactions when atomicity is required. So, second, We add event listeners to Session.begin and Session.end that respectively turn off and turn on autocommit for the session.

AUTOCOMMIT_DRIVERS = ['postgresql']

TRANSACTION_CONNECTIONS = {}

create_engine(sa_url, engine_opts)

set the engine isolation_level to AUTOCOMMIT if supported

create_scoped_session(options=None)

use begin and commit/rollback to disable autocommit mode