Building data-driven web apps with Pyramid and SQLAlchemy Transcripts
Chapter: Modeling data with SQLAlchemy classes
Lecture: Concepts: Modeling
0:00 Before we actually start using SQLAlchemy to
0:02 insert data and query data and so on,
0:05 Let's talk about some of the core concepts we've seen
0:07 and some of the fundamental building blocks
0:09 for modeling with SQLAlchemy.
0:12 So we started with the SQLAlchemyBase.
0:14 Remember, the idea was every class we're going to
0:18 store in the database derive from this
0:20 dynamically defined SQLAlchemyBase class.
0:23 You can call it whatever you want.
0:25 I like SQLAlchemyBase, but there's other
0:27 you know, it's just a variable
0:29 name it as you like.
0:30 So we want to create this singleton base class
0:32 to register the classes and types that go in the database.
0:36 Remember, there's one and only one instance
0:38 of this SQLAlchemyBase shared across all of the types
0:42 per database. So for example, we're going to have
0:46 a package, a release, and user
0:47 they all derive from this one, and only one
0:50 SQLAlchemyBase type here.
0:52 To model data in our classes, we put a bunch of
0:55 class level fields here:
0:57 ID, summary, size, homepage, and so on.
0:59 And each one of them is a column.
1:01 SQLAlchemy.column and they have different types
1:04 like integer, string, and so on.
1:06 We can see some of them are primary keys
1:08 and even if it's an integer
1:09 they can even be auto-incrementing primary keys
1:11 which is really really nice.
1:13 And we can also have relationships
1:15 like we do between package and releases.
1:19 One really nice feature of databases is
1:20 they have default values.
1:22 We saw with our auto-incrementing ID
1:24 our primary key we don't have to set it
1:26 the database does that for us.
1:28 So here we can pass datetime.now
1:31 the function not the value, the function
1:34 and then it's going to call that function, now
1:37 whenever a row is created and set that value
1:39 to be, well, right now. That's super nice.
1:43 We can also do that up here with more complex expressions.
1:47 So in the bottom one we've literally passed
1:49 an existing function, datetime.now
1:51 but above we wanted to define this default behavior
1:55 in a more rich way.
1:56 So we're passing our very own Lambda expression
1:58 that takes the uuid for identifier
2:00 converts it to a string and then drops the dashes
2:03 that normally separate it
2:04 into just one giant scrambled alphanumeric soup thing.
2:10 You can create these default values by passing
2:12 any function a built in one or one of your own making.
2:16 You also want to model keys and indexes.
2:20 So primary keys automatically have indexes
2:23 we don't have to do anything there.
2:24 That's got a uniqueness constraint
2:26 as well as a indexes. This created one
2:29 maybe we want to sort by the newest users
2:32 for example. Well if we're going to do that
2:34 we very much want to put an index on that.
2:36 As I pointed out, indexes can have
2:39 tremendous performance benefits.
2:41 It's totally reasonable to have a
2:42 thousand times difference performance in a query
2:45 if you have tons of data
2:46 on whether you have an index or not.
2:49 Indexes do slow write time
2:51 but certainly, in this case the rate of user creation
2:54 versus querying and interacting with them is
2:57 you know, it's no comparison, right?
2:59 We're creating far fewer users probably
3:01 than we are querying or interacting with them.
3:05 We could also specify uniqueness.
3:07 We didn't do that in our example.
3:08 We can say this email
3:10 we can't have two users with the same email.
3:12 You know, emails are very often used to like
3:15 reset your password.
3:16 And if you have two users
3:17 who's going to get their password reset?
3:20 All of 'em? One of 'em?
3:22 Who knows, none of 'em?
3:23 So you might want to say there's a uniqueness constraint
3:25 on the email to say
3:27 "Only one user gets to use particular email"
3:30 and that's super easy to do by just
3:31 saying unique equals True.
3:33 Finally, once all of the modeling is done
3:36 we have to actually create the tables
3:39 and it turns out that that's super easy.
3:41 We import all the packages.
3:43 Get the connection string
3:45 and we create an engine based on the connection string
3:48 and then we just go to SQLAlchemyBase
3:49 to it's metadata and say
3:51 create_all and pass the engine.
3:53 Boom, everything is done.
3:55 Remember though, this only creates new tables
3:57 it does not modify existing ones.
3:59 So if you need to modify it
4:02 wait till we get to the Alembic chapter
4:04 the migrations chapter or do it yourself.
4:07 Or, you know, if you're just in development mode
4:09 maybe deleting it and just letting it recreate itself.
4:12 That might be the easiest thing
4:13 that's what we did.