Flask and Database: SQLAlchemy Model Definition

In web development, the database is the core for storing and managing data. Flask itself does not have built-in database operation functionality, but it can easily interact with databases by leveraging the extension library SQLAlchemy. SQLAlchemy is a powerful ORM (Object-Relational Mapping) tool that allows us to directly operate on databases through Python objects without writing complex SQL statements.

1. Install Required Libraries¶

First, install Flask and Flask-SQLAlchemy (the SQLAlchemy extension for Flask). If using the SQLite database (suitable for development environments), no additional driver installation is required; if using MySQL or PostgreSQL, install the corresponding driver (e.g., pymysql or psycopg2).

pip install flask flask-sqlalchemy

2. Initialize Flask Application and SQLAlchemy¶

Create app.py in your project. First, import the necessary libraries and initialize the Flask application and SQLAlchemy:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

# Initialize Flask application
app = Flask(__name__)

# Configure database connection URI (using SQLite here, database file named mydatabase.db)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///mydatabase.db'
# Disable SQLAlchemy's modification tracking (reduces performance overhead)
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

# Initialize SQLAlchemy instance and bind it to the Flask application
db = SQLAlchemy(app)

3. Define the First Model¶

A model is essentially a Python class that inherits from db.Model, where class attributes correspond to database table fields. Here is a simple example of a User model:

class User(db.Model):
    # 1. Primary key field (auto-incrementing integer)
    id = db.Column(db.Integer, primary_key=True)

    # 2. Regular field: string type, length 50, not nullable, unique
    username = db.Column(db.String(50), nullable=False, unique=True)

    # 3. String type, length 120, unique, not nullable
    email = db.Column(db.String(120), unique=True, nullable=False)

    # 4. Integer type, allows null values
    age = db.Column(db.Integer)

    # 5. Text type, stores long text
    bio = db.Column(db.Text)

    # __repr__ method: Displays friendly information when object is printed
    def __repr__(self):
        return f'<User {self.username}>'

Model Field Types and Parameter Explanation¶

Key Parameters:
- primary_key=True: Set as primary key, unique and auto-incrementing
- unique=True: Field values must be unique (e.g., username, email)
- nullable=False: Field cannot be empty (required)
- default: Set default value (e.g., default=0 means default age is 0)

4. Define Models with Relationships¶

In actual development, data often has associations. Taking “User-Post” as an example: one user can publish multiple posts (one-to-many relationship):

class Post(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(100), nullable=False)
    content = db.Column(db.Text, nullable=False)

    # Foreign key: relates to the id field of the User table (table name defaults to lowercase)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)

    # Define relationship: User model reversely associates with Post through the 'posts' property
    author = db.relationship('User', backref=db.backref('posts', lazy=True))

    def __repr__(self):
        return f'<Post {self.title}>'

Relationship Explanation:
- db.ForeignKey('user.id'): Foreign key constraint, ensuring user_id must be an existing id in the User table
- relationship('User', backref=...): Defines a bidirectional relationship between User and Post. The backref automatically adds a posts property to the User model (e.g., user.posts can retrieve all posts by this user)

5. Create Database Tables¶

After defining the models, map them to database tables. Use the db.create_all() method to automatically create tables (must be executed within the application context):

# Create tables within the application context
with app.app_context():
    db.create_all()  # Generate all tables corresponding to models

Note: If the model is modified (e.g., adding new fields), re-execute db.create_all(). SQLAlchemy will automatically handle table structure updates without deleting existing data.

6. Use Models for Data Operations¶

Once the models are defined, you can operate on the database through Python objects (CRUD: Create, Read, Update, Delete). Here are common operation examples:

1. Create Records (Add Data)¶

# Create a user
new_user = User(
    username="Zhang San",
    email="zhangsan@example.com",
    age=25,
    bio="A beginner passionate about programming"
)

# Add to database session and commit
db.session.add(new_user)
db.session.commit()

2. Query Records¶

# Query all users
all_users = User.query.all()
print(all_users)  # Output: [<User Zhang San>, ...]

# Query by condition (e.g., user with username "Zhang San")
user = User.query.filter_by(username="Zhang San").first()
print(user.age)  # Output: 25

# Relationship query (get all posts by a user)
user = User.query.get(1)  # Get user with id=1
posts = user.posts  # Get list of posts by this user via the 'posts' property

3. Update Records¶

user = User.query.filter_by(username="Zhang San").first()
user.age = 26  # Modify age
db.session.commit()  # Commit changes

4. Delete Records¶

user = User.query.filter_by(username="Zhang San").first()
db.session.delete(user)  # Mark for deletion
db.session.commit()  # Execute deletion

7. Summary¶

SQLAlchemy model definition is the foundation for Flask to interact with databases. By defining Python classes that inherit from db.Model, we can:
- Map database table fields using class attributes
- Set field types and constraints via db.Column
- Define table relationships using relationship
- Automatically create table structures with db.create_all()

After mastering model definition, you can easily implement CRUD operations without writing SQL statements directly. Further learning can include more complex table relationships (e.g., many-to-many) and advanced query techniques.

Tip: Use flask shell for quick testing of model operations. After executing flask shell, import models and operate in the Python environment.