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