Python-powered chat apps with Twilio and SendGrid Transcripts
Chapter: Using our database with the SQLAlchemy ORM
Lecture: Concept: Inserting data with SQLAlchemy
0:00 Insert data with SQLAlchemy. We use this unit of work pattern.
0:04 Some other ORM's use what's called active record,
0:07 where you work with one item and save it with SQLAlchemy.
0:10 You get everything all set up and then at the end,
0:12 boom. Make all those changes go to the database.
0:14 In order to do that, we created this wrapper around what they call a session
0:18 Call a session context. A wrapper is called as the Session Context,
0:22 and this allows us to use with blocks and just say,
0:26 If you go through this with block successfully,
0:28 please commit those changes to the database.
0:30 Everything that happened inside that with Block.
0:33 If there's an error, some kind of exception along the way,
0:36 roll back the transaction threw away the changes.
0:39 So what we're gonna do is going to say with session context.
0:42 And if you're doing changes, say,
0:44 commit_on_success=true. If you're not just doing queries,
0:47 you omit this or set it to false the default as well.
0:52 And then we're going to just do a query based on the SQLAlchemy session.
0:55 So ctx.session.query(user).filter,
0:59 and then we need to do two things either the email matches or the phone number
1:04 matches. So we're going to use this or_user.email==e.email,
1:09 It's very cool.
1:11 SQLAlchemy overrides what column type is with this descriptor,
1:16 so double equals(==) actually turned into a query.
1:18 This is quite neat, so we'll say,
1:20 user.email==u.email the passed.
1:23 in email or user.phone==u.phone in the passed in phone.
1:28 And that's going to get us the user who either has this email address or this
1:32 phone number. That's a query that could theoretically return many of them.
1:36 But we just want one and the first one if it's not going to be there
1:39 If there is no such user,
1:41 it's just going to come back as none.
1:42 So we need to test. Was there a match?
1:45 Was their user? If there's none,
1:47 let's just go quickly. Create one,
1:49 and we need to tell SQLalchemy as part of this context.
1:52 Part of this unit of work,
1:53 please insert this user into the database because they're new.
1:56 So we say, session.add_user,
2:00 Then finally, we're going to create an order object.
2:02 This is what we're trying to do here.
2:03 We're creating an order for what's coming.
2:06 So we've got c.size,
2:08 c.flavour, cake.size,
2:09 cake.flavour basically and so on.
2:11 I want to create the order and finally to tell it to insert into the database
2:14 Also, we're going to use that relationship between users and orders.
2:18 So we go to the user,
2:20 go to their orders list pin down the order and SQLAlchemy knows.
2:24 Oh, I need to associate these things and also put the order in the database
2:26 That's it. We've done some really cool stuff.
2:30 We've done some queries and potentially 1 to 2 inserts into the database in a really
2:34 clean and object oriented way using SQLalchemy.