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