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.