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