RESTful and HTTP APIs in Pyramid Transcripts
Chapter: Adding a database backend with SQLAlchemy
Lecture: Concept: SQLAlchemy building blocks

Login or purchase this course to watch this video and the rest of the course contents.
0:01 Let's round out this database section by focusing on some core concepts around sqlalchemy ORM.
0:08 The first thing that we did was we created a declarative base, remember this is a factory function that will create a type
0:14 and this type will then be used for all of the base classes of all the models that we want, all the objects we want to store in our database.
0:22 So, we come over here and we say sqlalchemy.ext.declarative.declarative_base (), call that function it will generate a type,
0:30 now this single base type when you derive from it will register that hey here's another type that I'm going to manage
0:35 so for example, if we have a class car that derives from sqlalchemy base and service record and
0:41 owner we can go to sqlalchemy base this type we just generated and say create the database structure to deal with
0:47 and manage and store all the things that you are derived from. So this is usually the first step in the modeling side of things.
0:55 Now, once we want to create these individual classes that model the data in our database,
1:01 we're going to of course as we saw it derive from sqlalchemy base and then add a bunch of columns here
1:06 and these column types are both at runtime instances of the value they represent,
1:11 so at run time the id will be an integer, but at design time or query time they will act more like schema descriptors, ok.
1:21 So the way it works is we name all the fields, so id, name, year, price, image and so on,
1:27 each one of those is a column and then we can configure that column like the id is an integer, the name is a string, the price is a float and so on,
1:34 we also should indicate which one is the primary key, so id is a primary key,
1:38 and since it's an integer, we might want it to just be managed in the database so we can say auto increment equals true,
1:44 as soon as we save it a new id will be stored there for that record. Notice also we didn't talk about it in this chapter
1:51 but sqlalchemy represents and models relationships with cascading deletes, lazy loading, all sorts of stuff, so if you need to model relationships
2:01 be sure to use the sqlalchemy structures to do that. Now, sometimes we would explicitly set the values
2:07 and sometimes we would like the system to just generate them; an example we already saw was that auto incrementing primary key
2:14 the default values, you know, whatever the database decides the next id number it's going to be but, what if we have some other type,
2:21 for example, what if we want to have a last seen field and if we're updating the car in the database, maybe we want to manually set this last seen
2:30 but the very first time it's totally reasonable to say well we just put it in the database so we're seeing it now
2:36 we shouldn't have to explicitly set the last seen value which is if you create a new one, its value is when it gets inserted,
2:41 so how do we do that in sqlalchemy, we can set a default on the column and that is going to be a function that returns the right type of value.
2:48 So here we have our last seen, it's a datetime and we're going to pass the new function,
2:54 so remember, don't call the function don't say () that will actually just basically give you
3:01 a default value of whatever the time the program was run, you would like this function now to be called during inserts
3:07 so make sure you leave off the parenthesis, you can see that it doesn't have to be a built in function it could be like some kind of lambda,
3:14 so what if we want an interesting primary key that it is basically the uuid4 32 characters or something like that without the dashes
3:20 so we can specify a specific lambda expression it takes no arguments and returns the right types, here we're returning a string into our id column
3:29 which we've now adapted to be a string. We also should add indexes and keys, we've already seen primary key that makes a lot of sense
3:36 so primary keys automatically have indexes but if we would like to say go to our database and say show me all the cars
3:43 and show me the ones that are last seen latest, so some kind of sort on the last seen field;
3:48 or show me the cars that have only been seen in the last week, then we want to make sure we add an index here
3:54 and all we have to do is say index = true and sqlalchemy will manage that for us
3:58 but indexes dramatically improve performance when you have lots of data so it's very very important if you want your database to be responsive
4:06 you have indexes for anything you're going to sort by, or you're going to filter by. Now we can also enforce uniqueness constraints
4:16 so we didn't do this in this sample because I'm not sure about the data set but imagine the name of the car somehow had to be unique, right
4:22 we could have an index that is also a uniqueness constraint by saying unique = true,
4:27 then if we try to insert a car that has the name that already is in the database
4:31 it will crash and say no, no, no, you're going to conflict with this, things like that. The common use case might be e mail address for a user's table.
4:38 Right, now once we have it all modeled, how do we create the tables in the database, so one thing that we didn't have to do, it's kind of strange
4:47 sometimes you have to do this sometimes you don't, it just depends on how you interact with your code before you run this create all step here,
4:54 sometimes it's important that you explicitly import the types that are going to be modeled in the database,
5:00 so here we're doing import car, import owner, a bunch of other imports potentially, because if for whatever reason those modules
5:07 have not been loaded by the time you call sqlalchemy base metadata.create_all you are not going to have that table, it's not going to create it,
5:13 so you have to kind of show the sqlalchemy base type those things
5:17 and the most reliable way to do that is just to explicitly import them in this particular file. Then we need to create a connection string
5:25 our example use sqlight so we use sqlite:/// some file and if it doesn't exist it will automatically create that, and then we create an engine
5:32 and the engine is a thing that does connection pulling and things like that; you can say echo = false which makes it quiet,
5:38 or echo = true and it will spit out all kinds of information about what it's doing, that's good when you're getting started,
5:44 you're not really sure what happens when, flip that to true, watch it for a while get tired of it, turn it back off.
5:49 Once it's all ready to go, you can say sqlalchemy base, go to the metadata and say create all, pass it to the engine so it can find the database
5:56 and it will go and create all the tables, indexes, columns etc. And then, you're off to the races,
6:02 you have a database that maps exactly to your schema. Now, remember create all only makes new things it does not update
6:08 so if you make a change to car after the table exists, that change will not be moved to the database, you got to manage that directly in the database.
6:18 Finally, if we want to talk to the database, we're going to need to use this concept of a unit of work,
6:24 in sqlalchemy, the unit of work is manifesting this thing called the session factory so we're going to need to create a session factory
6:32 and that is created by calling this function session maker and passing the engine, so this is basically where the session factory
6:41 it will behind the scenes manage the connections and the unit of work and the transactions and all that kind of stuff,
6:48 so we give it the engine that we just work with these sessions. So once we have this, we have one of these in the entire program
6:53 one instance a session factory basically per connection string. Then, every time we want to do a unit work,
7:00 a couple of queries and insert an update, and delete and put that all together and commit or don't commit that
7:06 we need to use this session factory, so we'll say session of factory and call it, it's a callable, and it's going to create an instance of this session
7:12 we'll do things like ads and stuff, do some queries, maybe add some more things, change some of the items that came back from the query
7:20 and then when we're ready to push to the database, we just call commit. If you don't want to commit this work, right
7:25 if you only did a query there's no reason to commit it back, there's nothing to save you might as well just call session.close,
7:30 so that's a pretty quick whirlwind tour of sqlalchemy, but it shows you the vast majority of the moving parts
7:35 and I think you're ready to get started with sqlalchemy.

Talk Python's Mastodon Michael Kennedy's Mastodon