Adding a CMS to Your Pyramid Web App Transcripts
Chapter: Appendix: Modeling data with SQLAlchemy classes
Lecture: Connecting to our 'database'

Login or purchase this course to watch this video and the rest of the course contents.
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.

Talk Python's Mastodon Michael Kennedy's Mastodon