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