Flask-SQLAlchemy is a powerful extension that integrates the SQLAlchemy ORM into Flask applications. It abstracts database interactions, allowing developers to communicate with various database systems—such as SQLite, PostgreSQL, and MySQL—using Pythonic object-oriented syntax.

1. Initializing the Extension

To start, install the extension and bind it to your Flask instance by configuring the SQLALCHEMY_DATABASE_URI.

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
# Connection string example for SQLite
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///app_data.db'
db = SQLAlchemy(app)

Common connection string patterns include:

• PostgreSQL: postgresql://user:password@localhost/dbname
• MySQL: mysql://user:password@localhost/dbname
• SQLite: sqlite:////absolute/path/to/database.db

2. Defining Models and Initializing Tables

Models are defined as Python classes that inherit from db.Model. You can define table columns using db.Column.

import secrets
from my_app import db

class Account(db.Model):
   __tablename__ = 'accounts'
   id = db.Column(db.Integer, primary_key=True)
   handle = db.Column(db.String(50), unique=True, nullable=False)
   secret_key = db.Column(db.String(128))

   def __init__(self, handle, secret):
       self.handle = handle
       self.secret_key = secrets.token_hex(16)

   def __repr__(self):
       return f"<Account {self.handle}>"

# Create tables based on defined models
with app.app_context():
   db.create_all()

3. Data Persistence

Adding records involves creating an instance of your model, adding it to the session, and committing the transatcion.

new_user = Account(handle='jdoe', secret='supersecret')
db.session.add(new_user)
db.session.commit()
# Access ID after commit
print(new_user.id)

4. Querying Records

Flask-SQLAlchemy provides a robust query interface. You can retrieve records by primary key, filter them using specific attributes, or apply logical conditions.

# By Primary Key
user = Account.query.get(1)

# Using filter_by for exact matches
user = Account.query.filter_by(handle='jdoe').first()

# Using filter() for advanced expressions
users = Account.query.filter(Account.handle.like('%doe%')).all()

# Logical operators
from sqlalchemy import or_, and_, not_

# OR condition
results = Account.query.filter(or_(Account.handle == 'alice', Account.handle == 'bob')).all()

# AND condition
results = Account.query.filter(and_(Account.handle != 'admin', Account.id > 10)).all()

5. Modifying and Removing Data

Updating records is as simple as modifying object attributes and calling db.session.commit(). Deleting follows a similar workflow.

# Update
target = Account.query.filter_by(handle='jdoe').first()
if target:
   target.handle = 'jane_doe'
   db.session.commit()

# Delete
target = Account.query.filter_by(handle='jane_doe').first()
if target:
   db.session.delete(target)
   db.session.commit()