WEBVTT 1 00:00:00.000 --> 00:00:01.810 Hi guys and welcome back. 2 00:00:01.810 --> 00:00:03.640 In this video we're going to look at how to 3 00:00:03.640 --> 00:00:06.010 connect to SQLite using Python. 4 00:00:06.010 --> 00:00:08.250 We've already seen the presentation that covers 5 00:00:08.250 --> 00:00:09.600 the theory behind doing this. 6 00:00:09.600 --> 00:00:11.930 Now we're just going to write the code for it. 7 00:00:11.930 --> 00:00:14.600 Just as a reminder, we've got here our ab.py 8 00:00:14.600 --> 00:00:16.390 with our two functions. One to prompt 9 00:00:16.390 --> 00:00:19.270 for a new entry to the user, and one to display 10 00:00:19.270 --> 00:00:21.630 entries given as a parameter. 11 00:00:21.630 --> 00:00:23.790 And then we've got our menu that just asks the 12 00:00:23.790 --> 00:00:26.080 user over and over for their options 13 00:00:26.080 --> 00:00:29.150 and also gets the entries from the database 14 00:00:29.150 --> 00:00:31.070 to display in here. 15 00:00:31.070 --> 00:00:34.180 The prompt new entry adds entries to the database as well. 16 00:00:34.180 --> 00:00:36.240 So we've got our two functions add entry 17 00:00:36.240 --> 00:00:39.673 and get entries in database.py that avenue entry 18 00:00:39.673 --> 00:00:42.080 into this entries list or get the entries 19 00:00:42.080 --> 00:00:44.540 form the entries list and return it. 20 00:00:44.540 --> 00:00:47.250 Now we're going to modify these functions so that 21 00:00:47.250 --> 00:00:50.470 instead of a list, we've got a SQLite table. 22 00:00:50.470 --> 00:00:51.750 So the first thing we're going to do 23 00:00:51.750 --> 00:00:55.257 as discussed in the presentation is we are going 24 00:00:55.257 --> 00:00:56.970 to go ahead and create a connection. 25 00:00:56.970 --> 00:01:01.100 So we do connection equal SQLite three.connect 26 00:01:01.100 --> 00:01:03.850 and the file is going to be data.db. 27 00:01:03.850 --> 00:01:05.310 Once we do this, we no longer need 28 00:01:05.310 --> 00:01:07.040 the entries list, but we do need 29 00:01:07.040 --> 00:01:10.240 to import SQLite three instead. 30 00:01:10.240 --> 00:01:12.900 So now we've got that. 31 00:01:12.900 --> 00:01:14.470 Let's save. 32 00:01:14.470 --> 00:01:17.100 Notice that any underlines and stuff should disappear 33 00:01:17.100 --> 00:01:18.760 at this point. If you get any errors 34 00:01:18.760 --> 00:01:20.880 like SQLite three module not found or something 35 00:01:20.880 --> 00:01:23.520 like that and then ask away in the course Q&A 36 00:01:23.520 --> 00:01:26.120 for this because SQLite three should come with Python. 37 00:01:26.120 --> 00:01:27.810 There should be nothing that you have to instal 38 00:01:27.810 --> 00:01:29.280 at this point. 39 00:01:29.280 --> 00:01:31.617 Now notice that our functions do complain that 40 00:01:31.617 --> 00:01:34.280 our entries list doesn't exist and that's because 41 00:01:34.280 --> 00:01:37.650 we now have to change this to use the connection 42 00:01:37.650 --> 00:01:40.110 and data.db for the table. 43 00:01:40.110 --> 00:01:42.520 So that we don't have to go into data.db 44 00:01:42.520 --> 00:01:44.540 ourselves with the SQLite viewer, I'm also 45 00:01:44.540 --> 00:01:47.300 going to create a third function to create 46 00:01:47.300 --> 00:01:49.120 the table that we need. 47 00:01:49.120 --> 00:01:52.590 So we'll have a create table function and in here 48 00:01:52.590 --> 00:01:56.070 we're going to do connection.execute and we will 49 00:01:56.070 --> 00:01:57.850 create table entries 50 00:01:57.850 --> 00:01:59.230 and we will give the two columns that 51 00:01:59.230 --> 00:02:01.000 we need the content and the date. 52 00:02:01.000 --> 00:02:03.963 So content text and date text. 53 00:02:05.310 --> 00:02:07.090 Remember from the presentation that this is 54 00:02:07.090 --> 00:02:10.000 how we execute a query using Python. 55 00:02:10.000 --> 00:02:12.750 Now that we're connected to SQLite three 56 00:02:12.750 --> 00:02:17.140 we can go ahead and run the create table function. 57 00:02:17.140 --> 00:02:20.190 However, it's important to remember that we need 58 00:02:20.190 --> 00:02:23.040 to commit the transaction that this runs in 59 00:02:23.040 --> 00:02:23.873 as we've learned. 60 00:02:23.873 --> 00:02:26.550 So it's important to put this in the context 61 00:02:26.550 --> 00:02:28.727 manager like that 62 00:02:28.727 --> 00:02:32.450 or alternatively, you can go back 63 00:02:32.450 --> 00:02:35.630 and do a connection commit. 64 00:02:35.630 --> 00:02:38.260 Either of those is fine, I do prefer the context 65 00:02:38.260 --> 00:02:39.840 manager approach though, so I'm going 66 00:02:39.840 --> 00:02:41.470 to go for that. 67 00:02:41.470 --> 00:02:43.630 Now remember if you run the code at this point 68 00:02:43.630 --> 00:02:45.410 it's not going to work because we've got rid 69 00:02:45.410 --> 00:02:46.740 of the entries list. 70 00:02:46.740 --> 00:02:50.091 We need to learn a little bit more before we can 71 00:02:50.091 --> 00:02:53.310 add content to these functions that uses a database. 72 00:02:53.310 --> 00:02:55.370 So over the next few videos we're going to learn 73 00:02:55.370 --> 00:02:57.170 how to insert data 74 00:02:57.170 --> 00:02:59.500 and how to retrieve data 75 00:02:59.500 --> 00:03:03.400 and were going to fill in these functions as we go along. 76 00:03:03.400 --> 00:03:05.380 But I'm sure you can imagine that this is going 77 00:03:05.380 --> 00:03:06.720 to be fairly straight forward. 78 00:03:06.720 --> 00:03:09.550 There's going to be one query that runs in here 79 00:03:09.550 --> 00:03:12.250 to add data to the database 80 00:03:12.250 --> 00:03:14.400 and there's going to be one query that runs in here 81 00:03:14.400 --> 00:03:16.270 to retrieve data from the database. 82 00:03:16.270 --> 00:03:18.120 But we just have to learn a few more things before 83 00:03:18.120 --> 00:03:19.380 we delve into that. 84 00:03:19.380 --> 00:03:20.910 So, thank you for watching. 85 00:03:20.910 --> 00:03:22.150 I'll see you in the next video. 86 00:03:22.150 --> 00:03:22.993 Let's get to it.