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