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 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.

Talk Python's Mastodon Michael Kennedy's Mastodon