Building Data-Driven Web Apps with Flask and SQLAlchemy Transcripts
Chapter: Using SQLAlchemy
Lecture: Querying data: Latest releases
Login or
purchase this course
to watch this video and the rest of the course contents.
0:00
We have our little stat slice working just fine but the pieces here, not so much. Remember, this is just fake data.
0:07
See the desk, all right, so now we're going to write the code to get the new releases. Let's go over here and have a look.
0:15
So we're not going to call this test packages anymore we're actually just going to inline it Like that so we're going to go and implement
0:29
this method right there. Obviously, it's not doing any database work yet, is it. Now, as we talk about this, there's going to be several
0:38
ways to do this, some of them more efficient some of them less efficient and I want to look at the output, so the first thing I actually want to do
0:45
is go over to our db_session and I told you about this echo parameter. Let's make it true and see what happens. You can see all the info
0:54
SQLAlchemy looking at the tables to just see if it needs to create new ones. It doesn't and if I erase this and hit the homepage
1:01
you can see it's doing three queries it's doing a count, against users against releases and against packages.
1:10
Where is that, that is that part right there. Okay, now I just want to have it really clear what part is doing what, so I'm going to turn these off
1:19
and just have them return 200 or whatever, some random number. I'm also going to do that for the user_service.
1:29
So now let's refresh, I'll clean up this here and refresh, notice there's no database access. That's good because we're going to do database access
1:38
here and we need to understand it really clearly. All right so let's go over here and work on implementing get_latest_packages.
1:46
In fact, we were returning packages but what we really want to do is we want to return the latest releases with ways to access the associated packages.
1:57
If we look at the release, remember each release has a package relationship here. So I'm going to change this around
2:04
and rename this to latest releases like that and we're going to implement something here. It's going to start like this and let's go ahead
2:15
and tell it that it is going to return a list of release, release is good, import that from typing. And it's also going to have a limb
2:25
and that equals 10 or something like that. So if we don't specify how many latest releases we get 10. Okay so we're going to do some work
2:33
and eventually we're going to return a list right here. Well what are we going to do, well it turns out
2:38
that this is actually the most complicated thing we're going to do in the entire website. It's not super complicated but it's also not super
2:45
simple so let's do one more import. I'm going to import sqlalchemy.orm and because we need to change or give some hints to how the ORM works.
2:55
So let's go over here and say the releases are equal to, want to create a query so we've already done this a few times
3:01
session query of release, now what we want to do is get the latest ones so we're going to do an order by. So we'll come down here and say .order_by
3:12
and when we do an order by we go to the type and it's the thing we want to order by and we can either order ascending like this
3:18
or we can say descending like so. That's a good part. And maybe we want to only limit this to however many they pass in, 10 by default
3:29
but more, right, that's the variable passed in that's the function it takes and we want to return this as a list. So snapshot this into a list
3:38
we're going to just return releases. So is this going to work, first of all, is this going to work? I think it will probably work.
3:47
I'm certain it will be inefficient. Let's find out. So we rerun this and just clean that up here
3:52
and let's go hit this page remember these are all turned off so the only database access is going to be to derive
3:58
a little bit, if I refresh it what happens? Sort of works, well it actually worked let's go fix it really quick and then we'll come back
4:06
and talk about whether it's good. So here we actually had r in releases and I think we also need to pass
4:15
that in from our view 'cause even though we called this releases this packages so let's make it a little bit more consistent, releases.
4:25
That's not super, p is undefined okay and now we can fix this. So we have r, now remember r is a package and it doesn't have a name but it has an ID
4:35
and then r, it doesn't have a version it has a major, minor and build version but it also has a property which we wrote called version text.
4:43
So if we go check out version text it just makes a nice little formatted bit out of the numbers and that make up it's version.
4:51
And then here want to go to the r and we're going to navigate to its package and then we're going to say summary. Let's see what we get.
4:59
Ooh, is it good, it is good, it's super good. We were actually able to use that relationship that we built to go from a bunch of new releases
5:08
back over to a bunch of packages. Now there is some possible issues here. We could have these show up twice.
5:15
AWSCLI two versions, but maybe that's okay. We're showing the releases not just the packages. However if I pull this back up, ooh, problems.
5:26
Look at all this database access. Let's do one clean request. So here we are going in to our releases and then we go to packages and packages
5:36
and packages over and over again. Why is that happening? It's happening every time we touch it right here, that's a new database query.
5:47
Moreover that database query is actually happening in our template, which is not necessarily wrong
5:53
but to me, strikes me as really the inappropriate place. In my mind I call this function database stuff happens and by the time we leave it's done.
6:05
Well, let's make that super explicit. Let's close the session here and see how it works now. It's going to work any better?
6:15
Think that's not better, not so much. It's a detached instance error. The parent release has become unbound so we can't navigate its package property.
6:28
Actually we don't want to do that. It's okay that we close it here, we don't want more database access happening lazily throughout our application.
6:36
Instead what we want to have happen is we want to make sure all the access happens here. So we can do one cool thing, we come over here
6:43
and say options, so we're going to pass to this query we can say SQLAlchemy.orm.joinedload and I can go to the release and I say
6:53
I would like you to pull in that package. So any time you get a particular release also go ahead and query join against
7:00
its package so that thing is pre-populated all in one database query. So by the time we're done running it we no longer need to traverse
7:09
reconnect to the database for this. Is it going to work, well let's find out. Hey it works, that's a super good sign.
7:18
All of the data is there and look at this. Let's do one fresh one so it's super obvious. That's it, so we come in and we begin talking
7:28
to the database, we do our select releases left outer join to packages and where it's set to be the various IDs and whatnot
7:36
and a limit and offset and then this rollback here is actually what's happening on line 18. So we're just, we started a transaction
7:45
when we interacted with it and it says okay actually we don't need this anymore, roll it back. Which is fine you've got to close your transaction
7:51
either commit or rollback so rollback I guess. We don't really try to commit anything and we didn't want to so this is good. How cool is that, huh?
7:59
I think this is pretty awesome. So we've done a single database query we've solved the N plus one problem and we've got our latest releases
8:09
and we used the latest releases to pull back the package names and the package summaries and so on.
8:16
So that we know our database stuff is working efficiently let's go and put these queries back here. So it's working for real.
8:27
Go back and pull up our inspect element. Remember we're still running the debugger but we should get some sense of performance here.
8:34
There we go, 13 milliseconds. What was it, 11 before, so going and get those releases and those packages in that join barely added any effort.
8:45
Now remember we're running the debugger we could probably make this faster still but this homepage is working super super well.
8:51
I'm really happy with how everything's coming together. And if we have true indexes it doesn't matter if we have a decent amount of data
8:59
our queries should still be quite quick. All right, awesome, homepage is done.