// on this page
Flask-SQLAlchemy is a powerful extension for Flask that integrates SQLAlchemy with your Flask applications. SQLAlchemy is a popular SQL toolkit and Object Relational Mapper (ORM) that allows developers to work with databases in a more Pythonic way. This guide will walk you through the installation, setup, and usage of Flask-SQLAlchemy, covering key concepts such as models, queries, CRUD operations, and more.
01Installation
To get started with Flask-SQLAlchemy, you’ll need to install Flask and Flask-SQLAlchemy. You can do this using pip:
pip install Flask Flask-SQLAlchemy02Setting Up Flask-SQLAlchemy
Once you have installed Flask-SQLAlchemy, you can set up your Flask application and configure the database. Here’s a basic example using SQLite:
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///example.db'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db = SQLAlchemy(app)
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}>'
if __name__ == '__main__':
db.create_all() # Create database tables
app.run(debug=True)
03Creating Models
In Flask-SQLAlchemy, models are Python classes that represent database tables. Each class inherits from db.Model, and its attributes are mapped to columns in the corresponding table. Here’s an example:
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}>'04Performing CRUD Operations
Creating Records
To add new records to the database, create an instance of the model and add it to the session:
new_user = User(username='john_doe', email='john@example.com')
db.session.add(new_user)
db.session.commit()
Reading Records
To query records from the database, use methods like query.all() and query.filter_by():
# Get all users
users = User.query.all()
# Get a specific user
user = User.query.filter_by(username='john_doe').first()
Updating Records
To update a record, modify the instance and commit the changes:
user = User.query.filter_by(username='john_doe').first()
user.email = 'john.doe@example.com'
db.session.commit()
Deleting Records
To delete a record, use the session.delete() method:
user = User.query.filter_by(username='john_doe').first()
db.session.delete(user)
db.session.commit()
05Example Application
Here’s a simple example application demonstrating basic CRUD operations:
from flask import Flask, request, jsonify
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///example.db'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db = SQLAlchemy(app)
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}>'
@app.route('/users', methods=['POST'])
def add_user():
data = request.get_json()
new_user = User(username=data['username'], email=data['email'])
db.session.add(new_user)
db.session.commit()
return jsonify({'message': 'User created successfully'}), 201
@app.route('/users', methods=['GET'])
def get_users():
users = User.query.all()
return jsonify([{'username': user.username, 'email': user.email} for user in users])
@app.route('/users/<int:id>', methods=['PUT'])
def update_user(id):
data = request.get_json()
user = User.query.get(id)
if not user:
return jsonify({'message': 'User not found'}), 404
user.email = data['email']
db.session.commit()
return jsonify({'message': 'User updated successfully'})
@app.route('/users/<int:id>', methods=['DELETE'])
def delete_user(id):
user = User.query.get(id)
if not user:
return jsonify({'message': 'User not found'}), 404
db.session.delete(user)
db.session.commit()
return jsonify({'message': 'User deleted successfully'})
if __name__ == '__main__':
with app.app_context():
db.create_all()
app.run(debug=True)
06Advanced Querying with Flask-SQLAlchemy
Flask-SQLAlchemy offers a rich set of tools for querying the database. You can perform complex queries, join tables, and utilize SQL functions to manipulate and retrieve data effectively.
Filtering and Sorting
To filter records, use methods like filter(), filter_by(), and order_by():
# Filter users by email
users = User.query.filter_by(email='john@example.com').all()
# Filter and sort users by username
users = User.query.filter(User.username.like('%john%')).order_by(User.username).all()
Pagination
For large datasets, you can paginate your queries to retrieve a subset of results at a time:
# Paginate results
page = 1
per_page = 10
pagination = User.query.paginate(page, per_page, error_out=False)
users = pagination.items
Joining Tables
You can perform joins between tables using relationships defined in your models. Here’s an example with two related models:
class Post(db.Model):
id = db.Column(db.Integer, primary_key=True)
title = db.Column(db.String(120), nullable=False)
user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
user = db.relationship('User', back_populates='posts')
User.posts = db.relationship('Post', back_populates='user')
# Query posts with user information
posts = Post.query.join(User).add_columns(User.username, Post.title).all()
Grouping and Aggregation
Flask-SQLAlchemy allows you to perform aggregation operations such as counting, summing, and grouping:
from sqlalchemy import func
# Count users
user_count = db.session.query(func.count(User.id)).scalar()
# Group by email domain
email_domains = db.session.query(
func.substr(User.email, func.instr(User.email, '@')).label('domain'),
func.count(User.id).label('count')
).group_by('domain').all()
07Conclusion
Flask-SQLAlchemy is a powerful tool that makes it easy to work with databases in Flask applications. By using models, queries, and pagination, you can manage your database in a more Pythonic and efficient way. This guide has covered the basics of setting up and using Flask-SQLAlchemy, performing CRUD operations, and advanced querying techniques. For more information, refer to the Flask-SQLAlchemy documentation and explore the Flask-SQLAlchemy GitHub repository for examples and further details.
