Python for .NET Developers Transcripts
Chapter: Database access and ORMs in Python
Lecture: Connecting to the DB
Login or
purchase this course
to watch this video and the rest of the course contents.
0:00
The next thing that we need to do here is create the database context equivalent into SQLAlchemy. You saw in the C# version
0:08
we create a class that derives from DB context. It talks about the connection things like that. So we've something like that going on over here
0:17
and it has mostly to do with the unit of work and the database connection. So I'll call this session_factory
0:23
and the idea is we're going to set up the connection create the tables, and then we're going to use this thing to make the unit of work that we use.
0:32
The thing that we say begin and we do the querys and inserts and all that then we say commit or rollback.
0:37
So let's go ahead and get started by importing sqlalchemy. We're also going to need sqlalchemy.orm like that and that's probably good for now.
0:46
So we're going to need two things that we want to keep track of. There's this thing called an __engine and that's going to be something
0:53
and there's going to be a __factory. So what is goin on with the double underscore? Remember that makes it private. So from the outside
1:03
someone that's session_factory dot they don't get access to these variables. These are like static variables of this module
1:09
more or less. And then we need to have a function that we call at the beginning to say set up the database here's your connection stream
1:16
if the tables don't exist go and create them and so on. So I'll create a function called global_init and we'll pass a db_name which is a string
1:25
and it's going to have no return value. Much like we had non local previously we want to say global __engine, __factory you have to explicitly say that
1:34
otherwise you'd just create local ones. We tend to assign values to these. We'll just create local ones that shadow the outer ones.
1:41
So we're going to do it like this, and say no no I want to mess with the global data and we also want to check if this has already been called:
1:47
don't do anything just bail. So we'll say if __factory return nothing to do. So for this part we're going to create a connection string.
1:56
We're going to be working with SQLite. SQLite is sweet because it's literally built into and shipped with Python.
2:02
So if somebody can run your code at all they have this embedded in memory database called SQLite. And the way we construct connection string
2:10
to SQLite for SQLAlchemy is you have to say what type of database are you talking to. Is it Postgres? Is it SQLite? Whatever.
2:18
So it can use its' dialect for that database. And then we give it some sort of path here.
2:24
What we're going to do is we're just going to store that into a folder called DB. I want a really simple way to get access to that folder
2:31
no matter where this code is run from. So let me go and create one more file a little utility. I'll call it db_folder. So I'll just throw it in here.
2:39
So what we're going to do is you call this function it's going to say where is this file located you can see where it is right there right now
2:47
and it says give me that path and then we're going to take whatever database name your path, saying basically generate that path.
2:55
Here is a nice platform independent way to work with paths. We'll say from guitary.db import db_folder so put here the DB path from the DB name.
3:11
So that's going to be the connection string. Then we can create our one and only singleton instance of the __engine. We go to sqlalchemy.create_engine.
3:21
Now there are one of these places where it says you can pass any positional parameters or any keyword argument parameters. I hate this thing.
3:28
Why do people create apis like this? What am I supposed to pass here? I don't know. You can pass anything. Well how do I know?
3:37
I have to go look it up in the documentation. If I'm lucky I can go to the definition over here and it might give me information.
3:43
You can see that there are some params and so on but I just don't like this pattern. But what you got to know is
3:49
You can say you pass in the connection string you can say echo=False as a keyword. This is a debug feature
3:56
I could say true and you would see every single SQL statement sent to the database spit out. So if you want to see what's going on
4:02
set that to true. I'm going to set it to false but it's like a reminder. Oh here's how you turn on debugging.
4:07
And then from the engine we can create a factory. Create the sessionmaker. I'm going to go to the sessionmaker and you say bind=__engine like this.
4:21
So what we did here is we're creating something a function basically that when we call that function by that I mean like this and we do this
4:29
what we get back is one of these units of work. This will connect us to the database and we'll be able to start creating sessions
4:37
and doing queries inserts, updates all that good stuff.