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