Building Data-Driven Web Apps with Flask and SQLAlchemy Transcripts
Chapter: 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
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.