Python-powered Chat Apps with Twilio and SendGrid Transcripts
Chapter: Using our database with the SQLAlchemy ORM
Lecture: Saving an order to the database
Login or
purchase this course
to watch this video and the rest of the course contents.
0:00
We have our database created. We have our SQLalchemy ORM models are ready to go, our connection string set up. Let's go back to our API.
0:08
mean, after all, our goal has to fill this thing out, so we could record in order and let our people in the bakery
0:15
know, Hey, there's a new order you got to work on and ultimately fulfill it later on down the road,
0:20
I put three comments about some stuff that we're going to need to do here. We're going to focus on this video.
0:26
And how do we create or update a user and record their order, later We're also going to want to send them an invoice about this and let him know
0:35
Here is your receipt for your order, and then eventually we're going to need to send when we have it.
0:40
We're going to send that information back through JSON back to studio because our WhatsApp conversation
0:46
won't be finished. What we're gonna do is we're gonna say they were like,
0:49
I want a cake and I want this information and we'll say something like great your
0:53
order id is 'xyz', we will send you some more information through WhatsApp.
0:58
When your order is ready to be picked up and eventually somewhere down the road we'll do some baking, do some cooking,
1:04
and we will send another WhatsApp message as part of that conversation. So in order to keep that flow going, we want to return some kind of id
1:12
or something about the order details, so the studio can keep working with that, right now, like I said, we're going to focus on this.
1:20
I could start writing SQLalchemy code here, right in my API end point. That would be wrong. What I want to do is I want to isolate our
1:28
data access code into a special section. This will mean if we want to write a test around this function,
1:34
we can just patch or mock out that one function, call to some other place and completely remove a database dependency in our testing story and
1:42
also just for maintenance are code will be over in some other place. I'm going to call that a service not like a Web service,
1:49
but just a service provided to our application. Don't call this a user service. It's going to put the order on the user,
2:00
but also potentially create the user. So over here we have a record order. That effect was put a pass.
2:10
Now we're going to need to know something about the cake and something about the user
2:14
Luckily, we've already done a lot of the hard work on both ends of this story. Here on the DB side. We've got our models we want to save,
2:22
but on the model side coming in, remember, we're doing this cake order **data.
2:27
This is validated and parsing the data that comes in to give us a customer and a cake. If we look at customer,
2:34
well, these are the things we're going to need. So what we can do is actually pass that information along in our user service.
2:41
So we can say that's gonna take a user which is a customer. Maybe that's an odd way to put it, and it's going to have a cake, now down in here,
2:51
we can just say things like user.email or cake.size. Remember, these are all the things that we're going to need.
2:58
It was really nice that that information is already there and let's go and do this
3:02
little bit. Here is take away that you do the import a user service and then we can say record order and it's just cake_order.customer
3:13
cake_order.cake. Let's call this db_order. We're going to get some kind of database order back and then we're going to pass
3:25
it along. We want to do a little bit of extra work before we pass along, but basically, that's the flow. We've got this data sent over from Studio.
3:32
We're going to validate it automatically through pydantic and pass it along here, in order to
3:41
interact with the database. We're going to use the unit of work or that session and, like I said, my favorite ways to use a context manager.
3:50
We're going to import the session context from that session while that we had there and then later on throughout this whole thing,
4:02
we could just work with the context. Now this is intending to make a change to the database. Sometimes it's just a query, but this one.
4:09
We wanted to actually do a save, but this has an optional thing here that says should commit on success. I'm going to say True, these context managers,
4:20
these with blocks, they know whether or not you exited them cleanly or you exit with an exception. So all we have to do to make a change to the
4:27
database is, say, if you make it through this code, successfully save it to the database. If not, roll it back.
4:33
Don't save it. So it's a really clean way to do error handling in our application. The first thing we want to do is make sure that there's not already
4:41
another user here. We'll say existing_user. Actually, let's just shorten these because we're gonna want to use these names over and
4:49
over. I'll just call this the user here, and I want to do a query The way we do it was a session.query of the type is going to be user.
4:58
This is the ORM type. Then we could do a filter and say, user.email== u.email. If they pass in an email, we want to maybe say get that one back.
5:11
Or maybe we want to say user.phone= number, There we go. It looks like that might be an or this is actually an end.
5:22
And it's not what we're looking for, most likely. So what we want is if they have specified the email and we've
5:30
seen that email before, use that person. If they specify the phone and they've used that phone before, even if they've given us a different email,
5:37
it's still in our mind the same person. Okay, so in order to do that, we have to go up here and say from SQLalchemy,
5:45
import or_ like this, Here we can say or_ and pass in these things like that. We can wrap that around,
6:00
and then we also want to just get one of them so we can just say .first, right, Either that's going to give us no users back,
6:06
or it's going to give us the first one that matches. But because the way we're doing this, we should really only ever have one or zero.
6:14
If we've got to user back, great, we're going to associate their order with that user. But if not if not user and we want to create one real quick,
6:22
it's going to be a new one. So we'll say it's going to be a user. And what do we need to specify?
6:26
Has an ID. We don't need to set It is auto increment has a creative date. We don't need to set it. It's got a default function to create it.
6:34
The name, though the name we want to set. So this will be a you.name. What else have we got? We've got phone. It's gonna be you.number.
6:44
An email is going to be you.email. Anything else? Orders. We don't have to set those that's good.
6:52
This creates the user, but it doesn't held the database. We want to commit it, so we could just say session.add(user).
7:01
But we've either gotten our user or we're going to insert a new one. Either way, our user object user variable is ready to go, now
7:09
It's incredibly easy from here. We just create an order is going to be an order object like that from our database.
7:16
Then we specify the values again id not set as automatic creates Automatic fulfilled is later but size.
7:24
It's gonna go down to our cake.size and we'll just set these values. We're not passing the price over.
7:31
So what we need to do is add another variable to make sure we get that So let's go back and say Price as a float.
7:38
Now the last thing to notice we could set the user ID you notice. Here there's a user id is set to none.
7:44
We also could go to the session and add the order, but check this out. If we just go to the user and I go to
7:49
their orders, remember that relationship we set up? Check this out going to append as a list of the order.
7:57
That's it. It's now associated the foreign key relationship between order and user,
8:01
and it said that this order object needs to be inserted to the database, regardless of whether it's an existing or a new user.
8:08
When we exit this context block here, it's going to assume that makes that far succeeds. It's going to automatically commit because of this.
8:16
How neat is that? I kind of want to clean it up just a little bit. Let's just in line this session thing here because we're only using it in
8:22
one spot, So I'm gonna in line that with the refactoring tools using those two spots. Okay, so it's a little bit cleaner,
8:30
but this is it. Create a user or update an existing user, create the order and put them both into the database in a single transaction.
8:39
Super cool. We do need to update what we're calling this, So that's right here, we have the price just directly on the order,
8:50
along with a cake and customer. All right, I think it's ready. I think it's going to work. And we're also saying We're returning something.
8:56
So over here, let's go back and outside of our context, Manager, I'll just say Return order. Perfect. I think our whole processing in order.
9:08
Get it in the database. Associate with the user. It looks Good.