55: Flask ‐ SQLAlchemy [EN] - MantsSk/CA_PTUA14 GitHub Wiki

Intro and Installation

SQLAlchemy is a powerful Python SQL toolkit and Object-Relational Mapping (ORM) library that simplifies database interactions by allowing developers to work with databases using Python objects. In this lesson, we'll explore how to use SQLAlchemy with Flask, a popular web framework in Python.

First, let's install Flask and SQLAlchemy using pip:

pip install Flask-SQLAlchemy

Creating a Flask Application with Database

Let's create a simple Flask application in a file named app.py, but this time we will add code that will create a database:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app: Flask = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI']: str = 'sqlite:///example.db'
db: SQLAlchemy = SQLAlchemy(app)

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

Things you should already know in this code:

We import Flask and SQLAlchemy.
We create a Flask application instance.
Database related things:
We configure SQLAlchemy to use SQLite database named example.db.
We initialize SQLAlchemy with our Flask app.
We create a context using app.app_context() to make sure that the application context is pushed when using the database.
We call db.create_all() to create the necessary database tables. This line ensures that the database schema is created before the application starts running. Currently, the database will be empty, but we are going to fix that at the moment.

Defining Models with SQLAlchemy

Now, let's define a model representing a simple User table:

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True, nullable=False)
    email = db.Column(db.String(120), unique=True, nullable=False)

In the above code:

We create a User class that inherits from db.Model, indicating it's an SQLAlchemy model.
We define columns for id, username, and email.
So now, we have a simple Flask application that can create a database, add a table, and columns to that database.

Performing CRUD Operations

Put all code below the db.create_all()

Create

Creating new records in the database involves instantiating an object of the corresponding SQLAlchemy model, adding it to the session, and committing the changes to persist in the database.

Creating a new user

    new_user: User = User(username='john_doe', email='[email protected]')
    db.session.add(new_user)
    db.session.commit()

In this code:

We create a new instance of the User model, passing the desired values for the username and email fields.
We add the new user object to the current session using db.session.add().
Finally, we commit the changes to the database using db.session.commit(), which saves the new user record.

Read

from typing import List

    # Fetching all users
    all_users: List[User] = User.query.all()

    # Fetching a user by id
    user_obj: User = db.session.get(User, 1)

    if user_obj:
        print(user_obj.email)

In this code:

We get queries all records from the User table using the query.all() method provided by SQLAlchemy. It returns a list of all User objects present in the database.
After that, we retrieve a user with its primary key (id) using the db.session.get() method.

Update

Updating records in the database involves retrieving the record, modifying its attributes, and committing the changes to persist them in the database.

    user_obj: User = db.session.get(User, 1)

    if user_obj:
        user_obj.email = '[email protected]'
        db.session.commit()
        print(user_obj.email)  # [email protected]

In this code:

We retrieve the user record with the desired primary key.
We modify the email attribute of the retrieved user object.
Finally, we commit the changes to the database using db.session.commit() to persist the updated user record.

Delete

Deleting records from the database involves retrieving the record and then deleting it from the session and committing the changes to persist the deletion in the database.

    # Fetching a user by id
    user_obj: User = db.session.get(User, 1)

    if user_obj:
        db.session.delete(user_obj)
        db.session.commit()

In this code:

We retrieve the user record with the desired primary key.
We delete the retrieved user object from the session using db.session.delete(user).
Finally, we commit the changes to the database using db.session.commit() to persist the deletion of the user record.

Exercise

You are presented with initial Python code that already includes the Student model and comments explaining the basic functions.

Your task is to expand this code by adding function implementations to perform CRUD (Create, Read, Update, Delete) operations on student data in the database:

Create a student: Write a function that allows adding a new student to the database. The function parameters should be the student's name, age, and grade.
Get a student by ID: Write a function that takes a student ID as a parameter and returns the information of that student if they exist in the database, or a message indicating that the student doesn't exist.
Get all students: Write a function that returns a list of all students from the database.
Update a student: Write a function that allows updating a student's information (name, age, grade) based on their ID.
Delete a student: Write a function that allows deleting a student based on their ID.
Please note that this task is intended for Python function implementations only and does not include the creation of a user interface (GUI).

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from typing import List, Optional

app: Flask = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI']: str = 'sqlite:///students.db'
db: SQLAlchemy = SQLAlchemy(app)

# Define the Student model
class Student(db.Model):
    id: int = db.Column(db.Integer, primary_key=True)
    name: str = db.Column(db.String(100), nullable=False)
    age: int = db.Column(db.Integer, nullable=False)
    grade: str = db.Column(db.String(10), nullable=False)

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

# Create the database and tables
with app.app_context():
    db.create_all()

# Helper functions for CRUD operations

def add_student(name: str, age: int, grade: str) -> None:
    # TODO: Implement this function to add a new student to the database
    pass

def get_student_by_id(id: int) -> Optional[Student]:
    # TODO: Implement this function to get a student by their ID
    pass

def get_all_students() -> List[Student]:
    # TODO: Implement this function to get all students from the database
    pass

def update_student(id: int, name: str, age: int, grade: str) -> None:
    # TODO: Implement this function to update a student's information by their ID
    pass

def delete_student(id: int) -> None:
    # TODO: Implement this function to delete a student by their ID
    pass

if __name__ == '__main__':
    # Sample usage of the functions:
    with app.app_context():
        pass
        # Add students
        # TODO: Use the add_student function to add new students

        # Get students by ID
        # TODO: Use the get_student_by_id function to retrieve a student by ID

        # Get all students
        # TODO: Use the get_all_students function to retrieve all students

        # Update student
        # TODO: Use the update_student function to update a student's information

        # Delete student
        # TODO: Use the delete_student function to delete a student