Python for Entrepreneurs Transcripts
Chapter: Accessing databases from Python: SQLAlchemy ORM
Lecture: Demo: Reading data
Login or
purchase this course
to watch this video and the rest of the course contents.
0:01
OK so how do we use this? Well, there is a couple of things we need to do. One, our database is empty, so we are going to need
0:09
to insert some data, that's interesting. Two, we are going to need to query the data
0:14
and I think it will be more fun and have a little more dramatic effect if we write up the code to query the data, we'll see there is nothing there
0:21
and then when we actually insert it it will immediately appear. So let's go back to our albums controller here
0:26
and have a quick look. Remember, we wrote this method here long ago, I am sure it feels like a long time ago and we have this AlbumService,
0:34
and this is a design pattern I love to use for these ORMs. The ORM deals with mapping, classes directly to and from the database
0:44
but there is oftentimes more application-scale sort of things are happening, well, I want to join with this or any time I query one of those,
0:53
I want to make sure I also update over here... those types of things. So I am very keen to have these service things, right,
1:00
so services, these are the applications view of accessing the data, you don't work directly in the ORM, you can if you want
1:08
but we are going to go over here and work like this, we have a function called get_albums,
1:13
so you can see this is the old_get_albums, remember we had this fake data, we are going to have a new one here.
1:19
So what we are going to do is we are going to go and do a query so let's say like this, we are going to need to get a hold of one of these sessions,
1:27
and again, this is going to be DbSessionFactory and we are going to need to import that and I don't really like this "Factory.factory",
1:34
let's give this a function over here, so we come down here def, make this a static method as well,
1:42
and we'll call this create_session, something like this. and we'll just return like so, OK? So instead of doing this,
1:52
we can do something a little more obvious like create_session
1:55
and then our goals are going to be to get the data from the database, so how do we do this?
1:58
What we are going to do is we are going to be able to create queries from these sessions, so we are going to say session.query
2:05
and we give it the type that we want to work with, in this case we want to query an album, and of course,
2:10
we are going to need to import this, maybe we want to do some kind of filtering,
2:15
like let's see, I changed the album just a little bit here to have an is_published. And this will let us do some kind of interesting queries
2:22
if we don't have too much data, so we come over here and we'll do a query and we'll say something to the effect of filter(Album).
2:30
Wow, this is what's cool about ORM, is you get to express the queries, this is kind of the "where" clause if you will,
2:36
in terms of the classes, so I can say is_published, leave it alone like that or == True, however you want.
2:45
A lot of times these get kind of complicated, you might want to do them on multiple lines, so we are going to say order_by,
2:51
maybe we want to order by Album.year, where is year? There is year, so we want to order by Album.year
3:00
and maybe we want to actually show the newest ones first, let's be a little consistent there,
3:05
notice how PyCharm puts little line continuation for us, thank you. So this is pretty straightforward, order_by,
3:12
but what do I do if I want to order by descending, I can tell you already, there is not a order by DESC, OK?
3:17
This is the only order_by function we have. so it's not entirely obvious but we can come over here and on these we have like descending, ascending,
3:26
"like" for substring matches, there is all sorts of interesting functions on these columns that you can do filtering or ordering by.
3:33
OK, so this is going to give us basically a result, a set of results here, or leave it like that.
3:42
Now, we can go over here and we can say things like .all and that will snapshot into a list but if we don't do that here
3:51
we just have an iterable, that's still running across the database, we haven't actually returned anything yet.
3:58
So, we are going to need to somehow do that. So let's go ahead and do that, if our goal really is just to return a list,
4:04
we don't want to return this "results", which still kind of partially tied to the database,
4:10
we'd like to snapshot that like this, come over here say .all and then return albums. OK, so that means outside of this function
4:22
there is going to be no more data access, it's not like a dangling open session sort of issue going on,
4:28
OK, so this should run and it should give us back nothing, because there is no data in our database,
4:34
but let's do one thing, so we can actually see what is happening. Remember our engine, it has an echo, not there, our engine,
4:41
our engine has an echo, and we can say echo=True and this will turn on all the debug output, when I run it, you should see a bunch of stuff.
4:50
Hopefully, I haven't messed anything up, we are about to find out. So notice all this output, that's not an error,
4:57
that's SQLAlchemy looking at the tables, trying to understand. Tell me about the album, tell me about the track, OK?
5:04
That's from echo=True. Now we go over here, this is all well and good but if I hit this album's button,
5:10
or navigation item here, we are going to go run that query and let's see what happened. So check this out, BEGIN implicit transaction, SELECT,
5:18
just refer to this as star, right this is all the columns... I come down here, FROM album, WHERE album is_published, ORDER BY "Album".year DESC.
5:35
How cool is that? Let's go back and look at what we wrote. session.query(album).filter(Album.is_published),
5:42
order_by(Album.year.desc()), right, there is album year descending and then we snapshot that into a list.
5:49
Right, so we came back, we passed that off to our view, our view said let's loop over it, oh, there is actually nothing here.