WEBVTT 1 00:00:00.140 --> 00:00:01.560 Hi guys and welcome back. 2 00:00:01.560 --> 00:00:03.750 In this video we're going to be inserting data 3 00:00:03.750 --> 00:00:06.533 into SQLite database using Python. 4 00:00:07.490 --> 00:00:09.500 A few things to remember, we can use the 5 00:00:09.500 --> 00:00:11.350 connection to execute a query. 6 00:00:11.350 --> 00:00:13.230 We don't need to create a cursor. 7 00:00:13.230 --> 00:00:15.590 We can use the context manager as we saw here 8 00:00:15.590 --> 00:00:17.110 to automatically commit the 9 00:00:17.110 --> 00:00:18.770 changes when we're done. 10 00:00:18.770 --> 00:00:20.600 And the third thing is we need to be able 11 00:00:20.600 --> 00:00:22.860 to tell the query what data we want to 12 00:00:22.860 --> 00:00:24.880 insert in the database. 13 00:00:24.880 --> 00:00:26.823 So I'm going to get rid of this here. 14 00:00:27.820 --> 00:00:30.336 And now we're going to connection.execute 15 00:00:30.336 --> 00:00:33.355 and now we need to start writing our query. 16 00:00:33.355 --> 00:00:37.910 So it's going to be insert into the entries table 17 00:00:37.910 --> 00:00:40.150 because that's what we called it when we created 18 00:00:40.150 --> 00:00:41.113 the table up there. 19 00:00:42.678 --> 00:00:45.800 And now, we're going to go direct into values 20 00:00:45.800 --> 00:00:50.510 since we are going to put data into both columns. 21 00:00:50.510 --> 00:00:52.600 Now in here we're going to enter two strings. 22 00:00:52.600 --> 00:00:57.090 For example, this is some test content, and 23 00:00:57.090 --> 00:01:00.820 we can add 01-01-2020 as another one. 24 00:01:00.820 --> 00:01:03.630 Remember to close the brackets at the end as 25 00:01:03.630 --> 00:01:04.463 well. 26 00:01:04.463 --> 00:01:06.240 And for good measure let's put a semicolon 27 00:01:06.240 --> 00:01:08.180 which sometimes you don't need to put in, 28 00:01:08.180 --> 00:01:09.580 but I recommend that you do. 29 00:01:11.051 --> 00:01:11.884 So now we've got this here. 30 00:01:11.884 --> 00:01:14.190 Remember the context manager with connection 31 00:01:14.190 --> 00:01:18.820 to automatically commit this query as otherwise 32 00:01:18.820 --> 00:01:21.390 you won't be able to see the contents anywhere. 33 00:01:21.390 --> 00:01:23.650 So we've got that. Lets save this. 34 00:01:23.650 --> 00:01:26.960 So now we can go into app.py and run this code. 35 00:01:26.960 --> 00:01:29.530 And remember, as long as we don't execute 36 00:01:29.530 --> 00:01:32.900 the get entries function which relies on this 37 00:01:32.900 --> 00:01:34.920 variable that no longer exists. 38 00:01:34.920 --> 00:01:36.810 Then this code will run. 39 00:01:36.810 --> 00:01:39.010 The thing we must do first though is we have 40 00:01:40.790 --> 00:01:43.450 to import the create tables function at the top 41 00:01:43.450 --> 00:01:45.400 and we have to make sure to run that. 42 00:01:45.400 --> 00:01:47.800 So we'll do create tables just like that. 43 00:01:47.800 --> 00:01:50.230 It is called create tables. Just create table. 44 00:01:50.230 --> 00:01:53.320 So, my bad. Create table right there. 45 00:01:53.320 --> 00:01:56.030 Okay, so lets run this code. 46 00:01:56.030 --> 00:01:58.300 I'm going to open a terminal in here 47 00:01:58.300 --> 00:02:01.203 and we're going to do Python 3.8 app.py. 48 00:02:02.090 --> 00:02:04.860 So now, if we open the file viewer, 49 00:02:04.860 --> 00:02:07.770 we should have data.db created 50 00:02:07.770 --> 00:02:09.540 inside our directory. 51 00:02:09.540 --> 00:02:11.160 That's because when we connect through 52 00:02:11.160 --> 00:02:13.360 the database and try to do something with it 53 00:02:13.360 --> 00:02:15.760 its going to create that file for us and now 54 00:02:15.760 --> 00:02:18.580 it contains the entries table. 55 00:02:18.580 --> 00:02:21.400 So I can go ahead and add a new entry for today. 56 00:02:21.400 --> 00:02:23.780 And it doesn't actually matter what I enter, 57 00:02:23.780 --> 00:02:27.420 because the values that we're entering are hard 58 00:02:27.420 --> 00:02:28.540 coded in this query. 59 00:02:28.540 --> 00:02:30.420 We're not using the entry content and 60 00:02:30.420 --> 00:02:32.740 the entry date and parameters at all, 61 00:02:32.740 --> 00:02:34.050 so there we go. 62 00:02:34.050 --> 00:02:36.330 Now, of course if I type two we're going to 63 00:02:36.330 --> 00:02:38.320 get an error name entries is not defined 64 00:02:38.320 --> 00:02:39.850 because we're relying on this variable that 65 00:02:39.850 --> 00:02:41.110 doesn't exist. 66 00:02:41.110 --> 00:02:43.860 But, because we used this context manager, 67 00:02:43.860 --> 00:02:45.990 the result of executing this query will 68 00:02:45.990 --> 00:02:48.700 already have been saved into the data base. 69 00:02:48.700 --> 00:02:50.630 So now what we can do is open the SQLite 70 00:02:50.630 --> 00:02:52.270 viewer and have a look to see if all 71 00:02:52.270 --> 00:02:53.163 that worked. 72 00:02:54.550 --> 00:02:56.810 So I've opened the SQLite viewer and 73 00:02:56.810 --> 00:02:58.690 you can see that I've got my entries table 74 00:02:58.690 --> 00:03:00.060 that we created. 75 00:03:00.060 --> 00:03:01.890 If I go on browse the date from the 76 00:03:01.890 --> 00:03:04.270 entries table, you can see that we get 77 00:03:04.270 --> 00:03:05.760 this is is some test content 78 00:03:05.760 --> 00:03:07.600 and 01-01-2020. 79 00:03:07.600 --> 00:03:09.590 It looks a little bit bizarre there there 80 00:03:09.590 --> 00:03:11.410 almost looks like underscores don't worry 81 00:03:11.410 --> 00:03:13.560 about that. It's all correct. They are dashes 82 00:03:13.560 --> 00:03:16.460 as we entered in here. 83 00:03:16.460 --> 00:03:20.170 So, now that we've got this, we know that 84 00:03:20.170 --> 00:03:22.410 we can insert data, the connection works, 85 00:03:22.410 --> 00:03:24.400 the table has been created and so forth. 86 00:03:24.400 --> 00:03:26.667 A couple of things that we want to take 87 00:03:26.667 --> 00:03:28.150 care of is to make sure that if we run 88 00:03:28.150 --> 00:03:30.720 this app again, we don't get this error. 89 00:03:30.720 --> 00:03:32.810 Table entries already exists. 90 00:03:32.810 --> 00:03:34.920 We know how to fix this error though 91 00:03:34.920 --> 00:03:38.180 we add if not exists into there. 92 00:03:38.180 --> 00:03:40.406 By the way, I'm going to close this viewer, 93 00:03:40.406 --> 00:03:41.760 we don't really need it anymore. 94 00:03:41.760 --> 00:03:43.910 So create table if not exists. 95 00:03:43.910 --> 00:03:46.550 Notice how Python is automatically formatting 96 00:03:46.550 --> 00:03:48.500 these strings into separate lines. 97 00:03:48.500 --> 00:03:49.990 That's just to keep the line from being 98 00:03:49.990 --> 00:03:50.890 too long. 99 00:03:50.890 --> 00:03:52.820 If you don't like that you can keep it 100 00:03:52.820 --> 00:03:54.970 all in one line in your code. 101 00:03:54.970 --> 00:03:57.580 Now lets start working with these parameters 102 00:03:57.580 --> 00:03:59.520 to enter the data that they use or actually 103 00:03:59.520 --> 00:04:00.880 typed in. 104 00:04:00.880 --> 00:04:03.770 Now when we're using SQLite, we have two 105 00:04:03.770 --> 00:04:04.890 ways of doing things. 106 00:04:04.890 --> 00:04:07.660 The first way is the sort of easy way 107 00:04:07.660 --> 00:04:09.310 that a lot of Python developers want to 108 00:04:09.310 --> 00:04:11.220 start doing at the beginning. 109 00:04:11.220 --> 00:04:15.317 Which is to put the entry content in here 110 00:04:17.570 --> 00:04:20.830 and the entry date in here. 111 00:04:20.830 --> 00:04:22.383 Entry date there we go. 112 00:04:23.510 --> 00:04:24.610 Make this an f string. 113 00:04:25.850 --> 00:04:28.670 If we do this, then naturally the 114 00:04:28.670 --> 00:04:31.400 entry content is going to get interpolated 115 00:04:31.400 --> 00:04:35.070 into this set of quotation marks and the 116 00:04:35.070 --> 00:04:36.360 entry date is going to go in there. 117 00:04:36.360 --> 00:04:37.980 We're going to write into the database. 118 00:04:37.980 --> 00:04:39.840 You'll see as soon as I run this 119 00:04:39.840 --> 00:04:41.650 I'm going to just clear the output 120 00:04:41.650 --> 00:04:42.850 and run it again. 121 00:04:42.850 --> 00:04:44.450 Add the new entry today I've learned 122 00:04:44.450 --> 00:04:46.806 how to avoid SQL injection attacks. 123 00:04:46.806 --> 00:04:50.900 More information on SQL injection attacks in a minute. 124 00:04:50.900 --> 00:04:52.690 I've mistyped that but that's okay. 125 00:04:52.690 --> 00:04:57.550 Now lets go back to SQLite viewer and refresh 126 00:04:57.550 --> 00:04:59.220 and you can see that we get the data that 127 00:04:59.220 --> 00:05:00.053 we typed. 128 00:05:01.052 --> 00:05:02.690 So, therefore this works. Right. 129 00:05:02.690 --> 00:05:06.320 But, as I was saying about SQL injection attacks, 130 00:05:06.320 --> 00:05:11.070 if you do this, you are at a big risk and it's 131 00:05:11.070 --> 00:05:13.080 definitely a bad idea to do this. 132 00:05:13.080 --> 00:05:14.890 We're going to learn more about SQL injection 133 00:05:14.890 --> 00:05:16.720 attacks at the end of this section. 134 00:05:16.720 --> 00:05:19.390 But essentially you are opening up to the 135 00:05:20.294 --> 00:05:22.130 user typing SQL code as the content of 136 00:05:22.130 --> 00:05:25.130 the entry and the SQL code will run when 137 00:05:25.130 --> 00:05:27.010 typed in here. 138 00:05:27.010 --> 00:05:29.681 So there's more detail at the end of this 139 00:05:29.681 --> 00:05:32.230 section as I mentioned, but we want to avoid 140 00:05:32.230 --> 00:05:33.063 this. 141 00:05:33.063 --> 00:05:35.220 Instead, what we do normally using SQLite 142 00:05:35.220 --> 00:05:37.250 is we put a question mark and then another 143 00:05:37.250 --> 00:05:38.630 question mark. 144 00:05:38.630 --> 00:05:40.497 So here we've got two question marks, 145 00:05:40.497 --> 00:05:42.210 two things that we're going to add to 146 00:05:42.210 --> 00:05:43.970 our table. 147 00:05:43.970 --> 00:05:46.010 We can now remove the f string. 148 00:05:46.010 --> 00:05:47.710 When we do this, when we put in the 149 00:05:47.710 --> 00:05:49.900 question marks there, we need to tell SQLite 150 00:05:49.900 --> 00:05:53.110 what we want to replace these question marks by. 151 00:05:53.110 --> 00:05:55.530 So it's similar to doing string interpolation, 152 00:05:55.530 --> 00:05:57.160 but SQLite does it. 153 00:05:57.160 --> 00:05:59.640 When SQLite does it, it protects us against 154 00:05:59.640 --> 00:06:01.290 this SQL injection attack. 155 00:06:01.290 --> 00:06:03.710 So the way we now have to do this 156 00:06:03.710 --> 00:06:06.850 is we add the comma afterwards for the 157 00:06:06.850 --> 00:06:09.840 second argument and we're going to pass in 158 00:06:09.840 --> 00:06:13.610 the entry content and the entry date. 159 00:06:13.610 --> 00:06:16.080 So this is important here. We've got the 160 00:06:16.080 --> 00:06:20.070 connection execute method here and the first 161 00:06:20.070 --> 00:06:22.630 argument to that is the query we want to run 162 00:06:22.630 --> 00:06:25.900 with the question marks as arguments. 163 00:06:25.900 --> 00:06:28.220 Then after the comma as a second argument 164 00:06:28.220 --> 00:06:30.820 to the connection executes. We're passing a 165 00:06:30.820 --> 00:06:34.080 tuple of the arguments that we want to pass 166 00:06:34.080 --> 00:06:35.400 into the query. 167 00:06:35.400 --> 00:06:37.270 So entry content will become the 168 00:06:37.270 --> 00:06:38.790 first question mark, entry date 169 00:06:38.790 --> 00:06:40.410 will become the second question mark, 170 00:06:40.410 --> 00:06:41.940 and everyone's happy. 171 00:06:41.940 --> 00:06:44.960 So this is the way to do it. 172 00:06:44.960 --> 00:06:47.140 And that's really everything. 173 00:06:47.140 --> 00:06:48.920 Our database.py function was simple 174 00:06:48.920 --> 00:06:51.020 and encapsulated. It's logic was hidden 175 00:06:51.020 --> 00:06:53.872 from other parts of the app so we really 176 00:06:53.872 --> 00:06:56.040 have to change nothing at all inside app.py 177 00:06:56.040 --> 00:06:57.760 to make this work as you've seen. 178 00:06:57.760 --> 00:06:59.610 So try running the app a few times insert 179 00:06:59.610 --> 00:07:01.390 some data, open your database viewer, have 180 00:07:01.390 --> 00:07:03.810 a look around and see what happens. 181 00:07:03.810 --> 00:07:05.950 Then we will move on to selecting data 182 00:07:05.950 --> 00:07:08.080 and retrieving data from the database in 183 00:07:08.080 --> 00:07:10.244 the next couple of videos and we will 184 00:07:10.244 --> 00:07:11.077 complete this. 185 00:07:11.077 --> 00:07:12.880 All right, thanks guys for watching. 186 00:07:12.880 --> 00:07:14.430 I'll see you in the next video.