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