corros corros - 4 months ago 160
Python Question

Flask-SQLAlchemy - how do sessions work with multiple databases?

I'm working on a Flask project and I am using Flask-SQLAlchemy.

I need to work with multiple already existing databases.

I created the "app" object and the SQLAlchemy one:


from flask import Flask
from flask_sqlalchemy import SQLAlchemy


app = Flask(__name__)
db = SQLAlchemy(app)


In the configuration I set the default connection and the additional binds:


SQLALCHEMY_DATABASE_URI = 'postgresql://pg_user:pg_pwd@pg_server/pg_db'
SQLALCHEMY_BINDS = {
'oracle_bind': 'oracle://oracle_user:oracle_pwd@oracle_server/oracle_schema',
'mssql_bind': 'mssql+pyodbc://msssql_user:mssql_pwd@mssql_server/mssql_schema?driver=FreeTDS'
}


Then I created the table models using the declarative system and, where needed, I set the
__bind_key__
parameter to indicate in which database the table is located.

For example:


class MyTable(db.Model):
__bind_key__ = 'mssql_bind'
__tablename__ = 'my_table'

id = db.Column(db.Integer, nullable=False, primary_key=True)
val = db.Column(db.String(50), nullable=False)


in this way everything works correctly, when I do a query it is made on the right database.



Reading the SQLAlchemy documentation and the Flask-SQLALchemy documentation I understand these things
(i write them down to check I understand correctly):



  • You can handle the transactions through the session.

  • In SQLAlchemy you can bind a session with a specific engine.

  • Flask-SQLAlchemy automatically creates the session (scoped_session) at the request start and it destroys it at the request end



so I can do:


record = MyTable(1, 'some text')
db.session.add(record)
db.session.commit()


I can not understand what happens when we use multiple databases, regarding the session, in Flask-SqlAlchemy.

I verified that the system is able to bind the table correctly at the right database through the
__bind_key__
parameter,
I can, therefore, insert data on different databases through
db.session
and, at the commit, everything is saved.



I can't, however, understand if Flask-SQLAlchemy create multiple sessions (one for each engine) or if manages the thing in a different way.

In both cases, how is it possible refer to the session/transaction of a specific database?

If I use
db.session.commit()
the system does the commit on all involved databases, but how can I do if I want to commit only for a single database?

I would do something like:


db.session('mssql_bind').commit()


but I can not figure out how to do this.



I also saw a Flask-SQLAlchemy implementation which should ease the management of these situations:



Issue: https://github.com/mitsuhiko/flask-sqlalchemy/issues/107

Implementation: https://github.com/mitsuhiko/flask-sqlalchemy/pull/249



but I can not figure out how to use it.



In Flask-SQLAlchemy how can I manage sessions specifically for each single engine?

Answer

Flask-SQLAlchemy uses a customized session that handles bind routing according to given __bind_key__ attribute in mapped class. Under the hood it actually adds that key as info to the created table. In other words, Flask does not create multiple sessions, one for each bind, but a single session that routes to correct connectable (engine/connection) according to the bind key. Note that vanilla SQLAlchemy has similar functionality out of the box.

In both cases, how is it possible refer to the session/transaction of a specific database? If I use db.session.commit() the system does the commit on all involved databases, but how can I do if I want to commit only for a single database?

It might not be a good idea to subvert and issue commits to specific databases mid session using the connections owned by the session. The session is a whole and keeps track of state for object instances, flushing changes to databases when needed etc. That means that the transaction handled by the session is not just the database transactions, but the session's own transaction as well. All that should commit and rollback as one.

You could on the other hand create new SQLAlchemy (or Flask-SQLAlchemy) sessions that possibly join the ongoing transaction in one of the binds:

session = db.create_scoped_session(
    options=dict(bind=db.get_engine(app, 'oracle_bind'),
                 binds={}))

This is what the pull request is about. It allows using an existing transactional connection as the bind for a new Flask-SQLAlchemy session. This is very useful for example in testing, as can be seen in the rationale for that pull request. That way you can have a "master" transaction that can for example rollback everything done in testing.

The issue you linked to on the other hand does list ways to issue SQL to specific binds:

rows = db.session.execute(query, params,
                          bind=db.get_engine(app, 'oracle_bind'))

There were other less explicit methods listed as well, but explicit is better than implicit.

Comments