Building Data-Driven Web Apps with Pyramid and SQLAlchemy Transcripts
Chapter: Modeling data with SQLAlchemy classes
Lecture: Concepts: Modeling
Login or
purchase this course
to watch this video and the rest of the course contents.
0:00
Before we actually start using SQLAlchemy to insert data and query data and so on, Let's talk about some of the core concepts we've seen
0:08
and some of the fundamental building blocks for modeling with SQLAlchemy. So we started with the SQLAlchemyBase.
0:15
Remember, the idea was every class we're going to store in the database derive from this dynamically defined SQLAlchemyBase class.
0:24
You can call it whatever you want. I like SQLAlchemyBase, but there's other you know, it's just a variable name it as you like.
0:31
So we want to create this singleton base class to register the classes and types that go in the database. Remember, there's one and only one instance
0:39
of this SQLAlchemyBase shared across all of the types per database. So for example, we're going to have a package, a release, and user
0:48
they all derive from this one, and only one SQLAlchemyBase type here. To model data in our classes, we put a bunch of class level fields here:
0:58
ID, summary, size, homepage, and so on. And each one of them is a column. SQLAlchemy.column and they have different types
1:05
like integer, string, and so on. We can see some of them are primary keys and even if it's an integer they can even be auto-incrementing primary keys
1:12
which is really really nice. And we can also have relationships like we do between package and releases. One really nice feature of databases is
1:21
they have default values. We saw with our auto-incrementing ID our primary key we don't have to set it the database does that for us.
1:29
So here we can pass datetime.now the function not the value, the function and then it's going to call that function, now
1:38
whenever a row is created and set that value to be, well, right now. That's super nice. We can also do that up here with more complex expressions.
1:48
So in the bottom one we've literally passed an existing function, datetime.now but above we wanted to define this default behavior in a more rich way.
1:57
So we're passing our very own Lambda expression that takes the uuid for identifier converts it to a string and then drops the dashes
2:04
that normally separate it into just one giant scrambled alphanumeric soup thing. You can create these default values by passing
2:13
any function a built in one or one of your own making. You also want to model keys and indexes. So primary keys automatically have indexes
2:24
we don't have to do anything there. That's got a uniqueness constraint as well as a indexes. This created one maybe we want to sort by the newest users
2:33
for example. Well if we're going to do that we very much want to put an index on that. As I pointed out, indexes can have
2:40
tremendous performance benefits. It's totally reasonable to have a thousand times difference performance in a query if you have tons of data
2:47
on whether you have an index or not. Indexes do slow write time but certainly, in this case the rate of user creation
2:55
versus querying and interacting with them is you know, it's no comparison, right? We're creating far fewer users probably
3:02
than we are querying or interacting with them. We could also specify uniqueness. We didn't do that in our example. We can say this email
3:11
we can't have two users with the same email. You know, emails are very often used to like reset your password. And if you have two users
3:18
who's going to get their password reset? All of 'em? One of 'em? Who knows, none of 'em? So you might want to say there's a uniqueness constraint
3:26
on the email to say "Only one user gets to use particular email" and that's super easy to do by just saying unique equals True.
3:34
Finally, once all of the modeling is done we have to actually create the tables and it turns out that that's super easy. We import all the packages.
3:44
Get the connection string and we create an engine based on the connection string and then we just go to SQLAlchemyBase to it's metadata and say
3:52
create_all and pass the engine. Boom, everything is done. Remember though, this only creates new tables it does not modify existing ones.
4:00
So if you need to modify it wait till we get to the Alembic chapter the migrations chapter or do it yourself.
4:08
Or, you know, if you're just in development mode maybe deleting it and just letting it recreate itself. That might be the easiest thing
4:14
that's what we did.