Building Data-Driven Web Apps with Pyramid 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
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.