Python for Entrepreneurs Transcripts
Chapter: Accessing databases from Python: SQLAlchemy ORM
Lecture: Demo: Part 5 Indexes and nullability
0:02 The final thing that I want to talk about are indexes and uniqueness.
0:06 So uniqueness constraints are super important,
0:09 for example, when we get to modelling users in the database,
0:12 we want to make sure that their username and their email are unique,
0:15 so that people don't create two accounts with the same name or same email,
0:20 that would be bad.
0:22 We are not going to worry about uniqueness just this moment,
0:24 although I guess we could, with like track names and so on,
0:27 but let's look at this one.
0:29 Display order is very likely something we want to query by, in fact,
0:33 in this relationship we said we even want to do an order_by here,
0:37 the best way to make databases fly, to make your site super fast
0:41 is to make sure you have indexes on anything that you are going to do a query by.
0:47 If you want to find all the albums, for a given year,
0:50 you are going to want an index on this.
0:53 If you want to order by price, you are going to want an index on this.
0:56 Now, there is a trade off, the more indexes you add the slower inserts become
0:59 but how often do you insert an album versus how often do you query it?
1:03 Thousands of times more reads than writes,
1:06 hopefully if you have a lot of traffic, right.
1:08 What we can do is let's suppose we want to query by year
1:11 so we can come over here and we can say "index", and this is all it takes,
1:14 index=True, boom, we're done.
1:17 So now we'll be able to query and order by index,
1:21 maybe we want to do that by price as well, order by,
1:25 let's go over here and display order we definitely want this to be like that,
1:29 maybe we want to have a uniqueness constraint,
1:32 on the name on both albums as well as the track names,
1:36 so we can't have three tracks with exactly the same name, there we go,
1:40 so now we have a little bit of a richer data model
1:43 that is going to enforce things like uniqueness here.
1:47 maybe we also want to make sure that our albums have a name
1:50 and so the name is required, it's not nullable,
1:53 so you can say nullable=False and that will tell SQLAlchemy
1:56 to put a basically to construct the tables to say guess what,
2:00 nullable=False, alright, again, just like we did last time,
2:04 there is no data in here, so let's just blow it away
2:07 and rerun this and we'll just recreate the data.
2:09 So we can look over here one final time,
2:12 and now you can see there is this vertical bars here
2:15 that represent their indexes on these things,
2:17 there is the dot (.) to represent that it's non-nullable,
2:21 in fact you can even come over here and look at the table
2:25 and you can see what does it look like, we could add new things here,
2:29 we could look at the primary keys, the indexes, the foreign keys and so on.
2:33 If we open up name, you can see that "not null" is checked here.
2:38 Whereas by default, these things are nullable,
2:41 so if you don't say "nullable=False", they're nullable
2:44 you might want to have that, keep that in mind.
2:47 So there you have it, we have albums,
2:49 we have tracks and I think they are modeled in a really decent real world way,
2:53 we have even the relationships between the model,
2:56 the uniqueness constraints, the indexes, these things are ready to go
3:00 and it's a little bit of extra work to create the fields like this,
3:04 but I think you'll see as we go through the rest of this course
3:07 that having this album class here makes the rest of our app a thing of beauty.