Working with Databases

SQLAlchemy is a popular ORM in the Python ecosystem that allows you to work with databases using Python classes and objects. It abstracts the database layer, making it easier to perform operations like querying, inserting, updating, and deleting data without writing raw SQL.

Key Benefits of Using SQLAlchemy:

• Object-Oriented Approach: Work with Python classes instead of SQL queries.
• Database Agnostic: Supports multiple database backends (e.g., SQLite, PostgreSQL, MySQL).
• Automatic Schema Generation: Automatically generates database schema from your models.
• Powerful Query Language: Provides a high-level query API that resembles SQL but is written in Python.

To use SQLAlchemy in your Flask application, you’ll first need to install the Flask-SQLAlchemy extension.

Step 1: Install Flask-SQLAlchemy

pip install Flask-SQLAlchemy

Step 2: Configure Flask to Use SQLAlchemy

In your app.py file, you’ll set up the database connection by configuring Flask and initializing SQLAlchemy:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)

# Configure the SQLAlchemy part of the app instance
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///site.db'  # Using SQLite for simplicity
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False  # Disable modification tracking

# Create the SQLAlchemy db instance
db = SQLAlchemy(app)

# Later, you'll define models here

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

• SQLALCHEMY_DATABASE_URI: This configures the database URI, which tells SQLAlchemy where to find your database. Here, we’re using SQLite, but you can replace this with the URI for other databases like PostgreSQL or MySQL.
• SQLALCHEMY_TRACK_MODIFICATIONS: This disables a feature that tracks modifications to objects and emits signals, which isn’t necessary in most cases and adds unnecessary overhead.

With SQLAlchemy set up, you can define models that represent the tables in your database. Models are Python classes that inherit from db.Model, and each attribute represents a column in the table.With SQLAlchemy set up, you can define models that represent the tables in your database. Models are Python classes that inherit from db.Model, and each attribute represents a column in the table.

Step 1: Define a Model

Let’s define a simple User model in your app.py:

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

def __repr__(self):
    return f"User('{self.username}', '{self.email}')"

Explanation:

• id: A primary key that uniquely identifies each record.
• username, email: String fields that must be unique and cannot be null.
• password: A string field for storing the user’s password.
• __repr__: This method provides a string representation of the object, useful for debugging.

Step 2: Create the Database Tables

Before you can perform any operations, you need to create the tables in your database based on your models:

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

Running this code will create the User table in your database.

Now that the User table is set up, you can perform CRUD (Create, Read, Update, Delete) operations.

• Create:

new_user = User(username='johndoe', email='john@example.com', password='password123')
db.session.add(new_user)
db.session.commit()

This code creates a new User object, adds it to the session, and commits it to the database.

• Read:

user = User.query.filter_by(username='johndoe').first()
print(user.email)  # Outputs: john@example.com

This retrieves the first user with the username johndoe from the database.

• Update:

user = User.query.filter_by(username='johndoe').first()
user.email = 'john.doe@example.com'
db.session.commit()

This updates the email of the user johndoe and saves the changes to the database.

• Delete:

user = User.query.filter_by(username='johndoe').first()
db.session.delete(user)
db.session.commit()

This deletes the user johndoe from the database.

As your application evolves, you’ll need to modify your database schema, such as adding new columns or tables. Flask-Migrate simplifies this process by handling database migrations.

Step 1: Install Flask-Migrate

pip install Flask-Migrate

Step 2: Set Up Flask-Migrate

In your app.py, import Flask-Migrate and initialize it:

from flask_migrate import Migrate

migrate = Migrate(app, db)

Step 3: Initialize Migrations

Before making any migrations, you need to initialize the migration directory:

flask db init

Step 4: Create a Migration Script

After making changes to your models, create a migration script:

flask db migrate -m "Add User table"

This command generates a migration script that reflects the changes in your models.

Step 5: Apply the Migration

Apply the migration to your database using:

flask db upgrade

This command updates your database schema to match the current state of your models.

Working with databases in Flask is made easier with SQLAlchemy, which provides an object-oriented way to interact with your database. By defining models, you can perform CRUD operations seamlessly, and with Flask-Migrate, managing database schema changes becomes straightforward. This combination gives you a powerful and flexible way to handle data in your Flask applications.