Python-powered chat apps with Twilio and SendGrid Transcripts
Chapter: Appendix: 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're going to be sort of be aiming for. Alright, now 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 let's 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. Okay, great, actually I've 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 different releases
0:59 and we can pull up the details about different ones. Jump back there. We have downloads, we have information like the homepage.
1:08 So, right over here we can go to GitHub apparently that has to do with Celery. We could pull up some statistics about it.
1:14 It has a license, it has an author. So remember, up here we have a login and register so we could actually go login to the site
1:23 or create an account just as a regular user and then we could decide as Barry Pederson 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 it's probably a normalization table.
1:39 We also have a license, BSD in this case. If 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
1:53 and it's going to have things like a summary and a description and a homepage license, keywords, things like that.
2:01 It has an author, but it's also potentially has other maintainers. So we have our users, name, email, password things like that.
2:09 And then I don't have the relationship drawn in this diagram but there'll be a relationship between the user id and the user id
2:15 and the package id and the package id there. So this is what's often referred to as a normalization table for many-to-many relationships
2:23 so that's one part. And then the package, remember, it has releases. So here, each release has an id it has a major/minor build version
2:31 a date, comments, ways to download that 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 say to that id right there. Finally, we're not going to track any of this
2:49 but there actually are download statistics about this about downloading all these packages and the different releases and so on
2:56 so we went ahead and threw that in there. So this is what we're going to try to build but we're not going to build it in the database.
3:02 We're going to build it in SQLAlchemy and SQLAlchemy will maintain the database for us. Think the place to get started is packages
3:09 so let's go on and do that.