Below I walk through the difference between Flask app set-up with 1) SQLAlchemy ORM and 2) Flask_SQLAlchemy ORM. While the names can throw you off, they are indeed different. Flask_SQLAlchemy is a layer that sits on top of SQLAlchemy, allowing use of both the SQL and ORM tools. That said, Flask_SQLAlchemy’s set-up involves a bit more “magic,” which can make it confusing. First, the SQLAlchemy ORM Flask app set-up:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | from flask import Flask from sqlalchemy import create_engine from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import sessionmaker app = Flask(__name__) engine = create_engine('sqlite:///foo.db') # create a Declarative base class which stores the classes representing tables Base = declarative_base() Base.metadata.reflect(bind=engine) # create a configured "Session" class Session = sessionmaker(bind=engine) # create a Session session = Session() # create all tables that don't yet exist Base.metadata.create_all(engine) |
Flask_SQLAlchemy’s set-up creates near identical results, but with less code and more “magic”:
1 2 3 4 5 6 7 8 9 10 11 12 | from flask import Flask from flask_sqlalchemy import SQLAlchemy app = Flask(__name__) # Magic that creates everything, including Base (db.Model), engine (db.engine), and session (db.session) app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///foo.db' db = SQLAlchemy(app) # create all tables from app import models models.db.create_all() |
Notice in the comments above a couple important details that aren’t easy to find in documentation:
- Base in example 1 is equivalent to db.Model in example 2
- engine in example 1 is equivalent to db.engine in example 2
- session in example 1 is equivalent to db.session in example 2
All in, which you choose doesn’t make much difference for smaller/simpler apps, but consistent set-up will save a lot of headache. For further background reading on what all of the above things actually are/are doing, as well as more info on set-up, I recommend:
- SQLAlchemy ORM Tutorial: http://docs.sqlalchemy.org/en/latest/orm/tutorial.html
- Working with Engines and Connections – Especially helpful for someone coming from raw SQL
- Engine Configuration – Nice visualization of the general structure and specifics for different databases
- Session Basics – This is what you’ll mostly interact with the engine through.
- Beyond the Quickstart, the Flask_SQLAlchemy docs aren’t super helpful. Though the page on Binds is important, because the term means something different than in SQLAlchemy.
Please leave thoughts or questions in the comments below!
7 comments
Thank you, in a few words you explain very clearly the relevant difference.
Thank you for the explanation, how do I add Isolation_level in flask-sqlalchemy?
Thank you very much! Just getting started with flask_sqlalchemy and was a bit confused seeing the different ways things were done in different tutorials.
I didn’t know that both SQLAlchemy and Flask-SQLAlchemy are different Until I visited this site.
Thanks for clarification.
Fantastic explanation. Thank you!
Thank u This is Really Helpful !!!
I’m just looking into flask and sqlalchemy and I seem to be missing something here, I appreciate this is not a full example, but a little help with the below would be appreciated.
In the above Flask-SQLAlchemy ORM Example you have…
from app import models
models.db.create_all()
Which seems to mean that you have 2 instances of db, is that right and if so is there anything special with how db is setup in models or is it the same as setup __init__.py?
Thanks