Building Data-Driven Web Apps with Pyramid and SQLAlchemy Transcripts
Chapter: Modeling data with SQLAlchemy classes
Lecture: Concepts: Modeling
Login or
purchase this course
to watch this video and the rest of the course contents.
0:00
Before we actually start using SQLAlchemy to
0:02
insert data and query data and so on,
0:05
Let's talk about some of the core concepts we've seen
0:07
and some of the fundamental building blocks
0:09
for modeling with SQLAlchemy.
0:12
So we started with the SQLAlchemyBase.
0:14
Remember, the idea was every class we're going to
0:18
store in the database derive from this
0:20
dynamically defined SQLAlchemyBase class.
0:23
You can call it whatever you want.
0:25
I like SQLAlchemyBase, but there's other
0:27
you know, it's just a variable
0:29
name it as you like.
0:30
So we want to create this singleton base class
0:32
to register the classes and types that go in the database.
0:36
Remember, there's one and only one instance
0:38
of this SQLAlchemyBase shared across all of the types
0:42
per database. So for example, we're going to have
0:46
a package, a release, and user
0:47
they all derive from this one, and only one
0:50
SQLAlchemyBase type here.
0:52
To model data in our classes, we put a bunch of
0:55
class level fields here:
0:57
ID, summary, size, homepage, and so on.
0:59
And each one of them is a column.
1:01
SQLAlchemy.column and they have different types
1:04
like integer, string, and so on.
1:06
We can see some of them are primary keys
1:08
and even if it's an integer
1:09
they can even be auto-incrementing primary keys
1:11
which is really really nice.
1:13
And we can also have relationships
1:15
like we do between package and releases.
1:19
One really nice feature of databases is
1:20
they have default values.
1:22
We saw with our auto-incrementing ID
1:24
our primary key we don't have to set it
1:26
the database does that for us.
1:28
So here we can pass datetime.now
1:31
the function not the value, the function
1:34
and then it's going to call that function, now
1:37
whenever a row is created and set that value
1:39
to be, well, right now. That's super nice.
1:43
We can also do that up here with more complex expressions.
1:47
So in the bottom one we've literally passed
1:49
an existing function, datetime.now
1:51
but above we wanted to define this default behavior
1:55
in a more rich way.
1:56
So we're passing our very own Lambda expression
1:58
that takes the uuid for identifier
2:00
converts it to a string and then drops the dashes
2:03
that normally separate it
2:04
into just one giant scrambled alphanumeric soup thing.
2:10
You can create these default values by passing
2:12
any function a built in one or one of your own making.
2:16
You also want to model keys and indexes.
2:20
So primary keys automatically have indexes
2:23
we don't have to do anything there.
2:24
That's got a uniqueness constraint
2:26
as well as a indexes. This created one
2:29
maybe we want to sort by the newest users
2:32
for example. Well if we're going to do that
2:34
we very much want to put an index on that.
2:36
As I pointed out, indexes can have
2:39
tremendous performance benefits.
2:41
It's totally reasonable to have a
2:42
thousand times difference performance in a query
2:45
if you have tons of data
2:46
on whether you have an index or not.
2:49
Indexes do slow write time
2:51
but certainly, in this case the rate of user creation
2:54
versus querying and interacting with them is
2:57
you know, it's no comparison, right?
2:59
We're creating far fewer users probably
3:01
than we are querying or interacting with them.
3:05
We could also specify uniqueness.
3:07
We didn't do that in our example.
3:08
We can say this email
3:10
we can't have two users with the same email.
3:12
You know, emails are very often used to like
3:15
reset your password.
3:16
And if you have two users
3:17
who's going to get their password reset?
3:20
All of 'em? One of 'em?
3:22
Who knows, none of 'em?
3:23
So you might want to say there's a uniqueness constraint
3:25
on the email to say
3:27
"Only one user gets to use particular email"
3:30
and that's super easy to do by just
3:31
saying unique equals True.
3:33
Finally, once all of the modeling is done
3:36
we have to actually create the tables
3:39
and it turns out that that's super easy.
3:41
We import all the packages.
3:43
Get the connection string
3:45
and we create an engine based on the connection string
3:48
and then we just go to SQLAlchemyBase
3:49
to it's metadata and say
3:51
create_all and pass the engine.
3:53
Boom, everything is done.
3:55
Remember though, this only creates new tables
3:57
it does not modify existing ones.
3:59
So if you need to modify it
4:02
wait till we get to the Alembic chapter
4:04
the migrations chapter or do it yourself.
4:07
Or, you know, if you're just in development mode
4:09
maybe deleting it and just letting it recreate itself.
4:12
That might be the easiest thing
4:13
that's what we did.
