Python for Entrepreneurs Transcripts
Chapter: Accessing databases from Python: SQLAlchemy ORM
Lecture: Demo: Creating albums and tracks
0:01 Let's just run this and see what we're going to get.
0:05 So over here in albums, we have now an add new
0:08 and this takes us to our admin section and here is our form,
0:11 you can see it looks pretty decent bootstrap.
0:13 What we are going to need to do is we are going to need to put in some data,
0:16 so where does this data come from?
0:18 Remember we had tracks there before,
0:20 and those tracks came from this fake data that I entered
0:23 we're going to reuse that fake data, let's look at what we're going to do
0:26 in the admin controller here.
0:29 Now, a pattern that I really like to use when working with ORMs
0:32 is I like to have a layer of separation between the raw ORM
0:37 and just straight up queries against the database
0:40 and the controller our view action methods here.
0:44 And the way I do that, you can do whatever you want
0:47 but the way I do this is I have this thing called an album service,
0:50 we have an account service and things like that
0:52 and it will do sort of orchestration of the data, higher order things, right,
0:55 so here we are going to call get albums that's already being called
0:58 and it's returning nothing, so that's why that album's page is down empty,
1:01 here is where the fake data was living, we are also going to be able to create an album,
1:05 so let's go over here, and let's just do that and insert an album here,
1:09 we'll go and call this method so I'll say album service which I've already imported,
1:14 say create album, and what do we have to give it, we have to give it the title,
1:18 so the vm has a title, and we want to send it the track titles,
1:24 so these are the track titles, notice the p here for the property
1:27 not the t for straight text.
1:30 Okay, so we are going to pass that along and we are going to actually have it
1:35 return a new album, this is going to be an instance of this class here, right,
1:42 we are going to create one of those and we want to get it back from the database
1:46 and the reason why do we want to get it back
1:48 why don't we just want to insert it and forget it, well,
1:51 maybe we want to log that was created, we are going to need a piece of information
1:54 that is not immediately accessible from what the user has given us.
1:57 Namely the primary key that is an auto incrementing auto-generating id,
2:02 so we'll say something like this,
2:06 there, so once we save it to the database it's actually going to come back
2:12 with its auto-incrementing or auto created id set
2:15 of course this isn't actually doing anything you saw that it just did a pass
2:19 but this is what our controller method is going to do
2:22 now we just have to go and actually create this item here.
2:26 So as we saw earlier, in the db session factory,
2:30 we have to create one of these sessions, right,
2:33 we are going to create a unit of work, we are going to make some inserts,
2:36 and then we are going to commit that unit of work.
2:38 So here we'll say session=this we want to do a bunch of work
2:41 and then we'll say session.commit.
2:44 So the first thing we want to do is we want to create an album and insert it,
2:47 well, how do you create an object in Python if you weren't doing database stuff,
2:51 something like this- there right, so that will create an album
2:56 and the way we tell the database to insert it we go here and we say add this album.
3:03 And then when we commit it, everything is going to be great,
3:07 also we want to return the album, like so.
3:11 Okay so this is great but none of the values have been set, so let's go and set these,
3:15 so here you'll see that we can set the title is title year=year,
3:21 and now these names just happen to line up they don't have to,
3:25 as of course, now that's pretty close, there is one other thing we want to set,
3:30 there is an is published we want to set that to true
3:34 because we are going to want to do some kind of query there based on that.
3:40 So this will create the album and insert it, the other thing we want to do is
3:43 actually create the associated tracks in the other table so how do we do that,
3:48 well, we could do this commit to generate the key and then we could actually go back
3:53 and associate the album id on each track with this one
3:57 but there is a much better way in SQLAlchemy.
4:00 We can traverse that relationship, so we can come down here
4:03 and say for a title in track titles, right, this is a list of strings,
4:07 we can create a track like so,
4:12 and then we can go to the album and go to its tracks here
4:17 and say .append and just like any list, track.
4:20 And then this tells SQLAlchemy well when you insert this album,
4:24 also take all the tracks and put it in that other table, and set up the foreign key relationship
4:28 to match whatever the auto-incremented or auto-generated id is,
4:33 so again, we are going to have to set a few things here let's just look
4:36 and see what tracks have, so the id is going to be fine,
4:39 we are going to need to set the name,
4:43 also we are going to need to be a little careful here this is name as well I believe.
4:48 Yes, it is name, okay so we want to set the name = title, and what else-
4:57 actually I don't think we need this, but we do need display order,
5:01 remember display order is actually used in figuring out how these will come in here
5:06 Now how do we get the display order,
5:08 let's just put the order in which they appear here
5:11 the easiest way to do that in Python is to use enumerate
5:14 and we'll project back an index and the title not just the title
5:18 so we can come down here and say this is going to be index
5:20 maybe we'll have one to end not zero to end minus one,
5:23 and so this is going to associate that with our tracks,
5:27 and now we are going to call commit, and that should add everything to the database,
5:32 it's going to be great, except for up here we have our query
5:35 which I had accidentally deleted, I'll put it back,
5:37 there is going to be a small problem with this,
5:39 I want you to see the error because this is super frustrating
5:41 sometimes when you are working with these views and web apps
5:45 and I am going to talk about two possible solutions but let's have this work,
5:49 sort of and then it's going to redirect, and so the working part will be inserted
5:53 into the database, it's going to redirect to albums,
5:55 it's going to run this method and this method shouldn't work.
5:58 Let's go. Alright, so over here let's go to albums, see there is none,
6:04 so that query works right now, and let's take this test data here
6:09 and I am just going to throw some of this in,
6:13 okay great we've got this all filled out now
6:15 I am going to click it, notice it should go way from new album,
6:18 and it should go back to /albums, fingers crossed for the insert.
6:22 Boom, wow that actually surprises me, that this works,
6:26 let's do a quick clear down here and have a look at what's going on,
6:29 so if I do a query it's going to write obviously go to the database,
6:33 find me where it's published and sort by this,
6:36 but you should see actually a second query
6:38 and if there were ten albums, you would see eleven queries at the database, that's bad.
6:45 Okay so let's go to the very beginning, here is the select album ta ta ta ta from that
6:48 where it's published, this is the one that gave us the album and then,
6:52 we in Chameleon try to loop over the tracks, well the tracks weren't loaded,
6:57 we only loaded the albums here, so that is going to go back to the database
7:01 down here it's going to begin another query
7:05 where you select the track ta ta ta from track where the album id
7:09 is something and the display order is something, order by display order.
7:13 So here this is album one and we are getting that back.
7:18 Okay, so we are going to this database, the error I thought you might see
7:26 was a detached instance what happens is if the session gets cleaned up,
7:29 we are going to make it happen, no it's still working.
7:33 What happens is if this session gets closed and then you try to navigate that relationship,
7:39 if you go to one of these albums and say album.tracks
7:43 it might not have a connection it can use to get back to the database,
7:47 and so that's not great.
7:50 So what we can do is we can actually, there is a couple of things we can do,
7:54 say hang on to the session basically is not good,
7:57 the other one is we can go and do a join and we could actually,
8:01 we know that when we call this function it's very likely we want to get the tracks as well,
8:04 so we could pre join that and remove what's called an n+1 performance problem
8:09 and just make it one query not one that returns an item and then n more things
8:16 back to navigate those relationships.
8:18 So how do we do that, well, we go over here and we say there are options
8:21 and we say joined load now you've got to see import that from SQLAlchemy.orm
8:24 and then you give it to actual relationship it's going to follow
8:28 so it's going to follow tracks on the album, I'm going to continue that,
8:32 so notice, down here we had those two queries, let's run this again,
8:35 we've got to rerun the whole site, because the Python changed.
8:40 If we rerun this again, same behavior,
8:44 although, we can tell it probably was faster though,
8:47 but check this out, we were going to do select* from albums
8:52 left outer join with track as track one on album id=track one.album id
9:00 so it's actually doing that join, so even we've got a hundred albums back,
9:03 we are only making one query to the database, so this is really cool,
9:07 you don't want to use it all the time, like if you don't always need tracks don't do this,
9:10 but it's great to be able to do it to make everything run faster.
9:15 While we're at it, let's just add one more album,
9:17 because a single album or a single record
9:19 in the database, that's kind of boring, right. let's add new again,
9:23 and I'll just copy this over from the old data.
9:28 Okay, here is the other album, from the fake data that I had entered
9:32 and let's go ahead and hit create here and now that should work perfectly,
9:35 it should create it, insert into the database, redirect to albums,
9:38 that's going to do a query again, and let's see does it order over here,
9:42 it orders by the year descending; the first one was 2001, this is 1999
9:48 so it should go on the end, ready, set go.
9:51 And that was just lightning fast, boom, there we are,
9:55 we've got our year of the snake entered and you can see
9:59 that the order in which the things are shown here the relationship is traverse,
10:05 is exactly the order in which they were put in.
10:08 Let's do one final thing, let's actually open this up and see what's in here
10:11 so we go to table editor, I am going to close this,
10:15 and you can see here is the two albums,
10:18 and we can go over to the track as well
10:21 and you can see here we've got the new tracks,
10:24 and the display order, it's 1, 2, 3, 4, 5, 6,
10:28 1, 2, 3, 4, 5, and here if the foreign key that we are using
10:30 to associate them back with the album.