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,
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.