Full Web Apps with FastAPI Transcripts
Chapter: Storing data with SQLAlchemy
Lecture: SQLAlchemy session/unit of work setup
Login or
purchase this course
to watch this video and the rest of the course contents.
0:00
Welcome to chapter seven databases. Copy over the final result from Chapter 6 to 7, set it up as a new project and open it here so you'll have what we
0:09
finished with in 6 as well as what we're doing. Here we go. Let's open it up and get going.
0:14
First thing we need to do when we work with SQLAlchemy is create this thing that will create what are called sessions or units of work.
0:21
In order to do that, we've got to create an engine which binds to a database connection string. There's all that kind of initial set up.
0:28
We want to create the table structure based on the classes, and that's done through common couple of lines of code that always gets done once, and
0:36
exactly once, only once at startup for the application. You've probably seen SQLAlchemy before or you've watched the appendix
0:44
series that we talked about that, that does this. So let's go over here. I'm gonna create a class. Really, I'm gonna create a module,
0:51
excuse me, db and call it db_session and in here I already have some code. I want to just walk you through because it's really not super valuable for
0:58
us to take the time to go through all the details here. And if you're familiar with SQLAlchemy,
1:03
you'll know it. We don't have SQLAlchemy set up as a dependency yet. PyCharm will actually do that for us. It says, you want to install that? Yes,
1:10
please do. And then, once that's installed, it will say, you know, you really should add that to the requirements.
1:19
And I'm gonna say give me a compatible version. What I've done so far with, I'm going to say, give me the exact version,
1:25
actually. What I've done so far is just left the dependencies and versions open. That's usually the best, you'll get the latest.
1:34
But with this 1.0 - 2.0 difference, I'm gonna be really careful. So what we're gonna do is, I'm gonna pin it to this exact version.
1:42
You can put it on, whatever the latest version that works when you get a hold of it. But for now, I want to make sure if you check it out and run it,
1:48
it's gonna work now. And what we're gonna need to do is put 1.4.* something, 1.4.* something, or higher when we get to the next section to enable the
1:56
async and await features as well. So, here we are. We've got that specified and saved, so it's easy to get this back.
2:02
All right, so there's one other thing that we're gonna need in order to work with the ORM,
2:06
and it's we're gonna need to define a class called whatever you want. But I'm gonna call mine SqlAlchemyBase and we're gonna put it into a module
2:14
called modelbase. Let's do that first and we'll talk through this file here. So I'll call it this, and we'll say import sqlalchemy.ext.declarative and
2:27
we'll come down here and I will say this is gonna be just call declarative_base() and that's it. That's all we gotta do.
2:34
We just need this dynamically created base class to exist.
2:38
And actually, you can create multiple ones and associate different connection strings and databases with every class that derives from this.
2:46
So now we've got that in place. Let's talk through this. So what we need to do is at the beginning,
2:51
we're gonna use SQLite and SQLite takes basically just a file as a connection
2:56
string. If you had PostgreSQL or Microsoft SQL Server or whatever else you want to use, they have their own way of writing a connection string. For us,
3:03
we're gonna use SQLite, so we're gonna specify just a file. We're gonna check that If we've already done the set up, not gonna run a
3:09
second time, it'll make sure that there is a file, it doesn't have to exist. SQlAlchemy and SQLite will actually create the file if it doesn't exist.
3:18
But one thing that we do want to make sure is the folder structure where you specify it does. So down here you can see mkdir, I'm using this pathlib
3:27
thing say create the parents and it's fine if it exists. Going to say "sqlite:///", file name. That's the SQLAlchemy connection String for SQLite.
3:36
Then just shoot out a quick note about what's happening here. Then, we're gonna, we're gonna go and create an engine with that connection string.
3:42
If you want to see every command that goes to the database, set that to True, it's super chatty,
3:47
but it does give you a good sense of what's happening and then SQLite has, sometimes complaints about threading,
3:54
but it's also thread safe, so we can just tell it not to complain to us. And then what we're gonna do is create this thing called a factory
4:00
and that job is to create these units of work or this sessions, as you can see down here,
4:05
it's we just call this function, creates one of these by calling it, this is a
4:11
handy little thing that makes working with fields after you've saved them easier and then we're
4:16
gonna return it. OK, The last thing to do is we need to make sure that SQLAlchemy, the base class has seen everything that derives from it.
4:24
And the way that we're gonna do that is, we're gonna go over here and create a file like this, and there we'll say from data.package import
4:36
Package and data.user import User and release as well. These are not SQLAlchemy classes yet, but you know what? They're gonna be really soon.
4:49
So let's get this in place. Now, PyCharm is usually super, super helpful here, it says, look, you're not using that. If you wanna go over here,
4:55
you could Alt, Alt + Enter and it would go away because you're not using it. But in fact, we do wanna have this exist for this particular file
5:03
only because the whole point is just to do an import. And that's enough to trigger what we need for this base class to see that it
5:11
derives from those various other classes, as it will shortly. And then finally, we call
5:17
create_all on the engine, and it's gonna go create the database for all the tables that don't yet exist but have classes.
5:23
It will create those, it won't make updates to them, so be aware of that. But it'll create all the tables in this db_file
5:31
that we're going to have here. All we gotta do now is make sure that we call this and it should get our database all set up and ready to go.
5:40
Remember, this gets called once and only once. Hence the global_init not all_the_time_init. Let's go down here and add another thing to our config.
5:49
See these start to grow, those will be configure_db and what we need to do here is we need to come up with a file name or for the SQLAlchemy
5:57
db_file. So what I want it to look like is I want it to be in the working directory, then db then pypi.sqlite like that, and an
6:06
easy way to get a hold of that is we can go to the main file, Path, we'll import that from pathlib and we say it's the current file,
6:18
so that's the full path to main. And then from here we can say, parent, that will give us this directory and then we can say "/".
6:29
It's kind of funny, they override the divide operator to look like path concatenation, but it works. Then we can say pypi.sqlite like so. And then all
6:41
we've got to say is file equals that, and that should be what we get, what we want here. And we also want to have the absolute path.
6:49
I guess we could, do I need to do it like this? I'm not sure, whether I'd rather do it in a second line or do it like this,
6:55
but that'll give us the absolute path. And let's just print file for a second. OK, make sure we call over here configure_db(dev_mode).
7:03
We don't really need to use the dev_mode here, but let's go and make it available. We could do things like turn on the SQL
7:09
tracing or turn it off. Let's go and run this and just see we've got the file working. And here's the path that it found.
7:16
So here you see we have courses/fastapi/fastapi-apps/code/ch7... and I got the full user name there, chapter seven,
7:25
then db,/pypi.sqlite, perfect. That is what we wanted. So instead of printing out, let's go ahead and call it. We'll say db_session
7:32
and let PyCharm import that and then just say global_init(file). And over here, in a moment, we should be able to see a db folder created.
7:42
If I got everything right, let's find out. Look at that. I always forget this when I'm working with this Posix pathlib objects. When we called this,
7:52
we got a path object, when we called this, we got a path object. When we did this division combination thing, we got a path of object and so on.
8:00
All the way to this file, it's still not a string, it's a pathlib object. So to actually get it as a string, there's two things we could do.
8:07
We could say str() of it like that or we could come over and say as_posix() and that will print out, that'll give us just the string representation.
8:17
The problem was global_init tried to call strip. If there is white space, maybe we read it from a config file, there's like white space at the end,
8:23
or some weird thing like that, wants to guard against that. But it can't do that to a PosixPath, It can only do it to a string.
8:30
So let's try it one more time. Oh it worked. OK, that's good. And look what we printed out: connecting to the database with.
8:36
Here's our scheme that we need to use for SQLAlchemy. I noticed it looks like there's one too many slashes there,
8:43
but there's not. The thing that we're passing here is just the full absolute path. So triple slash and then the path just happens to be that also has a
8:52
forward slash. So it looks like we've got this set up correctly. If we go over here, we say reload from disk.
8:59
We now have a db folder with a little SQLite database in it.
9:04
It does not yet have any tables because the classes we've created are not actually SQLAlchemy classes yet. Once we create them,
9:13
then that whole process will actually create additional tables. Right now, it's just an empty database, but still, that's pretty cool.