Building Data-Driven Web Apps with Pyramid 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
So our package table came out pretty well. But notice, there's a few things that you might want in a database that are missing here.
0:08
For example, we always have to set the created date explicitly, and if we forget it's going to be null, which is not great.
0:15
Maybe this is a required field and we don't want to allow it to be null. I think by default they're actually not nullable everything
0:23
but you can control those types of things. Default values. One of the really important things that people often overlook are indexes.
0:31
Indexes are incredibly important for fast and high performing databases. The difference between a large set of data with an index
0:39
and without can be a thousand times the query speed. So, it's pretty important to think about it. And in turns out to be really easy to do.
0:46
The primary key one already has an index by default and a uniqueness constraint. Let's just start knocking out those additional requirements.
0:54
Like, I want a default value here so we can just put a default. Now, you got to be really careful with this.
1:00
It can go wrong if you just make a minor, simple misstep. So what we're going to do is we're going to use datetime. So we'll import at the top.
1:09
We're going to use datetime. And we want to use now, okay? It's so easy to type that. If you type that, it is going to be very bad.
1:19
What that means is, the default value for when everything's created is going to be basically, app start time.
1:25
But if we omit this, and we pass the function not the return value of it, then the default will be
1:32
execute that function, and get the time when it's inserted. That's what we want. Maybe we want the summary to be nullable
1:39
so we'll say nullable as True. That means that it's not required. Something I want to leave for the description.
1:45
Same with the homepage, the doc, and the package. Maybe we don't necessarily have an author name. Well, we might not have one actually for the e-mail
1:54
but if we do, we would like to be able to search and say really quickly, show me all the packages that this author has published.
2:03
So we come down here and say index equals True, which is really nice. And similarly, maybe we want to know show me all the packages with MIT license.
2:12
That'd be kind of crazy, but if we want to ask that question having an index here is really important.
2:17
Of course, these are going to be foreign key relationships when we get them in place, but there. Now this is the table that we really wanted
2:23
to build at the beginning, just didn't want to overwhelm you with all the pieces. So watch this, if I run it again I'm sure its going to be amazing.
2:30
It's now, we've done that create table. Let me refresh it, and hmm. Where are my relationships? Here's something you got to learn about SQLAlchemy.
2:40
It will create the tables, it will not upgrade them it will not change them, it will not modify them. Once they're created, done. No changes.
2:49
What do you do? Well, in this situation, you just go over here and you just delete the database. You just delete it, and it's gone
2:57
and that works really well when you're just like really quickly prototyping, getting stuff in place. But if there's data there, if it's
3:04
in production, that's not going to fly. So later we're going to talk about something called Alembic
3:08
which will let you do migrations to take the database from one step to the next step, to the next step to the next step, and that works really well.
3:15
But for now, in this step, we're just going to delete the data, and put it back. So you can go over to here and drop the table.
3:25
It's gone. Rerun our code. And if we re-sync it, package is back, but this time notice that there's a little indexy right there and there.
3:36
And if we go and actually open it you can see we have an index on author email, and index on the ID of course, and so on.
3:47
I don't think, I guess the SQLite doesn't store the nullability. Doesn't have the concept of non-nullability versus nullability maybe here.
3:57
That's fine, we connect something like Postgres it'll convert that over, and definitely store those.