Python-powered chat apps with Twilio and SendGrid Transcripts
Chapter: Appendix: 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
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.