WEBVTT 1 00:00:00.320 --> 00:00:01.710 Hi guys and welcome back! 2 00:00:01.710 --> 00:00:03.050 In this video, we're going to learn 3 00:00:03.050 --> 00:00:06.970 how to improve on our connections by using a connection pool 4 00:00:06.970 --> 00:00:10.040 instead of creating and destroying connections all the time. 5 00:00:10.040 --> 00:00:11.390 Let's get to it. 6 00:00:11.390 --> 00:00:14.490 Here we've got our Poll class, and you can see 7 00:00:14.490 --> 00:00:16.780 that every time we're creating a new connection, 8 00:00:16.780 --> 00:00:18.350 then we're closing it, and in the middle, 9 00:00:18.350 --> 00:00:20.220 we're just using it once. 10 00:00:20.220 --> 00:00:24.910 This is okay but it is very slow and expensive, 11 00:00:24.910 --> 00:00:27.030 always creating and destroying new connections, 12 00:00:27.030 --> 00:00:28.810 and also it requires a lot of communication 13 00:00:28.810 --> 00:00:30.360 with the database server, 14 00:00:30.360 --> 00:00:31.360 so instead what we're gonna do 15 00:00:31.360 --> 00:00:33.960 is we're going to use a connection pool. 16 00:00:33.960 --> 00:00:36.420 Let's go over to connections.py, 17 00:00:36.420 --> 00:00:39.760 and here instead of this function that creates a connection, 18 00:00:39.760 --> 00:00:42.740 we're going to create our pool. 19 00:00:42.740 --> 00:00:44.820 Note that we're calling create_connection 20 00:00:44.820 --> 00:00:46.540 over and over again, and that means 21 00:00:46.540 --> 00:00:49.940 that we're using this connection environment variable 22 00:00:49.940 --> 00:00:51.720 over and over again. 23 00:00:51.720 --> 00:00:53.870 Because we're doing this so many times 24 00:00:53.870 --> 00:00:58.120 is why I decided last time to remove the prompt 25 00:00:58.120 --> 00:01:00.773 for the users to be able to change the DATABASE_URI, 26 00:01:01.880 --> 00:01:03.980 but now that we're gonna create the pool once 27 00:01:03.980 --> 00:01:06.060 and use it over and over again, 28 00:01:06.060 --> 00:01:08.910 we're only going to use this in one place, 29 00:01:08.910 --> 00:01:11.190 therefore we're gonna bring back the prompt 30 00:01:11.190 --> 00:01:13.690 that asks the user for where they want to connect. 31 00:01:13.690 --> 00:01:16.200 I've brought it in here, we've got the database prompt back, 32 00:01:16.200 --> 00:01:18.810 now we're asking the users if they don't type anything 33 00:01:18.810 --> 00:01:21.580 and we use the DATABASE_URI there. 34 00:01:21.580 --> 00:01:23.820 We can get rid of this up here, 35 00:01:23.820 --> 00:01:26.040 and now instead of create_connection, 36 00:01:26.040 --> 00:01:28.330 we can go ahead and create our connection pool. 37 00:01:28.330 --> 00:01:31.460 So this is just gonna be a simple variable called pool, 38 00:01:31.460 --> 00:01:34.073 and it's gonna be SimpleConnectionPool. 39 00:01:34.990 --> 00:01:36.510 Now this takes a couple arguments, 40 00:01:36.510 --> 00:01:39.900 the first one is, how many connections to create initially, 41 00:01:39.900 --> 00:01:43.240 and this is minconn, and that's gonna be equal to one, 42 00:01:43.240 --> 00:01:46.580 because we're using a single user programme here, 43 00:01:46.580 --> 00:01:49.510 this application runs in a terminal or the command line, 44 00:01:49.510 --> 00:01:52.300 it's likely that we're only ever gonna need one connection. 45 00:01:52.300 --> 00:01:53.580 We might need more connections 46 00:01:53.580 --> 00:01:57.000 if more users are using the application at the same time, 47 00:01:57.000 --> 00:02:01.340 but for that we've got maxconn, which I'm gonna set to 10. 48 00:02:01.340 --> 00:02:03.013 Whenever we need more connections 49 00:02:03.013 --> 00:02:05.370 than we have in the pool at the moment, 50 00:02:05.370 --> 00:02:07.950 we're going to create a new connection as we've discussed, 51 00:02:07.950 --> 00:02:09.100 and then when we're done with it, 52 00:02:09.100 --> 00:02:10.690 we're gonna put it back into the pool, 53 00:02:10.690 --> 00:02:13.800 that is how we can go from minconn of one 54 00:02:13.800 --> 00:02:15.630 up to 10 connections. 55 00:02:15.630 --> 00:02:17.860 If we ever want to create more than 10 connections, 56 00:02:17.860 --> 00:02:19.670 then the part of the programme that wants a connection 57 00:02:19.670 --> 00:02:22.650 has to wait for another to become available. 58 00:02:22.650 --> 00:02:25.210 Finally the last thing we need is a dsn 59 00:02:25.210 --> 00:02:26.910 which is database_uri. 60 00:02:26.910 --> 00:02:29.170 This is what the connection pool is gonna use 61 00:02:29.170 --> 00:02:31.760 for each connection when it creates it. 62 00:02:31.760 --> 00:02:33.640 Of course, instead of import psycopg2, 63 00:02:33.640 --> 00:02:36.500 now we can do from psycopg2.pool 64 00:02:36.500 --> 00:02:40.070 import SimpleConncectionPool just like that. 65 00:02:40.070 --> 00:02:43.000 Using the connection pool is really quite easy. 66 00:02:43.000 --> 00:02:44.520 Every time that we want to use the pool, 67 00:02:44.520 --> 00:02:48.060 now we're gonna do from connections import pool, 68 00:02:48.060 --> 00:02:50.440 and then instead of connection equal create_connection, 69 00:02:50.440 --> 00:02:54.550 we're gonna do pool.getconn, and that is gonna give us 70 00:02:54.550 --> 00:02:56.610 a connection from the pool. 71 00:02:56.610 --> 00:03:00.250 When we're done, we're gonna do pool.putconn, 72 00:03:00.250 --> 00:03:02.080 so we'll do pool.putconn in here, 73 00:03:02.080 --> 00:03:04.190 and we put the connection in there. 74 00:03:04.190 --> 00:03:06.310 This is what we do instead of creating 75 00:03:06.310 --> 00:03:07.620 and closing a connection. 76 00:03:07.620 --> 00:03:08.830 Notice that it's really important 77 00:03:08.830 --> 00:03:10.950 that we never close the connection 78 00:03:10.950 --> 00:03:13.960 because it has to remain open for the next person 79 00:03:13.960 --> 00:03:15.780 that wants to get a connection from the pool. 80 00:03:15.780 --> 00:03:18.050 I'm gonna go through these methods and use this 81 00:03:18.050 --> 00:03:19.850 instead of what we've got right now. 82 00:03:23.300 --> 00:03:24.730 So I've gone ahead and updated the methods 83 00:03:24.730 --> 00:03:27.660 to use pool.getconn, now I'm gonna make the changes 84 00:03:27.660 --> 00:03:29.410 to put the connection back as well. 85 00:03:33.250 --> 00:03:35.460 And that's the changes that we need to do. 86 00:03:35.460 --> 00:03:37.410 Notice then we still have quite a lot 87 00:03:37.410 --> 00:03:39.540 of duplication in the code. 88 00:03:39.540 --> 00:03:41.060 Everywhere we're getting a connection 89 00:03:41.060 --> 00:03:42.910 and then putting it back, and we're gonna learn 90 00:03:42.910 --> 00:03:46.210 how we can improve on this later on using a context manager. 91 00:03:46.210 --> 00:03:48.690 For now though, let's go over to the option.py file, 92 00:03:48.690 --> 00:03:49.950 and we're gonna do the same. 93 00:03:49.950 --> 00:03:52.560 We're gonna import pool, and then everywhere here 94 00:03:52.560 --> 00:03:54.320 we're gonna do pool.getconn, 95 00:03:56.520 --> 00:03:59.510 and everywhere else down here, we're gonna do pool.putconn, 96 00:03:59.510 --> 00:04:01.913 so we'll just do the same thing for everybody. 97 00:04:05.890 --> 00:04:08.620 Right, so I've gone ahead and added those changes in here, 98 00:04:08.620 --> 00:04:11.573 and now this too is using our connection pool. 99 00:04:12.750 --> 00:04:14.930 Now, I'm going to go ahead into the project, 100 00:04:14.930 --> 00:04:18.110 and we're going to rename the connections.py file. 101 00:04:18.110 --> 00:04:19.650 I'm not a big fan of the name, 102 00:04:19.650 --> 00:04:21.440 this made a bit more sense when it was handling 103 00:04:21.440 --> 00:04:25.440 just connections, but now it's handling a connection pool, 104 00:04:25.440 --> 00:04:26.870 so we're gonna right-click on it, 105 00:04:26.870 --> 00:04:29.880 go to Refactor, and then Rename, 106 00:04:29.880 --> 00:04:34.280 and I'm gonna name it connection_pool.py. 107 00:04:34.280 --> 00:04:36.940 If Python has these ticked, that means it's gonna try 108 00:04:36.940 --> 00:04:40.760 to find where connections.py is used in your code, 109 00:04:40.760 --> 00:04:42.910 and then it's gonna tell you essentially it's used here, 110 00:04:42.910 --> 00:04:44.050 and here, and here, 111 00:04:44.050 --> 00:04:46.700 do you want to change the name everywhere. 112 00:04:46.700 --> 00:04:48.130 I find that to be a little bit slow, 113 00:04:48.130 --> 00:04:51.420 so I'm gonna untick them, and then I'm gonna press Refactor, 114 00:04:51.420 --> 00:04:54.500 and now you can see that the red lines appear, 115 00:04:54.500 --> 00:04:57.520 we'll just have to rename these to connection_pool, 116 00:04:57.520 --> 00:04:59.793 make sure you do that in both places. 117 00:05:01.060 --> 00:05:02.820 Finally, we can go to app.py 118 00:05:02.820 --> 00:05:03.970 because down at the bottom, 119 00:05:03.970 --> 00:05:07.160 we're also using create_connection. 120 00:05:07.160 --> 00:05:09.493 So first of all, we'll change the import, 121 00:05:11.290 --> 00:05:13.330 and then we'll go down here, 122 00:05:13.330 --> 00:05:17.143 and we're going to do pool.getconn, 123 00:05:18.200 --> 00:05:19.980 and notice how earlier we were actually 124 00:05:19.980 --> 00:05:21.900 creating a connection but we never did close it, 125 00:05:21.900 --> 00:05:25.610 so that was a mistake, but now we are gonna do pool.putconn 126 00:05:25.610 --> 00:05:27.240 and put the connection back this time. 127 00:05:27.240 --> 00:05:29.593 All fine in here as well. 128 00:05:30.800 --> 00:05:32.760 Remember the SimpleConnectionPool class 129 00:05:32.760 --> 00:05:34.270 that we have created is good 130 00:05:34.270 --> 00:05:37.410 for simple threaded applications in Python. 131 00:05:37.410 --> 00:05:39.520 If you're making multi-threaded applications, 132 00:05:39.520 --> 00:05:42.750 psycopg2 has a threaded connection pool class 133 00:05:42.750 --> 00:05:44.652 that you can use instead. 134 00:05:44.652 --> 00:05:46.020 All right that's everything for this video, 135 00:05:46.020 --> 00:05:48.120 thank you guys for joining, thanks for watching it, 136 00:05:48.120 --> 00:05:49.770 and I'll see you in the next one.