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