"""
enhancements for working with `Flask-SQLAlchemy <https://flask-sqlalchemy.palletsprojects.com/en/2.x/>`_
"""
import collections
from sqlalchemy import event
from fictive.sqlalchemy import FindOrCreateDescriptor
[docs]class FilterByOrCreateMixin(object):
# pylint: disable=too-few-public-methods
"""
Apply this mixin to the SQLAlchemy extension class
"""
[docs] def make_declarative_base(self, model, metadata=None):
"""
attach a `filter_by_or_create` descriptor to the declarative base
"""
base = super().make_declarative_base(model, metadata)
base.find_or_create = FindOrCreateDescriptor(lambda: self.session)
return base
[docs]class OptInTransactionMixin(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 :code:`BEGIN`
a transaction; it is implied that the database connection begins a
new transaction automatically when it is initialized and after
each :code:`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 :code:`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
:code:`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
:code:`isolation_level="AUTOCOMMIT"`. This puts
`SQLAlchemy`/`psycopg2` in PostgreSQL's autocommit mode where
statements are instanty executed without issuing any
:code:`BEGIN`/:code:`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.
"""
TRANSACTION_CONNECTIONS = collections.defaultdict(set)
AUTOCOMMIT_DRIVERS = ['postgresql']
[docs] def create_engine(self, sa_url, engine_opts):
"""
set the engine `isolation_level` to :code:`AUTOCOMMIT` if supported
"""
if sa_url.drivername in self.AUTOCOMMIT_DRIVERS:
engine_opts.setdefault('isolation_level', 'AUTOCOMMIT')
return super().create_engine(sa_url, engine_opts)
[docs] def create_scoped_session(self, options=None):
"""
use `begin` and `commit`/`rollback` to disable `autocommit` mode
"""
options = options or {}
options.setdefault('autocommit', True)
if options['autocommit']:
options.setdefault('autoflush', False)
options.setdefault('expire_on_commit', False)
session = super().create_scoped_session(options)
if options['autocommit']:
# pylint: disable=unused-variable
@event.listens_for(session, 'after_begin')
def receive_after_begin(session, transaction, connection):
# pylint: disable=unused-argument
"""
turns autocommit off when a transaction begins
"""
if session.bind.driver != 'psycopg2':
return
dbapi_connection = connection.connection.connection
if transaction.nested:
assert not dbapi_connection.autocommit
return
assert dbapi_connection.autocommit
dbapi_connection.autocommit = False
self.TRANSACTION_CONNECTIONS[transaction].add(dbapi_connection)
@event.listens_for(session, 'after_transaction_end')
def receive_after_transaction_end(session, transaction):
# pylint: disable=unused-argument
"""
Restores autocommit where this transaction turned it off.
"""
if session.bind.driver != 'psycopg2':
return
connections = self.TRANSACTION_CONNECTIONS.get(transaction, [])
if not connections:
return
for dbapi_connection in connections:
assert not dbapi_connection.autocommit
dbapi_connection.autocommit = True
del self.TRANSACTION_CONNECTIONS[transaction]
return session