Why ORM?¶
In web development, database operations are one of the core components. While writing raw SQL statements is flexible, it becomes costly to maintain and hard to read for complex projects. ORM (Object-Relational Mapping) maps database tables to Python objects, allowing you to manipulate the database using object-oriented syntax without worrying about underlying SQL details.
SQLAlchemy is one of the most popular ORM libraries in Python. Flask simplifies integration with SQLAlchemy via the Flask-SQLAlchemy extension. This article will guide you through mastering basic database operations with Flask + SQLAlchemy from scratch.
Environment Setup and Installation¶
First, ensure the required libraries are installed:
pip install flask flask-sqlalchemy
flask: Flask web framework coreflask-sqlalchemy: Extension for integrating Flask with SQLAlchemy
Initialize Flask App and SQLAlchemy¶
Create an app.py file to configure the app and database:
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
# Initialize Flask app
app = Flask(__name__)
# Configure database (using SQLite as example; no extra installation needed)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///mydatabase.db' # Local file path
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False # Disable modification tracking to save resources
# Initialize SQLAlchemy instance
db = SQLAlchemy(app)
Define Database Models¶
The core of ORM is the model class, where the class name maps to the database table, and class attributes map to table columns. Here’s a simple User model example:
class User(db.Model):
# Define table columns:
id = db.Column(db.Integer, primary_key=True) # Primary key, auto-incrementing
username = db.Column(db.String(80), unique=True, nullable=False) # Unique non-null username
email = db.Column(db.String(120), unique=True, nullable=False) # Unique non-null email
age = db.Column(db.Integer) # Optional age field
# Optional: String representation for debugging
def __repr__(self):
return f'<User {self.username}>'
db.Column(): Defines a column with parameters like data type (Integer,String) and constraints (unique,nullable).primary_key=True: Marks the field as the primary key (auto-increments by default).db.Model: All model classes must inherit from this base class.
Create Database Tables¶
After defining models, create the actual database tables. Execute this in the Flask shell or app entry point:
# In Flask shell (recommended):
# 1. Open terminal, navigate to project directory
# 2. Run `flask shell` to enter Python interactive mode
# 3. Import db and models
from app import db, User
# Create all tables
db.create_all() # Generates mydatabase.db with the User table
db.create_all()automatically creates tables based on model classes; no manualCREATE TABLEstatements needed.
Basic CRUD Operations (Create, Read, Update, Delete)¶
Database operations use a session (db.session), which manages transactions (commit/rollback).
1. Create Data¶
# Create a new user
new_user = User(
username="Alice",
email="alice@example.com",
age=25
)
# Add to session
db.session.add(new_user)
# Commit transaction (saves to database)
db.session.commit()
- Note: Always
add()first, thencommit(); otherwise, data won’t persist.
2. Read Data¶
# Query all users
all_users = User.query.all() # Returns list of User objects
for user in all_users:
print(user.username, user.email)
# Filter by field (e.g., find user with username "Alice")
user = User.query.filter_by(username="Alice").first() # Returns first match
print(user.email) # Output: alice@example.com
# Query by primary key
user = User.query.get(1) # Fast lookup by ID (e.g., ID=1)
print(user.age) # Output: 25
filter_by(): Filters results by field value (e.g.,username="Alice").first(): Returns the first matching result;all()returns all matches.get(): Quick lookup by primary key (more efficient).
3. Update Data¶
# Query the user
user = User.query.get(1)
if user:
user.age = 26 # Modify attributes
db.session.commit() # Save changes to database
- Modify object attributes, then
commit()to persist changes.
4. Delete Data¶
# Query the user
user = User.query.get(1)
if user:
db.session.delete(user) # Remove from session
db.session.commit() # Persist deletion
Complete Example: Flask App with Database Operations¶
Integrate the above into a complete Flask app:
from flask import Flask, render_template_string
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///mydatabase.db'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db = SQLAlchemy(app)
# Define model
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}>'
# Create tables on app startup
with app.app_context():
db.create_all()
# Route: List all users
@app.route('/')
def index():
users = User.query.all()
return render_template_string('''
<h1>User List</h1>
<ul>
{% for user in users %}
<li>{{ user.username }}: {{ user.email }}</li>
{% endfor %}
</ul>
''', users=users)
# Route: Add new user
@app.route('/add')
def add_user():
new_user = User(username="Bob", email="bob@example.com")
db.session.add(new_user)
db.session.commit()
return "User added successfully!"
if __name__ == '__main__':
app.run(debug=True)
Summary¶
ORM with SQLAlchemy simplifies database operations by letting you manipulate data via Python objects. Key steps:
1. Initialize db with database configuration.
2. Define model classes to map table structures.
3. Use db.create_all() to generate tables.
4. Perform CRUD operations via db.session.
Start with SQLite for practice, then migrate to MySQL/PostgreSQL (only change SQLALCHEMY_DATABASE_URI). Explore advanced features like relationships and querying later!