-: Hi guys and welcome back!
In this video, we're going to learn
how to improve on our connections by using a connection pool
instead of creating and destroying connections all the time.
Let's get to it.
Here we've got our Poll class, and you can see
that every time we're creating a new connection,
then we're closing it, and in the middle,
we're just using it once.
This is okay but it is very slow and expensive,
always creating and destroying new connections,
and also it requires a lot of communication
with the database server,
so instead what we're gonna do
is we're going to use a connection pool.
Let's go over to connections.py,
and here instead of this function that creates a connection,
we're going to create our pool.
Note that we're calling create_connection
over and over again, and that means
that we're using this connection environment variable
over and over again.
Because we're doing this so many times
is why I decided last time to remove the prompt
for the users to be able to change the DATABASE_URI,
but now that we're gonna create the pool once
and use it over and over again,
we're only going to use this in one place,
therefore we're gonna bring back the prompt
that asks the user for where they want to connect.
I've brought it in here, we've got the database prompt back,
now we're asking the users if they don't type anything
and we use the DATABASE_URI there.
We can get rid of this up here,
and now instead of create_connection,
we can go ahead and create our connection pool.
So this is just gonna be a simple variable called pool,
and it's gonna be SimpleConnectionPool.
Now this takes a couple arguments,
the first one is, how many connections to create initially,
and this is minconn, and that's gonna be equal to one,
because we're using a single user programme here,
this application runs in a terminal or the command line,
it's likely that we're only ever gonna need one connection.
We might need more connections
if more users are using the application at the same time,
but for that we've got maxconn, which I'm gonna set to 10.
Whenever we need more connections
than we have in the pool at the moment,
we're going to create a new connection as we've discussed,
and then when we're done with it,
we're gonna put it back into the pool,
that is how we can go from minconn of one
up to 10 connections.
If we ever want to create more than 10 connections,
then the part of the programme that wants a connection
has to wait for another to become available.
Finally the last thing we need is a dsn
which is database_uri.
This is what the connection pool is gonna use
for each connection when it creates it.
Of course, instead of import psycopg2,
now we can do from psycopg2.pool
import SimpleConncectionPool just like that.
Using the connection pool is really quite easy.
Every time that we want to use the pool,
now we're gonna do from connections import pool,
and then instead of connection equal create_connection,
we're gonna do pool.getconn, and that is gonna give us
a connection from the pool.
When we're done, we're gonna do pool.putconn,
so we'll do pool.putconn in here,
and we put the connection in there.
This is what we do instead of creating
and closing a connection.
Notice that it's really important
that we never close the connection
because it has to remain open for the next person
that wants to get a connection from the pool.
I'm gonna go through these methods and use this
instead of what we've got right now.
So I've gone ahead and updated the methods
to use pool.getconn, now I'm gonna make the changes
to put the connection back as well.
And that's the changes that we need to do.
Notice then we still have quite a lot
of duplication in the code.
Everywhere we're getting a connection
and then putting it back, and we're gonna learn
how we can improve on this later on using a context manager.
For now though, let's go over to the option.py file,
and we're gonna do the same.
We're gonna import pool, and then everywhere here
we're gonna do pool.getconn,
and everywhere else down here, we're gonna do pool.putconn,
so we'll just do the same thing for everybody.
Right, so I've gone ahead and added those changes in here,
and now this too is using our connection pool.
Now, I'm going to go ahead into the project,
and we're going to rename the connections.py file.
I'm not a big fan of the name,
this made a bit more sense when it was handling
just connections, but now it's handling a connection pool,
so we're gonna right-click on it,
go to Refactor, and then Rename,
and I'm gonna name it connection_pool.py.
If Python has these ticked, that means it's gonna try
to find where connections.py is used in your code,
and then it's gonna tell you essentially it's used here,
and here, and here,
do you want to change the name everywhere.
I find that to be a little bit slow,
so I'm gonna untick them, and then I'm gonna press Refactor,
and now you can see that the red lines appear,
we'll just have to rename these to connection_pool,
make sure you do that in both places.
Finally, we can go to app.py
because down at the bottom,
we're also using create_connection.
So first of all, we'll change the import,
and then we'll go down here,
and we're going to do pool.getconn,
and notice how earlier we were actually
creating a connection but we never did close it,
so that was a mistake, but now we are gonna do pool.putconn
and put the connection back this time.
All fine in here as well.
Remember the SimpleConnectionPool class
that we have created is good
for simple threaded applications in Python.
If you're making multi-threaded applications,
psycopg2 has a threaded connection pool class
that you can use instead.
All right that's everything for this video,
thank you guys for joining, thanks for watching it,
and I'll see you in the next one.