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.
