WEBVTT 1 00:00:00.090 --> 00:00:01.480 Hi guys and welcome back. 2 00:00:01.480 --> 00:00:02.890 In this video, we're going to learn how 3 00:00:02.890 --> 00:00:06.483 to connect to a SQLite database with Python. 4 00:00:07.770 --> 00:00:09.980 We're going to use the SQLite Three Module 5 00:00:09.980 --> 00:00:11.680 which comes with Python so there's nothing for us 6 00:00:11.680 --> 00:00:15.720 to instal in order to connect to SQLite databases. 7 00:00:15.720 --> 00:00:17.800 There's a three-step process to opening connection. 8 00:00:17.800 --> 00:00:19.560 First, we have to import the module 9 00:00:19.560 --> 00:00:21.230 because that contains everything we need 10 00:00:21.230 --> 00:00:23.180 in order to actually connect. 11 00:00:23.180 --> 00:00:26.310 Then we'll use the module to connect to the data file. 12 00:00:26.310 --> 00:00:28.840 SQLite databases are always in data files 13 00:00:28.840 --> 00:00:31.310 as opposed to you know, servers running somewhere 14 00:00:31.310 --> 00:00:33.350 and so, if the data file doesn't already exist 15 00:00:33.350 --> 00:00:35.080 when we're trying to connect then, 16 00:00:35.080 --> 00:00:38.350 this connecting is gonna create one. 17 00:00:38.350 --> 00:00:39.290 When we're connected, 18 00:00:39.290 --> 00:00:42.663 we can execute SQLite queries against that connection. 19 00:00:43.660 --> 00:00:46.020 So it's gonna look somewhat like this. 20 00:00:46.020 --> 00:00:48.220 First, we import the module at the top then, 21 00:00:48.220 --> 00:00:51.390 we open a connection with sqlite3.connect. 22 00:00:51.390 --> 00:00:54.360 We give that the data file that we wanna connect to 23 00:00:54.360 --> 00:00:57.780 and again, that creates data.db in this case, 24 00:00:57.780 --> 00:00:59.680 if it didn't already exist 25 00:00:59.680 --> 00:01:00.740 and then we use the connection 26 00:01:00.740 --> 00:01:04.260 to execute queries with connection.execute. 27 00:01:04.260 --> 00:01:05.220 Finally, we always want 28 00:01:05.220 --> 00:01:07.630 to close the connection at the end of our programmes. 29 00:01:07.630 --> 00:01:09.830 So that's something to keep track of. 30 00:01:09.830 --> 00:01:11.510 Normally though, I would create the 31 00:01:11.510 --> 00:01:14.530 connection in a function. 32 00:01:14.530 --> 00:01:16.270 And often we want to delete 33 00:01:16.270 --> 00:01:18.230 and re-create the data file and so on, 34 00:01:18.230 --> 00:01:20.150 so having an easy-to-call function 35 00:01:20.150 --> 00:01:22.190 that creates the data file can be convenient. 36 00:01:22.190 --> 00:01:23.850 So these are something you can do 37 00:01:23.850 --> 00:01:25.200 and you know, import SQLite 38 00:01:25.200 --> 00:01:27.569 then create a function called createTable 39 00:01:27.569 --> 00:01:31.150 and then connect there and create the table. 40 00:01:31.150 --> 00:01:33.460 Really, it's not so much about opening 41 00:01:33.460 --> 00:01:35.300 the connection in this function that is 42 00:01:35.300 --> 00:01:36.590 the most important but 43 00:01:36.590 --> 00:01:39.340 to have a function that creates the tables for you. 44 00:01:39.340 --> 00:01:41.123 So, how many connections can you open? 45 00:01:41.123 --> 00:01:42.825 This is a very frequent question 46 00:01:42.825 --> 00:01:44.840 and the answer is you can really open 47 00:01:44.840 --> 00:01:49.017 as many as you want and this is true for any RDBMS 48 00:01:49.017 --> 00:01:51.950 but there are some limits 49 00:01:51.950 --> 00:01:55.010 to how many connections the database server 50 00:01:55.010 --> 00:01:56.490 is going to support. 51 00:01:56.490 --> 00:01:59.780 So again, with SQLite, there is no server, 52 00:01:59.780 --> 00:02:01.630 you're connecting directly to the file. 53 00:02:01.630 --> 00:02:04.070 So, this connection limit doesn't apply 54 00:02:04.070 --> 00:02:06.330 to SQLite but with things like Postgres 55 00:02:06.330 --> 00:02:07.460 as we will learn later on, 56 00:02:07.460 --> 00:02:09.510 there are connection limits that depend on 57 00:02:09.510 --> 00:02:11.180 the RAM of the server. 58 00:02:11.180 --> 00:02:12.630 We're going to learn more about that later so, 59 00:02:12.630 --> 00:02:14.140 don't worry about it too much. 60 00:02:14.140 --> 00:02:15.570 For SQLite, we can open as many 61 00:02:15.570 --> 00:02:19.520 as we want but only one connection out 62 00:02:19.520 --> 00:02:21.520 of all of the ones that are open, 63 00:02:21.520 --> 00:02:23.180 can write to the database, 64 00:02:23.180 --> 00:02:26.690 can modify the contents of the database at any one time. 65 00:02:26.690 --> 00:02:29.160 So, if you have two connections, 66 00:02:29.160 --> 00:02:32.800 one of them can be inserting new data into the database, 67 00:02:32.800 --> 00:02:34.040 the other one if it wants 68 00:02:34.040 --> 00:02:35.980 to insert data into the database at the same time, 69 00:02:35.980 --> 00:02:39.050 it has to wait until the other one has finished. 70 00:02:39.050 --> 00:02:42.110 So therefore, for single-user application 71 00:02:42.110 --> 00:02:43.910 like most console apps that we're gonna 72 00:02:43.910 --> 00:02:45.520 be writing in the early stages 73 00:02:45.520 --> 00:02:48.890 of this course that are only used by a single-user 74 00:02:48.890 --> 00:02:51.410 at anytime, having one connection for 75 00:02:51.410 --> 00:02:53.510 the entire application lifetime is enough. 76 00:02:54.440 --> 00:02:55.760 When you have many users 77 00:02:55.760 --> 00:02:57.610 using an application at the same time, 78 00:02:57.610 --> 00:03:00.330 then you may want to start adding more connections 79 00:03:00.330 --> 00:03:02.550 but for now, it's not necessary. 80 00:03:02.550 --> 00:03:04.940 So often for console apps, 81 00:03:04.940 --> 00:03:07.630 we just create one connection at the top 82 00:03:07.630 --> 00:03:10.360 of the file and then we use that connection in 83 00:03:10.360 --> 00:03:13.360 the functions below it or whenever we need to. 84 00:03:13.360 --> 00:03:16.010 So instead of creating a function to create the connection, 85 00:03:16.010 --> 00:03:17.590 we just create the connection at the top. 86 00:03:17.590 --> 00:03:18.630 Kind of like this. 87 00:03:18.630 --> 00:03:21.420 We've got here, the connection equals sqlite3.connect 88 00:03:21.420 --> 00:03:22.830 at the very top of the file 89 00:03:22.830 --> 00:03:25.550 and then we can define multiple functions that all use 90 00:03:25.550 --> 00:03:28.150 that connection because we know that none 91 00:03:28.150 --> 00:03:30.620 of these functions are gonna run at the same time. 92 00:03:30.620 --> 00:03:31.740 Therefore, none of them are going 93 00:03:31.740 --> 00:03:34.960 to compete for using this connection to write data 94 00:03:34.960 --> 00:03:36.070 or anything like that 95 00:03:36.070 --> 00:03:38.090 to the database because this is a 96 00:03:38.090 --> 00:03:42.226 single-user application in most cases at least. 97 00:03:42.226 --> 00:03:45.860 Okay so, let's learn about transactions 98 00:03:45.860 --> 00:03:47.350 because the term transaction is 99 00:03:47.350 --> 00:03:50.053 pretty important in relational databases. 100 00:03:51.080 --> 00:03:55.800 We can run multiple queries together in a transaction 101 00:03:55.800 --> 00:03:58.750 and when you run queries in a transaction, 102 00:03:58.750 --> 00:04:00.710 they still run in the order in 103 00:04:00.710 --> 00:04:04.050 which you execute them but 104 00:04:04.050 --> 00:04:06.870 when you put multiple queries in a transaction, 105 00:04:06.870 --> 00:04:10.460 all of the queries in the transaction must succeed 106 00:04:10.460 --> 00:04:12.130 or none will and so, 107 00:04:12.130 --> 00:04:13.560 if you have multiple queries, 108 00:04:13.560 --> 00:04:16.350 they all have to pass or succeed, 109 00:04:16.350 --> 00:04:18.310 there have to be no errors or anything like that. 110 00:04:18.310 --> 00:04:20.380 If there is an error in one query, 111 00:04:20.380 --> 00:04:23.000 then all the queries in the transaction will be cancelled 112 00:04:23.000 --> 00:04:25.460 or their, the changes that they apply 113 00:04:25.460 --> 00:04:28.180 to the database will be reverted. 114 00:04:28.180 --> 00:04:30.210 So therefore, transactions allow us 115 00:04:30.210 --> 00:04:32.550 to group related queries together 116 00:04:32.550 --> 00:04:35.963 so we're sure that they will happen together successfully. 117 00:04:37.220 --> 00:04:38.910 So this concept of the transaction is 118 00:04:38.910 --> 00:04:40.560 pretty important because again, 119 00:04:40.560 --> 00:04:42.930 it allows us to be sure that multiple things 120 00:04:42.930 --> 00:04:45.300 that we wanted to do all happened 121 00:04:45.300 --> 00:04:47.480 and not one part happened 122 00:04:47.480 --> 00:04:48.670 and the other didn't especially 123 00:04:48.670 --> 00:04:51.220 when multiple queries are somehow related 124 00:04:51.220 --> 00:04:52.763 or interdependent. 125 00:04:53.950 --> 00:04:57.270 You can commit and rollback transactions. 126 00:04:57.270 --> 00:05:00.490 So committing saves the database changes permanently, 127 00:05:00.490 --> 00:05:01.330 so they'll be available 128 00:05:01.330 --> 00:05:03.590 to read later on from another connection 129 00:05:03.590 --> 00:05:08.090 or another transaction and rollback undoes the changes 130 00:05:08.090 --> 00:05:09.100 so as if nothing happened. 131 00:05:09.100 --> 00:05:12.530 So those are the two end states of a transaction. 132 00:05:12.530 --> 00:05:14.050 When you get to the end of the transaction, 133 00:05:14.050 --> 00:05:16.276 you either commit it to save the changes 134 00:05:16.276 --> 00:05:18.910 or you roll it back to undo the changes 135 00:05:18.910 --> 00:05:21.700 in case you've found something that shouldn't have happened. 136 00:05:21.700 --> 00:05:24.200 Of course, the third option is you do neither of them 137 00:05:24.200 --> 00:05:27.510 and the result is a rollback essentially, 138 00:05:27.510 --> 00:05:29.400 that's the default. 139 00:05:29.400 --> 00:05:32.430 If you use a transaction, you execute a bunch of queries 140 00:05:32.430 --> 00:05:33.660 and then you don't commit, 141 00:05:33.660 --> 00:05:35.750 nothing gets saved to the database so, 142 00:05:35.750 --> 00:05:37.430 nothing happens really. 143 00:05:37.430 --> 00:05:39.310 So, are we using transactions? 144 00:05:39.310 --> 00:05:42.160 Yes, every time we call connection.execute, 145 00:05:42.160 --> 00:05:44.250 a transaction is created for us. 146 00:05:44.250 --> 00:05:47.630 Then we execute the query but we haven't been committing 147 00:05:47.630 --> 00:05:51.390 or rolling back the transaction that gets created and so, 148 00:05:51.390 --> 00:05:53.160 you'll see that if you execute the code 149 00:05:53.160 --> 00:05:55.140 that we've shown earlier in this presentation, 150 00:05:55.140 --> 00:05:56.800 creates a table or something like that 151 00:05:56.800 --> 00:05:59.380 and then you open the database, the table won't be there. 152 00:05:59.380 --> 00:06:00.700 We're gonna show that in a moment in the 153 00:06:00.700 --> 00:06:04.220 SQLite Viewer that this doesn't really happen. 154 00:06:04.220 --> 00:06:07.040 So, we must commit the transactions 155 00:06:07.040 --> 00:06:09.290 in order for the data to be persisted 156 00:06:09.290 --> 00:06:12.450 or saved permanently to the database. 157 00:06:12.450 --> 00:06:16.100 We can do connection.commit after executing a query, 158 00:06:16.100 --> 00:06:17.420 kind of like this. 159 00:06:17.420 --> 00:06:19.470 Connection.execute, we create the table 160 00:06:19.470 --> 00:06:21.920 and then connection.commit saves the stuff 161 00:06:21.920 --> 00:06:24.780 to the database or we can use a context manager 162 00:06:24.780 --> 00:06:27.690 to automatically handle committing for us and so, 163 00:06:27.690 --> 00:06:29.073 this is a Python Construct, 164 00:06:29.073 --> 00:06:32.730 this is the With statement, With connection. 165 00:06:32.730 --> 00:06:34.210 Then connection.execute 166 00:06:34.210 --> 00:06:36.470 and what happens with the context manager 167 00:06:36.470 --> 00:06:39.900 is that it will execute the queries 168 00:06:39.900 --> 00:06:42.410 for us in the connection.execute line 169 00:06:42.410 --> 00:06:44.760 and when we get to the end of the context manager, 170 00:06:44.760 --> 00:06:46.790 at the end of With connection, 171 00:06:46.790 --> 00:06:49.510 then it's gonna commit for us automatically. 172 00:06:49.510 --> 00:06:51.480 Remember that you can have multiple 173 00:06:51.480 --> 00:06:54.420 connection.execute statements before committing 174 00:06:54.420 --> 00:06:56.140 and then that just groups them all 175 00:06:56.140 --> 00:06:57.960 together in the transaction. 176 00:06:57.960 --> 00:06:58.793 If there is an error in any 177 00:06:58.793 --> 00:07:01.400 of those statements that you're trying to execute, 178 00:07:01.400 --> 00:07:02.800 then they will all be cancelled 179 00:07:02.800 --> 00:07:05.570 as is the norm with transactions. 180 00:07:05.570 --> 00:07:06.870 So, let's go and code this. 181 00:07:06.870 --> 00:07:08.070 Let's jump into the code editor, 182 00:07:08.070 --> 00:07:09.790 write this code that we've shown you 183 00:07:09.790 --> 00:07:11.770 and that's gonna let us start using SQLite 184 00:07:11.770 --> 00:07:14.420 as our database for this app. 185 00:07:14.420 --> 00:07:15.970 I'll see you in the next video.