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
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
That’s it! We now have an SQLite database set up with tables for our
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.
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 app
4. 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.
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.
db.session is a session object tied to the Flask app. Here’s what it does:
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. The
instanceshould be an instance of a SQLAlchemy model. The record isn’t actually written to the database until you call
- 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 call
- Deleting records:
db.session.delete(instance)stages a record to be deleted from the database. The record isn’t actually deleted until you call
- Committing changes:
db.session.commit()writes all staged changes (from
add(), modifications to loaded objects, and
delete()) 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 the
Sessionback to the state of the last
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.
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
# Get a supplier by ID supplier = Supplier.query.get(1)
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.
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
Supplier.query.get(1) is equivalent to
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
user = Supplier.query.get((1, 'firstname.lastname@example.org')
This would return the Supplier with
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
# Create a new product for this supplier new_product = Product(name='Amazing Product', supplier=supplier) db.session.add(new_product) db.session.commit()
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:
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
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
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()
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
Products before you could delete the
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!