RESTful and HTTP APIs in Pyramid Transcripts
Chapter: Adding a database backend with SQLAlchemy
Lecture: Modeling classes

Login or purchase this course to watch this video and the rest of the course contents.
0:01 Okay so we have the basic foundational stuff that we need for sqlalchemy in place, we have our base class, we've got our connection string,
0:08 we've got our engine, we've got our session factory, alright that's all like boiler plate, basically it doesn't change at all per system.
0:14 What is very unique to this particular app, is the type of data models, right, so let's go over to this car and change how it works,
0:23 ok so we've got this stuff about parse, we're not going to need that anymore, let's create this car but this time instead of doing this stuff here
0:31 we're going to leave this to dict, but instead of doing this we're going to want to make this car a database object, basically
0:38 so we're going to come over here, and we're going to say this will be the sqlalchemy base, we're importing
0:44 this is the one that we made at the beginning of this chapter so then we're going to come down here and we need to define
0:50 the things that are associated with this object and the way you do this in sqlalchemy is you basically say
0:54 you define what look like fields, but they are really descriptors and they basically both tell sqlalchemy what kind of data
1:02 and how to model it at design time and then at runtime when you can instance of it, it'll become that value, all right,
1:07 so what we're going to start out by saying is we're going to have an id and this is going to be id just like we had before, this id right here
1:15 and it's going to be a string, so in sqlalchemy, let's go ahead and import sqlalchemy, we'll come down here and we'll say things like
1:26 this is going to be a column, then we have to say what type sqlalchemy.string, ok, and I guess I'll leave the name space module name on there for you
1:38 just to make it really clear where this is coming from, okay. Now, it doesn't have to be a string, it could be an integer
1:43 but I think we want to leave it to be a string, so this is interesting, let's say this is the primary key, the id is the primary key
1:51 now how to get a value, we'll come back to that in just a second. The next thing we have to have is a name, which is again a string
2:00 but this time it's not the primary key obviously, damage also a string we're going to have the year, now the year is actually not a string,
2:11 it shouldn't be a string, anyway, it's going to be an integer, this is like 1973, okay we're also going to have to last seen,
2:19 which is another type of date that's different, so we'll have last seen this is a datetime, okay, so it's a datetime object here
2:27 and these are basically the two ways in which we're managing the date, one was the year it was created which is obviously just a number
2:34 and then the last seen, we might want to sort by these or somehow usually sort by these, or maybe even match
2:41 like I want to see all the cars of 2001, whatever, right so we'll probably want to consider the kinds of queries that we're going to do there
2:50 let's go and say we're going to have another one of these, and we're going to have an image, also a string,
2:55 but unlikely that we need to say index on that or anything. okay, so here we have our class and let's see if I miss anything
3:03 oh we don't have a brand, let's do a brand, brand, name, price as an integer,
3:09 no, price is not there, price, we could say that's a float, but it hasn't been a float yet it's just an integer in our data set, so integer it is,
3:18 price, year, damage isn't there, last seen, image and id, great. Okay, so now we've got this object again here, this is good,
3:28 and the other thing we might want to control is where does it go in the database, like how does it get represented
3:36 so we can come here and say table name equals and we can put whatever we want like autos or whatever,
3:43 I find that life is basically easier if it exactly matches the type when you get into complex systems, because you have to talk about relationships
3:50 and sometimes it refers to the table and sometimes it refers to the object and if they're the same, then you don't have to think about which is which,
3:57 so that's all good, now the other thing we want to do while we're already here and already doing this modeling
4:03 is how do we want to query this and how do we generate a key, primary key. So, let's talk about the queries first, when you're working with databases,
4:14 it's super important that you think of all the queries you're going to do and that those are almost always—
4:20 there is certainly times where it doesn't have to be the case but if it's anything that you care about the performance,
4:25 is that it has an index, some things have to be unique some things have to be just fast, but this is where you're doing a where clause
4:33 or an order by clause, so let's go through and add the indexes, so you might want to say show me all the brands, right,
4:40 I want to see just the Chevrolets, just the Opels whatever. You also might want to sort by brand, if you're getting multiple ones
4:46 maybe you want to group them by brand, so either way we'll have index=true and that will make both of those operations faster.
4:53 Name, it's unlikely we care about quering by the name, it's very funky thing, we referred so far in our API everything by id anyway
5:01 same thing for damage, image as well, you might want to check, show me all the things that don't have images, maybe,
5:07 then like having an index might help, but we're not going to add one. Sort by price, minimum price, maximum price, absolutely
5:14 so price is going here, same thing I want to find all the modern cars that are less than five years old index right there,
5:21 and also you might want to sort by most recently seen so let's add an index to make sure the sorts and filters there are also quick.
5:29 The other thing that we might care about is that certain ones of these are required like brand is required, name is required,
5:34 and we have our validation and our view models but we also want to have that validation at the database level
5:40 in case somehow someone skips that, right. So let's go here and we'll say this is nullable, equals false
5:46 by default nullable is true they are not required but if you say this then it's basically in order to insert this object
5:53 it's going to have to have a brand, a name, damage is optional image is optional, price is required, year is required
6:00 and the last seen, let's say that's also required, but for last seen maybe like when we first created what we actually care about is
6:09 is well we are just seeing it now, we just put it in there, so instead of saying you must supply value
6:14 let's let sqlalchemy actually supply the value for us, so instead of doing nullable = false, let's say default and give it something for the default.
6:22 For the default, we can give it a function and it will call that function to generate the default value when it saves it, when it inserts it,
6:29 so let's go up here and say import datetime and the function I would like called is now,
6:36 so make sure you don't put it like this, no parenthesis, just the function the default is the function, right so it's going to call that
6:44 and it's going to set that value and you can imagine that the return value here should be that type and of course it is.
6:52 Okay, so this is all good, the other place where we need to worry about how this gets created, how these default values are
6:58 when we insert an object, let's just have this thing create its own id, if this was an integer, we could say auto incrementing is true
7:07 and it go 1234 and that would be fine, but it's not so let's use this default thing again here, now the thing I want to use
7:13 if we come down, let's just go here, I want to use this like that, uuid4, so I want to take this and basically have the primary keys look like that
7:27 when those are basically what we had already so lets just do that here now, if it wasn't for the string
7:33 I could just pass it this, right, and that would be cool but I want to call string on it as well, so we can give it a lambda,
7:40 a no argument lambda that's going to return this and of course we have to import right at the top, right, so here our default value is going to be
7:49 something that we create in terms of function and down here we just say call this other function that already exists.
7:54 All right, I feel like this type is good to go, we're pretty much ready to create this and insert it but again, we have no database, how do we do this?

Talk Python's Mastodon Michael Kennedy's Mastodon