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