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 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.