Building data-driven web apps with Flask and SQLAlchemy Transcripts
Chapter: Modeling data with SQLAlchemy classes
Lecture: Indexes
Login or
purchase this course
to watch this video and the rest of the course contents.
0:00
Let's return to our package class that defines
0:02
the table and the database.
0:03
I said there was a few other things that we needed to do
0:05
and well, let's look at a few of the problems
0:07
and then how to fix them.
0:09
created_date. created_date is great.
0:11
What is it's value if we forget to set it?
0:14
Null. That's not good.
0:16
We could say it's required and make us set it
0:18
but wouldn't it even be better if SQLAlchemy itself
0:21
would just go right now, the time is now
0:24
and that's the time it's created on first insert?
0:26
Super easy to do that.
0:28
But, we've got to explicitly say that here.
0:29
We're here in the emails.
0:31
Maybe we want to be able to search by author/email.
0:33
So, we might want to have an index here so we can ask
0:35
quickly ask the question: show me all the packages authored
0:40
by the person with such and such email. Boom.
0:43
If we had a index, this could be super, super fast.
0:46
The difference in in terms of query time for a query
0:49
with an index and without an index with a huge amount
0:52
of data can be like a thousand time slower
0:55
it's an insane performance increase to have an index.
0:58
Helps you sort faster, it helps you query faster
1:01
and so on.
1:02
So, we're going to want to add an index on some of these.
1:04
And maybe the summary is required, but the description
1:07
is not required.
1:08
So, we would like to express that here as well.
1:11
So, SQLAlchemy has capabilities for all these.
1:14
So, let's start with the default value.
1:16
So, pretty easy, we're going to set defaults here
1:18
and it could be something like 0 or True or False
1:21
if that made sense, it doesn't for datetime.
1:23
Well, what would be the value for a datetime?
1:26
Well, let's use the datetime module and we can import
1:28
that at the top.
1:31
And use datetime.now.
1:32
Now, notice if I just press end and then enter
1:35
bicharm is super helpful and it puts parenthesis here.
1:39
That is a horrible thing that happened.
1:41
What this will do is take the current time
1:44
when the application starts and say well that time
1:46
is the default value. No no.
1:48
That's not what we want.
1:49
What we want, is we want this function now to be passed off
1:53
in any time SQLAlchemy is going to put something
1:55
in the database, it goes, oh, I have a default value
1:58
which is a function so let me call that now
2:01
to get the value.
2:02
So, that will do the trick of getting the insert time
2:05
exactly as you want.
2:06
Here we can say, nullable=False
2:09
you can say, nullable=True.
2:12
Not all databases support the concept of nullability
2:15
like, I think SQLite doesn't, but you don't want to
2:18
necessarily guarantee that you're always working
2:19
with that, right?
2:20
We may also want to say, all like, all the packages
2:23
or the top ten packages are the most recent ones.
2:26
And, for that you might want an index cause then that
2:30
makes it really fast to do that sort, so we can say
2:31
index=True.
2:33
And that's all you got to do to add an index
2:35
it's incredible.
2:36
We also may want to ask, you know, uh, show me all
2:39
the packages this person has written.
2:41
So, then we'd say index is true, that'll be super fast.
2:44
Also, you might ask, what are all the packages
2:46
or even, how many of them are there that have say the
2:49
MIT license?
2:51
And, then you could do a count on it or something, right?
2:53
So, this index will make that query fast.
2:56
These we'll deal with when we get to relationships.
2:59
But, these simple little changes here will make it
3:02
much, much better and this is really what we wanted
3:05
to define in the beginning, but I didn't want
3:06
to overwhelm you with all the stuff at the start with.
3:10
All right, so we have our database over here.
3:11
Let's go and you know, and we have it here as well.
3:13
Let's go and run the app, rerun it
3:15
see if everything's working and if we just refresh this
3:18
what's going to happen. Uh, sad face, nothing happened.
3:21
Where are my indexes?
3:23
Where is my null, well, nullability statements
3:26
things like that.
3:27
This is a problem, this is definitely a problem.
3:31
The reason we don't see any changes here is that SQLite
3:34
will not create or modifications to a table.
3:37
It'll create new ones, great new tables.
3:40
If I add a new table it'll create it like uh
3:41
release it or something, you would see it show up
3:43
when I ran it. But, once it exists, done.
3:47
No changes, it could lose data or it could make other
3:49
problems, so it's not going to do it.
3:51
Leader, and this is very common, you want this
3:53
but SQLAlchemy won't do it.
3:55
We're going to later talk about something called
3:57
Alembic which will do database migrations
3:59
and evolve your database schema using our classes here.
4:03
But, we're not there yet.
4:04
We just are trying to get SQLAlchemy going.
4:07
So, for now, what do we do, how do we solve this?
4:09
We could either just delete this file, drop that table
4:13
and just let it recreate it, right, we don't really
4:15
have any data yet.
4:16
When you get to production migrations, but for now
4:19
just super quick, let's just drop that table.
4:23
I'll rerun the app, refresh the schema, expando
4:28
and look at that, here's our indexes and author/email
4:30
creator date and license, here's our uniqueness
4:33
constraint on the id which comes with part
4:35
of the primary key.
4:36
You can see those, uh, blue things right there
4:40
those are the indexes.
4:43
So, we come over here and modify the table, you can see
4:45
here's your indexes and your keys and so on. Cool, huh?
4:50
All we need to do is put the extra pieces of information
4:53
on here and when we enter one of these packages
4:56
we'll get in and out and we'll have an index for it
4:58
and so on. Super, super cool.
