Declaring Models in Flask with Flask-SQL Alchemy

This section contains the step-by-step explanation of the code. After doing the necessary imports, we define the create_app function which returns the flask app (the app-factory method of creating flask apps). We create the app and define everything inside this function since this app is small. If it is large, then in-practice, one would generally create multi-file definitions and then add them to the app inside this function by using flask features such as blueprints. Here is the explanation of the code inside this function –

Step 1: Creating flask app

First we create the flask app with the line

app = Flask(__name__, ...)

as usual and do the configurations with app.config… .

Python3

app = Flask(__name__, instance_relative_config=True)
app.config.from_pyfile('config.py', silent=True)
app.config.from_mapping(SECRET_KEY='dev')

Step 2: Creating the instance folder

Instance folder is where our SQL Lite database will be saved. The path to the instance folder is accessed through app.instance_path (Note: the instance path is defined in configurations and can be altered from there if one wants). Then we try to create the instance folder with os.makedirs method. If it already exists, OSError will be raised. We catch the error and do nothing in this case because the folder already exists if error occurs.

Python3

try:
    os.makedirs(app.instance_path)
except OSError:
    pass

Step 3: Configure the database location

Next we need to specify flask where to find the database in configurations. For SQL Lite, we need to tell the path of the database file relative to the path to instance folder preceded by “sqllite:///” string. This is done with the line “app.config[“SQLALCHEMY_DATABASE_URI”] = …” LINE. Remember this file doesn’t need to exist yet.

Python3

app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///event_database.db"

Step 4: Creating the database object (db)

Define the Base class inheriting “DeclarativeBase” (this can be altered if you want some different model other than delclarative base by modifiying this class”). Next we create the database object using SQLAlchemy and passing this Base class to it and store it in “db”. Next, we initiate db with the app using “db.init_app(app)” line.

Python3

class Base(DeclarativeBase):
    pass
 
db = SQLAlchemy(model_class=Base)
db.init_app(app)

Step 5: Declaring the model(s)

Now we begin to define the models. A model defines what data elements/rows of the database contain. A model must inherit the db.Model class where db is our database object as defined in last step. Each of the attribute is defined as a class variable in this class (like date and event here) and is assigned the type of data it contains. We define the date to be the primary key. This is SQL Alchemy syntax and one needs to refer to its documentation to get more details.

Our model here is Event which contains the attributes date and event (containing event description).

Python3

class Event(db.Model):
    date = mapped_column(db.String, primary_key=True)
    event = mapped_column(db.String)

Step 6: Creating the database

Now we need to define the database to store the data for our model(s). This is done by calling the “db.create_all()” method. This creates the necessary tables and the database files to represent all the models defined by inheriting “db.Model” class.

Since we don’t need to call this function always, we create a command named ‘init-db’ using the click library and register it with the flask app using “app.cli.add_command” function and passing it the function. Once registered, we can call this command hence call the “db.create_all()” function only when we want using the command just like the flask run –

flask --app eventLog init-db

Once we call this command, our database file and database is ready to handle data for our model(s).

Python3

@click.command('init-db')
def init_db_command():
    with app.app_context():
        db.create_all()
        click.echo('Database created successfully')
 
app.cli.add_command(init_db_command)

Step 7: Use the SQL Alchemy syntax to query the database

With everything ready here, we can query the database using SQL Alchemy syntax from db.session object. For example, here we define a home page querying the database at two places depending upon the request type. When it receives a GET request, it returns an HTML page containing table of all the events. For this we query the database to receive all the events using –

db.session.execute(db.select(Event).order_by(Event.date)).scalars()

and pass it to the rendering template. This query returns an iterable of Event class (or Event model) objects. So if e is an element in this list of iterables, we can access date using e.date and event description using e.event which are the attributes of our model. This is done in the template.

Python3

@app.route('/', methods=['GET', 'POST'])
    def home():
        if(request.method == 'POST'):
            db.session.add(Event(date=datetime.datetime.now(
            ).__str__(), event=request.form['eventBox']))
            db.session.commit()
            return redirect(url_for('home'))
        return render_template('home.html', eventsList=db.session.execute(db.select(Event).order_by(Event.date)).scalars())
 
    return app

Similarly, homepage can receive a POST request to add an event. In this case we receive the event details from the form and execute a query to add the event with the current date and time and the provided description to the database. For this, we use “db.session.add” and pass it an instance of our Event model/class containing the information. Finally, call “db.session.commit()” to commit the changes i.e., the addition here.