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