Python-powered chat apps with Twilio and SendGrid Transcripts
Chapter: Appendix: Modeling data with SQLAlchemy classes
Lecture: The rest of the tables

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

Talk Python's Mastodon Michael Kennedy's Mastodon