Building data-driven web apps with Flask 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 Now for the rest of the tables
0:01 I don't want to go and build them piece by piece by piece.
0:04 That'll get pretty monotonous pretty quickly.
0:06 We did that for package and it's quite representative.
0:09 So let's just do a quick tour of the other ones
0:11 I just dropped them in here for you.
0:13 So we have download and of course
0:15 change the database name
0:17 the table name to downloads, lower case.
0:19 And again we have an id, this one is an integer
0:22 which is primary key in auto-incrementing, that's cool.
0:24 created_date, most of our tables will have this
0:27 because of course you want to know
0:28 when that record was entered.
0:30 You almost always at some point want to go like
0:32 actually, when did this get in here?
0:34 Or all of these or something like that.
0:36 So always have that in there.
0:38 And then this is going to represent a download of a package
0:40 like pip install flask, right, like when that happens
0:43 if that hits the server
0:44 we want a analytical record of that.
0:46 So we want to know what package and what release we hit
0:49 and we may want to query or do reporting based on that
0:52 so we'll use an index here.
0:54 We also might just want to track the IP address
0:56 and user agent so we can tell what it is
0:59 that's doing the install.
1:00 That one's pretty straightforward, what's next?
1:02 Languages, again wrote in this trick
1:04 where the name of the language are like, Cython
1:07 or Python 3 or whatever.
1:09 That's going to be the primary key 'cause it was very unique
1:12 and then we also have when it was put there
1:13 and a little description about what that means.
1:16 Licenses like MIT or GPL or whatever, same thing.
1:20 One more, one more ID is the name.
1:23 Or you can avoid, join potentially
1:25 and get things a little quicker that way.
1:27 Little description and create a date, always want that.
1:30 This is an interesting one
1:32 here this is for our many to many relationship.
1:34 We have users and we have packages
1:36 and a user can maintain many packages
1:38 and a package be maintained by multiple users.
1:42 So here's our normalization table
1:43 we have the user ID and package ID
1:46 and these are primary keys.
1:47 Possibly we should go ahead and set up these
1:49 as foreign key relationships, but I didn't do it.
1:52 We'll do that over here, so this one hasn't changed
1:55 we still got to add our relationship there at the bottom.
1:57 Our releases, this one is going to have a major, minor
2:00 and build version all indexed.
2:03 Just an auto-increment and primary key.
2:05 Maybe some comments in the size of that release
2:07 and you know, stuff like this.
2:09 And finally we have our user
2:11 these are the users that log into our site
2:12 auto-incrementing ID, the name, it's just a string
2:17 email.
2:18 It has an index we should also say this probably is unique.
2:21 It's true as well 'cause we can't have two people
2:24 with the same email address, that would be a problem
2:26 when they want to reset their password.
2:28 We also, speaking of passwords, want to store that
2:30 but we never, never, never store just the password.
2:35 That is a super bad idea.
2:37 We're going to talk about how we do this
2:39 but we're going to hash the password in an iterative way
2:42 mix it in with some salt and store it here.
2:45 So to make that super clear
2:46 this is not the raw password
2:48 but that is something that needs transforming
2:50 we put hash password.
2:52 But we also want the created date, maybe their profile
2:55 and their last login just so we know like
2:57 who the active users are and whatnot.
2:58 That's all of the tables, or the classes that we've got.
3:02 If you look over here, I've updated this.
3:05 And guess what?
3:06 We don't need to go change like our db_session
3:09 or whoever cared about importing all these things.
3:12 It's all good.
3:13 Notice also that I put this in alphabetical order.
3:16 That means when you go add a new class or table
3:18 it's super easy to look here and see if it's listed
3:20 and if it's not, you know
3:21 put it where it goes alphabetically.
3:23 It'll help you keep everything sane.
3:25 So let's see about these tables
3:26 over here we have not run this yet.
3:28 Notice we just have packages, but if we rerun the app
3:31 what's going to happen?
3:36 It did not like that, our package singular.
3:39 Let's try again.
3:41 Here we go, and now if we refresh, resynchronize
3:45 boom, there they all are.
3:47 So we see our downloads and it has all the pieces
3:49 and the indexes say on like, package ID right there
3:52 our license and so on. Everything's great.
3:55 So SQLAlchemy did create the new tables
3:58 it did not make any changes to packages.
4:01 Luckily we haven't actually changed anything meaningful
4:04 about packages, so that's fine.
4:06 If we did we'd have to drop the table
4:08 or apply migrations, right.
4:09 But it's cool that at least these new tables we're defining
4:12 they do get created there and then
4:13 yeah it's pretty good.
4:14 All right so I think we're almost done
4:16 we're almost done we just have a few more things
4:18 to tie the pieces together to relationships
4:21 and our data modeling will be complete.