Python for Entrepreneurs Transcripts
Chapter: Accessing databases from Python: SQLAlchemy ORM
Lecture: Concept: Querying data
Login or
purchase this course
to watch this video and the rest of the course contents.
0:02
Let's take this unit of work concept and apply it to querying data. Now, here we have a function that give it an email and a password
0:11
we would like to finally account for the user. We never ever want to store the straight plain text password,
0:17
we always want to hash this in some interesting way so we are going to go and do that and then we want to do a query
0:22
to find the account where the email is what they specified in the hashed password is the result of hashing what they typed in.
0:29
So the way this works is we are going to do the unit of work when I create the session and then we are going to do a query
0:34
so on a session, you say .query and you give it a base type to start from, in this case we want to start form account
0:42
and then we can do filtering and ordering and so on so we are going to say session.query of account and then we want to filter account.email is email.
0:50
Here you can see we use the column names of the type in the boolean expression for the filtering. So we are going to account.email==email
1:00
and you can use and and all the standard Python boolean operators. And we are going to say account.password_hash==hash
1:08
and the fact that we have two filters means an and, there also is an and function you can use
1:13
but just piling on multiple filters will result in an and query. This we know should only return one item, one or zero,
1:20
depending on whether they got the password right, instead of getting a set back,
1:25
we can get just one of them and immediately pull that back from database and execute it by saying one.
1:30
So this will give us an account, if we see what goes to the database, like if we have echo=true turned on we would see something like this,
1:36
select * from account, where email =? and account.password_hash =? and then it passes those as parameters,
1:45
the email address is whatever is typed there, that's mine and the password is ABC, it's unlikely that's a hash but whatever, you get the idea.
1:54
Not only does this map to sql in a really nice way it uses parameters so you can avoid sql injection attacks entirely.
2:01
Now if we want a setback, we want more than one thing, we can call all, so here we're doing another filter
2:08
and this is not an exact match but this is like some albums, these are all the albums published or created since year 2001
2:16
and if we do the .all to it, this is actually going to snapshot it into a list, so we don't have to worry about staying connected to the database.
2:25
When we run this, you can see we will get this select * from album where album.year is greater than again parameters. Exactly the sql you would expect.
2:35
Now, you saw it technically lays out the names, of the columns that are specified in the album class, but the effect is the same.
2:45
So those are all pretty straightforward, greater than, equal to and so on, here is a few more that are not obvious but are really cool,
2:51
so 'equals', fine, 'not equals', we do this not equal like this, if we want to say it's 'like', so like substring search,
3:00
you can say 'in' sql something is like a sting say percent, that's like a wildcard,
3:05
you can do the same thing on the colons, remember the colons have extra function so we can say .like on the name, if you want to do a subset match,
3:14
so I'd like to find it all the elements or all the rows where the name is either Ed Wendy or Jack,
3:21
you can do this in similarly with a tilde you can do 'not in', 'null' is none, 'and' is either multiple filters or there is an and operator
3:29
and there is no way to combine those for 'or', so you've got to us the 'or' function. They use or_ because or itself is a keyword.
3:37
These are the common ones I think there is more, still you can see in the link below you can get the full exhaustive list
3:43
but this will take you very far. We also saw that we can order our results,
3:48
so for getting back a set non an individual item it might make sense to order them, so what we did is we said order by album.year that's an integer,
3:56
but we said descending because these columns can like I said, fill multiple roles they can both be the value when you have the instance,
4:05
but on this type, they enhance these queries. so here we can do an order by descending,
4:09
and we run that you get select * from album order by album.year descending. Finally, if we want to update the data, we are going to do the queries
4:19
just like you've seen we're going to pull them back from the database, like again, here is our one album, get the album by id,
4:25
but what we are going to do is we are just going to take the object in memory and make some changes, like here if we want to change the price
4:31
we'll just say album.price=new_price and as long as we commit the unit of work, this is going to do an update back to the database.