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