#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.


Talk Python's Mastodon Michael Kennedy's Mastodon