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 using
declarative_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.
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,
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
updated_atfields, you can define a
TimestampMixinwith 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)
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.
<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_update_mappingsmethods for better performance. Learn more about batching with an example
- Hybrid Attributes: Consider using SQLAlchemy’s
hybrid_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
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).
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.
cls in this context would represent the class in which this method is defined. When you’re calling
cls.tax, you’re referencing the
tax columns of the class/table in the context of a SQL query.
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.
- 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
updated_at fields that automatically get the current time when a new row is inserted or updated. The
User model inherits from both
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
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!