Building Data-Driven Web Apps with Pyramid and SQLAlchemy Transcripts
Chapter: Using SQLAchemy
Lecture: Concept: Querying data
Login or
purchase this course
to watch this video and the rest of the course contents.
0:00
We've written a few interesting queries and before we're done with this application we'll write a couple more.
0:07
But let's talk about some of the core concepts around querying data. So here's a simple function that says find an account by login.
0:15
We haven't written this one yet but you know, we're going to when we get to the user side of things. It starts like all interaction with SQLAlchemy.
0:23
We create a unit of work by creating a session. Here in the slides we have a slightly different factory method that we've written, but same idea
0:30
we get a session back, we're call calling it s. We go to our session or we say s.query of the type we're trying to query from, account
0:37
and then we can have one or more filter statements. Here we're doing two filter statements. Find where the account has this e-mail
0:45
and the hashed password is the one that we've created for them by rehashing it. And now we're calling one, which gives us one
0:53
and exactly one or none items back and we're going to return that account. So, if you actually look at what goes over to the database
1:00
it's something like this. Select * from account where account.email is some parameter and account.passwordhashed is some other parameter
1:08
and the parameters are Mysie Kennedy and ABC. You'll see that you can layer on these filter statements
1:14
even conditionally, like you can create the query and then say if some other value is there then also append or apply another filter operation
1:22
so you can kind of build these up. They don't actually execute until you do like a one operation or you loop over them or you do a first
1:29
or anything like that. So here's returning a single record. Also, it's worth noting that the select * here
1:36
is a simplification, everything is explicitly called out in SQLAlchemy. The concept is just give me all the records or give me all the columns.
1:45
If we want to get a set of items back, like show me all of the packages that a particular person with their email has authored.
1:54
We would go and again get our session. We would go and create a query based on package. We'd say filter, package.authoremail equals this email
2:03
==, remember double equal and then we can just say All and that'll give us all of the packages that match that query.
2:11
This one's not going against a primary key so there'll be potentially more than one. Of course this maps down to select * from packages
2:17
where package.authoremail equals when you know the email that you passed. Super simple and exactly like you would expect.
2:24
So the double equal filter, pretty straightforward. There's actually some that are not so straightforward
2:29
so equals obviously ==, user.name == Ed, simple. If you want not equals, just use the not equals operator. That's pretty simple.
2:40
You could also use like, so one of the things it takes some getting used to is these SQLAlchemy descriptor column field value type multi-purpose things
2:51
here is they actually have operations that you can do on them when you're treating the static type interacting with a static definition rather than
3:00
a record from the database. So here we say the usertype.name.like_ or in_ or things like that.
3:07
So, we saw the descending sort operation on there as well. So, if we want to do the Like query, this is like
3:14
find the substring ed in the name, then you can do .like_ and then pass the % operators as you would in a normal SQL query.
3:23
IF you want to say I want to find the user whose name is contained in the set, Ed, Wendy or Jack then you can do this .in_, remember the underscore
3:31
is because in is a key word in Python, so in_. If you want to do not, not in, this is kind of not obvious
3:39
but you do the ~ operator at the beginning to negate it. If you want to check for null, == None, the and you just apply
3:46
multiple queries, the or doesn't work that way. If you want to do an or, you've got to apply a special or operator to a tuple of things.
3:55
So, here are most of the SQL operators in terms of SQLAlchemy. You can do a lot of stuff with this. It's not all of them, but many of them.