Python-powered chat apps with Twilio and SendGrid Transcripts
Chapter: Using our database with the SQLAlchemy ORM
Lecture: Turning the classes into SQLAlchemy models

Learn more about this course
Login or purchase this course to watch this video and the rest of the course contents.
0:00 Now these classes were defined. They're interesting,
0:02 but they are definitely not SQLAlchemy classes.
0:06 So what we need to do is two things, we need to make these all derived
0:10 from a certain base class that comes from SQLAlchemy.
0:13 And then we need to actually initialize these two, what are called descriptors.
0:18 Certain column definitions saying this is an auto incrementing primary key integer column and this is
0:23 a string column and so on. At runtime,
0:26 it'll look exactly like what we had
0:28 Those will really be strings and integers,
0:31 but from the SQLAlchemy definition,
0:32 we need to tell it more information about the columns.
0:35 In order to do that, we need to define a base class.
0:39 Now, this might look like a little bit of overkill,
0:41 but I'm going to create a well,
0:43 I'll just call it base class or something like that,
0:48 and we're going to give it a name
0:50 SqlAlchemyBase. Now, normally we need to find a class.
0:54 You do it like this, you'd say class SQLAlchemyBase,
0:56 but we're going to dynamically defined this at runtime.
0:59 But we're going to use SQLAlchemy, now
1:02 We don't have SQLAlchemy installed yet,
1:03 do we? Because otherwise that would come up.
1:06 Your PyCharm would actually let us install it here if we like,
1:09 or we can come over and just put it right here like this.
1:14 Let's press install, wait a moment and we're good to go.
1:19 But it's not just SQLAlchemy
1:21 We want we want ext.declarative and copy that whole thing down here.
1:26 The way we're going to do this is at runtime.
1:28 We're going to say .declarative_base().
1:31 That's it. You can have multiple ones of these with different connection strings associated with
1:36 different databases all within the same app.
1:38 Okay, so what we're gonna do is we're going to use this as our base
1:41 class for a user like so and imported and for order,
1:48 like so and imported. That's step one.
1:51 The other one is Let's go up here and we're going to say,
1:54 import SQLAlchemy. We could just say this and SQLAlchemy,
1:59 but let's say as sa, so we can type a little bit less here because
2:02 we're going to say, SQLAlchemy or sa,
2:04 the package name a lot. I want to go as far as directly importing the
2:08 types I still like to have the auto complete from the name space you can say
2:12 Import column if you like. So we want to column here,
2:15 and this is going to be an sa integer.
2:18 But we also want to say it's a primary key,
2:21 right? Every table needs a primary key,
2:24 and we don't want to control it or specify what it is.
2:27 I just when I save a new order,
2:28 I want the database to automatically figure out what the latest number is safely and set
2:33 it, so we'll say auto increment is true, right over here.
2:39 We're going to say this is sa column of sa date Capital D DateTime
2:44 like that. Then add some more things to them.
2:47 But let's quickly throw these out.
2:48 It's going to be a string, going to be a float.
2:58 It's going to be another integer and this one I'm gonna comment it out for just
3:03 a second. We're going to set up some relationships in a moment,
3:05 but let's not get that much into the detail.
3:08 One more thing here. This just like here with this auto increment.
3:12 I don't wanna have to deal with setting the created date every time I inserted item
3:16 I just wanted to go look when you put a new item in there,
3:19 set the date. But there's a really nice way to do that.
3:22 We can come over and say Default.
3:24 Let's wrap this a little more. So it's easier for you all to read.
3:26 Default = And then we give it a function that's called when things are inserted.
3:30 So daytime.datetime.now, not be super careful.
3:34 If you hit enter, it's going to put parentheses here.
3:37 That is not what you want.
3:39 Then the default would be for all the things get inserted when the program started.
3:43 You want a function that's called every single time?
3:46 I want to set the default to this.
3:50 These all look okay. We don't necessarily need to do anything with that,
3:54 and we'll deal with our foreign keys set up here in just a minute.
3:57 Now, the next thing we want to think about what this order is,
3:59 how do we want to query it?
4:01 Well, maybe we want to order by create a date or ordered by fulfilled data
4:05 Show me the unfulfilled items,
4:07 and we want to do that quickly.
4:09 It's incredibly powerful and important to add the right indexes.
4:13 So what we can do over here and say index is true without one little bit
4:18 right there. We're going to make querying or sorting by created date incredibly easy.
4:23 Similarly, here, show me the unfulfilled items where that's none or null boom off
4:28 it goes. If you're going to sort by size or flavor or something,
4:32 you could do that. Maybe we'll do that for the price,
4:34 right? Show me, you know,
4:36 stuff that costs so much. This will be a foreign key,
4:39 so we shouldn't have to set an index on that.
4:41 Okay, so this is going to be the SQLAlchemy version of our order,
4:45 and we're going to do the same thing over here again.
4:48 id:int = sa.Column(sa.Integer, autoincrement=True) that is auto incrementing
4:55 like so and I'll just copy this created date as exact same story.
5:01 We wanted to auto insert auto-
5:03 set its value and sort by it.
5:05 And then down here, this will just be sa column.
5:09 I say sa.string again, we'll comment that out for just a moment now for
5:18 the email or phone. If we get a second order from the same phone,
5:21 I'd like to get that user.
5:23 I'm associating a second order with the same user,
5:25 not creating two users. In order to do that quickly,
5:29 we want an index. Thanks for the email.
5:33 Probably not by name, right?
5:35 They might say one time. Their names Michael, the next time their names.
5:37 Mike, right? We don't really care about that,
5:40 but that's important here. So these will come back into play when we set up
5:45 our relationship. But other than that,
5:47 we've created a SQLAlchemy Base are two classes derived from it,
5:50 and we've set the columns to be
5:52 SQLAlchemy descriptors, describing exactly what we want.
5:56 Primary keys, default values, indexes and so on.