When using SQLAlchemy with Flask, the standard approach is to use the Flask-SQLAlchemy extension.
However, this extension has some issues. In particular, we have to use a base class for our SQLAlchemy models that creates a dependency on flask (via flask_sqlalchemy.SQLAlchemy.db.Model). Also, an application may not require the additional functionality that the extension provides, such as pagination support.
Let's see if we can find a way to use plain SQLAlchemy in our Flask applications without relying on this extension.
This article focuses specifically on connecting a Flask application to SQLAlchemy directly, without using any plugins or extensions. It doesn't address how to get a Flask application working on its own, or how SQLAlchemy works. It may be a good idea to get these parts working separately first.
Below is the code that sets up the SQLAlchemy session (db.py):
import os
from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session
from sqlalchemy.orm import sessionmaker
engine = create_engine(os.environ['SQLALCHEMY_URL'])
Session = scoped_session(sessionmaker(bind=engine))
The key here is scoped_session: Now when we use Session, SQLAlchemy will check to see if a thread-local session exists. If it already exists, then it will use it, otherwise it will create one first.
The following code bootstraps the Flask application (__init__.py):
from flask import Flask
from .db import Session
from .hello import hello_blueprint
app = Flask(__name__)
app.register_blueprint(hello_blueprint)
@app.teardown_appcontext
def cleanup(resp_or_exc):
Session.remove()
The @app.teardown_appcontext decorator will cause the supplied callback, cleanup, to be executed when the current application context is torn down. This happens after each request. That way we make sure to release the resources used by a session after each request.
In our Flask application, we can now use Session to interact with our database. For example (hello.py):
import json
from flask import Blueprint
from .db import Session
from .models import Message
hello_blueprint = Blueprint('hello', __name__)
@hello_blueprint.route('/messages')
def messages():
values = Session.query(Message).all()
results = [{ 'message': value.message } for value in values]
return (json.dumps(results), 200, { 'content_type': 'application/json' })
This should be sufficient for integrating SQLAlchemy into a Flask application.
For a more detailed overview of the features Flask-SQLAlchemy provides, see Derrick Gilland's article, Demystifying Flask-SQLAlchemy
We also get the benefit of not having to create a dependency on Flask for our SQLAlchemy models. Below we're just using the standard sqlalchemy.ext.declarative.declarative_base (models.py):
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
Base = declarative_base()
class Message(Base):
__tablename__ = 'messages'
id = Column(Integer, primary_key=True)
message = Column(String)
def __repr__(self):
return "<Message(message='%s')>" % (self.message)
I could be wrong, but I would prefer to start a project with this approach initially, and only to incorporate the Flask-SQLAlchemy extension later if it turns out to be demonstrably useful.
This code is available on github: https://github.com/nestedsoftware/flask_sqlalchemy_starter
Top comments (12)
This is very helpful. I come from a database orientation, & I really have no need to have Flask generate my database for me, & then Alembic migrate every time I need to make a change. Strongly prefer using reflected tables in SQLAlchemy, & making database changes w/software that's dedicated to that purpose!
Very helpful article! I usually add
engine.dispose()as well to make sure there's nostaleconnection to the database.Thank you for the kind comment! It's possible I am mistaken, but I believe using
engine.dispose()in this way isn't a good idea. It appears to completely reset the entire connection pool that SQLAlchemy is using behind the scenes.From the documentation:
Thanks for the information! We used to ran into the problem of the connection being killed before (I suspected this is the database killing inactive connections but this should be already handled by the connection pool) and the workaround is to create a new connection every time, even if it adds overhead.
Hi @nestedsoftware .
Why do we need to remove session on teardown. What would happen if we don't release the resources used by a session after each request?
That's a good question @marinkreso95 ! It's important to make sure to release any database/transactional resources that are no longer needed by the current thread. For example, if a connection to the db used by
Sessionis not released back to its connection pool, it won't be available for use by another thread. Any external resources like this should be cleaned up.The documentation says that these resources will be released when the current thread ends:
So, depending on how Flask handles threads, we may not need to do this ourselves. I think it's still better to call
Session.removeexplicitly when the request is done, just to be certain.Thank you very much for quick response, and great article @nestedsoftware
Excellent introduction!
I am seeing an error message when trying to invoke Session.query() that method is not defined.
Hi @grubertm , my first thought is that maybe the sqlalchemy classes are not being imported properly. How did you install sqlalchemy? I did try to provide instructions in the readme file of the linked github project (the article itself only includes the code that needs to be added for sqlalchemy to work with flask - but it assumes everything else has already been done). Did you follow the readme? In that case maybe the readme has a mistake. It could also be an environment thing. I used ubuntu 18.04 with python 3.6 to set this up. If your environment is different, that could also be a possibility.
Great !!
but i wonder How to Achive to sperate data in multi databases like in flask_sqlalchmey
bind_key in sqlalchemy
I don't know how this works in flask-sqlalchemy, but you can create a separate session for each db.
I do use flask for backend api development. Great article. I also had the same concerns and ditched flask-sqlalchemy extension for sqlalchemy stand alone.