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