What is Flask-SQLAlchemy?

Flask-SQLAlchemy is an extension that adds SQLAlchemy support to Flask. It simplifies database operations and provides an ORM (Object-Relational Mapping) for working with databases.

Installation

Install Flask-SQLAlchemy:

pip install flask-sqlalchemy

Configuration

Configure database connection:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

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

db = SQLAlchemy(app)

Defining Models

Create database models:

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)
    
    def __repr__(self):
        return f'<User {self.username}>'

Creating Tables

Create database tables:

# In Python shell or script
from app import app, db

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

Database Operations

Create

user = User(username='alice', email='[email protected]')
db.session.add(user)
db.session.commit()

Read

# Get all users
users = User.query.all()

# Get by ID
user = User.query.get(1)

# Filter
user = User.query.filter_by(username='alice').first()
users = User.query.filter(User.email.like('%@example.com')).all()

Update

user = User.query.get(1)
user.email = '[email protected]'
db.session.commit()

Delete

user = User.query.get(1)
db.session.delete(user)
db.session.commit()

Relationships

Define relationships between models:

class Post(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(100), nullable=False)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
    user = db.relationship('User', backref=db.backref('posts', lazy=True))

Best Practices

  • Use migrations: Flask-Migrate for schema changes
  • Handle errors: Wrap database operations in try/except
  • Use transactions: Commit or rollback appropriately
  • Close connections: Properly manage database connections

Next Steps

Learn about Flask forms for user input, or explore sessions for user state.