Adding a CMS to Your Pyramid Web App Transcripts
Chapter: Appendix: Modeling data with SQLAlchemy classes
Lecture: Connecting to our 'database'
0:00 We define our class that maps the database we have our SQLAlchemy base class. Next thing to do is actually create
0:07 that connection and configure the database. So again, a new file to organize that code. Call it DBSession. So this is going to serve two purposes
0:17 it's going to initialize the access to the database and it's also going to manage this overall session
0:22 this unit of work design pattern so hence the name. We're going to come down here and define a class called DBSession
0:29 and we're going to give it two pieces of information. We're going to have a factory and we're going to have a engine. The engine is going to manage the
0:42 type of database we're talking to and the connection and the connection pooling. The factory is going to be the thing
0:47 that creates sessions on demand, okay and the rule about these is there should be one and only one of them per connection
0:56 okay so that's basically a singleton unless you're having multiple connections to multiple databases, in which case you got to manage that slightly
1:03 differently than I'm showing you here. You want to have a static method called global_init and then we'll just make sure we
1:13 call this once and only once, so we'll say if DBSession.factory and it's already been called no problem
1:21 So we're also going to need to take a DB file to the string. What we're going to do is we're going to use SQLLite for this.
1:28 That means when you set up this code and you run it you have no database to configure install, manage, etc. In production you probably if you're
1:37 using SQLAlchemy you'll want to use some like Postgres or something along those lines that's just changing the connection string
1:43 should be no problem but for here we're going to let them pass in a file and that file is going to represent just the actual SQLAlchemy file on disc.
1:53 That's all you need for a SQLAlchemy connection. So it's probably prudent to do a little test here. Alright, now we know we have at least
2:03 something that could be a file. Now let's define the connection string. SQLite always looks like this. sqlite:///<DB file>
2:16 and I always like to print this out like here's my connection string. If this had a username password in it maybe I wouldn't exactly print that
2:24 I'd print you know maybe not the password for sure but let's just go ahead and print. Connecting then like this connecting
2:32 to DB at the connection string and then what we're going to do is we're going to create the engine.
2:38 So we'll say engine equals SQLAlchemy.create_engine. Now it takes *args **kwargs which means thanks for the help, nothing sadly
2:51 but what we can do is we'll pass the connection string here and then echo equals What do we put here, let's put false for now.
2:59 If you would like to see every operation every bit of SQL that is sent to the database as it happens, print it out, change that to True
3:08 it's a really nice debugging technique to see what's happening. Alright so because we want one and only one of those we're going to store that here
3:19 and we also need while we're at it let's go ahead and create this factory and it'll just round out this thing here so we'll say DBSession.factory
3:27 say SQLAlchemy.session.orm Better import that at the top too .sessionmaker say bind equals engine I think we're in good shape.
3:41 Okay so this will allow us to initialize the connection and create this factory and then later when we want to actually
3:50 create the factories we'll see there's a couple of evolutions or steps of improvement that will apply to this factory thing here as well
3:59 but for now this is the sort of standard starting place so what we do is we come up with a connection string we create the engine based on that
4:07 and we hold onto it, one and only one of them same thing, we create the factory based on the engine
4:13 and this is going to manage both the connection string as well as the dialect, the type of database we're talking to because of that right there.