In the final part of our Flask-SQLAlchemy series, we’re going to delve into some best practices and tips to help you effectively work with Flask-SQLAlchemy. Throughout our series, we’ve been using the example of an e-commerce application, which we’ll continue with in this post.
Model Design Patterns
- Base Model (Declarative of db.Model): Use
db.Modelas the base for all of your models. This allows you to leverage SQLAlchemy’s declarative syntax for defining models and it also automatically gives your models certain useful methods and properties. If you are using sqlalchmey outside the flask context, then usingdeclarative_basefrom SQLAlchemy allows you to create models easily and in a Pythonic way. It enables you to use a class-based system to define your tables and relationships.
declarative_base and db.Model serve a similar purpose — they’re both used to define models that map to database tables. Both are used to generate a new base class for declarative models. An application will usually use one of them, not both.
The difference between the two lies in the context they’re used in.
declarative_base is a function provided by SQLAlchemy, which is a general-purpose ORM tool for Python that can be used with any web framework or even outside a web framework. When you use declarative_base, you’re using SQLAlchemy in its “raw” form. Here’s an example:
from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Product(Base):
__tablename__ = "products"
id = Column(Integer, primary_key=True)
name = Column(String)
When you’re working with Flask, db.Model is usually the better choice because it provides better integration with Flask. However, if you’re writing code that needs to work outside of a Flask context (for example, a script that does some batch data processing), you might choose to use declarative_base and SQLAlchemy’s core API so that your code isn’t tied to Flask.
In terms of functionality, db.Model and declarative_base are quite similar. The main difference is in how they integrate with the rest of your application.
- Migrations: Use Alembic or Flask-Migrate for database migrations. Alembic and Flask-Migrate are powerful database migration tools that integrates well with SQLAlchemy. It provides you with the ability to upgrade and downgrade your database schema programmatically, keeping track of past versions.
- Use Indexed Fields for Quick Searches: Creating an index on a column can significantly speed up searches and queries involving that column.
from flask_sqlalchemy import SQLAlchemy
db = SQLAlchemy()
class Product(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String, index=True)- Mixins: If multiple models share the same fields, consider using a mixin to define those common fields. For example, if all your models have
created_atandupdated_atfields, you can define aTimestampMixinwith these fields and have your models inherit from it. This can help reduce redundancy in your code. Here is an example of a mixing:
from flask_sqlalchemy import SQLAlchemy
from datetime import datetime
db = SQLAlchemy()
class TimestampMixin:
created_at = db.Column(db.DateTime, default=datetime.utcnow)
updated_at = db.Column(db.DateTime, onupdate=datetime.utcnow)
class User(db.Model, TimestampMixin):
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(80), unique=True)
Query Patterns
SQLAlchemy’s ORM is very convenient, but it can sometimes generate less-than-optimal SQL. To ensure you’re getting the best performance:
- Optimize Query Performance:
- Use joinedload() or subqueryload() for eager loading when you’re going to access related objects.
- Use contains_eager() when you’ve already joined the data you want to load.
- Be careful with lazy=’dynamic’ relationships, as they can lead to additional queries. Learn more about lazy loading
- When dealing with many rows, use yield_per() to load data in chunks rather than all at once.
Aside: Find the difference between joinedload(), subqueryload() and contains_eager
<strong>yield_per()</strong> is a method provided by SQLAlchemy that can be used to fetch large query results in batches, rather than loading all rows into memory at once. This can significantly reduce memory usage when dealing with large result sets.
Here’s an example:
orders = db.session.query(Order).yield_per(50)
for order in orders:
# process each order
...
In this example, yield_per(50) instructs SQLAlchemy to load rows in batches of 50. This way, even if the query matches millions of rows, only 50 rows will be loaded into memory at a time, which can make a significant difference in memory consumption.
- Batching: If you’re inserting or updating a large number of rows at once, consider using SQLAlchemy’s
bulk_insert_mappingsorbulk_update_mappingsmethods for better performance. Learn more about batching with an example - Hybrid Attributes: Consider using SQLAlchemy’s
hybrid_propertyandhybrid_methoddecorators to define class-level properties and methods that can be used both at the instance level and at the class level (for queries).
Hybrid attributes are a way to create a class-level property in your SQLAlchemy model that can be used in queries. You can think of them like virtual columns on your model that are based on calculations rather than stored data.
Hybrid attributes can be created with the @hybrid_property decorator, and the @hybrid_method decorator is for class-level methods.
Let’s see an example to clarify these concepts.
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy.ext.hybrid import hybrid_property
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:////tmp/test.db'
db = SQLAlchemy(app)
class Product(db.Model):
id = db.Column(db.Integer, primary_key=True)
price = db.Column(db.Float)
tax = db.Column(db.Float)
@hybrid_property
def price_with_tax(self):
return self.price + self.tax
@price_with_tax.expression
def price_with_tax(cls):
return cls.price + cls.tax
@app.route('/')
def home():
# Create a product
product = Product(price=100.0, tax=15.0)
db.session.add(product)
db.session.commit()
# Query for products where price with tax is greater than 100
expensive_products = Product.query.filter(Product.price_with_tax > 100.0).all()
return ', '.join(str(product.price_with_tax) for product in expensive_products)
if __name__ == "__main__":
db.create_all()
app.run(debug=True)
In this Flask application, I’ve used a Product model with price and tax attributes, and I’ve defined a price_with_tax hybrid property for it. This allows me to compute the price with tax either at the instance level (when dealing with a single Product object) or at the class level (when making a query).
In the home route, I’m creating a Product with a price of 100.0 and a tax of 15.0, and then querying for products where the price with tax is greater than 100.0. The resulting page will display the prices with tax for these products.
In SQLAlchemy’s hybrid properties, the @property_name.expression decorator is used to define a class-level SQL expression that can be used in queries. The function defined under this decorator should be a class method, and hence it takes cls as its first argument.
So, cls in this context would represent the class in which this method is defined. When you’re calling cls.price and cls.tax, you’re referencing the price and tax columns of the class/table in the context of a SQL query.
The price_with_tax and is_expensive can be used in the instance context as well as in the query context.
In this way, Flask-SQLAlchemy allows us to apply hybrid attributes and methods similar to SQLAlchemy. This makes complex calculations more manageable and reusable throughout the application.
We can also defining an instance method that sets the price_with_tax attribute. This is fine if you’re only ever going to use this logic in the context of an instance of your model, i.e., you’ve already fetched a record from the database and now you’re operating on it in Python.
This is useful in two primary ways:
- Performance: If you need to filter, sort, or aggregate your data based on some derived property (like
<code>price_with_taxin your example), it’s much more efficient to do this at the database level, rather than fetching all records into Python and then filtering/sorting/aggregating them there. - Consistency: If the logic for deriving a property (like
expires_onis somewhat complex, it’s helpful to define this logic in one place and use it consistently across your application. With a hybrid attribute or method, you can define this logic once and then use it in your Python code and in your database queries.
However, SQLAlchemy’s hybrid attributes and methods offer the added advantage of being usable at the class level as well, meaning you can incorporate them into queries that run at the database level.
That said, for simpler use cases like the one you’ve mentioned (where you’re only operating at the instance level and the logic is straightforward), it’s perfectly fine to just use a regular instance method. The power of hybrid attributes and methods really becomes apparent when you’re dealing with more complex situations.
Avoid N+1 Problem
The N+1 problem is a common performance pitfall where you load a list of objects, then access related data in a loop, causing an extra query for each object.
# Naive way: causes N+1 problem
products = Product.query.all()
for product in products:
print(product.supplier.name)
# Better way: uses joined loading to avoid N+1 problem
products = Product.query.options(joinedload(Product.supplier)).all()
for product in products:
print(product.supplier.name)
The second example does a single, larger query instead of many small queries.
Security Considerations
- SQL Injection: Always use SQLAlchemy’s query API or parameterized SQL to construct queries. Never build SQL queries using string formatting or concatenation with untrusted input to prevent SQL injection attacks. Example given below a.
- Data Validation: Use SQLAlchemy’s event system to validate data before it’s saved to the database. You can use the
@validatesdecorator to define validation methods on your models. - Sensitive Data: Be careful when storing sensitive data. Passwords should be hashed using a strong cryptographic hash function, not stored in plain text. Other sensitive data may need to be encrypted, depending on your application’s requirements and the regulations you need to comply with.
a. Avoiding sql injection
# Unsafe: vulnerable to SQL Injection
Product.query.filter("name = '%s'" % name)
# Safe: SQLAlchemy automatically escapes input
Product.query.filter(Product.name == name)
In this example, the TimestampMixin defines created_at and updated_at fields that automatically get the current time when a new row is inserted or updated. The User model inherits from both db.Model and TimestampMixin, so it gets these timestamp fields in addition to the ones it defines itself.
Note: It’s important to provide datetime.utcnow without parentheses, i.e., datetime.utcnow and not datetime.utcnow(). If you use datetime.utcnow(), the function will execute once when the server starts, and all created_at and updated_at values will have the same timestamp. When you use datetime.utcnow without parentheses, SQLAlchemy will call the function every time a new row is inserted or updated, which is what we want.
In conclusion, Flask-SQLAlchemy is a powerful tool that can make working with databases in Flask much more convenient. We hope this series of posts has given you a good understanding of how to use Flask-SQLAlchemy effectively in your projects. Stay tuned for more tips and guides on other Python topics!