#100DaysOfWeb in Python Transcripts
Chapter: Days 33-36: Database access with SQLAlchemy
Lecture: The rest of the queries
0:00 It probably doesn't feel like we've made tons of progress.
0:03 It seems like there's a lot to do.
0:04 We have these other queries we have to write.
0:06 But in fact, these are pretty simple
0:09 'cause they're kind of the same thing.
0:10 In fact, so much so
0:12 that I'm going to copy that line right there.
0:14 Any time we're going to do a query, we start with a session
0:19 and then we make some changes to it.
0:21 So we would like to do two things when we book a scooter.
0:25 One, we want to remove it from that location
0:28 and then we want to create a new rental.
0:30 Let's do the rental first.
0:32 To do the insert, we're going to do this
0:33 and we'll say session.add_rental
0:37 and then session.commit.
0:40 Okay, we need to set some properties here.
0:43 So we'll set the scooter_id to scooter.id
0:47 set the user_id, well, you guessed it, to the user.id
0:52 set the rental.start_time to now
0:57 and we'll set the rental.end_time to be the start_time
1:05 plus one day.
1:06 So the way we do that with time, so we say
1:08 timedelta and the days equals one.
1:11 So this is doing our rental, right?
1:13 We now have this rental, but the scooter
1:16 on the scooter object, it's still actually
1:18 oh, we want to do start time here.
1:22 So the scooter though, is still parked
1:24 as far as it's concerned.
1:25 Remember, if you look at the scooter
1:26 it's going to have a location id that's set.
1:29 So let's go and unset that.
1:31 Now, it would be nice if I could just say
1:32 scooter.location_id = None
1:39 however, this scooter object
1:41 was not retrieved from this session.
1:44 It's annoying, but that's the way it is.
1:46 So if you're going to make changes to the scooter
1:48 you have to get it from the database again.
1:50 So we'll say scooter =
1:53 session.query(Scooter).filter(Scooter.id == scooter.id)
2:06 So we want to go to the type, and sort of tell it
2:10 do the query where the id is actually this particular value.
2:13 And then, we want to make sure that we get one
2:15 and if it's not in the database
2:17 this is some sort of weird case that went wrong
2:19 so we'll say one().
2:21 And one will either give us the scooter back, or crash.
2:24 Now we're going to set the location like this
2:26 then we call commit.
2:27 That's actually going to push that change down
2:29 'cause it came from that session, right?
2:31 The other thing we want to do
2:32 is let's change the battery level
2:34 just so we can see the battery level change, right?
2:37 They've got it, it's going to drive
2:38 it presumably was charged when it was put
2:41 you know, left there at the scooter location.
2:44 Let's say 50 to 100, right?
2:46 So the battery level is going to be
2:47 somewhere between 50 and 100 on our scooter.
2:52 I think booking a scooter is all done.
2:54 Parking a scooter is going to be very similar.
2:57 So let's go, come over here and get our scooter
3:01 and we're just going to pass the id this time.
3:04 And then all we have to do is say the scooter location_id
3:08 is in this location, maybe set the battery level back
3:13 and then say session.commit.
3:16 Done, we've now parked that scooter in that location.
3:21 The final two queries we have to write here
3:23 are for show us the scooters
3:25 that are not parked in a location
3:27 or show us the scooters that are parked in a location.
3:30 And these are actually almost identical.
3:33 So let's come over here.
3:35 We'll do this, say scooters is this.
3:38 Of course, we don't want to do one
3:40 let's say we want them all.
3:41 And the query is going to be
3:43 that the scooter location_id is None.
3:48 Now, PyCharm is going to complain, and say
3:50 "well you're supposed to use is None."
3:52 In general that's true in Python
3:54 this is the way you have to write it
3:57 and you can tell PyCharm to chill a little bit.
3:59 It's still going to complain some, that's the way it goes.
4:02 Okay, so we're going to create a session
4:03 get our scooters back, make sure we iterate over them
4:07 so we can close our session and get that done, and so on.
4:10 So where the scooters have no location
4:13 those are the ones that are out for rental.
4:17 Where they do have a location
4:20 where the location is not None
4:22 these are the ones that are parked.
4:23 Look at this, these are all the queries we have to write
4:27 for this entire section.
4:29 We have our data access layer totally done
4:31 we just need to use it in our app
4:34 and also insert some data so we can actually test this out.