In this post, we’re going to explore how to perform the basic operations that make up the backbone of any application: Creating, Reading, Updating, and Deleting (CRUD) records. We’ll be using Flask-SQLAlchemy and our e-commerce application model as a reference.
Here are the Supplier and Product models for our e-commerce application:
class Supplier(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(50), nullable=False)
products = db.relationship('Product', backref='supplier', lazy=True)
class Product(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(100), nullable=False)
supplier_id = db.Column(db.Integer, db.ForeignKey('supplier.id'), nullable=False)
Creating the Database/Tables
Now that we have our models defined, we can create the database using db.create_all(). This will create the tables for our models in the database
db.create_all()That’s it! We now have an SQLite database set up with tables for our Supplier and Product models. You should call db.create_all() only once when you first set up your application, and you should make sure to call it before you start performing any operations on the database.
Remember, for a real-world application, you would typically not use SQLite or db.create_all(), but instead use a production-grade database system and a database migration tool like flask-migrate to handle changes to your schema over time.
The db.create_all() command is typically run once when you set up your application, to create the necessary tables in your database. You can run it in different ways depending on your application’s setup:
- In a setup script: If you’re setting up your database for the first time, you might include
db.create_all()in a setup script. This script would be a Python file that you run once to set up your application. For instance:
# setup.py
from myapp import db
db.create_all()Then you would run this script with python setup.py or python3 setup.py
2. In the Flask shell: If you’re using the Flask shell, you can run db.create_all() directly in the shell:
$ flask shell
>>> from myapp import db
>>> db.create_all()If the tables are not created, then you need to use app.app_context() before db.create_all() as shown below. If you are not clear on what app_context is , here is a quick rundown
3. In an application factory: If you’re using the application factory pattern, you might call db.create_all() inside your application factory function, but guarded by a condition so it only runs in a development environment or when explicitly requested:
# Inside your application factory
def create_app(config_name):
# ...
if app.config['DEBUG']:
with app.app_context():
db.create_all()
# ...
return app4. As part of a migration: In a production setting, you would typically not use db.create_all() at all, but instead use a database migration tool like Flask-Migrate. This tool keeps track of changes to your database schema and applies them in a controlled way. With Flask-Migrate, you would create a migration script with flask db migrate, then apply it with flask db upgrade.
Remember, db.create_all() creates tables for all models, but it doesn’t update existing tables if you change your models. When you start developing your application it grows continually and, you will keep adding new features, new columns to your tables. That’s why a migration tool like flask-migrate is preferred for production applications.
Creating new records
To create a new record, you first create a new instance of your model, then add and commit it to the database:
# Create a new supplier
new_supplier = Supplier(name='BestSupplier')
db.session.add(new_supplier)
db.session.commit()
# Create a new product
new_product = Product(name='Awesome Product', supplier_id=new_supplier.id)
db.session.add(new_product)
db.session.commit()
In the above example, we create the supplier first and create the product for that supplier immediately. In real world scenarios we will first query an existing supplier from the database and then we create new products for that supplier. Lets modify the above example to add products for an existing supplier.
A quick introduction to db.session()
In SQLAlchemy, the Session is the core interface to the database. It’s a holding zone for all the objects which you’ve loaded or associated with it during a transaction. The Session allows you to make queries to the database and also determines when changes should be written to the database.
In Flask-SQLAlchemy, db.session is a session object tied to the Flask app. Here’s what it does:
- Queries:
db.session.query(Model)allows you to make queries to the database. You can add filter conditions, order by clauses, etc. to this query to fetch the objects you want. - Adding records:
db.session.add(instance)stages a new record to be added to the database. Theinstanceshould be an instance of a SQLAlchemy model. The record isn’t actually written to the database until you calldb.session.commit(). - Updating records: If you’ve fetched an object from the database and modified it, the
Sessionautomatically schedules the changes to be written back to the database when you calldb.session.commit(). - Deleting records:
db.session.delete(instance)stages a record to be deleted from the database. The record isn’t actually deleted until you calldb.session.commit(). - Committing changes:
db.session.commit()writes all staged changes (fromadd(), modifications to loaded objects, anddelete()) to the database. If an error occurs,commit()will “roll back” to the start of the transaction, meaning all the changes in the transaction will be discarded. - Rolling back changes: If you’ve made some changes that you don’t want to commit, you can call
db.session.rollback()to discard all staged changes and modifications to loaded objects. This brings theSessionback to the state of the lastcommit().
The Session also does more advanced things like maintaining a “unit of work”, which is a set of changes that should be applied together, and managing transactions, which are a way of ensuring that multiple changes happen all together or not at all. It also helps with managing database connections and resources.
In short, db.session is your main interface for making changes to your database with Flask-SQLAlchemy.
Here’s an example of its usage:
Selecting a Supplier
First, let’s select a Supplier from our database. We’ll use the get method, which retrieves a record by its primary key (in this case, the id):
# Get a supplier by ID
supplier = Supplier.query.get(1)About query.get()
The get() method in SQLAlchemy is used to fetch an instance that has a certain primary key. When you say query.get(1), you’re asking SQLAlchemy to return the object with a primary key of 1.
The get() method is very efficient because it will first look at the objects already loaded in the session to see if the requested object is there before issuing a SELECT statement to the database.
In the query from the Supplier table, this will return the supplier instance that has a primary key of 1, or None if no such supplier exists. Note that Supplier.query is a shortcut provided by Flask-SQLAlchemy for db.session.query(Supplier). So Supplier.query.get(1) is equivalent to db.session.query(Supplier).get(1).
Another important point to note is that get() only works if the class has a single primary key column. If the table’s primary key is composed of multiple columns, you will have to use get() with a tuple that specifies a value for each column, in the order they’re defined in the table. For example, if your Supplier table had a composite primary key made up of id and email, you would do:
user = Supplier.query.get((1, 'supplier@example.com')This would return the Supplier with id=1 and email='supplier@example.com', or None if no such user exists.
Creating a New Product for the Supplier
Now, let’s create a new Product for this Supplier. Instead of setting supplier_id directly, we can set the supplier attribute to our supplier instance:
# Create a new product for this supplier
new_product = Product(name='Amazing Product', supplier=supplier)
db.session.add(new_product)
db.session.commit()About db.session.add()
The db.session.add() method in SQLAlchemy is used to queue up a new record to be added to the database.
When you create a new instance of a SQLAlchemy model, that instance is not immediately saved to the database. It exists only in your application’s memory. In our new_product example:
new_product = Product(name='Amazing Product', supplier=supplier)At this point, new_product is an instance of the Product model, but it isn’t in the database yet. You can verify this by checking new_product.id, which should be None because the instance hasn’t been assigned a primary key by the database.
If you want to save <code>new_product to the database, you have to add it to the session:
db.session.add(new_product)Now, new_product is queued up to be inserted into the database. But it still isn’t actually in the database yet. db.session.add() doesn’t immediately write to the database, it only schedules the instance to be inserted when the transaction is committed.
To commit the transaction and actually save new_product to the database, you have to call db.session.commit():
db.session.commit()After this line, new_product is finally saved in the database. You can verify this by checking new_product.id, which should now be the primary key assigned to the new user by the database.
So to summarize, db.session.add() stages new records to be added to the database, and db.session.commit() actually writes those records to the database.
Reading data from the database
To read data from the database, you can use the query attribute of your model class:
# Get all suppliers
suppliers = Supplier.query.all()
# Get a supplier by ID
supplier = Supplier.query.get(1)
# Get all products of a supplier
products = supplier.products
# Get the supplier of a product
supplier_of_product = Product.query.get(1).supplier
Updating records
To update a record, you first get the record, then change its attributes, and commit the changes to the database:
# Get a supplier
supplier = Supplier.query.get(1)
# Update the supplier
supplier.name = 'BetterSupplier'
db.session.commit()
# Get a product
product = Product.query.get(1)
# Update the product
product.name = 'Even Better Product'
db.session.commit()
Deleting records
To delete a record, you first get the record, then delete it from the session and commit the changes to the database:
# Get a product
product = Product.query.get(1)
# Delete the product
db.session.delete(product)
db.session.commit()
If you try to delete a Supplier that still has Products, SQLAlchemy will prevent you from doing so because it would break the referential integrity of the database. You would first need to delete or re-assign all of the Supplier‘s Products before you could delete the Supplier.
Remember, SQLAlchemy keeps track of all the changes you make to your models within a session, and when you call db.session.commit(), it writes all those changes to the database at once. This means you can perform multiple operations and have them all written in a single transaction, which can be more efficient and safer than performing each operation individually.
That’s it for the basics of CRUD operations with Flask-SQLAlchemy! In the next post, we’ll delve deeper into more advanced queries and how to manage complex relationships between models. Stay tuned!