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