#100DaysOfWeb in Python Transcripts
Chapter: Days 33-36: Database access with SQLAlchemy
Lecture: Inserts
Login or
purchase this course
to watch this video and the rest of the course contents.
0:00
Let's return to our program. We're making some really good progress here. In this part, we have some import data. So, for example, if I come down here
0:10
and say show me all the available scooters. It just says there's none, really. And the reason it says there's none is there's nothing in the database
0:17
and we haven't written the code to do the queries. Let's go and talk about inserting data and then I'll show you the little import program
0:25
that'll pre-populate the database so we all have interesting data to play with and not totally empty things. And then we'll go from there.
0:31
Notice there's a lot of database operations here. Like importing data, renting a scooter, and so on. And we got to do things like, get
0:40
the scooter, or scooters, here. Find available scooters, and of course find available scooters has no real implementation.
0:47
'Cause we haven't written that. Now, we could write all of that code here and have a thousand line file, that would be terrible.
0:56
Or we could create a separate data access layer and write the functions there and if we change our mind about using SQLAlchemy
1:02
we can switch to something else and probably to change this code. So I'm going to go over here, and create a new directory called services.
1:10
And here I'm going to create a thing I'm going to call data_service. And the idea is we write some functions here that the rest of our program can call
1:18
and in this section we do all of our data access. So again, I'm going to start from some existing code here that we
1:24
already have, but it's, as you can, see empty. So what we want to do is let's focus on just this get_default_user. What's this about?
1:32
Well the idea is up here, there's this user object, and we want to populate this user object with kind of your account, you're logged in account.
1:42
So the first thing we're going to do is write this function. We'll just sort of make our way down the line here. So this will let us see how to
1:48
insert some data into the database. So how do we create a new record to insert? How'd you do this if it was a class?
1:55
If it were a class, you would type this: User, and then you would probably set some properties. Like the email is going to be test@talkpython.fm.
2:05
Username, is going to be 'Test user 1'. What else we have? Create a password, we don't need to set those things. The id's auto-incrementing.
2:15
So this will create them, and return them. But it won't put them in the database, right? What we need to do to put them in a database
2:22
is to create one of those units of work. So back we go to our session_factory. So our session_factory is going to create these sessions.
2:32
And we already have this thing defined. So we can come down here, and we could just say I would like to create a session, and
2:40
the way we do it is we execute the session_factory we've got here. And then what we're going to do is we're going to return the session.
2:48
Okay, great. I think this is just PyCharm being a little confused about what the factory is. It thinks it's none, it's not none. We've initialized it.
2:57
I guess we could check like we did above. So it's going to create our session, and we'll return it. Now this will totally work, there's
3:04
one other thing that's a little annoying. So I'm going to set this over here and actually say this is a session.
3:11
We can say, session.expire_on_commit is false. So you'll see sometimes if you insert some data, and then you try to work
3:20
with it some more, it'll not work. It'll say, it's expired, you have to go get it from the database again.
3:25
But we're going to say just let us continue to work with it. So here's how we do this unit of work. We say, session = session_factory.create_session.
3:35
Then later, we say session.commit. And here we do database stuff in between those two things. And in order to do an insert, we say session.add(user).
3:47
If we had gotten them back from the database, and did an update, we don't even have to do a line like that. It's sort of tied to the session.
3:53
But because it's brand new, we have to add it here. So this will work, sort of, it'll work. If we look over here, our email
4:02
actually does not have unique equals true but probably should. So maybe the insert, if that were the case, would fail the second time.
4:10
So actually what we want to do is to a test. We only want to insert this person if they don't exist. So that let's us do a quick, little test.
4:17
Show you how to do a query, we're going to say user equals this, and we're going to go to our session, and create what's called a query.
4:25
And you pass the type, we do a filter and then here we say user.email.first. We're going to use the same email address. We'll say if user return user.
4:41
So we're going to try to get them from the database 'cause only the first time do they need to be inserted from the database, right?
4:47
And then afterwards, we're just going to give them back right here. But the first time we'll create them save them, and then return them.
4:54
So let's make sure we're making use of this. Say, user... User default user, and we can just print out, found default user
5:15
with our email or something like that. We'll delete this in a second but just to see that it's working. If you go over here to our database
5:21
we can go to the users, and say jump to the console. Select star from user, nobody's there. Let's go run our program.
5:31
See we found our user, we re-run our query. We now have our user inserted. Alright, it looks like that is working, and that's
5:39
basically the pattern for the rest of this demo app. Create a session, we can do queries like this. We can do inserts like this.
5:48
And then if we did make changes, we call commit. If we don't, we could return the user. Like in this case, we created the session
5:54
and then returned the user, and did not call commit in that execution path. That was pretty straight forward. Let's go write the rest of these queries.