Python-powered chat apps with Twilio and SendGrid Transcripts
Chapter: Appendix: Modeling data with SQLAlchemy classes
Lecture: Concepts: Modeling

Learn more about this course
Login or purchase this course to watch this video and the rest of the course contents.
0:00 Before we actually start using SQLAlchemy
0:02 to 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
0:17 we're going to store in the database derived
0:19 from this dynamically defined SQLAlchemyBase class.
0:23 You can call it whatever you want.
0:25 I like SQLAlchemyBase.
0:26 But there's other, you know, it's just a variable.
0:29 Name it as you like.
0:30 So I want to create this singleton base class
0:32 to register the classes and type sequence on the database.
0:36 Remember, there's one and only one instance
0:38 of this SQLAlchemyBase shared across
0:41 all of the types per database.
0:44 So for example, we're going to have a package
0:46 to release a new user, they all derive
0:48 from this one and only one SQLAlchemyBase type here.
0:52 To model data in our classes, we put a bunch
0:55 of classable fields here, ID, summary, size
0:58 homepage, and so on, 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, they can
1:09 even be auto-incrementing, primary
1:11 keys 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
1:20 is 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, the function
1:32 not the value, the function, and then it's going
1:35 to call that function now whenever a row is created
1:38 and set that value to be, well, right now.
1:41 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 literally passed
1:49 an existing function, datetime.now
1:51 but above, we want to define this default
1:54 behavior 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
2:02 the dashes that normally separate it
2:04 into just one giant scrambled alphanumeric super thing.
2:10 You can create these default values
2:11 by passing any function, a built-in
2:13 one or one of your own making.
2:16 We also want to model keys and indexes.
2:19 So primary keys automatically have indexes.
2:22 We don't have to do anything there.
2:24 Let's our uniqueness
2:25 constraint as well as indexes.
2:28 This created one, maybe we want to sort
2:30 by the newest users, for example.
2:32 Well, if we're going to do that, we very
2:34 much want to put an index on that.
2:36 As I pointed out, indexes can have tremendous
2:39 performance benefits, it's totally reasonable
2:42 to have a thousand times difference performance
2:44 in a query, if you have tons of data
2:46 on whether you have an index or not.
2:49 Indexes do slow write time but certainly in this case
2:52 the rate of user creation versus querying
2:55 and interacting with them is, 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 can also specify uniqueness
3:07 we didn't do that in our example.
3:08 We can say this email, we can't have two users
3:11 with the same email, emails are very often
3:14 used to reset your password and if you have two users
3:17 who's going to get their password reset, all of 'em?
3:21 One of 'em, 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 only one user
3:28 gets to use a particular email
3:30 and that's super easy to do by just saying
3:31 unique=True. Finally, once all of the modeling is done
3:36 we have to actually create the tables.
3:39 Now turns out that that's super easy.
3:41 We import all the packages, get the connection string
3:45 and we can create an engine based on the connection string
3:47 and then we just go to SQLAlchemyBase
3:49 to it's meta-data and say create underscore
3:51 all and pass the engine, boom, everything is done.
3:54 Remember though, this only creates new tables
3:57 it does not modify existing ones
3:59 so if you need to modify it wait till we get to the alembic
4:03 chapter, the migration chapter
4:05 or do it yourself or if you're just in development
4:09 mode maybe deleting it and just letting it recreate itself
4:12 that might be the easiest thing, that's what we did.