Python-powered chat apps with Twilio and SendGrid 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
Let's start writing some code
0:01
for our SQLAlchemy data model.
0:04
And as usual, we're starting in the final
0:06
here's a copy of what we're starting with.
0:08
This is the same right now, but we'll be evolved
0:10
to whatever the final code is.
0:12
So, I've already set this up in PyCharm
0:14
and we can just open it up.
0:16
So, the one thing I like to do
0:17
is have all my data models and all the SQLAlchemy stuff
0:20
in its own little folder, here.
0:22
So, let's go and add a new directory called data.
0:26
Now, there's two or three things we have to do
0:28
to actually get started with SQLAlchemy.
0:31
We need to set up the connection to the database
0:33
and talk about what type of database is it.
0:36
Is it Postgres, is a SQLite, is it Microsoft SQL Server?
0:39
Things like that.
0:40
We need to model our classes which map Python classes
0:44
into the tables, right, basically create and map
0:47
the data over to the tables.
0:49
And then, we also need a base class
0:51
that's going to wire all that stuff together.
0:54
So, the way it works is we create a base class
0:56
everything that derived from a particular base class
0:59
gets mapped particular database.
1:01
You could have multiple base classes, multiple connections
1:04
and so on through that mechanism.
1:05
Now, conceptually, the best place to start I think
1:08
is to model the classes.
1:09
It's not actually the first thing that happens
1:11
in sort of an execution style.
1:13
What happens first when we run the code
1:15
but, it's conceptually what we want.
1:16
So, let's go over here, and model package.
1:19
Do you want to create a package class Package?
1:23
Now, for this to actually work
1:24
we're going to need some base class, here.
1:26
But, I don't want to really focus on that just yet
1:28
we'll get to that in a moment.
1:29
Let's stay focused on just this idea of modeling data
1:33
in a class with SQLAlchemy that then maps
1:35
to a database table.
1:38
So, the way it works is we're going to have fields here
1:40
and this is going to be like an int or a string.
1:43
You will have a created_date
1:46
which, is going to be a datetime.
1:48
We might have a description, which is a string, and so on.
1:52
Now, we don't actually set them to integers.
1:55
Instead, what we're going to set them to our descriptors
1:57
that come from SQLAlchemy.
1:59
Those will have two distinct purposes.
2:03
One, define what the database schema is.
2:06
So, we're going to set up some kind of column information
2:08
with type equals integer or something like that.
2:10
And SQLAlchemy will use that to generate
2:12
the database schema.
2:14
But at runtime, this will just be an integer
2:17
and the creator date will just be a datetime, and so on.
2:20
So, there's kind of this dual role thing going on
2:23
with the type definition here.
2:25
We're going to start by importing SQLAlchemy.
2:27
And notice that that is turning red
2:30
and PyCharm says, "you need to install this", and so on.
2:33
Let's go make this little more formal
2:34
and put SQLAlchemy down here's a thing
2:37
and PyCharms like "whoa, you also have to install it here".
2:40
So, go ahead and let it do that.
2:42
Well, that looks like it worked really well.
2:44
And if we go back here, everything should be good.
2:46
Now, one common thing you'll see people do
2:48
is import sqlalchemy as sa
2:51
because you say, "SQLAlchemy" a lot.
2:54
Either they'll do that or they'll just
2:56
from SQLAlchemy import either *
2:58
or all the stuff they need.
3:00
I preferred have a little namespace.
3:01
So, we're going to do, we want to come here
3:02
and say sa.Column.
3:05
And then, we have to say what type.
3:06
So, what I had written there would have been an integer.
3:08
And we can do things like say
3:10
this is the primary_key, true.
3:12
And if it's an integer
3:14
you can even say auto_increment, true
3:16
which, is pretty cool.
3:17
That way, it's just automatically managed in the database
3:20
you don't have to set it or manage it or anything like that.
3:23
However, I'm going to take this away
3:24
because of what we actually want to do
3:26
is use this as a string.
3:29
Think about the packages in PyPI.
3:32
They cannot have conflicting names.
3:34
You can't have two separate packages with the name Flask.
3:37
You can have two distinct packages with the name SQLAlchemy.
3:41
This has to be globally unique.
3:43
That sounds a lot like a primary key, doesn't it?
3:46
Let's just make the name itself be the primary key.
3:49
And then over here, we're going to do something similar
3:51
say, column, and that's going to be sa.DateTime.
3:55
I always like to know almost any database table I have
3:59
I like to know when was something inserted.
4:01
When was it created?
4:03
That way you can show new packages
4:04
show me the things created this week or whatever
4:07
order them by created descending, who knows.
4:09
Now, we're actually going to do more
4:10
with these columns, here.
4:11
But, let's just get the first pass basic model in place.
4:14
Well, it's going to have a summary.
4:16
It's going to be sa.Column(sa.String
4:22
we'll have a summary, also a description.
4:25
We're also going to have a homepage.
4:27
Sometimes this will be just the GitHub page.
4:28
Sometimes it's a read the docs page, you never know
4:31
but, something like that.
4:32
We'll also have a docs page or let's say a docs_url.
4:38
And some of the packages have a package_url.
4:42
This is just stuff I got from looking at the PyPI site.
4:46
So, let's review real quick for over here on say SQLAlchemy
4:50
here's the, the name.
4:53
And then what, we're going to have versions
4:54
that's tied back to the releases, and so on.
4:57
Here's the project description
4:58
we have maybe the homepage
5:01
we'll also have things like who's the maintainers
5:04
what license does it have, and so on.
5:07
So, let's keep going.
5:08
Now, here's the summary, by the way
5:10
and then, this is the description.
5:11
Okay, so this stuff is all coming together pretty well.
5:14
Notice over here, we have an author, who is Mike Bayer
5:20
and we have maintainers, also Mike Bayer, right there
5:22
and some other person.
5:25
So, we have this concept of the primary author
5:27
who may not even be a maintainer anymore
5:29
and then maintainers.
5:30
So, we're going to do a little bit of a mixture.
5:33
I'm going to say, author name
5:36
it's going to be one of these strings
5:37
just embed this in here
5:39
and it will do email
5:41
so, we can kind of keep that
5:42
even if they delete their account.
5:45
And then later, we're going to have maintainers
5:48
and we're also going to have releases.
5:50
These two things I don't want to talk about yet
5:51
because they involve relationships
5:53
and navigating hierarchies, and all that kind of stuff
5:55
we're going to focus on that as a separate topic.
5:58
The last thing we want to do is have a license, here.
6:01
And for the license
6:02
we want to link back to a rich license object.
6:05
However, we don't necessarily want to have to do a join
6:09
on some integer id to get back just
6:11
the name to show it there.
6:12
It would be cool if we could use
6:14
somehow use this trick.
6:16
And actually, we can, we can make the name of the license
6:20
the friendly name, be just the id, right.
6:22
You would not have two MIT licenses.
6:24
So, we'll just say this is an sa.Column(sa.String
6:30
which, is an Sa.string.
6:34
That's cool, right, okay, so here is our first pass
6:37
at creating a package.
6:40
So, I think this is pretty good.
6:41
It models pretty accurately what you see over here on PyPI.
6:45
There's a few things that we're omitting like metadata
6:47
and so on, but it's going to be good enough for demo app.
6:52
A couple more things before we move on here.
6:54
One, if I go and interact with this and try to save it into
6:59
create one of these packages and save it into the database
7:01
with SQLAlchemy, a couple of things.
7:03
One, it needs a base class.
7:04
We're going to do that next.
7:05
But, it's going to create a table called Package
7:09
which is singular.
7:11
Now, this should be singular, this class
7:13
it represents a single one of the packages in the database
7:17
when you work with it in Python
7:18
but, in the database, the table represents many packages
7:22
all of them, in fact.
7:23
So, let's go over here and use a little invention
7:26
in SQLAlchemy to change that table name
7:29
but, not the class name.
7:30
So, we'll come down here and say
7:31
__tablename__ = 'packages', like so
7:36
So, if we say it's packages
7:37
that's what the database is going to be called.
7:40
And we can always tell PyCharm
7:42
that, that's spelled correctly.
7:43
The other thing to do when we're doing
7:45
a little bit of debugging
7:46
or we get a set of results back
7:48
in Python, not in the database
7:49
and we just get like a list or we want to look at it
7:52
it's really nice to actually see
7:53
a little bit more information in the debugger
7:55
than just package, package, package
7:57
at address, address, address.
7:59
So, we can control that by having a __repr__
8:02
and just returning some string here
8:04
like package such and such like this.
8:10
I'll say self.id.
8:12
So, you know, if we get back, Flask and SQLAlchemy
8:16
we'd say, angle bracket package Flask
8:19
angle bracket package SQLAlchemy.
8:20
So, that's going to make our debugging life
8:22
a little bit easier as we go through this app.
8:25
Alright, so not finished yet
8:27
but, here's a really nice first pass at modeling packages.