RESTful and HTTP APIs in Pyramid Transcripts
Chapter: Adding a database backend with SQLAlchemy
Lecture: Unit of work and the session factory

Login or purchase this course to watch this video and the rest of the course contents.
0:01 So now that we have our connection string figured out
0:03 let's actually print this out really quick, print connection string,
0:07 and of course, we're going to need to call this function
0:10 we want to make sure to not forget that, we want to do it once right, at start up
0:13 so you can imagine down here into the under init we go
0:16 and let's add something here that says config or init db, something like that.
0:23 Now, we could pass this config file here, we're not actually passing anything,
0:28 but if we did, so over here if we passed in
0:31 say like the base file name something like that,
0:33 then we could store that file name here, we could just add it
0:38 it's super easy, just like this, like db file, just go ahead and do it, huh
0:42 so let's go here, db file name, so we can pass that in there
0:49 and we'll just put it let's make the file name the same, db file name
0:54 and we just put this here, now it doesn't go in quotes, we'll just put it like this, okay
0:58 so we can go grab and we make sure this is in production as well,
1:01 or things are going to go bad later, I'm sure,
1:04 so I am going to put this here, so we can go grab this value in our dunder init,
1:09 so we're given a config, you can say settings= config.get_settings, like this
1:18 in the settings it's a dictionary, so I can say
1:21 db_file = setting.get (' db_filename '), like this, standard dictionary stuff,
1:29 and then we'll just call, let me copy the name here,
1:32 DbSessionFactory.global_init, so import this
1:39 and call global_init and pass the db file,
1:43 over here it's going to print out what the connection string is
1:46 based on the file, so if I run this, everything should be looking good,
1:49 connection string is really long, oh my goodness look at that,
1:54 but it's restful auto service db sqlite.
1:57 And that's looking really good, notice there's no file over here yet
2:01 but when we interact with sqlite, if there's no file it's going to create it,
2:04 so that's not a problem, so we comment that out
2:07 but that's going to be very, very helpful.
2:10 Now the next thing we need to do is basically work with connections to the database.
2:13 It doesn't matter what kind of database we're working with
2:15 we configure it basically the same, right
2:18 so it's going to create a pool of connections and when things get returned to the pool
2:22 is going to close them, roll back transactions, things like that
2:24 so what we're going to do is we're going to create this thing called an engine
2:27 so we continue to have access to sqlalchemy up here like so
2:32 and then we're going to create an engine, say sqlalchemy.create engine
2:37 now there's a couple of things we can pass here,
2:40 first thing we got to pass is the connection string,
2:43 again, this makes me crazy * args ** kwargs, thanks for the help folks,
2:48 so anyway, we pass the connection string,
2:51 the other interesting thing is we can say echo,
2:53 here we can say echo=true and now you want this off in production for sure,
2:56 you probably want it off normally, but if you're new to sqlalchemy
3:00 and you want to see what's going on, this will basically make
3:03 sqlalchemy log all of its behaviors, all those sql queries that sends off to the database
3:07 things like that will spit to the console or the terminal;
3:10 so this is kind of helpful, we'll go ahead and keep that there.
3:13 Now finally, you would think what we need to do is store this engine somewhere
3:17 so we can use it for stuff, but not really,
3:20 the idea is we're going to work with these things called sessions
3:23 and the session needs the engine to do its work, so what we're going to really store
3:27 is we're just going to store this thing called a session factory
3:30 and once we have the session factory and we associate the engine to it
3:34 it will just hold a reference to it, so we'll be golden.
3:37 So let's go over here and tell this thing that it has a session factory,
3:42 that is nothing for the starter here, and we'll go ahead and allocate this
3:46 we'll need it in just a little bit when we want to do anything interesting
3:49 so we're going to go over here and we're going to need another piece of sqlalchemy,
3:52 we're going to need the ORM layer,
3:55 because this session unit of work business is only an ORM thing,
3:58 so I'll come over here and we'll have a thing called a session maker
4:02 now the session maker is going to need things
4:06 like to know how to talk to the database as part of its session in life cycle
4:10 so we're going to give it this engine here, right,
4:12 so this we're going to hold and let's make this a little simpler,
4:16 make this a class method like so, we'll come down here
4:19 and say cls.session_factory is this, okay so this we're going to need later
4:24 and because we're holding it here it's implicitly holding on to the engine
4:28 which had a connection, so everything will be all wired together
4:31 and all we have to really keep track of is this session factory,
4:34 let's go and just do the one thing that we're going to have to do while we're here
4:37 let's define add another class method, we will say something like create session,
4:41 and we'll just go to the class, to our session factory,
4:48 and we'll call it and return that value.
4:51 Okay, so the way sqlalchemy works is we want one of these
4:54 for basically one of those per connection string
4:57 but every time we create a session, we're going to have to allocate a new one
5:00 because that more or less represents a transactional set
5:04 of processing, a transaction in the database.