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