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