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 datetime.datetime.now () 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.