Full Web Apps with FastAPI Transcripts
Chapter: Storing data with SQLAlchemy
Lecture: Making the User class a SQLAlchemy class
0:00 Now the next thing that we need to do is
0:01 start creating some of these classes and we kind of went down this path a little
0:05 bit already. Remember, I said,
0:06 it's really tricky to pass the right data with the right,
0:10 you know, properties and cascading properties and whatnot that we need to the package page
0:16 and so on without just creating some classes.
0:18 You know, all the time knowing that we're kind of headed towards the SQLAlchemy
0:21 path, which is done with the ORM and classes anyway.
0:24 So let's go and upgrade this class to be something that gets automatically queried and
0:30 serialized out of the database rather than just an in memory class.
0:33 So we're gonna use our SqlAlchemyBase.
0:36 We'll just do it here like this,
0:38 SqlAlchemyBase double Ctrl, double Ctrl + Space and we get all that magic happening.
0:43 Now It's a SQLAlchemy table.
0:45 Well, sort of. It doesn't have any columns yet.
0:48 This is not quite the way you do that there.
0:50 So let's do one thing before you forget.
0:53 I want to set the __tablename__, something that bugs me about these things.
0:58 Tables contain more than one thing,
0:59 so calling it user to me seems weird. Should be "users",
1:02 and capital, I don't dig that.
1:04 So let's call this "users", lower case.
1:07 I'll tell Pycharm. Now this is cool,
1:08 you can have tablename all day long.
1:10 It's not misspelled. Then let's add these fields,
1:14 which are going to be columns. We're gonna have an id,
1:16 which is a SQLAlchemy column. And one of the nice ways you can do
1:21 this is, you can so, import sqlalchemy as sa.
1:25 You can have a little bit shorter, something
1:26 to write here, sa.column,
1:29 whoops, capital Column. And then we have to specify the type,
1:33 and this is gonna be an sa.Integer. Now,
1:35 not just any integer, but this is gonna be the primary key,
1:38 and it's gonna automatically update itself, increment itself on the database so we'll set two things
1:43 primary_key is True, and autoincrement is True.
1:47 So this should also create an index for us,
1:49 we shouldn't have to have an index explicitly here cause it's a primary key.
1:53 Next we've got name, which is an
1:56 SQLAlchemy column of type sa.String.
1:59 Let's just put the type information in here for now
2:02 and we'll have an email, which is the same. We'll have a hashed_password,
2:06 which is the same. We'll have a created_date
2:10 and that is not a string that is a sa.DateTime. We'll have a profile_image_url,
2:16 which is now back to the sa.String.
2:21 Now all these done. And we've got our last_login,
2:25 which is gonna be one of this, last_login.
2:29 OK, super. Now, let's make this a little bit nicer to work with.
2:33 A couple of things, how often do we want to be able to query a
2:37 user by email? Well, when they log in with their email, very,
2:40 very often. So let's go over here and say index=True.
2:44 Also, if they log in and they say forget their password,
2:48 we wanna let them press a button and reset their password.
2:50 So we need their email to also be unique.
2:53 So we come down here and say this must be unique=True.
2:56 We can't have different accounts with the same email,
2:59 that would just be weird, password,
3:01 no index or anything like that.
3:04 create_date, this one,
3:05 when you create the user, we would like SQLAlchemy to automatically just set it to
3:09 right now. So let's say the default, gotta be really careful
3:13 here, is gonna be a datetime.datetime.now.
3:17 That's what I wanna type,
3:18 if I hit Enter, the parentheses
3:21 go on there. And that is not what I wanna type.
3:23 What I want to do is provide the function
3:25 now, that SQLAlchemy will call.
3:28 Not now() when the class gets parsed.
3:30 So for the whole lifetime of the app,
3:32 it's just when the app started.
3:33 No, no, no. We do it like this.
3:35 We can do the same here,
3:39 the last_login, the very first time is right when they create their account.
3:42 And then we're gonna have to set this the next time and the next time and
3:45 the next time. profile_url_image,
3:48 we don't really need anything on that either.
3:51 Maybe we want to do queries like,
3:52 show me the users who were created today.
3:55 You know, show me all the new users order by descending on created_date or
3:58 something. For that reason, we're gonna make this much faster
4:01 if we have an index on those two things.
4:03 I think that's it. I think that's it. Now,
4:05 one thing you wanna be careful about is if I run this code,
4:08 it's gonna create the users table.
4:10 Once it does that, SQLAlchemy will never make a change to it again.
4:13 It's like, that exists, we're not gonna break anything.
4:15 So you want to make sure it's right,
4:17 or you have to either throw away the database or directly edit it or do a
4:20 migration or something. So, you know,
4:22 double check the work. That look good, looks good to me.
4:26 So let's go and run this and then we'll go inspect the database.
4:30 Right, It didn't crash. That's a really,
4:33 really good sign, because it did go through that global_init process.
4:36 And importantly, it called this right, right here.
4:43 SqlALchemyBase.metadata.create_all with the engine. And that'll create all the class,
4:48 it'll take all the classes that derive from SqlAlchemyBase and create their corresponding tables
4:52 and relationships and foreign keys and so on.
4:55 We should be able to go look at our SQLite database over here.
4:58 Let me reload from disk and see if anything changes.
5:00 Yes, notice the icon changed now that it has an actual structure
5:06 in it. It knows it's a database, that's super cool.
5:09 What's not super cool is, I don't think that I can load it up here. Normally
5:13 in PyCharm I could just drag and drop this over here.
5:18 But notice there's issues. It's having issues.
5:22 The reason it's having issues,
5:23 I believe, is this is actually one of the Apple Silicon M1 Macs that
5:28 I'm recording on, which is great, super fast,
5:30 and this is the M1 version of PyCharm as well,
5:33 the native Apple version. Everything sounds great,
5:36 except for the SQLAlchemy database drivers don't work with it.
5:40 So let me go and show you something else that's cool. If you wanna use
5:42 that way, that's great. That's normally how I would do it in PyCharm.
5:45 But if for some reason that doesn't work or you don't have PyCharm Pro,
5:48 let me show you this.
5:50 So we've got this really cool,
5:53 nice looking database UI tool called Beekeeper Studio, and Beekeeper Studio is really quite a
6:01 nice UI. Talks to all these different databases,
6:04 lets you create different connection strings and basically do autocomplete queries and have a history
6:09 of them, a history of the queries, explore the schema and so on.
6:13 So what I'm gonna do is I'm gonna go open up this thing
6:16 here. I can go SQLite,
6:19 browse over to it. But you know what?
6:20 I've already done this so I can just click right here.
6:25 Click connect and check it out,
6:26 we've got our users table. We've got an id,
6:28 which is an integer, and our name and our hash_password, our created_date
6:33 and so on. Now there's nothing in here, you can see if we go
6:36 to a query, it's just empty,
6:38 right? We could come over here and we could say
6:40 "select * from users where..." You know, what
6:45 have we got? a name, id, name.
6:49 Really nice, right? We don't have any users yet,
6:51 but you can see that it did find the structure of it and let us open
6:56 that and explore it, which is pretty awesome.
6:58 So this will be really handy for us to make sure we're getting the right databases created when we work with SQLAlchemy