Full Web Apps with FastAPI Transcripts
Chapter: Appendix: Modeling data with SQLAlchemy classes
Lecture: Modeling packages
Login or
purchase this course
to watch this video and the rest of the course contents.
0:00
Let's start writing some code for our SQLAlchemy data model. And as usual, we're starting in the final here's a copy of what we're starting with.
0:09
This is the same right now, but we'll be evolved to whatever the final code is. So, I've already set this up in PyCharm and we can just open it up.
0:17
So, the one thing I like to do is have all my data models and all the SQLAlchemy stuff in its own little folder, here.
0:23
So, let's go and add a new directory called data. Now, there's two or three things we have to do to actually get started with SQLAlchemy.
0:32
We need to set up the connection to the database and talk about what type of database is it. Is it Postgres, is a SQLite, is it Microsoft SQL Server?
0:40
Things like that. We need to model our classes which map Python classes into the tables, right, basically create and map the data over to the tables.
0:50
And then, we also need a base class that's going to wire all that stuff together. So, the way it works is we create a base class
0:57
everything that derived from a particular base class gets mapped particular database. You could have multiple base classes, multiple connections
1:05
and so on through that mechanism. Now, conceptually, the best place to start I think is to model the classes.
1:10
It's not actually the first thing that happens in sort of an execution style. What happens first when we run the code
1:16
but, it's conceptually what we want. So, let's go over here, and model package. Do you want to create a package class Package?
1:24
Now, for this to actually work we're going to need some base class, here. But, I don't want to really focus on that just yet
1:29
we'll get to that in a moment. Let's stay focused on just this idea of modeling data in a class with SQLAlchemy that then maps to a database table.
1:39
So, the way it works is we're going to have fields here and this is going to be like an int or a string. You will have a created_date
1:47
which, is going to be a datetime. We might have a description, which is a string, and so on. Now, we don't actually set them to integers.
1:56
Instead, what we're going to set them to our descriptors that come from SQLAlchemy. Those will have two distinct purposes.
2:04
One, define what the database schema is. So, we're going to set up some kind of column information with type equals integer or something like that.
2:11
And SQLAlchemy will use that to generate the database schema. But at runtime, this will just be an integer
2:18
and the creator date will just be a datetime, and so on. So, there's kind of this dual role thing going on with the type definition here.
2:26
We're going to start by importing SQLAlchemy. And notice that that is turning red and PyCharm says, "you need to install this", and so on.
2:34
Let's go make this little more formal and put SQLAlchemy down here's a thing and PyCharms like "whoa, you also have to install it here".
2:41
So, go ahead and let it do that. Well, that looks like it worked really well. And if we go back here, everything should be good.
2:47
Now, one common thing you'll see people do is import sqlalchemy as sa because you say, "SQLAlchemy" a lot. Either they'll do that or they'll just
2:57
from SQLAlchemy import either * or all the stuff they need. I preferred have a little namespace. So, we're going to do, we want to come here
3:03
and say sa.Column. And then, we have to say what type. So, what I had written there would have been an integer. And we can do things like say
3:11
this is the primary_key, true. And if it's an integer you can even say auto_increment, true which, is pretty cool.
3:18
That way, it's just automatically managed in the database you don't have to set it or manage it or anything like that.
3:24
However, I'm going to take this away because of what we actually want to do is use this as a string. Think about the packages in PyPI.
3:33
They cannot have conflicting names. You can't have two separate packages with the name Flask.
3:38
You can have two distinct packages with the name SQLAlchemy. This has to be globally unique. That sounds a lot like a primary key, doesn't it?
3:47
Let's just make the name itself be the primary key. And then over here, we're going to do something similar
3:52
say, column, and that's going to be sa.DateTime. I always like to know almost any database table I have I like to know when was something inserted.
4:02
When was it created? That way you can show new packages show me the things created this week or whatever order them by created descending, who knows.
4:10
Now, we're actually going to do more with these columns, here. But, let's just get the first pass basic model in place.
4:15
Well, it's going to have a summary. It's going to be sa.Column(sa.String we'll have a summary, also a description. We're also going to have a homepage.
4:28
Sometimes this will be just the GitHub page. Sometimes it's a read the docs page, you never know but, something like that.
4:33
We'll also have a docs page or let's say a docs_url. And some of the packages have a package_url.
4:43
This is just stuff I got from looking at the PyPI site. So, let's review real quick for over here on say SQLAlchemy here's the, the name.
4:54
And then what, we're going to have versions that's tied back to the releases, and so on. Here's the project description we have maybe the homepage
5:02
we'll also have things like who's the maintainers what license does it have, and so on. So, let's keep going. Now, here's the summary, by the way
5:11
and then, this is the description. Okay, so this stuff is all coming together pretty well. Notice over here, we have an author, who is Mike Bayer
5:21
and we have maintainers, also Mike Bayer, right there and some other person. So, we have this concept of the primary author
5:28
who may not even be a maintainer anymore and then maintainers. So, we're going to do a little bit of a mixture. I'm going to say, author name
5:37
it's going to be one of these strings just embed this in here and it will do email so, we can kind of keep that even if they delete their account.
5:46
And then later, we're going to have maintainers and we're also going to have releases. These two things I don't want to talk about yet
5:52
because they involve relationships and navigating hierarchies, and all that kind of stuff we're going to focus on that as a separate topic.
5:59
The last thing we want to do is have a license, here. And for the license we want to link back to a rich license object.
6:06
However, we don't necessarily want to have to do a join on some integer id to get back just the name to show it there. It would be cool if we could use
6:15
somehow use this trick. And actually, we can, we can make the name of the license the friendly name, be just the id, right.
6:23
You would not have two MIT licenses. So, we'll just say this is an sa.Column(sa.String which, is an Sa.string.
6:35
That's cool, right, okay, so here is our first pass at creating a package. So, I think this is pretty good.
6:42
It models pretty accurately what you see over here on PyPI. There's a few things that we're omitting like metadata
6:48
and so on, but it's going to be good enough for demo app. A couple more things before we move on here.
6:55
One, if I go and interact with this and try to save it into create one of these packages and save it into the database
7:02
with SQLAlchemy, a couple of things. One, it needs a base class. We're going to do that next. But, it's going to create a table called Package
7:10
which is singular. Now, this should be singular, this class it represents a single one of the packages in the database when you work with it in Python
7:19
but, in the database, the table represents many packages all of them, in fact. So, let's go over here and use a little invention
7:27
in SQLAlchemy to change that table name but, not the class name. So, we'll come down here and say __tablename__ = 'packages', like so
7:37
So, if we say it's packages that's what the database is going to be called. And we can always tell PyCharm that, that's spelled correctly.
7:44
The other thing to do when we're doing a little bit of debugging or we get a set of results back in Python, not in the database
7:50
and we just get like a list or we want to look at it it's really nice to actually see a little bit more information in the debugger
7:56
than just package, package, package at address, address, address. So, we can control that by having a __repr__ and just returning some string here
8:05
like package such and such like this. I'll say self.id. So, you know, if we get back, Flask and SQLAlchemy we'd say, angle bracket package Flask
8:20
angle bracket package SQLAlchemy. So, that's going to make our debugging life a little bit easier as we go through this app.
8:26
Alright, so not finished yet but, here's a really nice first pass at modeling packages.