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 I've already created the other various tables that we're going to need.
0:08
So we went through creating packages in great detail and we're not quite done we still have to put some relationships on it.
0:14
But, let's just quickly look at the rest because they're exactly the same and it's not really worth taking tons of time to work it out. So downloads.
0:22
We're going to have a download thing. Again, it derives from SQLAlchemyBase when I altered the name here.
0:28
This is interesting. We have an integer, a bigint, primary key here and we're actually telling it to auto-increment.
0:35
So that way, we don't even have to set it. It's just going to set itself right? That's great. Similarly for created date.
0:41
So when we insert a new record we don't have to set those two values. But, for the download we got to store the release
0:47
and the package and the IP address and the user agent. And we might want to do queries by these showing me all the downloads for this release.
0:54
Count the number of downloads for that package. So we have an index on those. Languages, this is like programing languages
1:02
like Python 227 or something like that. So we have a little bit of info here. Pretty straightforward. Again, we're using the ID to be both the name
1:12
and the ID since it has to be unique. So, that will avoid one more join. Same thing for licenses basically here.
1:20
Want to have a little simple basic information. Here we have the maintainer table. And this represents the many to many relationship
1:27
between the user table and the packages. So we have one user can maintain many packages and one package can be maintained by more than one user.
1:36
So here we have that information. Real simple. These we already did. Releases. This is going to be a release of a package.
1:43
So the release is going to have an ID. More importantly its going to have a major minor build version and a date.
1:49
It also has a URL to download that version and maybe a comment on what's changed as well as the size of that download.
1:57
I'm going to put a package relationship in here but, that's not in place yet. We'll get to package relationships shortly.
2:01
And then finally, the user table. So we have the ID which is auto-incrementing. And we have their name, their email.
2:09
Hashed password, we will never store plain text passwords. Never! So, we don't want to just put password. We want to make it super clear that
2:18
this is a hashed password. Again, when do they create their account? What's their profile image? And we'll keep a record of when they last logged in.
2:25
Maybe we have a table, maybe an audit table but, for now, when did we last see these people? Have they logged in recently
2:30
or has it been like five years? And that's it. That's all the data that we need. Over here in our database we just have the one.
2:37
But if we re-run it. We go refresh. There are all of our tables. Perfect, right? So SQLAlchemy will make the new tables
2:47
but if we had made a change to packages that change wouldn't be applied. Remember, that's migrations. We'll get to that later.
2:54
Alright so here we are. We pretty much have this up and running and we've got all our data modeling done.
2:58
The only thing we don't have in place currently are the relationships. That's an important part of relational databases, right?
3:05
So we're going to put that in place and then I think we will have our models more or less than. Oh, one more thing I didn't point out
3:11
but I did kind of infer before. Now that we define these new models we have to put them all in here. I actually try and keep them in alphabetical order
3:20
so I can quickly look, oh have I added licenses? Let's see. Oh yeah there it is. You can end up with many of these.
3:26
So this alphabetical order thing can definitely help. But every time we add a new class, it has to go here so the SQLAlchemyBase sees it
3:34
that's why that create worked.