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

Learn more about this course
Login or purchase this course to watch this video and the rest of the course contents.
0:00 When you're trying to model something like PyPI
0:02 a website with a database the clearer of a picture you have
0:06 the better you're going to be.
0:09 So let's look around this running, finished version.
0:12 Remember this is not, even though it looks
0:14 very much like what we built.
0:15 This one is actually the finished one
0:17 that we going to sort of be aiming for.
0:20 Alright, and we're not going to look at the code
0:21 but we'll poke around what the web looks like.
0:24 And we could just as well look at
0:26 the real one but lets look at this.
0:28 So on any given package
0:31 this is pulling up the package AMQP.
0:35 And apparently that's a low level AMQP client for Python.
0:39 OK, great I actually have never used this.
0:41 We have a couple of things going on here.
0:43 We have the name of the package
0:45 the version, bunch of different versions actually
0:49 a description, right here.
0:52 We actually have a release history.
0:53 So each package has potentially multiple releases.
0:56 You can see this one had many
0:58 different releases and we can pull up the details
1:00 about different ones, and jump back there.
1:03 We have downloads, we have information
1:05 like the homepage, so right over here
1:07 we go to GitHub, apparently that has to do with Celery.
1:11 You could pull up some statistics about it.
1:13 It has a license, has an author.
1:17 So, remember up here we have a login and register
1:19 so we could actually go login to the site or
1:22 create an account just as a regular user
1:25 and then we could decide as Barry Peterson
1:28 apparently did, to just publish a package
1:31 and then there's a relationship
1:33 between that user and this package
1:35 as a maintainer and is probably a normalization table.
1:37 Also have a license, BSD in this case.
1:41 And we want to model this situation
1:43 in a relational database.
1:44 Let's see how we do that.
1:46 PyCharm has some pretty sweet tooling
1:47 around visualizing database structure.
1:50 So here we're going to have a package and it's going to
1:52 have things like a summary and a description
1:54 and a home page, a license, key words.
1:58 Things like that.
1:59 It has an author but it also potentially
2:02 has other maintainers so we have our users
2:05 name, email, password, things like that
2:08 and then I don't have the relationship drawn in
2:10 this diagram but there'd be a relationship
2:12 between the user ID and the user ID
2:14 and the package ID and the package ID there.
2:17 So this is what's often referred to as
2:19 a normalization table for many to many relationships.
2:22 So that's one part and then the package, remember
2:25 it has releases so here each release has an ID.
2:28 It has major and minor build version
2:30 a date, comments, ways to download that.
2:33 Different sizes as it changes over time.
2:36 We also have licenses that relate back there.
2:39 And we have languages.
2:42 So here, this is going to relate back to that ID right there.
2:46 Finally we're not going to track any of this but there actually
2:49 are download statistics about this.
2:52 About downloading all these packages and
2:54 the different releases and so on
2:55 so we went ahead and threw that in there.
2:57 This is what we're going to try to build
2:59 but we're not going to build it in the database.
3:01 We're going to build it in SQLAlchemy
3:03 and SQLAlchemy will maintain the database for us.
3:06 I think the place to get started is packages
3:08 so let's go on and do that.