Python for the .NET developer Transcripts
Chapter: Database access and ORMs in Python
Lecture: Querying data
0:00 Now that we have data in the database
0:02 let's forget this stuff here about this in-memory data.
0:06 We want to go and get it from the database
0:08 which could also do the sort, and just return it.
0:11 It should be super easy
0:13 now we have our context, our database context all set up.
0:17 So we're going to start again, by using our session factory
0:19 because that's how we get started.
0:22 When we're able to say with session_factory.create_session() as ctx
0:26 and then we want to do some kind of query.
0:30 We're going to do a query if there's no style
0:34 if there's no filtering, we're going to do one thing
0:36 otherwise we're going to do
0:38 a more of a database filter down here.
0:40 So if we just want all of them
0:42 the guitars, equals, the way it works
0:45 is we go to the context, the session
0:48 I say query of the type we want to do a query against
0:51 and we could say filter and do some kind of limiting
0:53 but we're not, we just want all the guitars.
0:55 So we're going to do an order by
0:58 and this is where it gets kind of interesting.
0:59 We say order by guitar dot, what do you want to order by?
1:02 You want to order by price, how do you want to order?
1:05 Descending. Pretty cool, right?
1:08 Want to go to the guitar, get its price
1:10 order by descending.
1:11 So, it's not exactly the same as LINQ
1:13 but it's quite similar to it.
1:15 And then let's read this all out into a list
1:18 because once we leave here
1:19 basically, the connection is closed
1:21 and we can't get any more data.
1:23 So when I go over here and just convert that
1:24 to a list, it'll be clear if we do it like
1:29 that right there.
1:30 Okay, so this should work for all the guitars
1:33 what about if we need the filtered guitars?
1:35 Well, this is no longer necessary.
1:37 It's going to be something like this.
1:40 I'm going to have this order as well.
1:42 Now, these start to get really long
1:43 and we can do some nice little hang breaks here
1:46 so let's go, just hit enter here.
1:48 It's a bit of a weird language feature
1:50 but it's how it works. You hit enter, and you do a continuation
1:54 you have to do either a backslash there
1:56 or since there's no semicolons
1:59 or if there's parentheses
2:00 then you actually don't have to do that.
2:02 So if we just wrap the list here
2:03 we can say dot order by this and we can also come here
2:07 and say dot filter whatever you want to filter on.
2:11 Remember, we were testing style, so guitar.style
2:15 and what do we want to test?
2:17 Equality, not less than greater than, or whatever, done.
2:21 So, here's what we're going to write.
2:22 We're going to take, we do a query against the guitar tables
2:25 going to filter on style, which we have an index for
2:29 we're going to order by price, which we have an index for
2:31 and we're going to make sure we read through the cursor
2:33 turn it to a list, and return it.
2:36 Alright, well, that's not too much work
2:38 for us to write these two queries
2:40 let's go ahead an run it, and see if our website still works.
2:43 So here we are, this page tells us nothing
2:45 about whether it's going to work
2:46 but let's see what's on the next page.
2:48 Cross your fingers, boom!
2:50 There they are, how awesome is that?
2:52 And how do I know this is real data
2:54 not that in-memory stuff?
2:55 Because it's the new version in the database.
2:58 Well that's all of them, let's look at just the electric guitars.
3:01 Just the acoustic ones. Beautiful!
3:04 How killer is that? That is just so sweet!
3:07 And that is all the code we had to write to implement that.
3:11 It was just as long as just doing it
3:13 with fake in-memory data
3:14 but now we have the full power of a real database
3:17 and right now just SQLLite, but it could be Postgres
3:19 or Microsoft SQL Server, or whatever we connect it to.
3:23 Super, super cool.
3:24 And the way that we consume it, remember, over here
3:28 this does not change at all.
3:30 Or as it knows, it still gets a list of guitar objects
3:33 and those guitar objects have all the properties
3:35 that it expects.
3:36 So we didn't even have to touch this code
3:38 which is great!