How to Query Data and Insert Database Rows With SQLAlchemy

Project Source Code

Get the project source code below, and follow along with the lesson material.

Download Project Source Code

To set up the project on your local machine, please follow the directions provided in the README.md file. If you run into any issues with running the project source code, then feel free to reach out to the author in the course's Discord channel.

Inserting rows
#

To be able to insert rows into the database, we need to create the tables on the database. SQLAlchemy provides us with a way to do that by looking at the models file and inferring what the database schema should be.

To start an interactive Flask sessions where we can interactively write Python, Flask provides us with a way to start an interactive shell

In your terminal (with the virtualenv activated), run the following command.

$ FLASK_ENV=development FLASK_APP="yumroad:create_app" flask shell

This launches a Python interactive shell with the application already initialized and imported as app.

Our first step will be to actually create the database and the associated tables. SQLAlchemy provides us with a method to do that with a method called create_all() which creates the database if necessary and any tables.

To run create_all(), we will need to import db from extensions since that is where our instance of Flask-SQLAlchemy has been defined. At this point, if we run db.create_all(), SQLAlchemy will set up a blank database, but it has no idea what models to create because the module containing all of the models (in models.py) has not been loaded/imported, so we will also need to import our models.

note
#

How does SQLAlchemy figure out which tables to create?

When a class that inherits from the base SQLAlchemy model class is defined, SQLAlchemy registers it as a table that may need to be created. If the class is never evaluated (because no code imports the models), SQLAlchemy is unaware of the existence of those models.

Another solution to this issue would have been to import the models directly in the yumroad/__init__.py file, since that file is loaded when we initialize the application

>>> from yumroad.extensions import db
>>> from yumroad.models import Product
>>> db.create_all()

At this point, a file named dev.db will be created in the yumroad directory (as specified in our configuration) and it will have the schema for the Product. At this point, we are able to insert and query for Product records.

Inserting
#

As mentioned earlier our ORM, SQLAlchemy, allows us to interact with models like Python objects. This interface also applies to creating new records. To create a new record, we first need to initialize and instance of the model class we're creating. In this case, the class name is Product and the fields that we've defined for it that require manual input are name and description. The id field is inferred to automatically be filled in by the database because it's configured as a primary key which means that the database will automatically assign a unique ID to each record.

To instantiate a record, we treat our model like any other Python class and pass in the named arguments for the fields we've defined:

Product(name="My Art", description="A random photo from my portfolio")

This instantiation doesn't automatically create a record in the database however. SQLAlchemy provides us with the concept of a session, which is essentially a shopping cart for changes we'd like to make to the database. When we're done with all of our changes, we can "checkout" by committing our changes. This distinction is an optimization that ensures we only have to talk to the database (which can take some time and slow down our program) only when necessary.

The session is the place where our Python objects are stored until we commit our changes, at which point SQLAlchemy figures out and executes the queries to our database to implement our associated changes. Flask-SQLAlchemy provides us with a convenient way to access the current database session through the session attribute on our instance of SQLAlchemy (which we've imported as db)

>>> art = Product(name="My Art", description="A random photo from my portfolio")
>>> print(art.name)
My Art
>>> art.id == None
True
>>> db.session.add(art)
>>> db.session.commit()
>>> art.id == None
False
>>> print(art.id)
1

Querying
#

SQLAlchemy provides methods for us to make queries and takes care of translating it into SQL. The database session that SQLAlchemy manages is also our interface to query the database. We can pass in the model(s) that we want to query against and what kind of results we would like.

To query for all records from the Product model, we would pass in the model to the query method and ask SQLAlchemy to fetch all records using the all() record. This returns a list of Product objects corresponding to the actual records in the database.

>>> db.session.query(Product).all()
[<Product 1>]

This query is essentially translated to SELECT * from product.

You can see exactly what queries SQLAlchemy is issuing by setting the SQLALCHEMY_ECHO configuration variable Within yumroad/config.py, if you add a configuration for this to true under the DevConfig SQLALCHEMY_ECHO = True, SQLAlchemy will print out the exact query it is issuing.

Since many queries only operate on a single model, Flask-SQLAlchemy provides a shortcut to the query method by accessing it through the model class Product.query.

>>> Product.query.count()
1
>>> Product.query.all()
[<Product 1>]
>>> product = Product.query.all()[0]
'My Art'
>>> art.id, art.name
(1, 'My Art')
>>> Product.query.get(1)
<Product 1>
>>> Product.query.get(2) == None
True
MethodEquivalent SQLDescription
.count()SELECT COUNT(*) from ...Get a count of all records for this query
.all()SELECT * from ...Get all records in this query
.get(1)SELECT * from ... where id=1Get the record where the argument matches the primary key of the table

Chaining and filtering Results
#

SQLAlchemy won't actually issue our query until we indicate we're ready to actually fire off the query to the database. Until then we will be working with Query objects, which store SQLAlchemy's internal representation of what kind of query should be created. When we are satisifed with the query that we have built, we can have SQLAlchemy fire it off to the database by using specific methods that actually will go and fetch the record(s) like all() or first().

When get the base query class with Product.query, we start off with a basic query that simply would fetch all records if asked to (which is what we saw when we ran Product.query.all())

We can constrain the Query object by using some of the methods that SQLAlchemy provides, such as filter_by. If we wanted to find all products with the name of My Art, we could create that query by running Product.query.filter_by(name='My Art').all().

This lesson preview is part of the Fullstack Flask: Build a Complete SaaS App with Flask course and can be unlocked immediately with a single-time purchase. Already have access to this course? Log in here.

This video is available to students only
Unlock This Course

Get unlimited access to Fullstack Flask: Build a Complete SaaS App with Flask with a single-time purchase.

Thumbnail for the \newline course Fullstack Flask: Build a Complete SaaS App with Flask

  • [00:00 - 00:15] In the last video, we set up SQL-Alchemy and we had it create a database for us . In this video, we're going to go over how to work with SQL-Alchemy to insert and query and update records.

    [00:16 - 00:25] We talked about how ORMs allow us to interact with our database as if they were Python objects. This interface also applies to creating new records.

    [00:26 - 00:38] To create a new record, we're going to initialize an instance of the model class we defined. In this case, the class name is product and the three fields we've defined for it can be input as well.

    [00:39 - 00:48] In our case, we want to input name and description. The ID field is going to be inferred and automatically be filled in by that database since it's a primary key.

    [00:49 - 01:08] So to instantiate our record, we're going to treat our model just like any other Python class and pass in the name arguments for the fields we've defined. So if we want to create a new product named myART, we can do that.

    [01:09 - 01:21] Then we can also pass in a description such as our random photo. Now that we've done that, ourART is an object in memory.

    [01:22 - 01:32] What we want to do is eventually get this into the database. The way we're going to do that is add it to something SQL-Alchemy calls a session.

    [01:33 - 01:46] The way you can think about a session, it's kind of like a shopping chart for changes that you're making to the database. When we're done with all of our changes, we can check out our changes by committing it to the database.

    [01:47 - 02:05] This distinction is a slight optimization that ensures that we only have to talk to the database, which takes some time, especially over the network, and slow down our program. And we can only talk to it when necessary instead of communicating to the database for every single query or change we want to make.

    [02:06 - 02:18] So the way we're going to do that is say dv.session.add.art. And then we're going to say dv.session.commit to actually commit our changes to the database.

    [02:19 - 02:30] And you can see the command that SQL-Alchemy is running here. It's actually running an insert query into the database.

    [02:31 - 02:46] Now we can see our.id is actually set to a value. All of the alchemy also provides us with an interface to make queries and takes care of translating that into SQL.

    [02:47 - 03:02] The database session in SQL-Alchemy manages our interface to query the database as well. The way we can do this is we can pass in models into the session query and ask for a specific field.

    [03:03 - 03:15] For example, if we want all of the records, we can ask for them or if we want a count of the records, we can ask for them. We can also look into specific fields.

    [03:16 - 03:29] So if we want to get one of the records that has id one, we can do that and that gets us our data. Then we can do something like .name to get the data as well.

    [03:30 - 03:43] If you want to know about all of the fields, here's a quick overview and similar to the query that gets submitted. So for example, we can do .count to get the count of what we want.

    [03:44 - 04:05] Going back in our terminal, we can do things like this and see what happens there. If we try and get something that doesn't exist, it's going to return none, which makes sense.

    [04:06 - 04:18] Another thing we can do is we can chain and filter requests. Just because I type db.session.query, doesn't mean a query actually gets submitted.

    [04:19 - 04:31] That means I can do things like db.session.query and then add some other things like filter by name is equal to myart. That still doesn't run a query.

    [04:32 - 04:40] It only runs a query when we're actually asking it to fetch results. So if I run .all, that's when it actually runs a query.

    [04:41 - 04:59] Or if I do .count, that's when it actually gets the issue of query. If you're looking for the specific fields that we can use here, we can do things like limit first, filter, filter by.

    [05:00 - 05:17] These are all things that we can chain onto queries to help to constrain the SQL query that we're doing. Then when we do things like .all or .first, that's what actually gets an issue of query.

    [05:18 - 05:40] Now as a shortcut because we're using flassequark, we can do things like product.query instead of doing db.session.query product. So we can do things like product.query.all or product.query.count.

    [05:41 - 05:49] That's a little easier. We can also use SQL. G me to edit the values of records.

    [05:50 - 06:07] So if we're doing something like our arc is equal to product.query.first, we now have a field here and we can say heart.name. But we can change that name to say heart.name is equal to their heart.

    [06:08 - 06:16] Now all we've done at this point is we've only changed this object that exists in memory. We haven't persisted our change to the database.

    [06:17 - 06:27] So what we can do is we can add it to our session just to make sure it's bare and then we can say db.session.commit. Perfect.

    [06:28 - 06:40] Now technically adding db.session isn't really required. So I can do things like art is equal to test name and I can do db.session.

    [06:41 - 06:53] commit. It's still going to issue that query. The reason is because SQL is already keeping track of the changes in the object in the current session since we just fetch them.

    [06:54 - 07:08] So we still do this as best practice to make sure the objects haven't left the session. And if the object's already in our shopping chart of changes, then that line just doesn't do anything and it's fine.

    [07:09 - 07:17] In the case of when we're creating records, SQL company doesn't know about that instantiation. It doesn't know that we actually intend to commit it.

    [07:18 - 07:24] So when we're creating new records, we definitely have to add things to our session. Okay.

    [07:25 - 07:36] And now if we've created an object, we're also able to delete objects as well. So we can do things like our art product there.

    [07:37 - 07:50] We can do db.session.deleteheart and then we can do db.session.commit. And now that we've done that, we can do product.query.count and see how many products are left.

    [07:51 - 07:55] And there are no more products because we just deleted them. Okay.

    [07:56 - 08:07] So the life cycle of a database session is pretty interesting. You might be wondering how SQL company is keeping track of everything in memory .

    [08:08 - 08:22] This is part of the magic of SQL company, but it also exposes some interface that we can use here. Basically SQL company keeps an internal ledger of what objects are in the session and which ones have had changes made to them.

    [08:23 - 08:46] When we commit the session and have the database persist all of the changes we made, SQL alchemy marks all of the changes, all the models in the session is expired, which means that the next time you attempt to access an attribute from a model object, SQL company is going to issue a new query to get the latest version of the record. There are a few ways that you can manually manage the session.

    [08:47 - 09:03] Though often you'll just let FASC alchemy handle the work of creating and managing the session, the most common methods that we'll use are commit, rollback, and refresh. So rollback just rolls back all of the changes to mean your session and commit saves them.

    [09:04 - 09:17] db.refresh.art reloads the object attributes from the query. Okay. Next up is how to test our models.

    [09:18 - 09:26] So we're back in our code editor here. Let's go and follow the test config setup we had before and we've followed earlier.

    [09:27 - 09:35] So conch test.py is something we're going to have to create as well as a test folder. And inside of conch test, we're going to have to define a few fixtures.

    [09:36 - 09:45] The first one that we're going to want to define is the one that creates the application. So that we're going to say from yumroad import create app.

    [09:46 - 09:53] And we're going to create a function called app here that returns create app. And we're going to run in a test setting.

    [09:54 - 10:03] Then we're going to write that as a fixture. Next, we're going to create a fixture that helps us create the database.

    [10:04 - 10:15] And the way we're going to do that is first off, we're going to import the database. So from yumroad dot extensions, import DB.

    [10:16 - 10:22] Then we're going to save create a fixture here. The name of the fixture is going to be init database.

    [10:23 - 10:29] Now this name can be whatever you want, but we're going to be using it all over the place. And I think in it, DB is pretty clear about what happens.

    [10:30 - 10:37] What we want to do is we want to run create all just like we did. And then we're going to use a yield block to actually run our test.

    [10:38 - 10:49] And then after we run our test, we're going to want to drop all of the changes we made to the database in this particular test. This is a cool use of yield here.

    [10:50 - 11:04] And if you want to learn more about how yield works, you can look up Python generators. It's a pretty neat setup that allows us to neatly encode setup, running a test and tear down all inside of one fixture.

    [11:05 - 11:12] Okay, so let's go and create a test for our models. I'm going to call this one test product dot py.

    [11:13 - 11:22] Okay. And then inside of this, we're going to create a function that allows us to test something like creating the product.

    [11:23 - 11:26] We're going to use a few fixtures here. The first one we're going to use comes from pytest flask.

    [11:27 - 11:39] And that actually just sets up our application and we've seen client before as something that also allows us to make HTTP request for application. So it's useful if we need to do that.

    [11:40 - 11:50] For these model tests, all we'll need to make to ensure is that the application is actually set up, which client does. Then the next one is init database.

    [11:51 - 12:08] We're going to need to have an actual database in order to test product creation. We're also going to have to import our model from the YumRoad folder.

    [12:09 - 12:15] Great. Now, what we're going to have to do is we're going to make some of search statements.

    [12:16 - 12:34] So we're going to say something like product dot query dot count is equal equal to zero. Now if we go back in our terminal, we can run pytest and have it run.

    [12:35 - 12:47] Notice that an error popped up here and it's talking about a configuration variable. Let's go and go into VS code and actually just set that configuration variable to false since we don't use that feature anyway.

    [12:48 - 13:01] All right, going back in our terminal, it's going to run pytest and you can see that the test passed. Okay, going back into our test, this actually doesn't test that a product is created.

    [13:02 - 13:04] So we're going to have to do that. Let's go and create a product here.

    [13:05 - 13:19] We can call it. And maybe this book is about a house hunting detective.

    [13:20 - 13:27] Developers often like to be cheeky in their test names. So we can continue that tradition here.

    [13:28 - 13:52] Okay, so if we've added a book and committed it at this point, if we do product dot query dot count, we should start that it's one. And then if we do assert product dot query dot first dot name, that we're going to start that that's equal to book dot name.

    [13:53 - 14:00] All right, that looks like a pretty decent test. So let's go back over into our terminal and one our test suite.

    [14:01 - 14:21] Ah, a common error in our test suite, so we'll go back and realize we have to import DB. That's actually something we can import from YumRoad.models, but we can also just import it from extensions like that.

    [14:22 - 14:33] Okay, so our test pass, which is great. Now we can have pi test run a full code coverage report at this point.

    [14:34 - 14:43] And you can see that it covers almost all of our code, except it doesn't cover that validation. So let's go ahead and write a test there as well.

    [14:44 - 14:52] All right, so we're going to call this test name validation. And again, use the same pictures.

    [14:53 - 15:09] Here, we're going to say with pi test, which we're going to need to import. DAW raises and we expect to trace the value error.

    [15:10 - 15:32] And it's just going to be raised at the creation of the object. All right, so let's go and run our test suite and see what happens.

    [15:33 - 15:45] Okay, so we ran it and we have 100% code coverage, which is great. Sigma alchemy also allows us to do things like creating indices on fields.

    [15:46 - 16:00] So for example, if we wanted to index by the name, all we would have to do is set index equals true true when we're creating the database. Similarly, it's possible to import composite indices, which are indices comprised of multiple keys.

    [16:01 - 16:14] And when we do that, we just specify that at a class level field and we give it a name, for example, name and description. And then we just specify the names of the columns.

    [16:15 - 16:24] So the names of the columns are coming from the attribute names we're giving here. Okay, we don't particularly need those, so we'll just leave those alone.

    [16:25 - 16:28] Now there are a few things we haven't talked about here. One of them is foreign keys.

    [16:29 - 16:35] So for building relationships, we might want to have foreign keys at a database level. We'll talk about those later on.

    [16:36 - 16:46] The other thing we haven't talked about as well is what happens when you change your schema, especially if you're in production, and how do we handle those? We do something called migrations and we'll talk about those later too.

    [16:47 - 16:53] However, in the next video, we're going to try and integrate this model into our web application. (silence)