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.

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.

First, we need to install the necessary library: Flask-SQLAlchemy.

You can install this library using the following pip command in your terminal:

Bash
Copy to clipboard
1pip 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.

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
Copy to clipboard
1import os  # Import the operating system module to interact with the file system
2
3# Determine the absolute path of the directory this script is in
4basedir = os.path.abspath(os.path.dirname(__file__))
5
6class Config:
7    # Set the URI for the SQLite database; it will be stored in 'data.db' in the basedir directory
8    SQLALCHEMY_DATABASE_URI = 'sqlite:///' + os.path.join(basedir, 'data.db')
9    
10    # Disable modification tracking to save memory (not necessary for most cases)
11    SQLALCHEMY_TRACK_MODIFICATIONS = False

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

1. We first import the os module, which allows us to interact with the operating system.
2. 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.
3. 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.

To interact with the database, we need to create a SQLAlchemy instance within our Flask project. We'll place this instance in the app/models/__init__.py file to ensure it is recognized as a module and can be easily imported elsewhere in our project.

Python
Copy to clipboard
1from flask_sqlalchemy import SQLAlchemy
2
3# Create an instance of SQLAlchemy
4db = SQLAlchemy()  

By creating an __init__.py file in the models folder and defining the db instance there, we make sure that all the modules within the models directory can be treated as a single package. This allows for easier imports and better organization of database models in our application.

Before diving into integrating our Todo model with SQLAlchemy, it's crucial to understand what ORM (Object-Relational Mapping) is and why we use it.

Object-Relational Mapping (ORM) is a technique that allows you to query and manipulate data from a database using an object-oriented paradigm. An ORM tool like SQLAlchemy translates Python classes to database tables and automatically converts function calls to SQL statements. This way, developers can interact with the database in a more natural, Pythonic way without writing raw SQL queries.

With this understanding, let's use SQLAlchemy to map our Todo model to a database table.

To start, let's update our Todo model.

Python
Copy to clipboard
1from models import db
2
3# Link this class to the database as a table
4class Todo(db.Model):
5    # Name of the table in the database
6    __tablename__ = 'todos'
7
8    # ID Column, integer and primary key
9    todo_id = db.Column(db.Integer, primary_key=True)
10    
11    # Title column, cannot be null and has a maximum length of 100 characters
12    title = db.Column(db.String(100), nullable=False)
13
14    # Description column, cannot be null and has a maximum length of 200 characters
15    description = db.Column(db.String(200), nullable=False)
16
17    # Constructor to initialize title and description
18    def __init__(self, title, description):
19        self.title = title
20        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:

1. 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.
2. The __tablename__ = 'todos' line sets the name of the table in the database to 'todos'.
3. We define a todo_id column, which is an integer and serves as the primary key for the table.
4. We also define a title column and a description column, both of which are strings with a maximum length and cannot be null.
5. The constructor method __init__ initializes the title and description attributes when creating a new Todo object.

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
Copy to clipboard
1from flask import Flask
2from controllers.todo_controller import todo_controller
3from models import db
4from config import Config
5
6app = Flask(__name__)
7
8# Load the configuration from the Config class
9app.config.from_object(Config)  
10
11# Initialize the database
12db.init_app(app)
13
14# Create tables if they don't exist
15with app.app_context():
16    db.create_all()
17
18app.register_blueprint(todo_controller)
19
20if __name__ == '__main__':
21    app.run(host='0.0.0.0', port=3000, debug=True)

In this code, we perform the following steps:

1. 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.
2. 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.
3. 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.

As our application grows, it's essential to maintain a clean and organized structure. Below is the structure of our Flask application as it stands now, incorporating all the components we've discussed:

Plain text
Copy to clipboard
1app/
2 ├── app.py
3 ├── config.py
4 ├── controllers/
5 │   └── todo_controller.py
6 ├── models/
7 │   └── __init__.py
8 │   └── todo.py
9 ├── services/
10│   └── todo_service.py
11├── templates/
12│   └── todo_edit.html
13│   └── todo_list.html
14└── data.db

Explanation of the new files:

• config.py: This file holds the configuration settings for our Flask application, including the database URI and other essential settings.
• models/init.py: This file initializes the SQLAlchemy instance (db), making it accessible as a module throughout the project.
• data.db: This is the SQLite database file where all the data for our Todo app will be stored.

Note that the data.db file is initially empty until we add ToDo items to the database. Currently, our service layer still uses in-memory storage. In the next lesson, we will update our service methods to interact with the database, ensuring that our ToDo items are stored persistently.

In this lesson, we:

• Installed Flask-SQLAlchemy.
• Configured the database connection in app/config.py.
• Created a SQLAlchemy database instance in app/models/__init__.py.
• Defined the Todo model with SQLAlchemy.
• Initialized SQLAlchemy in the Flask application and created the necessary tables.

These steps move us significantly closer to having a robust Todo application backed by a SQL database. Now, you should practice these operations in our hands-on exercises. In the next lessons, we will cover more advanced topics to solidify our understanding and enhance our application's functionality. Happy coding!