Building Data-Driven Web Apps with Pyramid and SQLAlchemy Transcripts
Chapter: Modeling data with SQLAlchemy classes
Lecture: Modeling packages

Learn more about this course
Login or purchase this course to watch this video and the rest of the course contents.
0:00 It's time to bring SQLAlchemy
0:02 into our application and start modeling things
0:04 in the database, and actually using a database.
0:07 How cool would that be?
0:09 The place to start, I think, is modeling
0:12 the tables with classes in SQLAlchemy.
0:15 That's not actually the order, the first thing that happens
0:18 in order when you execute the program.
0:20 We have to do things like create the connection
0:22 connect to the database, make sure the tables
0:24 are configured and match the stuff that we're modeling
0:28 things like that.
0:29 But conceptually, the primary thing we're doing is modeling
0:32 the database with these classes, so let's start there.
0:35 Now, I personally like to have a separate dedicated section
0:39 for the various classes.
0:41 So we're going to create a sub-package.
0:44 So here we have our data and let's start by modeling
0:48 package, we'll call it packages, something like that.
0:51 So here's how it works in SQLAlchemy.
0:53 We have a class, we give it a name.
0:55 Typically its name should be singular
0:56 but, you know, however you want.
0:59 It represents a single entity in the database anyway.
1:02 It's going to derive from something here
1:07 which we have to define, do that in a moment.
1:11 And then into it, we're going to put
1:14 a bunch of columns so it's going to be like an int.
1:18 And then we'll have, say this be a name
1:21 this is going to be a string, and so on.
1:24 The things that we put on the right here
1:27 are special SQLAlchemy values and they serve two purposes.
1:30 At design time, they tell SQLAlchemy how to actually
1:33 create the tables.
1:35 At run time, they're effectively this
1:37 integers, strings, and so on.
1:39 So lets start by importing SQLAlchemy.
1:44 Sometimes you'll see this as sa to keep things short
1:47 so maybe we'll do that.
1:50 Now notice it says this is not defined
1:53 so let's install this.
1:54 Cause, it's actually not defined.
1:56 While we're doing that, we can go over here
1:58 and also put it in the setup.
2:02 Alright, now let's try it again.
2:03 Oh perfect, so that's great.
2:05 So we want a column, and in this column
2:08 we would like.
2:09 Actually, we were going to hijack the name thing
2:13 and just make that the primary key.
2:15 Remember the name of the package
2:16 has to be unique on PyPI anyways.
2:18 So let's just make this an sa.string
2:22 and let's say primary key is true.
2:26 Okay, starts to feel database-y right?
2:28 The next thing, this is just something I like in databases
2:32 is always knowing when a record was created.
2:34 It lets you look at things that were recently created
2:37 or see them in order, things like that.
2:38 So let's add a created date.
2:43 A datetime.
2:45 And we're going to expand on these columns
2:46 and make them a little bit nicer.
2:47 But let's just get the basic structure in place first.
2:50 A summary, now let me try to tie these back.
2:54 So if we go over to SQLAlchemy
2:56 here's going to be the name or the ID.
3:00 This is the summary, right there
3:02 that little tiny short thing.
3:03 So we'll come over here this is the sa.column.
3:09 And then, this part down here
3:11 that is a big fat long description.
3:13 So we'll have a description as well.
3:19 We're also going to come over here
3:21 and we'll have this little home page.
3:22 We're going to be able to click on that
3:23 so we need to model the home page.
3:32 Also a string, and a docs URL some of them
3:35 have the ability to say here are the documentation.
3:41 and a package URL.
3:45 Also you'll see that these have authors and maintainers
3:51 and then, here they have an author.
3:53 So the author's Mike Bayer
3:54 and Mike Bayers's one of the maintainers as well
3:57 but so is this person, okay?
3:59 So we need to model it having a dedicated author
4:03 but then also maintainers.
4:07 Now we could do this to a relationship
4:08 or we could put it directly on there.
4:11 Maybe we want to keep it even in case
4:12 they delete their account.
4:13 So I'm going to put the author information here
4:15 and we'll use a join to get to the maintainers.
4:21 Then finally, these all have a license.
4:25 Now we're going to use, oh let's see if we find the license here.
4:29 Here we go.
4:30 It's MIT.
4:32 We're going to use a similar trick as we did here
4:35 we would like to show just this simple information
4:38 just the name of the license and maybe a link to it as well.
4:41 Which is just the same as the title basically.
4:44 So what we'd like to do is we could
4:46 set this up in a way where we have to do a join
4:48 on a license table because
4:50 we're going to have a set of licenses.
4:53 But, if we make the ID also be the name and be unique
4:57 why would you have two licenses with exactly the same name?
5:01 We won't.
5:02 So, because the name would be unique we could use this
5:04 and then this could actually both be just the string
5:08 but also the relationship.
5:10 So that'll avoid one join and make our table
5:13 a little bit faster. That'll be sweet.
5:15 So one more thing down here
5:16 we're going to have releases and we're going to have maintainers.
5:22 But the releases and the maintainers requires relationships.
5:26 So we're going to come back to that in just a little bit
5:28 but right now, things are looking pretty good for our table.
5:33 If I go and start working with SQLAlchemy
5:35 and saying here's my package table
5:37 well one it needs a base class for this to work.
5:39 But on the other, it's going to create a table called
5:42 Package as a singular.
5:45 I personally don't like that
5:47 I'd prefer to have plural names and maybe lowercase.
5:50 So we can control how this maps, so the database by putting
5:52 a __tablename__ here.
5:58 And we'll say lowercase packages
6:00 so on the table it's going to show up as lowercase packages.
6:03 But when we have one of them
6:05 it's going to show up as a Package
6:08 like a class would normally in Python.
6:10 The other thing is sometimes nice when we're debugging this
6:13 you would like to see in PyCharm
6:16 without actually expanding all the bits
6:18 what package you got back, and so on.
6:21 So we're going to add a __repr__.
6:25 And we'll just return something really simple here
6:27 we'll just return.
6:33 Just the ID, so it'll be package request
6:36 package SQLAlchemy
6:37 and so on if we're looking at a debugger output
6:40 or printing a list of these things.
6:42 Something like that.
6:43 So that'll be a little bit helpful along the way.
6:46 Alright so here's the basic concept
6:48 of what we want to build.