1. What is SQLAlchemy?¶
When developing web applications with Flask, we often need to interact with databases (e.g., storing user information, product data). Writing raw SQL statements directly is possible but not beginner-friendly, and syntax varies significantly across databases (e.g., MySQL, PostgreSQL). SQLAlchemy is the most popular ORM (Object-Relational Mapping) tool in Python. It allows you to manipulate databases using Python classes and objects without writing raw SQL, while supporting multiple databases (SQLite, MySQL, PostgreSQL, etc.).
2. Install Dependencies¶
To use SQLAlchemy with Flask, install two libraries:
- Flask: The core Flask framework
- Flask-SQLAlchemy: The SQLAlchemy extension for Flask
Execute in the terminal:
pip install flask flask-sqlalchemy
If using SQLite (no additional driver needed, ideal for testing), the above dependencies suffice. For MySQL or PostgreSQL, install the corresponding driver (e.g., pymysql or psycopg2-binary).
3. Initialize Flask and SQLAlchemy¶
First, create a Flask app and configure SQLAlchemy to connect to the database.
Example Code:
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
# 1. Create Flask app
app = Flask(__name__)
# 2. Configure database connection (SQLite example; the path is relative and auto-creates test.db)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///test.db'
# Disable SQLAlchemy's modification tracking (optional, reduces performance overhead)
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
# 3. Initialize SQLAlchemy with the Flask app
db = SQLAlchemy(app)
4. Define Data Models¶
SQLAlchemy uses classes to define database tables, where each class corresponds to a table and class attributes correspond to table fields.
Example: Define a User Table
class User(db.Model):
# Table name defaults to lowercase class name; customize with: __tablename__ = 'users'
id = db.Column(db.Integer, primary_key=True, autoincrement=True) # Primary key, auto-incrementing integer
username = db.Column(db.String(80), unique=True, nullable=False) # Username (string, unique, non-nullable)
email = db.Column(db.String(120), unique=True, nullable=False) # Email (string, unique, non-nullable)
def __repr__(self): # Format for object printing (for debugging)
return f'<User {self.username}>'
Field Types and Constraints:
- db.Integer: Integer (e.g., primary key ID)
- db.String(length): String (specify length, e.g., String(80))
- primary_key=True: Set as the primary key (unique identifier for records)
- unique=True: Field values must be unique (e.g., username, email)
- nullable=False: Field cannot be empty
- autoincrement=True: Primary key auto-increments (common for integers)
5. Create Database Tables¶
After defining models, convert them to database tables using db.create_all(), executed within the application context.
Example Code:
# Ensure this runs in the correct Python environment (import app and db if in a separate script)
from app import app, db # Assuming the above code is in app.py
with app.app_context(): # Required in Flask 2.0+ (application context)
db.create_all() # Creates tables based on models
After execution, a test.db file will be generated in the project root (for SQLite), with table structure matching the User class.
6. Basic Operations (CRUD)¶
CRUD (Create, Read, Update, Delete) is the core of database operations.
6.1 Create Data¶
Create a model instance, add it to the session, and commit.
Example:
from app import app, db, User # Import models
with app.app_context():
# Create a user instance
user = User(username='Alice', email='alice@example.com')
# Add to the session
db.session.add(user)
# Commit the session (executes SQL INSERT)
db.session.commit()
6.2 Read Data¶
Query data using the query object with various conditions.
Example:
with app.app_context():
# 1. Query all users
all_users = User.query.all() # Returns a list
print(all_users) # Output: [<User Alice>, ...]
# 2. Query by condition (e.g., username='Alice')
alice = User.query.filter_by(username='Alice').first() # Returns a single object
print(alice.email) # Output: alice@example.com
# 3. Query by ID (primary key)
user_by_id = User.query.get(1) # Returns user with ID=1 (assuming Alice is ID=1)
print(user_by_id.username) # Output: Alice
6.3 Update Data¶
Query the target object, modify its attributes, and commit.
Example:
with app.app_context():
# Query the user
alice = User.query.filter_by(username='Alice').first()
# Update the email
alice.email = 'alice_new@example.com'
# Commit the changes
db.session.commit()
6.4 Delete Data¶
Query the target object, delete it, and commit.
Example:
with app.app_context():
# Query the user
alice = User.query.filter_by(username='Alice').first()
# Delete the object
db.session.delete(alice)
# Commit the deletion
db.session.commit()
7. Summary¶
The core process for database operations with SQLAlchemy is:
1. Configure Flask and SQLAlchemy to connect to the database.
2. Define data models (classes) corresponding to database tables.
3. Use db.create_all() to create tables.
4. Perform CRUD operations via db.session.
SQLAlchemy’s advantage is eliminating the need to write SQL, allowing direct manipulation with Python objects—ideal for rapid development and maintenance. Beginners should first master single-table operations, then progress to multi-table relationships (e.g., foreign keys, relationship fields).