Building data-driven web apps with Pyramid and SQLAlchemy Transcripts
Chapter: Modeling data with SQLAlchemy classes
Lecture: The rest of the tables

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