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