#100DaysOfCode in Python Transcripts
Chapter: Days 91-93: Database access with SQLAlchemy
Lecture: Demo: Connecting to the database
Login or
purchase this course
to watch this video and the rest of the course contents.
0:00
Now SQLAlchemy is powerful because it can connect to any type of database that is relational. Oracle, SQLServer, SQLite, MySQL, you name it.
0:11
But that means we have to tell SQLAlchemy where the database is, what is the connection string, how do we get to it?
0:19
So we're going to real quick things to get started here. We're going to create a quick directory. In here, we're going to do a little trick
0:26
just to find this folder super, super easy. Call this db folder. We'll define one function, get_db_path. And it's going to take a base file name
0:37
and this'll be like rps.bin or something like that. And from that, we need the full path. So we're going to use a cool little trick here, using os.
0:45
And we'll say base folder is os.path.dirname of this particular file. So what folder is this file located in?
0:56
It's in here, and we want our database to also be created in that same folder. So we're just going to say return os.path.join, base folder, base file.
1:05
So not a whole lot going on here, but this is going to make it nice and easy for us to create,
1:09
we're create just a SQLite file that's going to live here. And we'll create a data access bit here. And let's add a new part here, say,
1:20
create this to be a session factory. These are not the models that we're modeling the data on, these are the sort of connection,
1:27
low level infrastructure type things. So we're going to create the session factory thing and its job is going to be
1:33
to create what's called a unit of work. And SQLAlchemy, the way it works, is you create this session, you do queries, inserts, updates, deletes.
1:40
And you can either commit those changes or throw them away. So the job of this is to set up the connection and create these sessions
1:47
that we can do the rest of our work with. So we're going to need a few things here. We're going to need the ORM, this is the object relational mapper,
1:54
this is really interesting. We're going to need the db folder. We're going to need our model base, we'll work with that.
2:01
Now there's one final thing that's a little bit weird but we're going to need it. Now at this stage in the program's life cycle,
2:09
it may not have interacted with these files, the move, the player and the role. For what's about to happen,
2:16
SQLAlchemy has to have seen and loaded into memory all of these things. And if that hasn't happened yet, we're going to miss some things.
2:24
Like maybe one of the tables won't get created or something weird like that. So we can make sure that this is not a problem here
2:30
by just importing everything we need. So move import, move player role. Whew, so that's a lot of imports that we're going to need but we are all ready.
2:41
Now we're going to create a thing called a factory. And it's going to be nothing in the beginning. So we need to do is write a function
2:47
that will get called one time and initialize everything. So we'll say def. It's going to work with this factory
2:54
so we'll say global factory so it can change this from outside without, and overwrite it with a local variable bit.
3:01
We want to change this, one and only factory, there should be one of these in the entire process per database, not a bunch.
3:07
So let's use our little db folder thing to get the path, and let's call this rock_paper_scissors.bin. Extension doesn't matter,
3:17
just something I like to stick with, or actually let's change it to sqlite, how's that? Even more clear what it's supposed to be.
3:23
And we can create our connections string, this you saw already, is going to be sqlite:///. So this tells SQLAlchemy what kind of database
3:33
it's talking to. Is it SQLServer, is it SQLite, is it Oracle? And then for SQLite the connection string is just the file name.
3:39
So this is nice and straightforward. The next thing we're going to do, is we need what's called an engine.
3:44
This manages all the connections and the connection pooling and stuff like that. And just like the factory, there's one of these
3:49
per database connection. So we say create_engine, and then all we have to do is give it the connection string.
3:58
And you can also, if you wanted to debug this, you could say echo equals True. I'm going to say false, so we can see what's going on,
4:04
but if you switch this to true, you'll see every command issued to the database by SQLAlchemy in SQL form. So that's really nice.
4:12
Now the next thing we need to do is actually create the structure. If the database doesn't exist, like right now, there's no database file here,
4:19
we would like to have SQLAlchemy get it up and running and get everything connected. So we can say model_base.metadata.create_all,
4:28
and we'll have to give it the engine. So this is going to run and actually look at all of these classes up here,
4:33
and it's going to create the related tables that we told it about. And finally, after all of that, we're ready to create our factory.
4:40
So we'll say sqlalchemy.orm.sessionmaker. And the session needs to talk to the database. So the way that happens is we bind the engine
4:50
to the session factory, therefore all created sessions know how to get back to the database. Whew, okay, and that is that.
4:58
The other thing we're going to need to do, just from making this work a little nicer, is we want to be able to safely create these sessions.
5:05
We could directly work with that but it's problematic. What if we forget to call this, how do we check that, and so on.
5:10
So let's do a little create_session function here. Instead of forcing other people to call that we can just check, do we need this to be called.
5:18
So we'll say global, we'll say if factory is none, like it hasn't been created yet, then we'll call global in it. Otherwise, this is super easy.
5:28
We'll just say factory, and again, you call it, it's a session factory, when you call it, it creates a session. Okay so, that's all we got to do.
5:36
The last thing I would like to do here well, maybe two things. One is, PyCharm thinks we're not using these, but they're important that they're here,
5:44
so let's go over here and say suppress that. That's good. Great, okay so no more complaints up there, everybody's happy.
5:51
The other thing to do, is when we import the session factory and hit dot, we'll see factory, we'll see create_session,
5:58
and global in that, what I'd kind of like to do is make this not accessible from the outside so we can do that by refactoring it to a double underscore
6:07
and then it'll be hidden by Python from the outside. That's kind of a private variable for this module.
6:13
Our database is all configured, our models are built, now all we have left to do is just use this database access layer
6:20
to actually create some history in our game.