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