Quick tutorial on how to use Flask SQLAlchemy

from flask_sqlalchemy import SQLAlchemy
Having a Flask app object, create a db object. Also, it is better to separate db object into separate python module, like so: database.py

from flask_sqlalchemy import SQLAlchemy
db = SQLAlchemy

Tie Flask app to this db:

# app is our Flask app object
from database import db
db.init_app(app)

Now define classes-models, that represent each object, like so:

class MyTable(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    # ...etc

We can add hybrid entries, that are calculated based on other fields:

from sqlalchemy.ext.hybrid import hybrid_property

# and in model definition of the class, add this
    @hybrid_property
    def timeago(self):
        return timeago.format(self.date_created, datetime.datetime.utcnow(), 'ru')

Relationship one to many can be defined as the following:

class Master(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    slaves = db.relationship('Slave', lazy='dynamic', backref='slave')

class Slave(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    masterId = db.Column(db.Integer, db.ForeignKey(Master.id))
    master = db.relationship('Master', foreign_keys='Slave.masterId')

Possible types of the fields: db.DateTime, db.String, db.Integer, db.Numeric(precision=?), and others
The most common actions on the data are: Adding an element: db.session.add(<object>) db.session.commit()

Getting an element or elements based on some criteria: <ModelClass>.query.filter(<ModelClass>.field == 'someval').first() <ModelClass>.query.all()

Some extra attributes an element can have: default= primary_key=True nullable=False
Also we can specify some extra stuff for the model class: __tablename__ = 'mymodel' def __repr__(self): return "Some string"
It is nice to have set up db migration - a feature that allows adding/removing columns to tables on the fly. Can be setup any time in the development/production process. To do that, just add the following lines:

from flask_migration import Migrate
migrate = Migrate(app, db)

Now, these new command line commands will appear:

flask db init # must be used once to create the "migrations" directory
flask db migrate -m "Initial" # also used once, and then used with every db schema change to alter the migration script in migrations dir
flask db upgrade # used to apply the migrations script to the database file

NOTE: sqlite doesn't allow by default modfying columns, so to init.py file we need to add "render_as_batch" as per https://stackoverflow.com/questions/30394222/why-flask-migrate-cannot-upgrade-when-drop-column. So, somewhere in our app.py, when initializing Migrate object, add this: if app.config['SQLALCHEMY_DATABASE_URI'].startswith("sqlite:///"): migrate = Migrate(app=app, db=db, render_as_batch=True) else: migrate = Migrate(app, db)

Table that references itself

class CommentPost(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    parentId = db.Column(db.Integer, db.ForeignKey(id, ondelete='cascade'), nullable=True)
    commentFor = db.Column(db.Text) # which url or material this comment belongs to?
    commentText = db.Column(db.Text) # HTML text of the comment? Should be filtered for safety
    author_name = db.Column(db.Text)
    author_email = db.Column(db.Text, nullable=True)

    children = db.relationship('CommentPost', backref=db.backref('parent', remote_side=id), cascade="all,delete", lazy='select')

and render it in Jinja2 like so:

<ul>
{% for comment in comments recursive %}
    <li>{{ comment.commentText }}</li>
    {% if (comment.children | count) > 0 %}
        <ul>{{ loop(comment.children) }}</ul>
    {% endif %}
{% endfor %}
</ul>

← Back to Articles