0 1 00:00:05,160 --> 00:00:05,993 Hello Again. 1 2 00:00:05,993 --> 00:00:06,826 In previous lecture, 2 3 00:00:06,826 --> 00:00:09,220 we created, we finished our design, sorry. 3 4 00:00:09,220 --> 00:00:10,730 Now, in this session we are 4 5 00:00:10,730 --> 00:00:12,430 going to create our database. 5 6 00:00:12,430 --> 00:00:13,910 To do that what I need to do, 6 7 00:00:13,910 --> 00:00:16,843 firstly I need to import sqlite3, 7 8 00:00:17,763 --> 00:00:21,085 import sqlite3 library. 8 9 00:00:21,085 --> 00:00:22,210 After that here I will create 9 10 00:00:22,210 --> 00:00:24,103 my connection and also cursor object, 10 11 00:00:24,103 --> 00:00:28,610 connection is equal to sqlite3 dot connect, 11 12 00:00:28,610 --> 00:00:32,880 and here I will say for the name of my database, 12 13 00:00:32,880 --> 00:00:37,880 I will say, library dot db, okay? 13 14 00:00:38,050 --> 00:00:41,023 Extension is very important, it must be db. 14 15 00:00:41,970 --> 00:00:44,063 Here I will create my cursor object. 15 16 00:00:45,104 --> 00:00:48,186 Cursor is equal to con, my connection 16 17 00:00:48,186 --> 00:00:53,186 over here dot connect, okay? 17 18 00:00:55,566 --> 00:00:56,399 Sorry, 18 19 00:00:56,399 --> 00:00:57,649 con dot cursor. 19 20 00:00:59,780 --> 00:01:02,350 Now I don't need to pass in something here. 20 21 00:01:02,350 --> 00:01:03,920 Now, if I run my application, 21 22 00:01:03,920 --> 00:01:06,250 I will create my database. 22 23 00:01:06,250 --> 00:01:08,390 Now, see here, it's only main.py 23 24 00:01:08,390 --> 00:01:10,350 If I close this window, 24 25 00:01:10,350 --> 00:01:11,470 as you see now, 25 26 00:01:11,470 --> 00:01:13,118 'library.db' is here. 26 27 00:01:13,118 --> 00:01:16,150 Let's right click, showing the explorer, 27 28 00:01:16,150 --> 00:01:19,297 and go to create our tables. 28 29 00:01:22,360 --> 00:01:25,810 By the way we will create 3 tables for our application 29 30 00:01:25,810 --> 00:01:28,780 Here I will say create table. 30 31 00:01:28,780 --> 00:01:30,602 The first table will be Books. 31 32 00:01:30,602 --> 00:01:32,060 Okay? 32 33 00:01:32,060 --> 00:01:34,144 Here the first field will be 33 34 00:01:34,144 --> 00:01:37,328 book_id, okay? 34 35 00:01:37,328 --> 00:01:40,376 Of course it is integers, primary key, 35 36 00:01:40,376 --> 00:01:42,209 and auto increment. 36 37 00:01:42,209 --> 00:01:44,792 Second field will be book_name, 37 38 00:01:47,481 --> 00:01:48,813 and the TEXT. 38 39 00:01:50,130 --> 00:01:52,813 Third one will be book_author, 39 40 00:01:55,490 --> 00:01:56,993 and again TEXT. 40 41 00:01:58,070 --> 00:02:00,360 The other one will be book, 41 42 00:02:00,360 --> 00:02:02,618 I think it was 42 43 00:02:02,618 --> 00:02:03,910 book_page, 43 44 00:02:03,910 --> 00:02:05,793 and also status, 44 45 00:02:07,691 --> 00:02:10,070 and also name. 45 46 00:02:10,070 --> 00:02:12,857 Sorry, author, page, status, 46 47 00:02:12,857 --> 00:02:13,690 that's right, 47 48 00:02:13,690 --> 00:02:14,523 and something else, 48 49 00:02:14,523 --> 00:02:17,183 I try to remember. 49 50 00:02:19,000 --> 00:02:19,833 Let me, 50 51 00:02:20,860 --> 00:02:23,200 book name, author, page, and language also. 51 52 00:02:23,200 --> 00:02:25,570 Okay, sorry, also language. 52 53 00:02:25,570 --> 00:02:27,220 So we need to say here, 53 54 00:02:28,628 --> 00:02:30,212 book name, author, page, 54 55 00:02:30,212 --> 00:02:33,053 book_page, okay, 55 56 00:02:34,060 --> 00:02:35,663 this one is also TEXT. 56 57 00:02:36,670 --> 00:02:39,640 Other one will be book_language, okay, 57 58 00:02:41,800 --> 00:02:42,693 again TEXT. 58 59 00:02:44,089 --> 00:02:46,383 The last one is going to be status. 59 60 00:02:46,383 --> 00:02:47,675 What is status? 60 61 00:02:47,675 --> 00:02:49,166 Let me explain it. 61 62 00:02:49,166 --> 00:02:51,083 Let's say book_status. 62 63 00:02:53,995 --> 00:02:55,860 This one will be INTEGER, 63 64 00:02:55,860 --> 00:02:57,470 and for the default value, 64 65 00:02:57,470 --> 00:02:59,360 I will say zero. 65 66 00:02:59,360 --> 00:03:01,160 What do I mean here? 66 67 00:03:01,160 --> 00:03:05,493 Status means, this book is in my library or not. 67 68 00:03:06,618 --> 00:03:08,090 If it's status zero, then that means 68 69 00:03:08,090 --> 00:03:10,000 this book is in my library. 69 70 00:03:10,000 --> 00:03:14,410 If the book status default value is one, 70 71 00:03:14,410 --> 00:03:17,670 then that means this book was taken by someone. 71 72 00:03:17,670 --> 00:03:18,503 Okay? 72 73 00:03:19,670 --> 00:03:24,190 Now I will say okay, and create table. 73 74 00:03:24,190 --> 00:03:28,290 Now I will create member, members table. 74 75 00:03:28,290 --> 00:03:32,443 The first one will be member_id. 75 76 00:03:32,443 --> 00:03:35,530 Of course, as usual, 76 77 00:03:35,530 --> 00:03:37,880 primary key, and auto increment 77 78 00:03:37,880 --> 00:03:39,977 Add fields, member_name, 78 79 00:03:43,463 --> 00:03:44,296 and TEXT. 79 80 00:03:45,947 --> 00:03:49,780 Add member_phone, and that's enough. 80 81 00:03:49,780 --> 00:03:52,573 You can of course add more fields, but that's enough. 81 82 00:03:53,940 --> 00:03:55,890 For members it's fine. 82 83 00:03:55,890 --> 00:03:59,070 Here I will click, okay. 83 84 00:03:59,070 --> 00:04:04,070 So we will create another table, 84 85 00:04:04,700 --> 00:04:06,450 and we will make a connection 85 86 00:04:07,420 --> 00:04:08,660 between all tables. 86 87 00:04:11,306 --> 00:04:12,343 What I mean, 87 88 00:04:12,343 --> 00:04:14,126 here we create a table, 88 89 00:04:14,126 --> 00:04:16,043 and I will say borrows. 89 90 00:04:17,525 --> 00:04:19,275 Add field, borrow_id. 90 91 00:04:23,344 --> 00:04:28,344 Borrow ID's, integer, primary key, and auto increment. 91 92 00:04:28,653 --> 00:04:31,563 Second one will be mybook_id, 92 93 00:04:31,563 --> 00:04:33,563 which book was borrowed. 93 94 00:04:35,270 --> 00:04:37,248 The last one will be member_id, 94 95 00:04:37,248 --> 00:04:40,915 so which book was borrowed, by which member. 95 96 00:04:41,902 --> 00:04:43,235 Here I will say, 96 97 00:04:44,838 --> 00:04:45,671 bbook_id, 97 98 00:04:47,346 --> 00:04:48,763 b means borrowed. 98 99 00:04:50,126 --> 00:04:52,193 That one will be TEXT, why is it text? 99 100 00:04:52,193 --> 00:04:53,759 I will explain. 100 101 00:04:53,759 --> 00:04:55,160 The other one will be, 101 102 00:04:55,160 --> 00:04:56,660 borrow bmember_id, 102 103 00:05:01,259 --> 00:05:02,926 and it will be TEXT. 103 104 00:05:04,406 --> 00:05:05,349 Why is that? 104 105 00:05:05,349 --> 00:05:06,615 Normally, it must be integer right? 105 106 00:05:06,615 --> 00:05:07,448 This is id value. 106 107 00:05:07,448 --> 00:05:08,990 Actually, this is true, okay, 107 108 00:05:08,990 --> 00:05:11,030 it must be integer if it's id value, 108 109 00:05:11,030 --> 00:05:15,477 but I will not only insert this field id's, okay? 109 110 00:05:16,718 --> 00:05:19,063 I want to insert here bbook_id. 110 111 00:05:20,260 --> 00:05:21,573 ID of my book, 111 112 00:05:22,791 --> 00:05:25,310 borrowed book ID, 112 113 00:05:25,310 --> 00:05:28,200 and also name of my book. 113 114 00:05:28,200 --> 00:05:30,000 That's why I want this here as TEXT. 114 115 00:05:30,850 --> 00:05:31,980 Name of my book, 115 116 00:05:31,980 --> 00:05:33,710 and also ID of my book, okay? 116 117 00:05:33,710 --> 00:05:34,833 For the member also 117 118 00:05:36,500 --> 00:05:40,150 here I want to insert here, 118 119 00:05:40,150 --> 00:05:42,830 my member ID, and my member name. 119 120 00:05:42,830 --> 00:05:43,663 Why is it like that? 120 121 00:05:43,663 --> 00:05:46,336 Because for this application 121 122 00:05:46,336 --> 00:05:51,336 it actually makes my SQL queries better and easier. 122 123 00:05:53,450 --> 00:05:56,710 That's why I will make less SQL queries 123 124 00:05:56,710 --> 00:06:00,510 if I create my table like that, okay? 124 125 00:06:00,510 --> 00:06:01,720 That's why I am, 125 126 00:06:01,720 --> 00:06:04,890 I prefer to use together, 126 127 00:06:04,890 --> 00:06:07,093 ID's and names together, okay? 127 128 00:06:08,070 --> 00:06:11,300 After that, here I will say, okay. 128 129 00:06:11,300 --> 00:06:12,133 Now what I need to do, 129 130 00:06:12,133 --> 00:06:14,980 I need to say here, Write Changes, okay? 130 131 00:06:14,980 --> 00:06:17,533 Now our database is ready. 131 132 00:06:19,458 --> 00:06:21,070 Let me run our application. 132 133 00:06:21,070 --> 00:06:21,903 Here it is. 133 134 00:06:22,960 --> 00:06:24,610 Now in the next lesson 134 135 00:06:24,610 --> 00:06:28,740 let's create this button function, 135 136 00:06:28,740 --> 00:06:32,050 because our database is totally empty, 136 137 00:06:32,050 --> 00:06:34,690 we can add books to our database, 137 138 00:06:34,690 --> 00:06:37,170 and after that we can show them here. 138 139 00:06:37,170 --> 00:06:39,357 For now that's all, see you in the next.