Environment Installation
Install the required dependencies via pip:
pip install flask-sqlalchemy
pip install pymysql
Basic Usage
Minimal Working Application
To get started with Flask-SQLAlchemy, you only need to configure your Flask applicaiton instence and initialize the SQLAlchemy instance with it. A common project structure splits configuration into a separate file:
project_root/
├── config.py
└── app.py
config.py content:
class DevelopmentConfig:
ENV = 'development'
DEBUG = True
# Database connection string
SQLALCHEMY_DATABASE_URI = 'mysql+pymysql://root:root@xxx.116.152.57:3308/flask_demo'
# Auto-commit database changes after each request completes
SQLALCHEMY_COMMIT_ON_TEARDOWN = True
# Toggle modification tracking for ORM objects
SQLALCHEMY_TRACK_MODIFICATIONS = True
# Print raw SQL statements to console during operations
SQLALCHEMY_ECHO = True
app.py content:
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from config import DevelopmentConfig
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = DevelopmentConfig.SQLALCHEMY_DATABASE_URI
db = SQLAlchemy(app)
db.init_app(app)
# Define ORM model for user table
class User(db.Model):
# Custom table name
__tablename__ = 'user'
# Column definition format: db.Column(data_type, constraints)
user_id = db.Column(db.Integer, primary_key=True, autoincrement=True)
username = db.Column(db.String(15), nullable=False)
email = db.Column(db.String(120), unique=True)
def __init__(self, username, email):
self.username = username
self.email = email
def __repr__(self):
return f'<User {self.username}>'
if __name__ == '__main__':
# Create all mapped tables in database
db.create_all()
app.run(debug=True)
db.create_all() scans all subclasses of db.Model and creates corresponding tables in the database. It will not recreate or update existing tables if they already exist in the database.
CRUD Operations
Create (Insert New Records)
After the user table is created, you can add new data with the following code:
def add_sample_users():
# Initialize new user objects
admin_user = User(username='admin', email='admin@example.com')
guest_user = User(username='guest', email='guest@example.com')
# Add objects to the current database session
db.session.add(admin_user)
db.session.add(guest_user)
# Commit transaction to persist changes to database
db.session.commit()
Delete Records
The example below deletes the record for username guest:
def delete_guest_user():
# Fetch the first matching record
target_guest = User.query.filter_by(username='guest').first()
# Mark record for deletion
db.session.delete(target_guest)
# Commit change
db.session.commit()
Update Existing Records
This example updates the email address of the admin user with primary key 1:
def update_admin_email():
# Fetch user by primary key
admin_user = User.query.get(1)
# Modify the attribute value
admin_user.email = 'admin_new@example.com'
# Add updated object to session
db.session.add(admin_user)
# Commit change
db.session.commit()
Query Records
The example below demonstrates common basic query operatinos:
def query_user_data():
# Get all records from the user table
all_users = User.query.all()
print(all_users)
# Get the first matching record by username
matched_admin = User.query.filter_by(username='admin').first()
print(matched_admin)
Defining Table Relationships
In relational databases, there are three common types of relationships between tables:
- One-to-Many: The most frequently used relationship. For example: one class (grade) has multiple students, and each student belongs to exactly one class.
- One-to-One: Create a one-to-one relationship by adding the
uselist=Falseparameter to therelationship()method call. - Many-to-Many: Requires a separate auxiliary association table to connect the two entities. It is strongly recommended to use a raw table instead of a full mapped model for the association table.
Common example relationship mapping after ORM analysis:
- Grade table ↔ Student table: One-to-Many (one grade contains multiple students)
- User table ↔ Role table: One-to-Many (one role can be assigned to multiple users)
- Role table ↔ Permission table: Many-to-Many (one role has multiple permissions, one permission can be granted to multiple roles)
Full example code for the relationships:
from flask import Flask
from datetime import datetime
from flask_sqlalchemy import SQLAlchemy
from config import DevelopmentConfig
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = DevelopmentConfig.SQLALCHEMY_DATABASE_URI
db = SQLAlchemy(app)
db.init_app(app)
class Grade(db.Model):
"""Grade model: one side of one-to-many relationship"""
__tablename__ = 'grade'
grade_id = db.Column(db.Integer, autoincrement=True, primary_key=True, comment='Grade ID')
grade_name = db.Column(db.String(20), unique=True, comment='Grade name')
created_at = db.Column(db.DateTime, default=datetime.now, comment='Creation time')
# Define one-to-many relationship
students = db.relationship('Student', backref='grade')
def __init__(self, name):
self.grade_name = name
def save(self):
db.session.add(self)
db.session.commit()
class Student(db.Model):
"""Student model: many side of one-to-many relationship"""
__tablename__ = 'student'
student_id = db.Column(db.Integer, autoincrement=True, primary_key=True, comment='Student ID')
student_name = db.Column(db.String(16), unique=True, comment='Student name')
gender = db.Column(db.Integer, comment='Gender')
# Foreign key referencing grade table
grade_id = db.Column(db.Integer, db.ForeignKey('grade.grade_id'), nullable=True)
def __init__(self, name, gender, grade_id):
self.student_name = name
self.gender = gender
self.grade_id = grade_id
def save(self):
db.session.add(self)
db.session.commit()
class User(db.Model):
"""User model: many side of one-to-many relationship with Role"""
__tablename__ = 'user'
user_id = db.Column(db.Integer, autoincrement=True, primary_key=True, comment='User ID')
username = db.Column(db.String(16), unique=True, comment='Username')
password_hash = db.Column(db.String(250), comment='Hashed password')
created_at = db.Column(db.DateTime, default=datetime.now, comment='Creation time')
# Foreign key referencing role table
role_id = db.Column(db.Integer, db.ForeignKey('role.role_id'))
def __init__(self, username, password):
self.username = username
self.password_hash = password
def save(self):
db.session.add(self)
db.session.commit()
class Role(db.Model):
"""Role model: one side of one-to-many relationship with User"""
__tablename__ = 'role'
role_id = db.Column(db.Integer, autoincrement=True, primary_key=True, comment='Role ID')
role_name = db.Column(db.String(10), comment='Role name')
# Define one-to-many relationship
users = db.relationship('User', backref='role')
def __init__(self, name):
self.role_name = name
def save(self):
db.session.add(self)
db.session.commit()
# Auxiliary association table for many-to-many role-permission relationship
role_permission_assoc = db.Table(
'role_permission',
db.Column('role_id', db.Integer, db.ForeignKey('role.role_id'), primary_key=True),
db.Column('permission_id', db.Integer, db.ForeignKey('permission.permission_id'), primary_key=True)
)
class Permission(db.Model):
"""Permission model: many side of many-to-many relationship"""
__tablename__ = 'permission'
permission_id = db.Column(db.Integer, autoincrement=True, primary_key=True, comment='Permission ID')
permission_name = db.Column(db.String(16), unique=True, comment='Permission name')
permission_code = db.Column(db.String(16), unique=True, comment='Permission unique code')
# Define many-to-many relationship
roles = db.relationship('Role', secondary=role_permission_assoc, backref=db.backref('permission', lazy=True))
def __init__(self, name, code):
self.permission_name = name
self.permission_code = code
def save(self):
db.session.add(self)
db.session.commit()
if __name__ == '__main__':
db.create_all()
In the code above, the relationship() method establishes the one-to-many relationship between the grade and student tables. The backref parameter defines a reverse reference, automatically adding a grade attribute to the Student model that lets you access the associated grade object directly.