Building Data-Driven Web Apps with Pyramid and SQLAlchemy Transcripts
Chapter: Modeling data with SQLAlchemy classes
Lecture: The database model
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 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.