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