Full Web Apps with FastAPI Transcripts
Chapter: Storing data with SQLAlchemy
Lecture: Package and releases SQLAlchemy classes
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.