Adding a CMS to Your Pyramid Web App Transcripts
Chapter: Appendix: Modeling data with SQLAlchemy classes
Lecture: Modeling packages
Login or
purchase this course
to watch this video and the rest of the course contents.
0:00
It's time to bring SQLAlchemy into our application and start modeling things in the database, and actually using a database. How cool would that be?
0:10
The place to start, I think, is modeling the tables with classes in SQLAlchemy. That's not actually the order, the first thing that happens
0:19
in order when you execute the program. We have to do things like create the connection connect to the database, make sure the tables
0:25
are configured and match the stuff that we're modeling things like that. But conceptually, the primary thing we're doing is modeling
0:33
the database with these classes, so let's start there. Now, I personally like to have a separate dedicated section for the various classes.
0:42
So we're going to create a sub-package. So here we have our data and let's start by modeling package, we'll call it packages, something like that.
0:52
So here's how it works in SQLAlchemy. We have a class, we give it a name. Typically its name should be singular but, you know, however you want.
1:00
It represents a single entity in the database anyway. It's going to derive from something here which we have to define, do that in a moment.
1:12
And then into it, we're going to put a bunch of columns so it's going to be like an int. And then we'll have, say this be a name
1:22
this is going to be a string, and so on. The things that we put on the right here are special SQLAlchemy values and they serve two purposes.
1:31
At design time, they tell SQLAlchemy how to actually create the tables. At run time, they're effectively this integers, strings, and so on.
1:40
So lets start by importing SQLAlchemy. Sometimes you'll see this as sa to keep things short so maybe we'll do that.
1:51
Now notice it says this is not defined so let's install this. Cause, it's actually not defined. While we're doing that, we can go over here
1:59
and also put it in the setup. Alright, now let's try it again. Oh perfect, so that's great. So we want a column, and in this column we would like.
2:10
Actually, we were going to hijack the name thing and just make that the primary key. Remember the name of the package has to be unique on PyPI anyways.
2:19
So let's just make this an sa.string and let's say primary key is true. Okay, starts to feel database-y right?
2:29
The next thing, this is just something I like in databases is always knowing when a record was created.
2:35
It lets you look at things that were recently created or see them in order, things like that. So let's add a created date. A datetime.
2:46
And we're going to expand on these columns and make them a little bit nicer. But let's just get the basic structure in place first.
2:51
A summary, now let me try to tie these back. So if we go over to SQLAlchemy here's going to be the name or the ID. This is the summary, right there
3:03
that little tiny short thing. So we'll come over here this is the sa.column. And then, this part down here that is a big fat long description.
3:14
So we'll have a description as well. We're also going to come over here and we'll have this little home page. We're going to be able to click on that
3:24
so we need to model the home page. Also a string, and a docs URL some of them have the ability to say here are the documentation.
3:42
and a package URL. Also you'll see that these have authors and maintainers and then, here they have an author. So the author's Mike Bayer
3:55
and Mike Bayers's one of the maintainers as well but so is this person, okay? So we need to model it having a dedicated author
4:04
but then also maintainers. Now we could do this to a relationship or we could put it directly on there. Maybe we want to keep it even in case
4:13
they delete their account. So I'm going to put the author information here and we'll use a join to get to the maintainers.
4:22
Then finally, these all have a license. Now we're going to use, oh let's see if we find the license here. Here we go. It's MIT.
4:33
We're going to use a similar trick as we did here we would like to show just this simple information
4:39
just the name of the license and maybe a link to it as well. Which is just the same as the title basically. So what we'd like to do is we could
4:47
set this up in a way where we have to do a join on a license table because we're going to have a set of licenses.
4:54
But, if we make the ID also be the name and be unique why would you have two licenses with exactly the same name? We won't.
5:03
So, because the name would be unique we could use this and then this could actually both be just the string but also the relationship.
5:11
So that'll avoid one join and make our table a little bit faster. That'll be sweet. So one more thing down here
5:17
we're going to have releases and we're going to have maintainers. But the releases and the maintainers requires relationships.
5:27
So we're going to come back to that in just a little bit but right now, things are looking pretty good for our table.
5:34
If I go and start working with SQLAlchemy and saying here's my package table well one it needs a base class for this to work.
5:40
But on the other, it's going to create a table called Package as a singular. I personally don't like that
5:48
I'd prefer to have plural names and maybe lowercase. So we can control how this maps, so the database by putting a __tablename__ here.
5:59
And we'll say lowercase packages so on the table it's going to show up as lowercase packages. But when we have one of them
6:06
it's going to show up as a Package like a class would normally in Python. The other thing is sometimes nice when we're debugging this
6:14
you would like to see in PyCharm without actually expanding all the bits what package you got back, and so on. So we're going to add a __repr__.
6:26
And we'll just return something really simple here we'll just return. Just the ID, so it'll be package request package SQLAlchemy
6:38
and so on if we're looking at a debugger output or printing a list of these things. Something like that.
6:44
So that'll be a little bit helpful along the way. Alright so here's the basic concept of what we want to build.