Python-powered chat apps with Twilio and SendGrid Transcripts
Chapter: Appendix: Modeling data with SQLAlchemy classes
Lecture: The database model
0:00 When you're trying to model something like PyPI
0:02 a website with a database
0:05 the clearer of a picture you have
0:07 the better you're going to be.
0:09 So let's look around this running, finished version.
0:12 Remember this is not
0:13 even though it looks very much like what we built
0:15 this one is actually the finished one
0:17 that we're going to be sort of be aiming for.
0:20 Alright, now 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 the real one
0:26 but let's 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
0:37 client for Python.
0:39 Okay, great, actually I've never used this.
0:41 We have a couple of things going on here.
0:43 We have the name of the package, the version
0:46 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 different releases
0:58 and we can pull up the details about different ones.
1:02 Jump back there.
1:03 We have downloads, we have information like the homepage.
1:07 So, right over here we can go to GitHub
1:09 apparently that has to do with Celery.
1:11 We could pull up some statistics about it.
1:13 It has a license, it has an author.
1:16 So remember, up here we have a login and register
1:19 so we could actually go login to the site
1:22 or create an account just as a regular user
1:25 and then we could decide as
1:28 Barry Pederson apparently did, to just publish a package.
1:31 And then there's a relationship between that user
1:34 and this package as a maintainer
1:36 and it's probably a normalization table.
1:38 We also have a license, BSD in this case.
1:41 If we want to model this situation in a relational database
1:44 let's see how we do that.
1:46 PyCharm has some pretty sweet tooling around
1:48 visualizing database structure.
1:50 So, here we're going to have a package
1:52 and it's going to have things like a summary
1:53 and a description and a homepage
1:56 license, keywords, things like that.
2:00 It has an author, but it's also potentially has other maintainers.
2:03 So we have our users, name, email, password
2:06 things like that.
2:08 And then I don't have the relationship
2:09 drawn in this diagram
2:10 but there'll be a relationship between
2:12 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.
2:23 And then the package, remember, it has releases.
2:26 So here, each release has an id
2:28 it has a major/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
2:44 say to that id right there.
2:46 Finally, we're not going to track any of this
2:48 but there actually are download statistics about this
2:52 about downloading all these packages
2:54 and the different releases and so on
2:55 so we went ahead and threw that in there.
2:57 So 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 Think the place to get started is packages
3:08 so let's go on and do that.