Manage Database Models with Flask-SQLAlchemyConnect your Flask app to a database using Flask-SQLAlchemy.
Todd BirchardBlockedUnblockFollowFollowingFeb 6By now you’re surely familiar with the benefits of Python’s core SQLAlchemy library: the all-in-one solution for basically anything database related.
Like most major Python libraries, SQLAlchemy has been ported into a version specifically compatible with Flask, aptly named Flask-SQLAlchemy.
Similar to the core SQLAlchemy package, Flask-SQLAlchemy provides an ORM for us to easily modify application data by using defined models.
Regardless of what your database of choice might be, Flask-SQLAlchemy will ensure that the models we create in Python will translate to the syntax of our chosen database.
Given the ease-of-use and one-size-fits-all nature of Flask-SQLAlchemy, it’s no wonder that the library has been the de facto database library of choice for Flask since the very beginning (seriously, is there even another option?)Configuring Flask-SQLAlchemy For Your ApplicationThere are a few essential configuration variables we need to set upfront before interacting with our database.
As is standard, we’ll be using a class defined in config.
py to handle our Flask config:import osclass Config: """Set Flask configuration vars from .
env file.
""" # General TESTING = os.
environ["TESTING"] FLASK_DEBUG = os.
environ["FLASK_DEBUG"]# Database SQLALCHEMY_DATABASE_URI = os.
environ["SQLALCHEMY_DATABASE_URI"] SQLALCHEMY_ECHO = os.
environ["SQLALCHEMY_ECHO"] SQLALCHEMY_TRACK_MODIFICATIONS = os.
environ["SQLALCHEMY_TRACK_MODIFICATIONS"]Let’s break these down:SQLALCHEMY_DATABASE_URI: the connection string we need to connect to our database.
This follows the standard convention: [db_type]+[db_connector]://[username]:[password]@[host]:[port]/[db_name]SQLALCHEMY_ECHO: When set to 'True', Flask-SQLAlchemy will log all database activity to Python's stderr for debugging purposes.
SQLALCHEMY_RECORD_QUERIES: Honestly, I just always set this to 'False,' otherwise, an obnoxious warning appears every time you run your app reminding you that this option takes a lot of system resources.
Those are the big ones we should worry about.
If you’re into some next-level database shit, there are a few other pro-mode configuration variables which you can find here.
By using the exact naming conventions for the variables above, simply having them in our config file will automatically configure our database connections for us.
We will never have to create engines, sessions, or connections.
As always, we’re going to use the Flask Application Factory method for initiating our app.
If you’re unfamiliar with the term, you’re going to find this tutorial to be confusing and pretty much useless.
The most basic __init__.
py file for Flask applications using Flask-SQLAlchemy should look like this:from flask import Flaskfrom flask_sqlalchemy import SQLAlchemydb = SQLAlchemy()def create_app(): """Construct the core application.
""" app = Flask(__name__, instance_relative_config=False) db.
init_app(app) app.
config.
from_object('config.
Config')with app.
app_context(): # Imports from .
import routes # Initialize Global db db.
create_all()return appNote the presence of db and its location: this our database object being set as a global variable outside of create_app().
Inside of create_app(), on the other hand, contains the line db.
init_app(app).
Even though we've set our db object globally, this means nothing until we initialize it after creating our application.
We accomplish this by calling init_app() within create_app(), and passing our app as the parameter.
Within the actual 'application context' is where we'll call create_all(), which we'll cover in a bit.
If that last paragraph sounded like total gibberish to you, you are not alone.
The Flask Application Factory is perhaps one of the most odd and poorly explained concepts in Python software development- my best advice is to not become frustrated, take the copy + paste code above, and blindly accept the spoon-fed nonsense enough times until it becomes second nature.
That’s what I did, and even as I worked through this tutorial, I still came across obnoxious quirks that caught me off-guard.
Take note of import we make inside of the application context called routes.
This is one of two files we haven’t written just yet: once we create them, our application file structure will look something like this:my-app├── /application│ ├── __init__.
py│ ├── routes.
py│ ├── models.
py├── .
env├── config.
py└── wsgi.
pyCreating Database ModelsCreate a models.
py file in our application directory.
Here we'll import the db object that we created in __init__.
py.
Now we can create database models by defining classes in this file.
A common example would be to start with a User model.
The first variable we create is __tablename__, which will correspond to the name of the SQL table new users will be saved.
Each additional variable we create within this model class will correspond a column in the database:from .
import dbclass User(db.
Model): """Model for user accounts.
"""__tablename__ = 'users' id = db.
Column(db.
Integer, primary_key=True ) username = db.
Column(db.
String(64), index=False, unique=True, nullable=False ) email = db.
Column(db.
String(80), index=True, unique=True, nullable=False ) created = db.
Column(db.
DateTime, index=False, unique=False, nullable=False ) bio = db.
Column(db.
Text, index=False, unique=False, nullable=True ) admin = db.
Column(db.
Boolean, index=False, unique=False, nullable=False ) def __repr__(self): return '<User {}>'.
format(self.
username)Each “column” accepts the following attributes:With our first model created, you’re already way closer to interacting with your database than you might think.
Creating Our First EntryLet’s create a user in our routes.
py file.
from flask import request, render_template, make_responsefrom datetime import datetime as dtfrom flask import current_app as appfrom .
models import db, User@app.
route('/', methods=['GET'])def entry(): """Endpoint to create a user.
""" new_user = User(username='myuser', email='myuser@example.
com', created=dt.
now(), bio="In West Philadelphia born and raised, on the playground is where I spent most of my days", admin=False ) db.
session.
add(new_user) db.
session.
commit() return make_response("User created!")Check out how easy this is!.All it takes to create a user is create an instance of the User class from models.
py, add it to our session via db.
session.
add(new_user), and commit the changes with db.
session.
commit()!.Let's see what happens when we run this app:Wait, what the fuck?Remember when I said Flask’s Application Factory is convoluted as hell?.We’ve just unlocked a special achievement: setting config vars for things which don’t exist yet.
Back when we set SQLALCHEMY_ECHO and SQLALCHEMY_TRACK_MODIFICATIONS in config.
py, we were actually making a misleading mistake.
Look back at __init__.
py and you'll notice that our instance of Flask-SQLAlchemy is set before we create our app.
Since db = SQLAlchemy() comes before app = Flask(name), we've confused the shit out of our app.
In essence what is happening here is we've set configuration variables for Flask extension which does not exist yet, thus giving us this cryptic error message of KeyError: True.
Shockingly, very few people on the internet have the explanation for this.
Seriously though: what are you people doing?.Is everybody building their Flask apps incorrectly?.Anyway, we fix this issue in __init__.
py:from flask import Flaskfrom flask_sqlalchemy import SQLAlchemydb = SQLAlchemy()def create_app(): """Construct the core application.
""" app = Flask(__name__, instance_relative_config=False) app.
config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False app.
config["SQLALCHEMY_ECHO"] = True db.
init_app(app) app.
config.
from_object('config.
Config') with app.
app_context(): # Imports from .
import routes # Initialize Global db db.
create_all()return appInstead of setting SQLALCHEMY_ECHO and SQLALCHEMY_TRACK_MODIFICATIONS in config.
py, we now set them after our app is created and after our db object is created.
Be sure to remove both SQLALCHEMY_ECHO and SQLALCHEMY_TRACK_MODIFICATIONS from config.
py.
This will now fix our app to run the following:User Created!Querying Our New DataCreating information is dope, but how can we confirm it exists?.I’ve added a few things to routes.
py to show us what's up:from flask import request, render_templatefrom datetime import datetime as dtfrom flask import current_app as appfrom .
models import db, User@app.
route('/', methods=['GET'])def entry(): """Endpoint to create a user.
""" new_user = User(username='myuser', email='myuser@example.
com', created=dt.
now(), bio="In West Philadelphia born and raised, on the playground is where I spent most of my days", admin=False ) db.
session.
add(new_user) db.
session.
commit() users = User.
query.
all() return render_template('users.
html', users=users, title="Show Users")The statement User.
query.
all() will return all instances of User in our database.
I created a Jinja template to show us all records nicely:{% extends "layout.
html" %}{% block content %} {% for user in users %} <ul id="user.
username"> <li>Username: {{ user.
username }}</li> <li>Email: {{ user.
email }}</li> <li>Created: {{ user.
created }}</li> <li>Bio: {{ user.
bio }}</li> <li>Admin: {{ user.
admin }}</li> </ul> {% endfor %}{% endblock %}Thus our app gives us:I Can’t Believe It’s Not Error Messages.
™Here, Take All My StuffSure, Flask-SQLAlchemy is great once you get going, but as we’ve already seen “getting set up” isn’t always a walk in the park.
This is one of those things that always seems to be wrong no matter how many times you’ve done it from memory.
As a parting gift, I’ve put the source for this tutorial up on Github for you to treasure and enjoy.
No seriously, take it.
Get it away from me.
I’m done with Flask for today.
I need to go play some Rocket League.
PS: Add me on PSNOriginally published at hackersandslackers.
com on February 6, 2019.
.