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