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