1. One-to-many relationships
In this section, we’ll start with an introduction to one-to-many relationships and discuss why they’re important. We’ll look at how to define a one-to-many relationship between two models using Flask-SQLAlchemy’s relationship() and ForeignKey() functions. An example could be a relationship between an eCommerce Order and its associated OrderLineItems. In an eCommerce context, one Customer can make many Orders, but each Order can only be made by one Customer.
class Customer(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(100))
email = db.Column(db.String(100))
orders = db.relationship('Order', backref='customer')
class Order(db.Model):
id = db.Column(db.Integer, primary_key=True)
order_date = db.Column(db.DateTime, default=datetime.utcnow)
customer_id = db.Column(db.Integer, db.ForeignKey('customer.id'))
Here, the Customer model has an orders attribute, which is a relationship to the Order model. The Order model, in turn, has a customer_id attribute, which is a foreign key referencing the Customer model.
The backref='customer' in the Customer.orders relationship means that each Order will also have a customer attribute, allowing us to access the Customer of an Order.
Let’s demonstrate this with a few examples:
# Create a new customer
new_customer = Customer(name='John Doe', email='john@example.com')
db.session.add(new_customer)
db.session.commit()
# Create a new order for the customer
new_order = Order(customer_id=new_customer.id)
db.session.add(new_order)
db.session.commit()
# Access the customer's orders
print(new_customer.orders)
# Access the order's customer
print(new_order.customer)In the example above, we create a new Customer and a new Order. Then, we demonstrate accessing the orders attribute of Customer and the customer attribute of Order.
This relationship enables us to navigate from a Customer to its Orders, and from an Order to its Customer, all with minimal code. SQLAlchemy handles the underlying SQL for us, allowing us to think more in terms of objects and less in terms of tables and rows.
Here is another relationship between the customer order and the items for each order. Here one order can have many line times but each line item will belong to one order
class Order(db.Model):
__tablename__ = 'orders'
id = db.Column(db.Integer, primary_key=True)
customer_id = db.Column(db.Integer, db.ForeignKey('customers.id'))
class OrderLineItem(db.Model):
__tablename__ = 'order_line_items'
id = db.Column(db.Integer, primary_key=True)
order_id = db.Column(db.Integer, db.ForeignKey('orders.id'))
product_id = db.Column(db.Integer, db.ForeignKey('products.id'))
2. Many-to-many relationships
Next, we’ll explore many-to-many relationships. We’ll demonstrate how to create a many-to-many relationship using an association table, which is a special type of table used to link the two related models.
In an eCommerce context, an excellent example of a many-to-many relationship is between Products and Categories. A single Product can belong to multiple Categories, and each Category can contain multiple Products.
To define a many-to-many relationship, we need to create an association table, like so:
product_categories = db.Table('product_categories',
db.Column('product_id', db.Integer, db.ForeignKey('product.id')),
db.Column('category_id', db.Integer, db.ForeignKey('category.id'))
)
class Product(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(100))
price = db.Column(db.Float)
categories = db.relationship('Category', secondary=product_categories, backref=db.backref('products'))
class Category(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(50))
n this setup, Product and Category are the two main models, and product_categories is the association table that links them together. The secondary parameter in the db.relationship call is used to specify this association table.
The backref attribute allows us to access the relationship in the opposite direction, so that we can get all the products associated with a category.
Here’s how you might use these relationships:
# Create some categories
shoes = Category(name='Shoes')
clothing = Category(name='Clothing')
db.session.add(shoes)
db.session.add(clothing)
db.session.commit()
# Create a product
sneakers = Product(name='Sneakers', price=59.99, categories=[shoes, clothing])
db.session.add(sneakers)
db.session.commit()
# Access the product's categories
print(sneakers.categories)
# Access the category's products
print(shoes.products)
print(clothing.products)
In this example, we create two Category instances and one Product instance. The Product is associated with both Category instances. We then demonstrate accessing the categories attribute of the Product and the products attribute of the Category.
This allows us to flexibly represent complex relationships between our models. Just like with one-to-many relationships, SQLAlchemy allows us to work with these relationships in an intuitive, Pythonic way.
3. Querying related data
Querying related data is a powerful feature of SQLAlchemy that allows you to retrieve data from related models in an intuitive way. Here’s how it works in the context of our eCommerce example.
Let’s say we have a Customer, Order, and Product models with relationships defined as below:
class Customer(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(100))
orders = db.relationship('Order', backref='customer')
class Order(db.Model):
id = db.Column(db.Integer, primary_key=True)
product_id = db.Column(db.Integer, db.ForeignKey('product.id'))
customer_id = db.Column(db.Integer, db.ForeignKey('customer.id'))
class Product(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(100))
orders = db.relationship('Order', backref='product')
We have already defined the relationships between these models, so we can now perform some interesting queries:
- Find all orders made by a specific customer:
# First, get the customer
customer = Customer.query.filter_by(name='John Doe').first()
# Then, access the customer's orders
orders = customer.orders
for order in orders:
print(order.id)
2. Find all customers who ordered a specific product:
# First, get the product
product = Product.query.filter_by(name='Sneakers').first()
# Then, access the product's orders and the customers who made them
for order in product.orders:
print(order.customer.name)
3. Find all products ordered by a specific customer:
# First, get the customer
customer = Customer.query.filter_by(name='John Doe').first()
# Then, access the customer's orders and the products in them
for order in customer.orders:
print(order.product.name)
These examples showcase the power of SQLAlchemy’s relationships. By defining relationships between our models, we can write queries that closely mirror the way we think about our data, rather than having to write complex JOINs manually. This makes our code easier to write, read, and maintain.
4. Eager and Lazy loading
Let’s explore eager and lazy loading in SQLAlchemy with the context of our eCommerce application.
“Eager” and “lazy” loading refer to when SQLAlchemy will load the data from the database. With lazy loading, SQLAlchemy will only load the data when you actually try to access it. With eager loading, SQLAlchemy will load the data as soon as the parent object is loaded.
Let’s consider the relationship between Customer and Order in our eCommerce example:
class Customer(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(100))
orders = db.relationship('Order', backref='customer', lazy='select')
class Order(db.Model):
id = db.Column(db.Integer, primary_key=True)
product_id = db.Column(db.Integer, db.ForeignKey('product.id'))
customer_id = db.Column(db.Integer, db.ForeignKey('customer.id'))
In this case, we’ve specified lazy='select' on the orders relationship. This is the default behavior and means that SQLAlchemy will only load the orders when we try to access customer.orders:
customer = Customer.query.get(1)
print(customer.orders) # SQLAlchemy loads the orders here
This is efficient in terms of memory usage, as SQLAlchemy doesn’t load any data that it doesn’t need. However, it can be inefficient in terms of database queries if you’re going to be accessing the relationship for many Customer objects at once.
In that case, you might want to use eager loading. You can specify eager loading using joinedload:
from sqlalchemy.orm import joinedload
customers = Customer.query.options(joinedload(Customer.orders)).all()
for customer in customers:
print(customer.orders) # The orders are already loaded!
With joinedload, SQLAlchemy will perform a SQL JOIN to load the Customer and Order data all at once. This can be more efficient in terms of database queries if you’re going to be accessing the orders relationship of each Customer, as it reduces the total number of queries.
In general, whether to use eager or lazy loading will depend on the specifics of your use case. It’s important to understand how each works so that you can make an informed decision.
Remember, relationships are a fundamental part of relational databases, and understanding how to work with them in Flask-SQLAlchemy is key to building efficient and effective web applications. So take your time to understand these concepts and experiment with the code examples provided.
In the next chapter, we’ll look at advanced querying techniques, such as aggregation, grouping, and more. Stay tuned!