RESTful and HTTP APIs in Pyramid Transcripts
Chapter: Adding a database backend with SQLAlchemy
Lecture: Updating the repository to use SQLAlchemy
Login or
purchase this course
to watch this video and the rest of the course contents.
0:01
Ok, so we pretty much have sqlalchemy up and running, 00:03 everything is ready to go, we've got our tables model with our classes, 00:07 we've got our session factory, things like that. 00:10 But we can't really use it yet, so if we try to go 00:12 let's go back to or api here, if we go in here notice, 00:15 all of our data access is through this repository class 00:18 and the cars, they're not really coming out of what they're supposed to 00:23 they're just like loading this in memory thing 00:25 if we go look at this load data it's just like load the csv, which is fine, 00:28 that worked well for us as a temporary solution, 00:31 but that's not where we want to be. 00:33 So let's go and actually convert this to use sqlalchemy . 00:37 So, where are we going to put it, we'll get rid of this in a little bit, 00:41 I'm going to write a function that imports the entire csv into sqlite, 00:44 but for now, let's just assume that we're going to start 00:47 with empty database and go from there, and write these functions. 00:50 So what's that going to look like, so let's start with this simple one, 00:53 this is going to involve our session factory, 00:55 so any time you want to talk to the database 00:57 you're going to create one of these sessions and interact with it, 01:00 so it's almost always going to look like this, session equals create a session 01:03 and then do sqlalchemy stuff, so we're going to go here to our db session factory 01:07 and we'll call the one function we'd wrote create session 01:11 and at the end, we'll say session.close, right. 01:14 Now, in here in the middle, we somehow want to do a query by car id 01:18 and this is where we got to work with sqlalchemy, 01:21 you may have worked with it before and this is all old hat to you 01:24 but if it's new, here's how it works— we're going to go to the session, 01:27 and I want to say create a query of some type, right, we'll create a query of car, 01:30 and we can do things like filter, no intelisense by the way 01:34 which is unfortunate, order, order by, and so on. 01:37 So let's start with filter, that's the one we're looking for, 01:40 we want to say that the Car.id == car_id . 01:46 Now, what that's going to do is it's going to return potentially many cars, 01:50 it turns out that's the primary key so that is going to be one or zero, 01:53 but what we get back from the query filter operation here is a set 01:57 that we have to work with, and if we want, 02:00 if we know we're join just after one 02:02 like a primary key query like this, we'll just say first, 02:06 so we'll say car, now car may be none, car might be a thing, 02:10 but it's either going to be the car we're looking forward it's going to be none, 02:13 so we can go like this and then down here we just return car 02:16 we want to make sure we closed the session before we get out of here, 02:19 we could put that into a try finally but it's not a lot cleaner, 02:23 so I am just going to roll with this. 02:25 So this is kind of how it's going to look,
2:28
Let's go do the one where we're getting all of the cars, 02:30 we're going to create a session, and later we'll say session.close,
2:34
now how do we want to get all of them, right, 02:37 so it's going to be very similar to this, 02:40 we are going to start out like this, we'll say query, say query is this, 02:46 so it's going to be that, and we may want to apply this limit 02:51 if it's a number, like if it's 27 or something like that, 02:54 if it's none, maybe you just want to return all the cars,
2:58
right, but we don't want to just return them raw at the database, 03:00 we probably want to do some kind of sort, so we'll say order by 03:04 and then the way this works is you name, you specify the column 03:07 like this car. let's say, let's go with the year, 03:11 and we probably want the newest ones first, so we'll say .descending, like this 03:15 so that's going to sort descending.
3:19
And now, we can do a cool trick for our paging and skipping and stuff, 03:21 which really is like this limit is a special case of that 03:23 so we could say like this, we could say if limit,
3:28
right, if there's some number specified here we can say cars
3:31
is equal to our query and sqlalchemy — it's not actually executed yet 03:34 this is like the potential of a query if I called first or I called all 03:39 there's things I can do that would execute it 03:41 but this is just the potential of a query to execute
3:45
so we can actually do this, we can say I want to go from zero to limit and do a slice 03:49 and that slice will manifest itself at the database later as paging, 03:52 so it will like only return the first 25 by default I think we're doing.
3:58
Down here we could say well there's no limit than cars is query.all 04:02 and that will basically turn that into a list 04:05 which is important, we can't just return the query 04:08 we want to make sure it's totally executed and done before we call close here, 04:12 and then we'll just leave the return cars like we had before.
4:17
So this is going to be great, we've got our car by id, and then load data, we're not really going to need 04:22 generate ids, we're not going to need,
4:26
add car, this one gets to be interesting, so on one hand
4:29
we're going assume that this is going to be a car object, right 04:32 we could test it and so on, but we want to be a little careful here, right 04:37 because it was just in memory, we're like I'll just stick it in here 04:40 but if this came from a different query,
4:43
from a session that was detached or whatever 04:45 it might get a little weird, so what we're going to do is 04:48 we're just going to like create another car
4:51
copy the values over and insert it to the database 04:52 so how does this start, we'll create a session, surprise 04:55 so, of course we always create a session,
4:58
and then I'm going to say db car equals car like that 05:01 then I'm just going to copy over the values into this 05:05 so we have the inbound car, we have the db car 05:08 db cars we are going to save, so I'll say to do set values 05:12 and then the way to insert is I'm going to go to the session and say 05:14 here's a new object for your database, 05:17 it's going to look at the type an d say oh that goes into the car database and save it,
5:22
and then this isn't actually do anything, this is done within a transaction 05:25 which would roll back, so we are going to say session.commit 05:28 and then when it commits, it saves a car, it actually generates all the default values 05:34 so the default id, if it was auto incremented 05:37 would auto increment in the database in assign it 05:40 and so this is like after we committed, this is the car that's like cut all of its value set 05:44 so that's great, actually sorry, db car is the one we want to return 05:48 so this is going to work really well, except for now we got to copy the values across 05:53 so we'll do things like db_car.year = car.year, 05:56 very exciting, and we have a lot into set, alright, here now we've set it, 06:01 there's probably not an image set on the car when it comes in 06:04 so we'll set this one, I guess we could do this, 06:07 we could say car.image, if car.image, else get a random one and set it 06:13 okay, so this is looking solid, we're going to add this car 06:15 save it to the database you know, add it to the database 06:19 commit the transaction represented by this session 06:21 and then give back the fully populated value. 06:24 Similarly, we're going to do something over here 06:26 so let me just copy this bit, the difference is with this one,
6:31
let's get rid of this little part here 06:32 the difference with update cars, we want to update the existing one 06:36 so instead of just saying I'll create a new car 06:39 we're going to run the same code that we ran up here to get the individual car,
6:43
like this, and then we want to copy those values over 06:52 and this will be a car.id for the car that's coming in, 06:56 we'll copy all those values across, we want to add, we're just going to say commit
7:03
so how does that work, like when we query the database to get this back 07:05 it knows that this thing came from the database 07:08 and as we make changes to it, it marks this object as dirty 07:11 meaning to be updated, so when we call commit 07:14 it goes oh, this car was modified since it came back from the database
7:18
so push all the changes into the database, alright.
7:20
And then, last thing to do here for delete, 07:22 slightly more complicated because it's basically nothing going on there 07:25 when I get the car, say if not db car return then we'll just say session.delete db car,
7:35
and session.commit okay so that's all there is to it if we get a car back by id
7:43
they ask to delete, like we called add, we call delete, 07:46 and then we commit the change that we wanted to delete it.
7:49
Ok so now we should be in pretty good shape 07:50 let's run this and see if I left anything out;
7:54
no, you notice it's talking about the car table 07:57 but it didn't do anything because it already exists, 07:59 alright, try again, okay, click and ta-da, empty 08:04 that doesn't sound very exciting, does it, but it is, it's very exciting, 08:08 it didn't crash like we did that query, notice down here 08:10 select star, all that craziness is star from car order by year descending 08:16 it just happened to be empty.
8:20
So, let's go over to postman and see if we can create something now. 08:23 So here we have create auto, we're going to do as post to this body 08:27 Opel, let's just do they get really quick here,
8:31
look empty array json, okay so if we create this, this thing should exist 08:36 and if we left the image out it should populate it for us, 08:39 all right let's give this a shot, 500 server error, what have we done—
8:44
oh yes, the way that we were doing this is not so good 08:46 this takes one argument, but nine were given, not amazing, 08:50 all right let's go here, so remember this business right here, 08:53 there's a couple of ways we can create this object and set its value 08:57 but we can't say brand equals brand, 09:01 name equals name use keyword arguments here 09:03 clean that up a little bit, ok, so yeah when we were in our view model
9:08
we were allocating the car we can no longer create it
9:11
using that custom initializer we had, we have to use keyword arguments 09:14 or create an empty one and just set the values, let's try this one more time,
9:19
but the fact we got this far is looking pretty solid, let's try it again 09:22 ta-da, how cool is that, look at this, we created an object 09:25 and here, its id is even set, remember that came out of sqlalchemy, 201 created,
9:30
let's go and see what we've got in the database; boom, we have one, awesome, let's go and make another,
9:36
so let's create another one, this will be Opel oldie, and this is going to be 1500 euros for the price and the year is 1988,
9:51
and I'll just leave it like that, let's take a last seen away, 09:54 let's take the image away and damage away,
9:58
like these are the required values, so we should be able to go and actually work 10:01 by just running that, so let's see what happens when we execute that create 10:05 oh cannot parse, expected double quotes, did I miss something—
10:11
oh the extra comma, okay try again, the last seen field is required,
10:17
well, I actually don't think it's required any more, let's go change that, 10:19 where is our view model, we're already in it 10:23 so remember, the database is now generating a last seen
10:27
so let's just let the database generate. Now this car, notice it was last seen on 5.5,
10:34
this one generated and you can see perfect, it created all the stuff 10:39 and it was last seen there is the time of the recording, right now 10:42 May 9th, 2017, so let's go over here and do a get again,
10:48
notice, we've got our concept here and then we've got our oldie.
10:52
And why was it sort of like this, recall we're sorting by year 10:55 let's add one that should land in the middle of these things,
10:59
so an Opel, I'm running out of my understanding knowledge of Opel,
11:05
so this would be 2000, and let's put the year 2001, 11:08 so that should be in the middle right created just fine,
11:12
now, because we're sorting by the year, I didn't select that,
11:16
sorting by the year, we've got 2020, 2001, 1988, perfect, 11:20 how about that for adding the data layer, 11:23 now what's super notable about this, I think, in addition to a lot of things 11:27 we did not change this code, like we literally did not change this api, 11:34 any of the api functions here to make querying the autos, 11:38 to make creating autos, you'll see that updating 11:42 also would work the same, there's nothing we had to do here,
11:46
it's because the creation and validation happened in the view model 11:49 and the repository has taken care of all the rest, 11:52 and so you can tell we have a really well factored application here
11:56
because each type of change we had to make one talking of the database
12:00
and one about creation happened those were all isolated 12:04 to a single file, single class whose job was to do that,
12:08
so I'm really excited that it turned out that way.