#100DaysOfWeb in Python Transcripts
Chapter: Days 33-36: Database access with SQLAlchemy
Lecture: The rest of the queries

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

Talk Python's Mastodon Michael Kennedy's Mastodon