Full Web Apps with FastAPI Transcripts
Chapter: Appendix: Modeling data with SQLAlchemy classes
Lecture: Concepts: Modeling
Login or
purchase this course
to watch this video and the rest of the course contents.
0:00
Before we actually start using SQLAlchemy to insert data and query data, and so on let's talk about some of the core concepts we've seen
0:08
and some of the fundamental building blocks for modeling with SQLAlchemy. So we started with the SQLAlchemyBase. Remember, the idea was every class
0:18
we're going to store in the database derived from this dynamically defined SQLAlchemyBase class. You can call it whatever you want.
0:26
I like SQLAlchemyBase. But there's other, you know, it's just a variable. Name it as you like. So I want to create this singleton base class
0:33
to register the classes and type sequence on the database. Remember, there's one and only one instance of this SQLAlchemyBase shared across
0:42
all of the types per database. So for example, we're going to have a package to release a new user, they all derive
0:49
from this one and only one SQLAlchemyBase type here. To model data in our classes, we put a bunch of classable fields here, ID, summary, size
0:59
homepage, and so on, and each one of them is a Column. SQLAlchemy.Column and they have different types like integer, string, and so on.
1:07
We can see some of them are primary keys and even if it's an integer, they can even be auto-incrementing, primary keys which is really, really nice.
1:14
And we can also have relationships like we do between package and releases. One really nice feature of databases is they have default values.
1:23
We saw with our auto-incrementing ID our primary key, we don't have to set it. The database does that for us.
1:29
So here we can pass datetime.now, the function not the value, the function, and then it's going to call that function now whenever a row is created
1:39
and set that value to be, well, right now. That's super nice. We can also do that up here with more complex expressions.
1:48
So in the bottom one, we literally passed an existing function, datetime.now but above, we want to define this default behavior in a more rich way.
1:57
So we're passing our very own lambda expression that takes the UUID for identifier converts it to a string, and then drops
2:03
the dashes that normally separate it into just one giant scrambled alphanumeric super thing. You can create these default values
2:12
by passing any function, a built-in one or one of your own making. We also want to model keys and indexes. So primary keys automatically have indexes.
2:23
We don't have to do anything there. Let's our uniqueness constraint as well as indexes. This created one, maybe we want to sort
2:31
by the newest users, for example. Well, if we're going to do that, we very much want to put an index on that.
2:37
As I pointed out, indexes can have tremendous performance benefits, it's totally reasonable to have a thousand times difference performance
2:45
in a query, if you have tons of data on whether you have an index or not. Indexes do slow write time but certainly in this case
2:53
the rate of user creation versus querying and interacting with them is, it's no comparison, right? We're creating far fewer users, probably
3:02
than we are querying or interacting with them. We can also specify uniqueness we didn't do that in our example.
3:09
We can say this email, we can't have two users with the same email, emails are very often used to reset your password and if you have two users
3:18
who's going to get their password reset, all of 'em? One of 'em, who knows, none of 'em? So you might want to say there's a uniqueness constraint
3:26
on the email to say only one user gets to use a particular email and that's super easy to do by just saying
3:32
unique=True. Finally, once all of the modeling is done we have to actually create the tables. Now turns out that that's super easy.
3:42
We import all the packages, get the connection string and we can create an engine based on the connection string and then we just go to SQLAlchemyBase
3:50
to it's meta-data and say create underscore all and pass the engine, boom, everything is done. Remember though, this only creates new tables
3:58
it does not modify existing ones so if you need to modify it wait till we get to the alembic chapter, the migration chapter
4:06
or do it yourself or if you're just in development mode maybe deleting it and just letting it recreate itself
4:13
that might be the easiest thing, that's what we did.