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