Python-powered chat apps with Twilio and SendGrid Transcripts
Chapter: Appendix: 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
0:02
to 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
0:17
we're going to store in the database derived
0:19
from this dynamically defined SQLAlchemyBase class.
0:23
You can call it whatever you want.
0:25
I like SQLAlchemyBase.
0:26
But there's other, you know, it's just a variable.
0:29
Name it as you like.
0:30
So I want to create this singleton base class
0:32
to register the classes and type sequence on the database.
0:36
Remember, there's one and only one instance
0:38
of this SQLAlchemyBase shared across
0:41
all of the types per database.
0:44
So for example, we're going to have a package
0:46
to release a new user, they all derive
0:48
from this one and only one SQLAlchemyBase type here.
0:52
To model data in our classes, we put a bunch
0:55
of classable fields here, ID, summary, size
0:58
homepage, and so on, 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, they can
1:09
even be auto-incrementing, primary
1:11
keys 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
1:20
is 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, the function
1:32
not the value, the function, and then it's going
1:35
to call that function now whenever a row is created
1:38
and set that value to be, well, right now.
1:41
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 literally passed
1:49
an existing function, datetime.now
1:51
but above, we want to define this default
1:54
behavior 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
2:02
the dashes that normally separate it
2:04
into just one giant scrambled alphanumeric super thing.
2:10
You can create these default values
2:11
by passing any function, a built-in
2:13
one or one of your own making.
2:16
We also want to model keys and indexes.
2:19
So primary keys automatically have indexes.
2:22
We don't have to do anything there.
2:24
Let's our uniqueness
2:25
constraint as well as indexes.
2:28
This created one, maybe we want to sort
2:30
by the newest users, for example.
2:32
Well, if we're going to do that, we very
2:34
much want to put an index on that.
2:36
As I pointed out, indexes can have tremendous
2:39
performance benefits, it's totally reasonable
2:42
to have a thousand times difference performance
2:44
in a query, if you have tons of data
2:46
on whether you have an index or not.
2:49
Indexes do slow write time but certainly in this case
2:52
the rate of user creation versus querying
2:55
and interacting with them is, 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 can also specify uniqueness
3:07
we didn't do that in our example.
3:08
We can say this email, we can't have two users
3:11
with the same email, emails are very often
3:14
used to reset your password and if you have two users
3:17
who's going to get their password reset, all of 'em?
3:21
One of 'em, 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 only one user
3:28
gets to use a particular email
3:30
and that's super easy to do by just saying
3:31
unique=True. Finally, once all of the modeling is done
3:36
we have to actually create the tables.
3:39
Now turns out that that's super easy.
3:41
We import all the packages, get the connection string
3:45
and we can create an engine based on the connection string
3:47
and then we just go to SQLAlchemyBase
3:49
to it's meta-data and say create underscore
3:51
all and pass the engine, boom, everything is done.
3:54
Remember though, this only creates new tables
3:57
it does not modify existing ones
3:59
so if you need to modify it wait till we get to the alembic
4:03
chapter, the migration chapter
4:05
or do it yourself or if you're just in development
4:09
mode maybe deleting it and just letting it recreate itself
4:12
that might be the easiest thing, that's what we did.