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