Connecting Flask Applications to Databases

Course: Python Training

Topic: Connecting Flask Applications to Databases

Course Link: Python Training Course

Demo Link: Register for a Course Demo

Website: JustAcademy

1. Introduction to Database Connectivity in Flask

Flask is a lightweight Python web framework used to develop websites, REST APIs, dashboards, and web applications. Many applications need to store information permanently, such as user accounts, product details, course records, orders, and registration information. Databases provide a structured way to store, retrieve, update, and delete this information.

Connecting a Flask application to a database allows the application to exchange information with a database management system. When a user submits a registration form, for example, Flask can validate the submitted information and save it in a database. When the user later opens a profile page, the application can retrieve the stored record and display it.

Flask does not require developers to use one particular database. It can work with SQLite, MySQL, PostgreSQL, and other database systems through Python database drivers or libraries such as SQLAlchemy and Flask-SQLAlchemy.

Why Is Database Connectivity Important?

• It allows applications to store information permanently.
• It makes it possible to retrieve records whenever users need them.
• It supports user registration, login, and profile management.
• It enables applications to display dynamic content from stored data.
• It helps manage large collections of records efficiently.
• It supports data relationships, reporting, and application workflows.
• It allows multiple users to work with shared application data.

2. Learning Objectives

After completing this topic, learners should be able to:

• Understand the role of databases in Flask applications.
• Identify common database options for Python web development.
• Connect Flask to SQLite using Python's built-in sqlite3 module.
• Configure Flask-SQLAlchemy for database operations.
• Create database tables and define models.
• Perform CRUD operations: Create, Read, Update, and Delete.
• Display database records using Jinja templates.
• Handle database connections, transactions, and exceptions.
• Use parameterized queries to reduce SQL injection risks.
• Apply database security and maintenance best practices.

3. Understanding Databases

3.1 What Is a Database?

A database is an organized collection of information that can be stored, accessed, modified, and managed using software. A database management system (DBMS) provides the tools needed to create databases, define structures, query information, and manage access.

For example, an online training platform might store the following information:

3.2 What Is a Relational Database?

A relational database stores data in tables consisting of rows and columns. Relationships between tables can be established through keys.

For example, a student table may store each student's information, while an enrollment table records the courses selected by each student. A student ID can connect the records in the two tables.

3.3 Common Database Systems

The database choice depends on the application's requirements, expected traffic, data structure, deployment environment, and maintenance needs.

4. Understanding the Flask–Database Connection

A Flask application generally communicates with a database through a database driver, a database toolkit, or an ORM (Object-Relational Mapper).

The overall workflow is:

1. A user requests a page or submits a form.
2. Flask routes the request to the appropriate view function.
3. The view validates input and executes the required database operation.
4. The database processes the query or transaction.
5. The database returns a result or operation status.
6. Flask passes the result to a template or returns a suitable API response.

For example, a course listing page may query the database for all available courses. Flask receives the records and passes them to a Jinja template, which renders the course information as HTML.

5. Choosing a Database Access Method

There are several approaches to database connectivity in Flask.

5.1 Direct Database Driver

A database driver allows Python code to communicate directly with a particular database system. Python's sqlite3 module is a built-in option for SQLite databases.

This approach is useful for learning SQL, building small applications, and understanding connections, cursors, and transactions.

5.2 SQLAlchemy

SQLAlchemy is a Python SQL toolkit and ORM. It allows developers to execute SQL statements and work with database records through Python objects and expressions.

5.3 Flask-SQLAlchemy

Flask-SQLAlchemy integrates SQLAlchemy with Flask and simplifies application configuration, database sessions, model definitions, and common database operations.

6. Setting Up the Flask Development Environment

Before connecting a Flask application to a database, create a project directory and install the required packages.

Step 1: Create a Project Folder

mkdir flask_database_project
cd flask_database_project

Step 2: Create a Virtual Environment

python -m venv venv

Activate the virtual environment on Windows:

venv\Scripts\activate

On macOS or Linux, use:

source venv/bin/activate

Step 3: Install Flask and Flask-SQLAlchemy

python -m pip install Flask Flask-SQLAlchemy

The sqlite3 module is included with standard Python installations, so a separate pip installation is normally unnecessary when using SQLite.

Step 4: Save Dependencies

python -m pip freeze > requirements.txt

This creates a list of installed packages for use when reproducing the development environment. For production projects, teams often maintain deliberately pinned and tested dependency versions rather than recording every package installed in an environment.

7. Connecting Flask to SQLite Using sqlite3

SQLite is a useful starting point because it stores data in a database file and does not require a separate database server. Python includes the sqlite3 module for connecting to SQLite databases and executing SQL statements.

7.1 Basic SQLite Connection

import sqlite3

connection = sqlite3.connect("school.db")

print("Database connection created")

connection.close()

Explanation:

• sqlite3.connect() opens a connection to the specified database file.
• If the file does not exist, SQLite can create it.
• The connection is used to execute SQL statements and manage transactions.
• connection.close() releases the connection when it is no longer needed.

7.2 Creating a Database Table

import sqlite3

connection = sqlite3.connect("school.db")

connection.execute("""
    CREATE TABLE IF NOT EXISTS students (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        email TEXT NOT NULL UNIQUE,
        course TEXT NOT NULL
    )
""")

connection.commit()
connection.close()

print("Students table created")

This code creates a students table if it does not already exist. The table includes an automatically generated ID, a required name, a unique email address, and a course field.

8. Creating a Flask Application with SQLite

A basic Flask application can connect to SQLite inside its view functions. For a small learning example, the following code demonstrates database initialization and reading records.

from flask import Flask, render_template
import sqlite3

app = Flask(__name__)
DATABASE = "school.db"

def get_db_connection():
    connection = sqlite3.connect(DATABASE)
    connection.row_factory = sqlite3.Row
    return connection

def initialize_database():
    connection = get_db_connection()

    connection.execute("""
        CREATE TABLE IF NOT EXISTS students (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            email TEXT NOT NULL UNIQUE,
            course TEXT NOT NULL
        )
    """)

    connection.commit()
    connection.close()

@app.route("/")
def home():
    return "Welcome to the Student Management Application"

@app.route("/students")
def students():
    connection = get_db_connection()
    student_records = connection.execute(
        "SELECT id, name, email, course FROM students ORDER BY id"
    ).fetchall()
    connection.close()

    return render_template(
        "students.html",
        students=student_records
    )

if __name__ == "__main__":
    initialize_database()
    app.run(debug=True)

This example creates the table when the application starts and retrieves student records when the user opens /students.

The sqlite3.Row setting allows column values to be accessed by column name, such as student["name"], in addition to numeric indexes.

Important: This compact example is suitable for learning. Larger applications should use structured initialization, robust connection cleanup, configuration management, and appropriate database lifecycle handling. Debug mode must not be enabled in production.

9. Managing Database Connections in Flask

Database connections consume resources. Applications should reuse connections appropriately within a request and ensure that connections are closed when they are no longer required.

9.1 Why Connection Management Matters

• It helps avoid unnecessary database resource consumption.
• It reduces the risk of leaving connections open.
• It makes request handling more predictable.
• It supports cleaner error handling.
• It helps prevent resource exhaustion under heavier workloads.

9.2 Request-Scoped SQLite Connection Pattern

Flask provides the g object for storing data associated with the current application context. A connection can be stored there and reused during a request.

import sqlite3
from flask import Flask, g

app = Flask(__name__)
app.config["DATABASE"] = "school.db"

def get_db():
    if "db" not in g:
        g.db = sqlite3.connect(
            app.config["DATABASE"]
        )
        g.db.row_factory = sqlite3.Row

    return g.db

@app.teardown_appcontext
def close_db(exception=None):
    db = g.pop("db", None)

    if db is not None:
        db.close()

The get_db() function creates a connection only when needed and reuses it during the same application context. The teardown handler closes the connection during context cleanup.

For larger projects, using Flask's application factory pattern and the current_app proxy can make this pattern easier to reuse across modules. Flask's official database tutorial explains this request-scoped connection approach. Read the Flask database tutorial.

10. Connecting Flask to a Database Using Flask-SQLAlchemy

Flask-SQLAlchemy is a Flask extension that simplifies database integration using SQLAlchemy. It provides an application-aware database object, ORM models, and session management.

10.1 Install the Required Packages

python -m pip install Flask Flask-SQLAlchemy

10.2 Configure the Database URI

A database URI tells SQLAlchemy which database to use and how to connect to it.

For SQLite, a common configuration is:

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

For other databases, the connection URI depends on the database system and its driver. Database usernames and passwords should be stored in environment variables or a secure secrets manager rather than committed to source code.

10.3 Create a Flask Application and Database Object

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)

app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///school.db"
app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False

db = SQLAlchemy()
db.init_app(app)

if __name__ == "__main__":
    app.run(debug=True)

Explanation:

• Flask(__name__) creates the Flask application.
• SQLALCHEMY_DATABASE_URI specifies the database location.
• SQLALCHEMY_TRACK_MODIFICATIONS disables an unnecessary modification-tracking feature.
• SQLAlchemy() creates the extension object.
• db.init_app(app) initializes the extension with the application.

The example establishes the application's database configuration. Models and tables are defined in the following sections.

11. Defining Database Models

A model is a Python class that represents a database table. Its attributes describe columns, while model instances represent individual records.

For example, a student model can define an ID, name, email address, and course name.

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///school.db"
app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False

db = SQLAlchemy(app)

class Student(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False)
    email = db.Column(db.String(120), unique=True, nullable=False)
    course = db.Column(db.String(100), nullable=False)

    def __repr__(self):
        return f"<Student {self.name!r}>"

Model explanation:

• db.Model is the base class for ORM models.
• id is the primary key for identifying each record.
• db.Integer defines an integer column.
• db.String(100) defines a string column with a specified maximum length.
• nullable=False means the database column cannot contain NULL values.
• unique=True requires the email value to be unique.

For new projects, developers should also consider database migrations so that changes to models can be applied safely to existing databases.

12. Creating Database Tables

After defining models, the application needs database tables that match those models.

with app.app_context():
    db.create_all()

This creates missing tables for models that have been registered with the SQLAlchemy metadata. It does not automatically update existing tables when a model changes.

In a learning project, you can place this code after the model definitions and run it once. In larger applications, database migrations are preferable for managing schema changes.

For example, install Flask-Migrate with:

python -m pip install Flask-Migrate

Flask-Migrate integrates migration management into Flask applications using Alembic. Its commands can create migration scripts, review proposed schema changes, and apply migrations to the database.

13. Performing CRUD Operations

CRUD stands for Create, Read, Update, and Delete. These are the four fundamental operations used to manage stored information.

13.1 Create: Insert a Record

with app.app_context():
    student = Student(
        name="Aarav Sharma",
        email="[email protected]",
        course="Python Development"
    )

    db.session.add(student)
    db.session.commit()

db.session.add() stages the new model instance for insertion. db.session.commit() commits the transaction and persists the record.

13.2 Read: Retrieve Records

with app.app_context():
    all_students = db.session.execute(
        db.select(Student).order_by(Student.name)
    ).scalars().all()

    for student in all_students:
        print(student.name, student.email)

This query retrieves student records and orders them by name. The scalars().all() chain returns the model objects from the query result.

13.3 Read: Retrieve a Single Record

with app.app_context():
    student = db.session.get(Student, 1)

    if student is not None:
        print(student.name)
    else:
        print("Student not found")

The db.session.get() method retrieves a record by its primary key.

13.4 Update: Modify a Record

with app.app_context():
    student = db.session.get(Student, 1)

    if student is not None:
        student.course = "Flask Web Development"
        db.session.commit()

When a mapped attribute changes, SQLAlchemy tracks the modification. Committing the session saves the update to the database.

13.5 Delete: Remove a Record

with app.app_context():
    student = db.session.get(Student, 1)

    if student is not None:
        db.session.delete(student)
        db.session.commit()

The record is marked for deletion in the session and removed when the transaction is committed.

Important: If a database operation fails, handle the exception and roll back the session before attempting further operations with that session.

14. Displaying Database Records on a Flask Web Page

Database connectivity becomes useful when retrieved information is displayed to users. Flask can pass query results into Jinja templates to generate dynamic HTML pages.

14.1 Create a Route

from flask import render_template

@app.route("/students")
def student_list():
    students = db.session.execute(
        db.select(Student).order_by(Student.name)
    ).scalars().all()

    return render_template(
        "students.html",
        students=students
    )

14.2 Create the HTML Template

Create a templates/students.html file:

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Student List</title>
</head>
<body>
    <h1>Registered Students</h1>

    <table border="1" cellpadding="8" cellspacing="0">
        <thead>
            <tr>
                <th>ID</th>
                <th>Name</th>
                <th>Email</th>
                <th>Course</th>
            </tr>
        </thead>
        <tbody>
            {% for student in students %}
                <tr>
                    <td>{{ student.id }}</td>
                    <td>{{ student.name }}</td>
                    <td>{{ student.email }}</td>
                    <td>{{ student.course }}</td>
                </tr>
            {% else %}
                <tr>
                    <td colspan="4">No students registered yet.</td>
                </tr>
            {% endfor %}
        </tbody>
    </table>
</body>
</html>

When the user visits /students, Flask queries the database, passes the results to the template, and renders a table containing the available records.

15. Building a Student Registration Form

A registration form allows users to submit data that the application can validate and store in the database. This example uses Flask-SQLAlchemy and an HTML form.

15.1 Registration Route

from flask import request, redirect, url_for, render_template
from sqlalchemy.exc import IntegrityError

@app.route("/register", methods=["GET", "POST"])
def register():
    if request.method == "POST":
        name = request.form.get("name", "").strip()
        email = request.form.get("email", "").strip().lower()
        course = request.form.get("course", "").strip()

        if not name or not email or not course:
            return render_template(
                "register.html",
                error="All fields are required."
            ), 400

        student = Student(
            name=name,
            email=email,
            course=course
        )

        try:
            db.session.add(student)
            db.session.commit()
        except IntegrityError:
            db.session.rollback()
            return render_template(
                "register.html",
                error="This email may already be registered."
            ), 409

        return redirect(url_for("student_list"))

    return render_template("register.html")

15.2 Registration Template

Create templates/register.html:

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Student Registration</title>
</head>
<body>
    <h1>Register a Student</h1>

    {% if error %}
        <p role="alert">{{ error }}</p>
    {% endif %}

    <form method="post">
        <label for="name">Full Name:</label>
        <input id="name" type="text" name="name" required>

        <label for="email">Email:</label>
        <input id="email" type="email" name="email" required>

        <label for="course">Course:</label>
        <input id="course" type="text" name="course" required>

        <button type="submit">Register</button>
    </form>
</body>
</html>

This example demonstrates basic validation and handling of a duplicate email constraint. A production registration workflow should also use CSRF protection, stronger server-side validation, appropriate logging, and a properly configured error-handling strategy.

16. Using Parameterized Queries

Parameterized queries separate SQL instructions from user-provided values. They help prevent SQL injection, an attack in which malicious input changes the intended meaning of a database query.

16.1 Unsafe Query Example

# Avoid constructing SQL using untrusted input
query = "SELECT * FROM students WHERE email = '" + email + "'"

Concatenating input directly into SQL can allow specially crafted values to change the query's behavior.

16.2 Safe SQLite Query

import sqlite3

connection = sqlite3.connect("school.db")

email = input("Enter email: ")

student = connection.execute(
    "SELECT id, name, email FROM students WHERE email = ?",
    (email,)
).fetchone()

print(student)
connection.close()

The question mark is a placeholder. The value is passed separately as a tuple, allowing the database driver to handle it as data rather than executable SQL.

16.3 Safe SQLAlchemy Query

from sqlalchemy import text

with db.engine.connect() as connection:
    result = connection.execute(
        text("SELECT id, name FROM students WHERE email = :email"),
        {"email": "[email protected]"}
    )

    records = result.fetchall()

SQLAlchemy's text() construct supports named parameters. The values are supplied separately rather than concatenated into the query string.

ORM queries such as db.select(Student).where(Student.email == email) are another convenient option.

17. Managing Transactions and Database Errors

A transaction groups database operations into a unit of work. Transactions help preserve consistency when multiple related changes must succeed together.

For example, an enrollment workflow might create an enrollment record and a payment record. If one operation fails, the application may need to roll back the transaction so that the database does not retain an incomplete operation.

17.1 Understanding Commit and Rollback

• Commit: Confirms the transaction and persists its changes.
• Rollback: Cancels uncommitted changes when a transaction fails.
• Exception handling: Allows the application to respond to database errors without exposing sensitive technical details to users.

from sqlalchemy.exc import SQLAlchemyError

try:
    student = Student(
        name="Neha Patel",
        email="[email protected]",
        course="Python Development"
    )

    db.session.add(student)
    db.session.commit()

except SQLAlchemyError:
    db.session.rollback()
    raise

This example rolls back the transaction if a SQLAlchemy database operation fails, then re-raises the exception so that a higher-level handler can process it. A production application should log the error safely and return an appropriate user-facing response.

18. Connecting Flask to MySQL

MySQL is a relational database management system commonly used for web applications. A Flask application can connect to MySQL through a compatible database driver or through SQLAlchemy with an appropriate MySQL driver.

18.1 Install a MySQL Driver

For example, a project using the PyMySQL driver can install:

python -m pip install PyMySQL

18.2 Configure the Database URI

app.config["SQLALCHEMY_DATABASE_URI"] = (
    "mysql+pymysql://username:password@localhost:3306/school"
)

Replace the example credentials and database name with values from your environment. Do not use these literal placeholder credentials in a real deployment.

For applications using MySQL, ensure that the database exists, the account has only the necessary privileges, the driver is installed, and the server is reachable.

18.3 Security Recommendations

• Store credentials in environment variables or a secrets manager.
• Use encrypted connections when required by the deployment environment.
• Limit database account permissions to the operations the application needs.
• Configure connection timeouts and appropriate connection pooling.
• Keep database drivers and related packages updated.

19. Database Configuration and Environment Variables

Database settings should be managed separately from application code, particularly when development, testing, and production environments use different databases.

For example, an application can read a database URI from an environment variable:

import os
from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)

app.config["SQLALCHEMY_DATABASE_URI"] = os.environ.get(
    "DATABASE_URL",
    "sqlite:///school.db"
)

db = SQLAlchemy(app)

The fallback value makes the example convenient for local learning. Production deployments should explicitly provide the intended database URI and fail clearly if a required configuration value is missing.

Environment variables help prevent passwords and connection strings from being hardcoded in source files. They should still be protected from unauthorized access.

20. Database Relationships

Applications frequently store related information in separate tables. Database relationships allow those records to be associated with one another.

Common relationship types include:

• One-to-one: One record is associated with one related record.
• One-to-many: One record is associated with multiple related records.
• Many-to-many: Multiple records in one table can relate to multiple records in another table.

For example, one student can enroll in multiple courses, and each course can have multiple students. This is typically represented using an enrollment or association table.

Example: Student and Enrollment Models

class Student(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False)

    enrollments = db.relationship(
        "Enrollment",
        back_populates="student"
    )

class Course(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(150), nullable=False)

    enrollments = db.relationship(
        "Enrollment",
        back_populates="course"
    )

class Enrollment(db.Model):
    id = db.Column(db.Integer, primary_key=True)

    student_id = db.Column(
        db.Integer,
        db.ForeignKey("student.id"),
        nullable=False
    )

    course_id = db.Column(
        db.Integer,
        db.ForeignKey("course.id"),
        nullable=False
    )

    student = db.relationship(
        "Student",
        back_populates="enrollments"
    )

    course = db.relationship(
        "Course",
        back_populates="enrollments"
    )

The enrollment model connects a student to a course. This structure can be extended with enrollment dates, payment status, grades, or other relevant information.

21. Database Migrations

A database schema changes as an application evolves. Developers may add new columns, create tables, change constraints, or introduce relationships. Database migrations provide a controlled way to apply these schema changes.

Flask-SQLAlchemy's db.create_all() creates missing tables but does not update existing table structures when models change. For ongoing development, Flask-Migrate is commonly used to manage schema changes.

Typical migration workflow:

1. Install and configure Flask-Migrate.
2. Initialize the migration repository.
3. Generate a migration after changing models.
4. Review the generated migration script.
5. Apply the migration to the target database.
6. Test the application against the updated schema.

Migration scripts should be reviewed before execution, particularly when they modify or remove existing data. Back up important databases before risky schema changes.

22. Common Database Connectivity Errors

23. Database Security Best Practices

Database security is essential because databases may contain personal information, credentials, financial records, or confidential business data.

• Use parameterized queries: Never concatenate untrusted input into SQL statements.
• Validate inputs: Check the type, length, format, and permitted values of user-submitted data.
• Protect credentials: Store secrets outside source code and restrict access to them.
• Apply least privilege: Give application database accounts only the permissions they need.
• Use password hashing: Store passwords using a suitable password-hashing method, never as plain text.
• Use HTTPS: Protect data transmitted between browsers and the application.
• Protect forms: Use CSRF protection for applicable state-changing requests.
• Handle errors safely: Avoid displaying raw database errors, connection strings, or sensitive information to users.
• Back up data: Establish and test a backup and recovery process.
• Control access: Enforce authorization checks before reading or modifying protected records.
• Keep dependencies updated: Maintain supported versions of Flask, SQLAlchemy, and database drivers.

24. Database Performance and Optimization

As an application grows, database performance can affect page loading times, user experience, and server resource consumption.

Useful optimization techniques include:

• Indexing: Create indexes for frequently searched or joined columns when appropriate.
• Efficient queries: Retrieve only the columns and records needed.
• Pagination: Display large datasets in manageable groups rather than loading every record at once.
• Connection pooling: Reuse database connections where supported and appropriate.
• Query inspection: Examine slow queries and query execution plans.
• Appropriate relationships: Avoid unnecessary repeated queries when loading related records.
• Transactions: Group related writes and keep transactions as short as practical.
• Caching: Cache suitable read-heavy data when consistency requirements permit it.

Performance improvements should be guided by measurement. Adding indexes or caching without understanding the workload may increase storage, write overhead, or maintenance complexity.

25. Testing Database Operations in Flask

Testing helps confirm that application routes and database operations behave correctly. Flask's test client can simulate requests, while a separate test database can isolate test data from development or production data.

Useful database tests include:

• Checking that a new record is inserted successfully.
• Verifying that records are retrieved correctly.
• Testing updates and deletions.
• Confirming that duplicate values violate the intended constraint.
• Checking that invalid record IDs return an appropriate status code.
• Ensuring failed transactions are rolled back.

Tests should use an isolated database and clean up after themselves. Avoid running destructive test operations against a production database.

26. Practical Project: Student Management System

Project objective: Develop a Flask web application that connects to a database and manages student records.

Project Features

• Add a new student.
• Display all registered students.
• View individual student details.
• Update student information.
• Delete a student record.
• Validate required fields and email addresses.
• Prevent duplicate email addresses.
• Display meaningful messages when records are missing.
• Use Jinja templates to present database records.
• Handle database errors safely.

Suggested Project Structure

student_management/
├── app.py
├── models.py
├── requirements.txt
├── templates/
│   ├── base.html
│   ├── students.html
│   ├── register.html
│   └── student_details.html
└── static/
    └── css/
        └── style.css

Development Steps

1. Create a virtual environment and install Flask and Flask-SQLAlchemy.
2. Configure the database URI.
3. Define the student model and its constraints.
4. Initialize the database schema using a suitable approach.
5. Create a route for student registration.
6. Create a route to list all students.
7. Create a dynamic route for individual student details.
8. Add routes to update and delete records.
9. Create templates for displaying forms and records.
10. Add validation, transaction handling, and error responses.
11. Test the application's CRUD operations.
12. Document how to install, configure, and run the application.

Expected Outcome

The completed application should allow users to manage student records through a browser. Information entered through a form should be saved in the database, and saved records should appear on the student listing page. Users should also be able to update or remove records through the appropriate routes.

27. Exercises for Practice

1. Connect a Flask application to SQLite and create a table named products.
2. Define a model for products containing an ID, name, price, and category.
3. Insert at least five product records into the database.
4. Create a route that displays all products in an HTML table.
5. Build a form that allows a user to add a new product.
6. Write a query to retrieve a product by its primary key.
7. Add an update feature to change a product's price.
8. Add a delete feature for obsolete products.
9. Configure a unique constraint on an appropriate field and test duplicate input.
10. Handle database exceptions using rollback and suitable error responses.
11. Connect a Flask application to MySQL using SQLAlchemy and a compatible driver.
12. Write tests that verify the successful creation and retrieval of records.

28. Interview Questions and Answers

Q1. Why does a Flask application need a database?

Answer: A database stores persistent application data, such as user accounts, product records, orders, and course information, allowing the application to retrieve and modify that information later.

Q2. Which databases can be used with Flask?

Answer: Flask can work with SQLite, MySQL, PostgreSQL, MongoDB, and other supported databases through suitable Python drivers or libraries.

Q3. What is SQLite?

Answer: SQLite is a file-based relational database. Python includes the sqlite3 module for connecting to and querying SQLite databases.

Q4. What is Flask-SQLAlchemy?

Answer: Flask-SQLAlchemy is an extension that integrates SQLAlchemy with Flask and simplifies database configuration, ORM models, and session management.

Q5. What is an ORM?

Answer: An Object-Relational Mapper maps database tables and records to programming-language objects, allowing developers to work with database data through object-oriented interfaces.

Q6. What is a database model?

Answer: A model is a Python class that represents a database table. Its attributes define columns, and instances represent records.

Q7. What does db.create_all() do?

Answer: It creates missing tables based on registered model metadata. It does not automatically migrate existing tables when models change.

Q8. What are CRUD operations?

Answer: CRUD means Create, Read, Update, and Delete. These are the basic operations for managing stored records.

Q9. What is the purpose of db.session.commit()?

Answer: It commits the current transaction, persisting its changes to the database.

Q10. Why is db.session.rollback() used?

Answer: It rolls back an unsuccessful or uncommitted transaction and helps restore the session to a usable state after a database error.

Q11. What is SQL injection?

Answer: SQL injection is a security vulnerability in which untrusted input alters the intended meaning of a SQL statement. Parameterized queries and ORM expressions help prevent this class of vulnerability.

Q12. What is the purpose of a primary key?

Answer: A primary key uniquely identifies each row in a table.

Q13. What is a foreign key?

Answer: A foreign key links a column in one table to a key in another table and can enforce referential integrity.

Q14. Why are database migrations necessary?

Answer: Migrations provide a controlled way to apply database schema changes as an application evolves.

Q15. How can database connections be managed in Flask?

Answer: Connections can be managed with request-scoped lifecycle hooks when using direct database drivers, or through SQLAlchemy's engine and session management.

Q16. How can Flask connect to MySQL?

Answer: Flask can use a compatible MySQL driver directly or use SQLAlchemy with a suitable MySQL driver and a configured database URI.

Q17. Why should database credentials not be hardcoded?

Answer: Hardcoded credentials may be exposed through source control or application files. Environment variables and secret-management systems help protect sensitive configuration.

Q18. How can database performance be improved?

Answer: Common techniques include efficient queries, suitable indexes, pagination, connection pooling, query-plan inspection, and carefully applied caching.

29. Quick Revision Checklist

• Understand the purpose of database connectivity in Flask.
• Identify when SQLite, MySQL, or PostgreSQL is appropriate.
• Create a database connection using sqlite3.
• Configure Flask-SQLAlchemy with a database URI.
• Define models, columns, primary keys, and constraints.
• Create tables and manage schema changes.
• Perform CRUD operations using SQL or ORM methods.
• Display database records in Jinja templates.
• Validate user input and use parameterized queries.
• Commit successful transactions and roll back failed ones.
• Manage connections and sessions appropriately.
• Protect credentials and database permissions.
• Test database operations using an isolated test database.

30. Conclusion

Connecting Flask applications to databases is an essential skill for building practical Python web applications. Database connectivity enables applications to save user information, retrieve records, process updates, and manage data through browser-based interfaces or APIs.

Python's built-in sqlite3 module provides a direct approach to working with SQLite, while SQLAlchemy and Flask-SQLAlchemy offer convenient abstractions for database operations and model-based development. By understanding CRUD operations, models, transactions, parameterized queries, migrations, and security practices, developers can create reliable and maintainable database-driven applications.

Additional Learning Resources

Explore these official resources to learn more about Flask database connectivity:

• Flask Official Tutorial: Define and Access the Database
• Flask-SQLAlchemy Official Quick Start
• Flask Documentation: SQLAlchemy in Flask

Learn Python with JustAcademy

Explore the Python Training Course at JustAcademy to develop your Python programming and application development skills.

You can also register for a course demo to learn more about the training options.

Visit the JustAcademy official website to explore additional courses and learning resources.