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