WEBVTT 1 00:00:00.100 --> 00:00:01.480 Hi, and welcome back. 2 00:00:01.480 --> 00:00:03.570 In this video, we're going to be looking at 3 00:00:03.570 --> 00:00:06.890 starting to move our Milestone project from Json 4 00:00:06.890 --> 00:00:08.360 to a database. 5 00:00:08.360 --> 00:00:10.330 If you've skipped the last couple of videos 6 00:00:10.330 --> 00:00:12.870 and you want straight from CSV to database, 7 00:00:12.870 --> 00:00:15.340 that's fine, we're just going to make 8 00:00:15.340 --> 00:00:16.590 a couple of changes here. 9 00:00:17.610 --> 00:00:22.170 Now, the first thing to do is to in the create book table, 10 00:00:22.170 --> 00:00:25.390 we're going to create our table. 11 00:00:25.390 --> 00:00:28.830 Now, we'll start by opening up a connection 12 00:00:28.830 --> 00:00:30.040 to the database. 13 00:00:30.040 --> 00:00:32.860 For that, we're going to need SQLite3. 14 00:00:32.860 --> 00:00:36.270 We're going to import SQLite3 instead of JSON. 15 00:00:36.270 --> 00:00:38.470 That's going to give us a few errors in our file, 16 00:00:38.470 --> 00:00:39.593 but that's OK for now. 17 00:00:40.850 --> 00:00:43.200 To open a connection, you know how to do that. 18 00:00:43.200 --> 00:00:47.530 We looked at that in the slides last couple of videos, 19 00:00:47.530 --> 00:00:50.230 so go ahead and pause the video and give it a go. 20 00:00:50.230 --> 00:00:52.870 Open up the connection, create your cursor, 21 00:00:52.870 --> 00:00:56.940 and then come back for we will implement the query here. 22 00:00:56.940 --> 00:00:58.060 I hope you got that. 23 00:00:58.060 --> 00:01:01.253 What to do is, of course, connect first of all. 24 00:01:04.370 --> 00:01:05.570 SQLite3 I'll connect. 25 00:01:05.570 --> 00:01:07.870 And then here we're gonna give it the file that we want 26 00:01:07.870 --> 00:01:09.490 to store all our data in. 27 00:01:09.490 --> 00:01:12.190 That's going to be data.db. 28 00:01:12.190 --> 00:01:14.720 Notice that data.db is going to appear 29 00:01:14.720 --> 00:01:19.720 in our project directory and not to be side database.py. 30 00:01:19.790 --> 00:01:22.540 So it'll appear at the top level, which is pretty nice. 31 00:01:23.510 --> 00:01:25.363 Then we'll create a cursor. 32 00:01:28.270 --> 00:01:30.900 Then we can perform the query, which is going to 33 00:01:30.900 --> 00:01:32.773 create our database. 34 00:01:35.230 --> 00:01:36.530 We'll do that in a moment. 35 00:01:37.470 --> 00:01:40.410 Then we'll have to, of course, commit our connection 36 00:01:40.410 --> 00:01:42.670 to make sure that the database does get created 37 00:01:42.670 --> 00:01:47.360 and that the table is all in the file, 38 00:01:47.360 --> 00:01:50.143 and at the end, we will close the connection. 39 00:01:51.020 --> 00:01:52.650 By the way, I said "create database" a couple 40 00:01:52.650 --> 00:01:54.710 of times there; I mean create table. 41 00:01:54.710 --> 00:01:56.503 We're going to be creating a table. 42 00:01:57.740 --> 00:02:01.320 Now, this SQL query is going to be a string 43 00:02:01.320 --> 00:02:04.313 that we're going to put inside this execute method here. 44 00:02:06.150 --> 00:02:07.830 It's really straightforward. 45 00:02:07.830 --> 00:02:10.227 All that we have to do is say "Create table," 46 00:02:11.090 --> 00:02:13.050 so it's pretty English-like so far, 47 00:02:13.050 --> 00:02:14.750 then we'll give it the table name, 48 00:02:14.750 --> 00:02:15.780 whatever we want to create. 49 00:02:15.780 --> 00:02:18.590 In this case, it's going to be called Books. 50 00:02:18.590 --> 00:02:21.960 The table name here is for us to understand 51 00:02:21.960 --> 00:02:24.450 what it's storing and also will be used 52 00:02:24.450 --> 00:02:26.790 when we want to retrieve data, so books seems 53 00:02:26.790 --> 00:02:29.770 like a good name, so that we can then, you know, 54 00:02:29.770 --> 00:02:31.573 know that we're storing books in it. 55 00:02:32.880 --> 00:02:36.810 Then we'll also need what columns are in our table, 56 00:02:36.810 --> 00:02:39.950 what types of data we want to store, 57 00:02:39.950 --> 00:02:42.710 and these are going to be fairly simple. 58 00:02:42.710 --> 00:02:45.230 We want to store the name of the book, 59 00:02:45.230 --> 00:02:48.693 the author of the book, and the read status. 60 00:02:50.850 --> 00:02:55.200 OK, so that goes in brackets after the table name. 61 00:02:55.200 --> 00:02:58.220 We also have to specify what type of data 62 00:02:58.220 --> 00:03:00.870 is going into each of these columns. 63 00:03:00.870 --> 00:03:05.870 Name is going to be a text, author is going to be a text, 64 00:03:06.110 --> 00:03:09.000 and read is going to be one or zero, 65 00:03:09.000 --> 00:03:10.693 so it's going to be an integer. 66 00:03:12.170 --> 00:03:15.170 SQLite only supports five types of data, 67 00:03:15.170 --> 00:03:16.650 so it's really simple. 68 00:03:16.650 --> 00:03:20.770 Null, which just means empty or void or no data in here, 69 00:03:20.770 --> 00:03:22.483 very similar to Python's none. 70 00:03:23.420 --> 00:03:26.540 Integer, which means a whole number, 71 00:03:26.540 --> 00:03:29.910 real, which means a floating point number, 72 00:03:29.910 --> 00:03:32.590 text, which just means any string, 73 00:03:32.590 --> 00:03:35.450 and blob, which is a binary data field 74 00:03:35.450 --> 00:03:37.457 where you can store things like images or documents, 75 00:03:37.457 --> 00:03:38.853 .pdfs and so forth. 76 00:03:40.070 --> 00:03:43.283 So those are the five data types that SQLite supports, 77 00:03:44.240 --> 00:03:47.330 and now we've created here our table Books 78 00:03:47.330 --> 00:03:50.610 with three columns: name, author and read. 79 00:03:50.610 --> 00:03:53.220 Notice how we don't have an ID column here 80 00:03:53.220 --> 00:03:54.760 because this is the only table we've got. 81 00:03:54.760 --> 00:03:57.310 We don't need to relate this table to any other table 82 00:03:57.310 --> 00:04:00.680 in our system, ergo, we don't need an ID. 83 00:04:00.680 --> 00:04:04.513 The name is going to be what's going to identify our table. 84 00:04:05.600 --> 00:04:08.870 As we progress, we will add some more complexity to this, 85 00:04:08.870 --> 00:04:12.380 but whichever column is going to be the main identifying 86 00:04:12.380 --> 00:04:17.380 column of your table, you can signal it as a primary key. 87 00:04:18.090 --> 00:04:20.650 So that's name, text, primary key, 88 00:04:20.650 --> 00:04:24.190 and that just tells SQLite that name can not contain 89 00:04:24.190 --> 00:04:27.890 any duplicates, so that no two books can have the same name, 90 00:04:27.890 --> 00:04:31.070 and also, it improves performance when we are going 91 00:04:31.070 --> 00:04:33.633 to be searching for specific names. 92 00:04:34.600 --> 00:04:38.620 The complexities of primary keys and what that entails 93 00:04:38.620 --> 00:04:41.280 won't be explained in this course, but in other courses. 94 00:04:41.280 --> 00:04:43.163 Here I just wanna talk about Python. 95 00:04:45.220 --> 00:04:50.220 Now, when we run this, we will be creating our data.db file, 96 00:04:50.890 --> 00:04:53.540 and inside it, we'll create our table, 97 00:04:53.540 --> 00:04:55.823 and indeed, we can go ahead and run app, 98 00:04:57.130 --> 00:05:01.500 and before we print this out, we know inside of our app 99 00:05:01.500 --> 00:05:04.280 that we are calling Create Book Table, 100 00:05:04.280 --> 00:05:07.940 so we can stop the app, and now notice how data.db 101 00:05:07.940 --> 00:05:09.410 has been created. 102 00:05:09.410 --> 00:05:14.410 We can open it, and this is some weird ass data, 103 00:05:14.430 --> 00:05:18.090 which represents whatever it is that SQLite is storing here. 104 00:05:18.090 --> 00:05:20.178 Notice how it's not terribly readable, but if you open 105 00:05:20.178 --> 00:05:23.970 data.db with your SQLite browser, 106 00:05:23.970 --> 00:05:26.700 some more stuff will pop up. 107 00:05:26.700 --> 00:05:29.620 OK, this is how to create a table using SQLite, 108 00:05:29.620 --> 00:05:33.200 use a create table, then the table name, 109 00:05:33.200 --> 00:05:35.978 and then the columns one by one, separated by commas, 110 00:05:35.978 --> 00:05:40.490 and including the type of data, and for one column 111 00:05:40.490 --> 00:05:42.673 whether they are the primary key or not. 112 00:05:44.860 --> 00:05:46.390 OK, that's it for this video. 113 00:05:46.390 --> 00:05:47.690 I'll see you on the next one where we're going 114 00:05:47.690 --> 00:05:49.083 to be inserting data.