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

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

Talk Python's Mastodon Michael Kennedy's Mastodon