Jonnie Grieve Digital Media: Blog

Blog: Integrating SQLAlchemy with Flask – #3 (More Posts)

In a new series of blogs related to SQLAlchemy, I’m moving away from the command line environment and moving on to exploring how Python, Flask, and SQLAlchemy can work together to make a website driven by external data.

Recap

When I left it in my last blog, there was an app that had a local development server with 4 interconnected routes that formed a small website.  This website contained a form, the idea of which would be to add the ability to add data to a database via a route with form elements.  By the end of this blog, I’ll have set up the ability to add that data. This has been a lot of fun to do and I hope you’ll learn a lot from it too. So let’s get started.

Setup

To get started we need to use the PIP package manager again in order to use Flask with SQLAlchemy.  So in the project root in the command line, type the following command.

pip install flask-sqlalchemy

And verify that it has in fact been installed with the following command.

pip freeze -> requirements.txt

I’m using Flask-SQLAlchemy==2.5.1 at the time of writing,  and in that file, if you see something like that listed in the file, you should be set to go.

It’s common practice in projects like this to use multiple files to run in component parts each with its own particular task, such as a file for the database model.  And that’s what we’re going to do here by moving some parts of the app.py file into a file called models.py.

# Declare imports for the app
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
import datetime

# Connect to SQLAlchemy
app = Flask(__name__)

app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///roster.db'
db = SQLAlchemy(app)

if __name__ == '__main__':
    
    db.create_all()
    app.run(debug=True, port=8000, host='127.0.0.1')

# Create Database Model - 3 fields + ID as primary key
class Roster(db.Model):
    id = db.Column(db.Integer(), primary_key=True)
    name = db.Column("Name", db.String(80), unique=True, nullable=False)
    age = db.Column("Age", db.Integer, unique=True, nullable=False)
    joined = db.Column('Joined', db.DateTime, default=datetime.datetime.now)

    def __repr__(self):
        return f'''<Roster %r> ({self.name} 
        Age: {self.age}
        Joined: {self.joined }
       '''

from flask import (render_template, url_for, request, redirect)

# import Database schema from models.py
from models import db, Roster, app

Models.py contains the Roster() class as defined in the model.

app.route('/')
def index():
    roster = Roster.query.all()
    return render_template("index.html", roster=roster)

Now, in the index() method, we can run a query on the data we’ve just defined. This is just a simple one that returns all the data in the table.

But we also need a way to add data to the database. We need a method that sees when a form has been submitted and is ready to act when that happens.  And that’s what request.form does in your code. It’s the method used to capture form input.

Before, all the main_form() function did was to render the form template.  Now what’s happening is that a new variable (new_data) is being defined that stores an instance of the Roster class from models.py. It takes variable arguments that match those defined in the schema to which request.form methods are assigned. The string passed in should be the same as the name attribute of the form inputs. Those form inputs are located in the form route (‘/form’).

The next lines in the method are quite straightforward. We are adding and committing the data to SQLAlchemy and then redirecting the form to a new route. I’ve specified the home route. We really need a function that adds flash messages on a successful form entry, but you could also redirect to a route that contains such a message.

@app.route('/form', methods=["GET", "POST"])
def main_form():

    if request.form:
        # print(request.form)
        # print(request.form['name'])

        # add new record
        new_data = Roster(
            name=request.form['name'],
            age=request.form['age']
        )
            # joined=request.form['joined'])

        db.session.add(new_data)
        db.session.commit()
    return redirect(url_for('index'))

return render_template("form.html")

@app.route('/')
def index():


roster = Roster.query.all()
return render_template("index.html", roster=roster)

{% for working_variablein iterable %}

{% endfor %}

Using the {% for %} code block we can iterate the results of an iterable such as a Class or a List. And what we have in our schema is a Class and a variable that we can use to iterate through called “roster”.  We’re going to use this to replace the multiple table row <tr> elements in the table with just one table row that is wrapped in a Flask looping content block.

e.g.

<table>
    <tr>
        <th>Name:</th>
        <th>Age:</th>
        <th>Date:</th>
    </tr>
    {% for people in roster %}
    <tr>
        <td>{{ people.name }}</td>
        <td>{{ people.age }}</td>
        <td>{{ people.joined }}</td>
    </tr>
    {% endfor %}
</table>

{{ people.name }}
{{ people.age }}
{{ people.joined }}

e.g.models.py

# Create Database Model - 3 fields + ID as primary key
class Roster(db.Model):
    id = db.Column(db.Integer(), primary_key=True)
    name = db.Column("Name", db.String(80), unique=True, nullable=False)
    age = db.Column("Age", db.Integer, unique=True, nullable=False)
    joined = db.Column('Joined', db.DateTime, default=datetime.datetime.now)

    def __repr__(self):
        return f'''<Roster %r> ({self.name} 
        Age: {self.age}
        Joined: {self.joined }
       '''

Flask: Integrating SQLAlchemy Data into Flask