Python for Entrepreneurs Transcripts
Chapter: Accessing databases from Python: SQLAlchemy ORM
Lecture: Demo: Reading data
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.