Full Web Apps with FastAPI Transcripts
Chapter: Storing data with SQLAlchemy
Lecture: Package and releases SQLAlchemy classes
Login or
purchase this course
to watch this video and the rest of the course contents.
0:00
So we've got our users created and we went real carefully through setting up the indexes and the primary keys and all that kind of stuff.
0:07
Let's just quickly drop in an existing package and release and talk through
0:11
it. There's really not a huge value in us spending tons of time working on the
0:15
data model. So I'm just gonna drop in two files and we're gonna talk through them.
0:19
All right. And this one is called "release" and this is called "releases".
0:23
So let's delete that and then call this one "release" just so we get that right and consistent. OK, so releases first,
0:32
it's a little simpler. Packages, there's one package, and there's many potential releases for each package and the idea is that it's gonna have an id,
0:40
just like before. And the release is gonna have a major, minor and build version,
0:44
which is an integer; and each of these air indexed because we might wanna query
0:49
by, you know, give me this certain version or all the versions above this version. Again, when was the release
0:55
created. There's a comment on the release, a URL for the release, a size in bytes of the release.
1:02
And then, it gets interesting because we have a relationship, we have a package_id, which actually represents a foreign key back to "packages.id".
1:12
So when you create these relationships, you've gotta be really careful. I find myself always just constantly getting tripped up by this.
1:19
When you say this line, you talk about the package_id and it's relationship, it's key. You speak database terms.
1:26
The database thing that is related is the lower case, plural, "packages". That's the table name that has a thing id.
1:34
But then we can also use this relationship to get an object that's lazy loaded through the ORM. When you speak in that thing,
1:41
you speak in terms of the object that happens to match that table. So here it's capital "P", singular "Package" class.
1:48
Here it's lower case "p", "packages", the table. Now once you get this down, no problem. But there it is. We're gonna create this foreign key,
1:56
one-to-many relationship. If you look at package, it's similar but slightly more complicated, id, created_date, last_updated, summary, description,
2:06
home_page, docs, package_url. All the stuff that we happen to have shown in the package details page, author_name,
2:13
author_email, the ur..., the license, excuse me, like MIT or whatever. And then, we have this relationship that is really quite an interesting ORM
2:22
thing right here. So the, this is the one-to-many. One package has many releases. Then we're gonna create a relationship over to the "Release" class
2:33
and it back populates "package", which is that thing right there. So if I get one, I don't have to do another query to get the
2:39
other, the reverse relationship. What's interesting is you can do an order by, not just an order by, but an order by composite key.
2:47
So show me the largest version by first, order by the major version and then the highest minor version and then the highest build version among those.
2:56
So I think that's pretty unique, you don't see that too often,
2:58
but really helpful. It'll let us easily get the latest to the oldest releases automatically just by touching this relationship,
3:06
we don't have to do anything. All right. Moment of truth. Let's see if I put everything together right here and if so,
3:13
our Beekeeper Studio, when we connect will not just have users, but it'll also have a package and a release.
3:20
It looks like it might, let's go connect again. Look at that. We have packages, again empty, but we'll fill it up a minute.
3:29
There's all the interesting elements and the releases right there. Super cool. You can't really see the relationships.
3:35
I don't know that there's like a visualizer diagram type thing. Maybe there is, I don't know where it is in Beekeeper Studio, there is in PyCharm
3:42
but sadly, it doesn't work in the version that I have. We've got our database all set up. It just has no data. Wouldn't it be cool if it had some data?
3:50
Well, we'll get to that in just a moment, but these three classes are now SQLAlchemy classes.
3:55
Let's just review real quick, ideas. They have to derive from SqlAlchemyBase, the elements get specified as columns and notice
4:03
one other thing I hadn't, didn't comment on before. Let's just do it for the users. Some editors are smart enough to know.
4:09
Yes, you said this is a column, but really, really, it says, it's an integer type column, so let's treat it as an integer.
4:15
And let's treat this one like a string and this one like a datetime. Now, not all the editors are smart enough to do that.
4:20
So let's go ahead and specify some type information here. We'll say, that's an int and that's a string and so on.
4:28
That's a string, that's a string. These are datetime.datetime's. So if I come down here and I say "u" equals "User" and I say u.created_date,
4:44
look at all the datetime stuff, perfect. It knows, right? If I go to "email.", look at all the string stuff,
4:50
it knows, perfect. So this just takes it up a little bit of a notch to say when you're working with the class in memory,
4:55
treat it like the native Python types that they ultimately will get out of the database
4:59
We've created a class, derive from SqlAlchemyBase, specified the columns and their features like indexes and uniqueness, and then importantly,
5:09
we put them into this so that they got imported. They got seen by the SqlAlchemyBase over here, on this line, right before we
5:18
called create_all. So it's that thing that looks at all the classes and then
5:22
creates the corresponding tables. And that's why our Beekeeper Studio, nice and cool, like it does over here with the tables.