Building Data-Driven Web Apps with Pyramid and SQLAlchemy Transcripts
Chapter: Modeling data with SQLAlchemy classes
Lecture: The rest of the tables
0:00 With our package class mostly in place I've already created the other various tables that we're going to need.
0:08 So we went through creating packages in great detail and we're not quite done we still have to put some relationships on it.
0:14 But, let's just quickly look at the rest because they're exactly the same and it's not really worth taking tons of time to work it out. So downloads.
0:22 We're going to have a download thing. Again, it derives from SQLAlchemyBase when I altered the name here.
0:28 This is interesting. We have an integer, a bigint, primary key here and we're actually telling it to auto-increment.
0:35 So that way, we don't even have to set it. It's just going to set itself right? That's great. Similarly for created date.
0:41 So when we insert a new record we don't have to set those two values. But, for the download we got to store the release
0:47 and the package and the IP address and the user agent. And we might want to do queries by these showing me all the downloads for this release.
0:54 Count the number of downloads for that package. So we have an index on those. Languages, this is like programing languages
1:02 like Python 227 or something like that. So we have a little bit of info here. Pretty straightforward. Again, we're using the ID to be both the name
1:12 and the ID since it has to be unique. So, that will avoid one more join. Same thing for licenses basically here.
1:20 Want to have a little simple basic information. Here we have the maintainer table. And this represents the many to many relationship
1:27 between the user table and the packages. So we have one user can maintain many packages and one package can be maintained by more than one user.
1:36 So here we have that information. Real simple. These we already did. Releases. This is going to be a release of a package.
1:43 So the release is going to have an ID. More importantly its going to have a major minor build version and a date.
1:49 It also has a URL to download that version and maybe a comment on what's changed as well as the size of that download.
1:57 I'm going to put a package relationship in here but, that's not in place yet. We'll get to package relationships shortly.
2:01 And then finally, the user table. So we have the ID which is auto-incrementing. And we have their name, their email.
2:09 Hashed password, we will never store plain text passwords. Never! So, we don't want to just put password. We want to make it super clear that
2:18 this is a hashed password. Again, when do they create their account? What's their profile image? And we'll keep a record of when they last logged in.
2:25 Maybe we have a table, maybe an audit table but, for now, when did we last see these people? Have they logged in recently
2:30 or has it been like five years? And that's it. That's all the data that we need. Over here in our database we just have the one.
2:37 But if we re-run it. We go refresh. There are all of our tables. Perfect, right? So SQLAlchemy will make the new tables
2:47 but if we had made a change to packages that change wouldn't be applied. Remember, that's migrations. We'll get to that later.
2:54 Alright so here we are. We pretty much have this up and running and we've got all our data modeling done.
2:58 The only thing we don't have in place currently are the relationships. That's an important part of relational databases, right?
3:05 So we're going to put that in place and then I think we will have our models more or less than. Oh, one more thing I didn't point out
3:11 but I did kind of infer before. Now that we define these new models we have to put them all in here. I actually try and keep them in alphabetical order
3:20 so I can quickly look, oh have I added licenses? Let's see. Oh yeah there it is. You can end up with many of these.
3:26 So this alphabetical order thing can definitely help. But every time we add a new class, it has to go here so the SQLAlchemyBase sees it
3:34 that's why that create worked.