Flask and SQLAlchemy

7 minute read

Flask and SQLAlchemy

In this post, we will build a very simple application using flask, sqlalchemy and postgres. The app will display a simple greeting by retrieving a value that is stored inside persons table. We will be using SQLAlchemy-ORM to define a model - which will create the persons table for us, and then, insert a row manually into the table, and finally retrieve the value from the table to display it.

Next, we will also learn how to run in an interactive mode which is very useful for debugging. Finally, I will provide an overview of querying the model object.

To get started, from your conda environment, install flask and flask-sqlalchemy.

  • pip3 install flask
  • pip3 install flask-sqlalchemy

Flask-SQLAlchemy is an extension for Flask that adds support for SQLAlchemy to your application. It aims to simplify using SQLAlchemy with Flask by providing useful defaults and extra helpers that make it easier to accomplish common tasks.

Flask-SQLAlchemy Docs

Basic structure of a flask app

  1. app = Flask(__name__) sets the name of your app to the name of your module (“app” if “app.py” is the name of your file).
  2. Using @app.route: In this case, @app.route is a Python decorator. Decorators take functions and returns another function, usually extending the input function with additional (“decorated”) functionality. @app.route is a decorator that takes an input function index() as the callback that gets invoked when a request to route / comes in from a client.
  3. Running a flask app: FLASK_APP=app.py FLASK_DEBUG=true flask run

Alternative approach to run a Flask app using __main__:

Instead of using flask run, we could have also defined a method

if __name__ == '__main__':
    app.run()

Connecting to the database

Start postgres locally, and get the string postgresql://shravan:shravan@localhost:5432/interview

(base) shravan-postgres# /usr/local/opt/postgresql/bin/pg_ctl -D /usr/local/var/postgres -l logfile start
waiting for server to start.... done
server started

Configure your app

from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql://shravan:shravan@localhost:5432/interview'
db = SQLAlchemy(app)

Use the db.Model - which lets you create and manipulate models. Or use the db.session to create and manipulate transactions.

Models which are defined as classes are mapped to tables within our database.

Given an instance of the SQLAlchemy class from Flask-SQLAlchemy db = SQLAlchemy(app):

  • db is an interface for interacting with our database
  • db.Model lets us create and manipulate data models
  • db.session lets us create and manipulate database transactions in the context of a session.

Code for app.py:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql://shravan:shravan@localhost:5432/interview'
db = SQLAlchemy(app)

class Person(db.Model):
    __tablename__ = 'persons'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(), nullable=False)

@app.route('/')
def index():
    return "Hello World"

if __name__ == '__main__':
    app.run()

The next thing we need to do is to actually create the tables in our database for all the models that we have declared using db.Model. In order to do this, we need to introduce db.create_all()

db.create_all detects models for us and creates tables for them, if those tables do not exist. If the tables do exist, then db.create_all doesn’t do anything for us.

This is important to know, since we will be calling db.create_all() multiple times within our Flask application.

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql://shravan:shravan@localhost:5432/interview'
db = SQLAlchemy(app)

class Person(db.Model):
    __tablename__ = 'persons'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(), nullable=False)

db.create_all()

@app.route('/')
def index():
    return "Hello World"

if __name__ == '__main__':
    app.run()

From here, we can run our Flask application and expect to see that the model has been declared and also the tables for it has been created for.

interviews=# \dt
           List of relations
 Schema |    Name     | Type  |  Owner
--------+-------------+-------+---------
 public | persons     | table | shravan
(1 rows)

interviews=# select * from persons;
 id | name
----+------
(0 rows)

interviews=#

interviews=# \d persons
                                 Table "public.persons"
 Column |       Type        | Collation | Nullable |               Default
--------+-------------------+-----------+----------+-------------------------------------
 id     | integer           |           | not null | nextval('persons_id_seq'::regclass)
 name   | character varying |           | not null |
Indexes:
    "persons_pkey" PRIMARY KEY, btree (id)

interviews=#


Insert a row

interviews=# INSERT INTO persons (name) values ('Shravan');
INSERT 0 1
interviews=# SELECT * FROM persons;
 id |  name
----+---------
  1 | Shravan
(1 row)

interviews=#

Retrieve the value

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql://shravan:shravan@localhost:5432/interviews'
# to avoid warning
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db = SQLAlchemy(app)

class Person(db.Model):
    __tablename__ = 'persons'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(), nullable=False)

db.create_all()

@app.route('/')
def index():
    person = Person.query.first()
    return "Hello " + person.name

if __name__ == '__main__':
    app.run()

By default, SQLAlchemy will create a table based on the class name, by making it lowercase. You can specify a different name using the __tablename__ attribute.

Running SQLAlchemy interactively for debugging

We can experiment with our app using the interactive mode of the Python interpreter.

(fullstack) shravan-flaskapps# python
Python 3.8.5 (default, Aug  5 2020, 03:39:04)
[Clang 10.0.0 ] :: Anaconda, Inc. on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> from app import Person
>>> Person.query.first()
<Person 1>
>>> Person.query.all()
[<Person 1>]
>>> query = Person.query.filter(Person.name == 'Shravan')
>>> query.first()
<Person 1>
>>> query.all()
[<Person 1>]
>>>

Adding a __repr__ to the model will print the values

class Person(db.Model):
    __tablename__ = 'persons'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(), nullable=False)

    def __repr__(self):
        return f'<Person ID: {self.id}, name: {self.name}>'

Now when you run the app interactively, you can see the values inside those objects.

(fullstack) shravan-flaskapps# python
Python 3.8.5 (default, Aug  5 2020, 03:39:04)
[Clang 10.0.0 ] :: Anaconda, Inc. on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> import app
>>> from app import Person
>>> Person.query.first()
<Person ID: 1, name: Shravan>
>>> Person.query.all()
[<Person ID: 1, name: Shravan>]

Inserting rows using db.session.add()

There’s another way of inserting records into our database, rather than entering a client like psql and using INSERT INTO SQL commands: we can call db.session from SQLAlchemy to create records using instances of our defined SQLAlchemy models.

In interactive mode, import db and your Person model. Then, create an instance of a Person, setting its attributes, and setting it equal to a variable person. We’re going to call db.session.add(), a method on the Session interface in SQLAlchemy, to add this object to a session, this will queue up a INSERT INTO persons (name) VALUES ('Veyd'); statement in a transaction that is managed by db.session.

We can then call db.session.commit()

(fullstack) shravan-flaskapps# python
Python 3.8.5 (default, Aug  5 2020, 03:39:04)
[Clang 10.0.0 ] :: Anaconda, Inc. on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> from app import db, Person
>>> person = Person(name='Veyd')
>>> db.session.add(person)
>>> db.session.commit()
>>>

and that person record will now exist in our persons table, within our database! You can double-check this in psql by running a SELECT * from persons; command from psql.

interviews=# SELECT * FROM persons;
 id |  name
----+---------
  1 | Shravan
  2 | Veyd
(2 rows)

So, in summary, we can insert new records into the database using SQLAlchemy by running:

person = Person(name='Veyd')
db.session.add(person)
db.session.commit()

which will build a transaction for inserting in a person instance in our model/table, and persist it to the database upon calling commit().

Inserting multiple rows using db.session.add_all()

(fullstack) shravan-flaskapps# python
Python 3.8.5 (default, Aug  5 2020, 03:39:04)
[Clang 10.0.0 ] :: Anaconda, Inc. on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> from app import db, Person
>>> Person.query.all()
[<Person ID: 1, name: Shravan>, <Person ID: 2, name: Veyd>]
>>> person1 = Person(name='Neil')
>>> person2 = Person(name='Pragna')
>>> db.session.add_all([person1, person2])
>>> db.session.commit()
>>> Person.query.all()
[<Person ID: 1, name: Shravan>, <Person ID: 2, name: Veyd>, <Person ID: 3, name: Neil>, <Person ID: 4, name: Pragna>]
>>>

Model.query is the SELECT of SQL.

db.Model.query offers us the Query object. The Query object lets us generate SELECT statements that let us query and return slices of data from our database.

Query has method chaining. You can chain one query method to another (indefinitely), getting back more query objects, until you chain it with a terminal method that returns a non-query object like count(), all(), first(), delete(), etc.

The Query object can be accessed on a model using either:

  • MyModel.query directly on the model, or
  • db.session.query(Model) using db.session.query instead.

The most relevant and commonly used methods on the query:

The only thing here that needs a little explaining is the difference between filter_by and filter.

  • MyModel.query.filter_by(my_table_attribute='some value')
  • MyModel.query.filter(MyOtherModel.some_attr='some value')

filter is similar to filter_by, but instead, you specify attributes on a given Model. It is more flexible than using filter_by itself, and is especially useful when querying from a joined table where you want to filter by attributes that span across multiple models.

Here’s quick reference that is useful SQLAlchemy Cheat Sheet