Full Web Apps with FastAPI Transcripts
Chapter: Storing data with SQLAlchemy
Lecture: User queries
Login or
purchase this course
to watch this video and the rest of the course contents.
0:00
Package details is working, home page is working. There's one final section that has important stuff going on like register,
0:08
that's not right anymore. But if I click on register, remember, we're just doing that fake data.
0:12
We're setting the cookie, so it looks like something happened. But there's really no persistence or storage of the users.
0:17
Similarly, when we try to log in, there's no checking that there's actually a user. So we need to write these two queries here.
0:24
We've already got the user_count, let's do this create_account. So I'm gonna go ahead and nab this code cause it's real similar and what we
0:30
need to do, that is just a little bit different, is instead of returning a query, we need to create an object, insert it in the database,
0:38
commit those changes and then return that object. So but, do like this and say the user is a User, and then we'll just set some of its values,
0:46
id we don't have to set, that's auto incremented email can be email. Over here, we're gonna set the name,
0:55
it's gonna be their personal name or whatever, created_date has a default, will get set
1:00
by SQLAlchemy, hashed_password is, just gonna set this to "TBD" because we're gonna do a little bit of work to do that
1:08
right. last_login is set by an auto, by a default, created_date is set by a default, profile_image_url is just gonna be None.
1:16
And that's all we gotta say. So the last thing to do is say session.add(user), session.commit() and then return our new user.
1:28
Cool, huh? So we're gonna use this later, we're not using it yet. We're gonna come back for a second pass and store the password
1:34
here. So this is our query, our create_user and then lets do, we really need something more like this. Let's go and do the query user
1:43
as well and maybe I'll just put "# TODO, set proper password". And while we're here, let's go and do this one as well.
1:51
Everyone having the password "abc", that's not the best. What we wanna do is come over here and say
1:56
I would like to get a user. Now, a naive query would be give me the user where the email is this and the password is that but you never,
2:03
never wanna do that because that means you'll be storing raw passwords. Don't wanna do that. What we wanna do instead is get the email and
2:09
then check that the re encrypted version of the hashed password match, the plain text password matches what the hashed password is.
2:18
So we'll do our filter where user.email is the email, I'll say first(), this will be user, here we go. We're gonna say,
2:26
give me the user. If there's no user, gonna return whatever that nothing is, it's very, very, very likely None. And
2:36
I'm gonna put just "# TODO: verify password". We're gonna leave that for a little bit later, not do that yet, and then we're gonna return the user,
2:46
right? Assuming that the password, say if. So if for some reason the password is wrong or whatever, we're not gonna return that.
2:55
Okay, so we'll return the user we got back from the database and let's see if we can do this round trip here. So we're gonna run,
3:04
down to beekeeper, and I'm gonna go over the users table and let's go and order by created_date, doesn't matter,
3:11
they're all created right then, but it will in a minute. We're gonna have something new in there.
3:15
So what we wanna do is we want to go over and try to register. So to register, I'm gonna put my name this time,
3:21
this is the password, put my fancy letters here, doesn't really matter, but I think it's required. All right,
3:27
moment of truth. This should now go save it in the database, it did not. Something went wrong, right here.
3:33
Ah, yes. Our AccountViewModel needs to be calling the database here. Yeah, so what we need to do is, if we look at the ViewModelBase,
3:40
we've already got the is_logged_in and let's, let's go and set this like so. Let's set this here, The logged in is that is not None.
3:50
All right, so if there is some kind of id set, then they're logged in. Okay, so we already have this user_id.
3:56
And what we need to do is just go and write one more function, is user_service.get_user_by_id, and it would be self.user_id
4:07
Didn't exist yet, let's go write that real quick. And this is an int in this case, and it's gonna return like before
4:13
an Optional[User]. See, there's a lot of similarities going on here. Copy, copy. This one's simpler but quite similar in structure.
4:22
So we try to get it and, this time where the id is the user_id and let say return that, but in fact,
4:29
we could inline this and just return that result, doesn't really matter if we look at it. Sometimes I find having a variable is nice for debugging.
4:36
You can say, well, what came back here? Alright, go and return that but this is gonna be straightforward. All right, I come over here,
4:44
try and it thinks we're logged in, but let's go and log out. Let's go register, again. Say Michael, email address, five a's and some number,
4:52
but let's just verify what's happening. The register actually worked, I believe. But then, we didn't get the right data back, right?
4:59
The error was in the account page, not this, so hit register, and we still have an error, what's going on here.
5:06
Ah we're not checking, apparently, we tried to register with the same email address.
5:09
We already have this email, so let's go and add, looks like we're missing something in our registration view model. Yeah, and we didn't do the test,
5:19
right? So another test we need to do, say elif user_service find user, user what do we get? It is get user by. Let's say get by email self.email.
5:32
If you're gonna have a uniqueness constraint, you can only have one of them. Okay, so maybe you should log in. Alright, so let's try that real quick,
5:41
and it's extremely similar to this. Except for that is email, and that is email. And that's a string. Okay,
5:59
let's go try to submit that again. Now it comes back, it goes no, no, no you can't do that. That email is already taken. Oh,
6:05
well, let's go and try to log in, we're just not checking the passwords so I'm gonna put only three a's this time,
6:10
not five. Remember we're coming back to do that authentication stuff. Look at that, I've logged in, I'm logged into our account.
6:18
Let's try one more time, I'll log out. I'm gonna log in. Beautiful. All right, awesome. So we've got our account set up.
6:27
Let's just go finally, look in the database, we'll do a query select * from users where users.email is my email. We run it and check it out,
6:44
there we go. Name is Michael Kennedy. password is not set yet, created_date is right now today,
6:52
profile image not set, but we created our user and we're using it to log in and log out. Let's check one more thing.
6:58
Let's try to log in using a non existing thing. Sorry, that account doesn't exist in our database. Super cool. So I would say we have these queries,
7:10
all working. We just needed a little bit extra back here to make sure we didn't create the same user twice, which the database won't allow. That's it.
7:18
We are 100% using our database and writing all the SQLAlchemy queries against our models to make this entire website run. Beautiful