Integrating SQLAlchemy with ToDo App

Welcome to our course on integrating a SQL database with your Flask application! Previously, we built a ToDo app using in-memory storage. For larger applications or those needing persistent data storage, transitioning to a SQL database is essential. In this lesson, we'll integrate a SQL database into our Flask app using SQLAlchemy, a powerful SQL toolkit and ORM library for Python. We'll utilize models with SQLAlchemy to map our data structure to database tables, making our development process simpler and more secure by converting raw SQL queries into Python code. By the end of this lesson, you will be able to: Install Flask-SQLAlchemy . Configure the database connection. Create a SQLAlchemy database instance. Define the Todo model to map it to a database table. Initialize SQLAlchemy in the Flask application.

What is SQLAlchemy and Why Use It with Flask?

SQLAlchemy is a powerful tool for working with SQL databases in Python. It provides an efficient and flexible way to interact with databases by converting Python classes into database tables (a feature known as Object-Relational Mapping or ORM). This abstraction allows developers to write Python code instead of SQL queries, making it easier to manage database operations. Flask-SQLAlchemy is an extension for Flask that simplifies the integration between Flask and SQLAlchemy. By using this extension, you can leverage Flask's capabilities along with SQLAlchemy's robust database management features, streamlining your development process. Some benefits of using SQLAlchemy with Flask include: Simplified Database Management: Automates many database operations and reduces the amount of boilerplate code. ORM Capabilities: Allows you to work with Python objects instead of writing raw SQL queries. Flexibility: Supports various types of SQL databases, including SQLite, PostgreSQL, MySQL, and others. Consistency: Ensures consistent behavior and performance across different types of databases. Now that we have an understanding of what SQLAlchemy and Flask-SQLAlchemy are, let's move on to installing the necessary libraries.

Installing Flask-SQLAlchemy

First, we need to install the necessary library: Flask-SQLAlchemy . You can install this library using the following pip command in your terminal: Shell pip install Flask-SQLAlchemy Since Flask-SQLAlchemy is an extension for Flask that adds support for SQLAlchemy, installing Flask-SQLAlchemy will also install SQLAlchemy as a dependency.

Join the 1M+ learners on CodeSignal

Be a part of our community of 1M+ users who develop and demonstrate their skills on CodeSignal

Configuring the Database Connection

Next, we'll configure our Flask application to use SQLAlchemy. We'll create a configuration file app/config.py to hold our database settings.

Here’s the code for app/config.py:

Python

import os  # Import the operating system module to interact with the file system

# Determine the absolute path of the directory this script is in
basedir = os.path.abspath(os.path.dirname(__file__))

class Config:
    # Set the URI for the SQLite database; it will be stored in 'data.db' in the basedir directory
    SQLALCHEMY_DATABASE_URI = 'sqlite:///' + os.path.join(basedir, 'data.db')
    
    # Disable modification tracking to save memory (not necessary for most cases)
    SQLALCHEMY_TRACK_MODIFICATIONS = False

import os  # Import the operating system module to interact with the file system

# Determine the absolute path of the directory this script is in
basedir = os.path.abspath(os.path.dirname(__file__))

class Config:
    # Set the URI for the SQLite database; it will be stored in 'data.db' in the basedir directory
    SQLALCHEMY_DATABASE_URI = 'sqlite:///' + os.path.join(basedir, 'data.db')
    
    # Disable modification tracking to save memory (not necessary for most cases)
    SQLALCHEMY_TRACK_MODIFICATIONS = False

Let’s break this down step-by-step:

We first import the os module, which allows us to interact with the operating system.
Using os.path.abspath and os.path.dirname(__file__), we determine the absolute path of the directory where this script is located. This helps us set a base directory path for our project, ensuring that our database file resides in a known location.
We then declare a class named Config. Within this class:
- We set the SQLALCHEMY_DATABASE_URI to specify the type of database we want to use and where it will be stored. In this case, we're using an SQLite database. The string 'sqlite:///' tells SQLAlchemy to use SQLite as the database engine, and os.path.join(basedir, 'data.db') sets the database file's location to data.db in the base directory.
- We disable SQLALCHEMY_TRACK_MODIFICATIONS, a feature of SQLAlchemy that tracks modifications of objects and emits signals. Disabling this feature saves memory and is not necessary for most cases.

In summary, by configuring these settings, we're instructing our Flask application to use an SQLite database named data.db located in the base directory of our project.

Mapping the Todo Model to a Database Table

To start, let's update our Todo model.

Python

from models import db

# Link this class to the database as a table
class Todo(db.Model):
    # Name of the table in the database
    __tablename__ = 'todos'

    # ID Column, integer and primary key
    todo_id = db.Column(db.Integer, primary_key=True)
    
    # Title column, cannot be null and has a maximum length of 100 characters
    title = db.Column(db.String(100), nullable=False)

    # Description column, cannot be null and has a maximum length of 200 characters
    description = db.Column(db.String(200), nullable=False)

    # Constructor to initialize title and description
    def __init__(self, title, description):
        self.title = title
        self.description = description

from models import db

# Link this class to the database as a table
class Todo(db.Model):
    # Name of the table in the database
    __tablename__ = 'todos'

    # ID Column, integer and primary key
    todo_id = db.Column(db.Integer, primary_key=True)
    
    # Title column, cannot be null and has a maximum length of 100 characters
    title = db.Column(db.String(100), nullable=False)

    # Description column, cannot be null and has a maximum length of 200 characters
    description = db.Column(db.String(200), nullable=False)

    # Constructor to initialize title and description
    def __init__(self, title, description):
        self.title = title
        self.description = description

By defining this model, we are essentially mapping this Python class to a table in our database. Let’s break this down step-by-step:

We define a Todo class and make it inherit from db.Model. This tells SQLAlchemy that this class should be linked to the database as a table.
The __tablename__ = 'todos' line sets the name of the table in the database to 'todos'.
We define a todo_id column, which is an integer and serves as the primary key for the table.
We also define a title column and a description column, both of which are strings with a maximum length and cannot be null.
The constructor method __init__ initializes the title and description attributes when creating a new Todo object.

Integrating the Database with the Flask App

Now that we have our database configuration, SQLAlchemy instance, and Todo model, we need to integrate them into our Flask application. Here's how we can do that in app/app.py:

Python

from flask import Flask
from controllers.todo_controller import todo_controller
from models import db
from config import Config

app = Flask(__name__)

# Load the configuration from the Config class
app.config.from_object(Config)  

# Initialize the database
db.init_app(app)

# Create tables if they don't exist
with app.app_context():
    db.create_all()

app.register_blueprint(todo_controller)

if __name__ == '__main__':
    app.run(host='0.0.0.0', port=3000, debug=True)

from flask import Flask
from controllers.todo_controller import todo_controller
from models import db
from config import Config

app = Flask(__name__)

# Load the configuration from the Config class
app.config.from_object(Config)  

# Initialize the database
db.init_app(app)

# Create tables if they don't exist
with app.app_context():
    db.create_all()

app.register_blueprint(todo_controller)

if __name__ == '__main__':
    app.run(host='0.0.0.0', port=3000, debug=True)

In this code, we perform the following steps:

Load Configuration: We load the configuration settings from the Config class using app.config.from_object(Config). This sets up the database URI and any other necessary configurations.
Initialize the Database: We initialize our SQLAlchemy instance (db) with the Flask application using db.init_app(app). This step links the database instance to the app configuration, allowing Flask to interact with the database properly.
Create Tables: We ensure the database tables are created by running db.create_all() within the application context. This command initializes all tables defined in our models when the app context is available.

When you run the application, you may notice that the database file (e.g., data.db) is created, but initially, it will be empty until you insert some Todo items into the database. This is expected behavior as the database file is set up, but no data has been added yet.