Building Data-Driven Web Apps with Pyramid and SQLAlchemy Transcripts
Chapter: Modeling data with SQLAlchemy classes
Lecture: The database model
0:00 When you're trying to model something like PyPI a website with a database the clearer of a picture you have the better you're going to be.
0:10 So let's look around this running, finished version. Remember this is not, even though it looks very much like what we built.
0:16 This one is actually the finished one that we going to sort of be aiming for. Alright, and we're not going to look at the code
0:22 but we'll poke around what the web looks like. And we could just as well look at the real one but lets look at this. So on any given package
0:32 this is pulling up the package AMQP. And apparently that's a low level AMQP client for Python. OK, great I actually have never used this.
0:42 We have a couple of things going on here. We have the name of the package the version, bunch of different versions actually a description, right here.
0:53 We actually have a release history. So each package has potentially multiple releases. You can see this one had many
0:59 different releases and we can pull up the details about different ones, and jump back there. We have downloads, we have information
1:06 like the homepage, so right over here we go to GitHub, apparently that has to do with Celery. You could pull up some statistics about it.
1:14 It has a license, has an author. So, remember up here we have a login and register so we could actually go login to the site or
1:23 create an account just as a regular user and then we could decide as Barry Peterson apparently did, to just publish a package
1:32 and then there's a relationship between that user and this package as a maintainer and is probably a normalization table.
1:38 Also have a license, BSD in this case. And we want to model this situation in a relational database. Let's see how we do that.
1:47 PyCharm has some pretty sweet tooling around visualizing database structure. So here we're going to have a package and it's going to
1:53 have things like a summary and a description and a home page, a license, key words. Things like that. It has an author but it also potentially
2:03 has other maintainers so we have our users name, email, password, things like that and then I don't have the relationship drawn in
2:11 this diagram but there'd be a relationship between the user ID and the user ID and the package ID and the package ID there.
2:18 So this is what's often referred to as a normalization table for many to many relationships. So that's one part and then the package, remember
2:26 it has releases so here each release has an ID. It has major and minor build version a date, comments, ways to download that.
2:34 Different sizes as it changes over time. We also have licenses that relate back there. And we have languages.
2:43 So here, this is going to relate back to that ID right there. Finally we're not going to track any of this but there actually
2:50 are download statistics about this. About downloading all these packages and the different releases and so on so we went ahead and threw that in there.
2:58 This is what we're going to try to build but we're not going to build it in the database. We're going to build it in SQLAlchemy
3:04 and SQLAlchemy will maintain the database for us. I think the place to get started is packages so let's go on and do that.