#100DaysOfWeb in Python Transcripts
Chapter: Days 33-36: Database access with SQLAlchemy
Lecture: Connecting to our DB

Login or purchase this course to watch this video and the rest of the course contents.
0:00 Well we've modeled our data, can we insert it?
0:03 Can we do queries?
0:04 Actually no, no we can't, not yet
0:06 because we need to actually establish a connection
0:09 to the database, as well as create the tables
0:12 and the database schema that was going to support
0:15 this particular model.
0:16 So let's go over here and create another file here
0:20 called session_factory.
0:24 Why did I name it that?
0:25 Well SQLAlchemy follows this concept
0:27 called a Unit of Work, which means I start the Unit of Work
0:31 I do a bunch of database things, inserts, updates, deletes
0:34 and then I commit that session.
0:36 I commit that Unit of Work back
0:39 and the object that actually manages that
0:41 is called a session.
0:43 That's actually the class name
0:44 the type name in SQLAlchemy
0:46 and so we have to create this thing
0:48 that establishes the connection
0:49 and then creates these sessions
0:51 that lets us do transactional like operations
0:55 on the database.
0:56 So session_factory, this is going to be the thing
0:58 that creates the sessions
0:59 that lets us do queries, and so on.
1:02 Now there's actually going to be three parts
1:04 there's going to be three things going on here.
1:06 One, I'll call global_init.
1:10 This is going to take a database name
1:12 and it's going to use that to create the connection
1:15 and the table schema, and all that.
1:17 And we're going to have one that's going to say create_tables.
1:21 We may not want to call that every time we call this
1:23 so we'll have those separated.
1:25 And then the last one is going to be create_session
1:29 and let's put a little type annotation on here.
1:35 This is going to be a sqlalchelmy.orm.
1:39 Now we got to go over here and import when I need them both
1:41 so I'll do it like this.
1:43 And in the ORM, we have a Session object
1:47 that's the thing that we're going to be creating.
1:49 This is the thing that represents the unit of work.
1:52 Now let's go up here.
1:53 We're going to have a thing called an engine
1:57 and we're going to have thing called a factory.
2:00 Now I'm putting them in double underscore
2:02 because I don't want to expose them directly.
2:04 They need to call either this function or this function
2:06 to sort of interact with them.
2:08 So this global_init, it's job is to create a connection
2:12 use that connection to create an engine
2:13 use that engine to create the factory. Let's go.
2:20 So our connection string for SQLite
2:22 it's going to be sqlite:/// kind of like the web.
2:27 And that tells SQLAlchemy how to talk to it
2:29 cause talking to the different databases
2:31 their query syntax is very similar but not identical.
2:35 Okay, so we're going to do that
2:36 and then we're going to come over here
2:37 and actually use this thing from the db, the db folder.
2:41 Okay, so we'll say db folder, and it will give us
2:45 the full path to this directory appear
2:50 plus the database name.
2:51 So if we just pass the base name
2:53 like hovershare.SQLite or something
2:56 it will figure out the full path that we need.
2:58 So the actual connection string is SQLite///
3:01 the full path name that we're going to use.
3:04 And then the next thing we want to do
3:06 is just create this engine, say sequelalchemy.createengine
3:10 point just pass at the connection string.
3:12 I'll put one more thing in here.
3:13 You don't have to do this, but it can be helpful
3:15 say echo=false, and that's what it is by default
3:18 but if I flip this to true
3:20 you'll see the SQL statements
3:22 the actual database commands printed out to the console
3:25 as you interact with it.
3:26 So this will help you understand what SQLAlchemy is doing.
3:29 But I'll put that as false
3:30 cause we don't need to see it right now.
3:32 Final thing, we're going to initialize the factory.
3:34 The factory is going to be sqlalchemy.orm.sessionmaker
3:39 let's say bind=engine.
3:43 We also want there to be only one of these
3:45 so if somebody calls this twice and it's already done
3:47 we want to bail, so let's say this.
3:51 If they've already initialized the factory
3:54 then we're going to not let them do it again.
3:56 Everything is already good.