1 00:00:00,470 --> 00:00:01,390 Hey, welcome back. 2 00:00:01,400 --> 00:00:08,660 In the previous video, we accessed the data entered by the user using this code here. 3 00:00:08,990 --> 00:00:12,200 So now we have those data stored in variables. 4 00:00:12,200 --> 00:00:19,370 And the next step is to get the values from these variables and store them in an SQL database. 5 00:00:19,370 --> 00:00:26,570 So we're going to use SQLite but through the SQL Alchemy Library. 6 00:00:26,780 --> 00:00:36,170 So you need to import from FLASK SQL Alchemy, which is the library which you need to install. 7 00:00:36,170 --> 00:00:46,610 So Flask underscore SQL Alchemy, you need to open your terminal and do PIP install flask, but use 8 00:00:46,610 --> 00:00:51,950 a dash so you import it with an underscore in between these two words. 9 00:00:51,950 --> 00:00:54,620 But then to install it you should use a dash. 10 00:00:54,620 --> 00:00:58,010 So Flask SQL Alchemy. 11 00:00:58,010 --> 00:01:04,099 So this is a separate line vary, but it does interact with flask. 12 00:01:04,099 --> 00:01:08,090 So from flask SQL alchemy you want to import. 13 00:01:09,400 --> 00:01:10,530 The SQL. 14 00:01:12,460 --> 00:01:13,900 Alchemy class. 15 00:01:14,200 --> 00:01:22,510 So why are we using SQL Alchemy and not using the SQLite three library which we use previously in the 16 00:01:22,510 --> 00:01:26,260 course in the apps, in the desktop apps? 17 00:01:26,290 --> 00:01:34,420 Well, because this library allows us to interact with the database using more high level codes, which 18 00:01:34,420 --> 00:01:39,070 makes it easier for us to manage the database and the data. 19 00:01:39,100 --> 00:01:41,050 You'll see how exactly. 20 00:01:41,140 --> 00:01:48,850 So once we import this, then below the app instance, you want to create a database instance with DB 21 00:01:48,880 --> 00:01:59,920 is equal to SQL alchemy and you provide the app variable which holds the flask instance as the argument 22 00:01:59,950 --> 00:02:01,120 of this class. 23 00:02:01,120 --> 00:02:05,240 So that will create an SQL Alchemy database instance. 24 00:02:05,260 --> 00:02:14,360 Now, in between these two we should specify a series of parameters using App.config, which is a dictionary. 25 00:02:14,360 --> 00:02:17,720 And out of this dictionary we should access. 26 00:02:21,030 --> 00:02:23,130 The keys of this dictionary. 27 00:02:23,130 --> 00:02:25,470 So App.config is a dictionary? 28 00:02:25,560 --> 00:02:26,050 Secret. 29 00:02:26,070 --> 00:02:29,580 Secret key is one of the keys of that dictionary. 30 00:02:29,580 --> 00:02:35,790 And we want to place to set here a secret key for our app. 31 00:02:36,000 --> 00:02:45,270 This will guard the application from hackers for things like cookies, tampering and hijacking the session. 32 00:02:45,270 --> 00:02:51,630 So those hackers will need this key here if they want to perform those actions. 33 00:02:51,900 --> 00:02:53,190 So just set a key. 34 00:02:53,220 --> 00:02:54,180 Whatever you like. 35 00:02:54,180 --> 00:02:56,970 Like my application. 36 00:02:57,150 --> 00:02:58,080 One, two, three. 37 00:02:58,080 --> 00:02:59,850 Just something simple for now. 38 00:03:00,360 --> 00:03:01,860 So that's the first parameter. 39 00:03:01,860 --> 00:03:04,350 The second parameter is app dot config. 40 00:03:04,800 --> 00:03:06,690 Again, you use the same syntax. 41 00:03:06,690 --> 00:03:14,430 So a dictionary and this will be SQL alchemy underscore database. 42 00:03:16,480 --> 00:03:18,070 Underscore Uri. 43 00:03:18,790 --> 00:03:26,710 Uri, I'm going to provide these also as lecture resources so you can just copy them and paste them 44 00:03:26,710 --> 00:03:28,930 because this is quite fragile. 45 00:03:28,930 --> 00:03:34,690 It's easy to to do a typo here and things will not work and it will be difficult to troubleshoot it. 46 00:03:35,020 --> 00:03:44,200 So this will be equal to SQLite colon slash, slash, slash three slashes and then data dot DB. 47 00:03:44,590 --> 00:03:45,910 So what is this? 48 00:03:45,940 --> 00:03:50,560 Well, this specifies that the URI of the database is this one here. 49 00:03:50,560 --> 00:03:55,610 So the URI specifies that we're using an SQLite database. 50 00:03:55,630 --> 00:04:01,720 If we were using MySQL, this would be something like my SQL and so on. 51 00:04:02,290 --> 00:04:09,760 But in this case it's SQLite and this is the name we want to have for the database file, which will 52 00:04:09,760 --> 00:04:11,610 be created by flask. 53 00:04:11,620 --> 00:04:16,490 So that's the value of this key of that dictionary. 54 00:04:16,490 --> 00:04:17,089 Uri. 55 00:04:17,980 --> 00:04:21,640 So these two specify the parameters of the database. 56 00:04:21,670 --> 00:04:26,020 Now, the next step is to create the database itself. 57 00:04:26,350 --> 00:04:34,320 To create the database, we need a database module and we create a database module using a class. 58 00:04:34,330 --> 00:04:36,520 Let's name this class form. 59 00:04:36,520 --> 00:04:41,080 And this should inherit from DB dot model this year. 60 00:04:41,080 --> 00:04:46,020 So DB is an instance of an SQL alchemy class. 61 00:04:46,030 --> 00:04:52,150 So this class has this module property which is also a class, right? 62 00:04:52,150 --> 00:04:57,580 And so this form will inherit from this module class. 63 00:04:57,940 --> 00:05:04,330 And here we simply specify the fields of the database table. 64 00:05:04,330 --> 00:05:05,650 So let's start with an ID. 65 00:05:06,310 --> 00:05:12,130 This is going to be DB dot column, DB dot integer. 66 00:05:12,520 --> 00:05:19,220 So the DB instance does have a lot of attributes such as column integer. 67 00:05:19,820 --> 00:05:22,540 Let's set this as a primary key. 68 00:05:22,550 --> 00:05:25,610 So primary key, set it to true. 69 00:05:25,610 --> 00:05:30,080 So this will be the first field of the database table, the first column. 70 00:05:30,080 --> 00:05:32,150 This is usually the standard practice. 71 00:05:32,150 --> 00:05:35,000 You create an ID and you set it as the primary key. 72 00:05:35,000 --> 00:05:42,740 So this will be like a unique ID that identifies each row of the data of the table. 73 00:05:42,740 --> 00:05:48,560 So in the table we're going to have an ID for each user submission, right? 74 00:05:48,590 --> 00:05:53,600 So user IDs will have one row in the database table. 75 00:05:53,600 --> 00:05:56,240 And so here we'll have the first name. 76 00:05:58,310 --> 00:06:07,090 Of that user, which is again DB column and the ID should be DB dot string with capital S. 77 00:06:07,090 --> 00:06:11,410 So don't confuse this with the string data type of python. 78 00:06:11,680 --> 00:06:17,140 This is from DB, not from the python standard library. 79 00:06:17,170 --> 00:06:18,880 String type. 80 00:06:18,880 --> 00:06:24,010 So let's set an 80 characters maximum for the first name. 81 00:06:24,850 --> 00:06:28,850 I'm going to duplicate this for the last name as well. 82 00:06:28,870 --> 00:06:31,270 I'm going to duplicate it for email. 83 00:06:32,560 --> 00:06:35,500 Let's put it as a string for the email as well. 84 00:06:35,800 --> 00:06:44,560 SQLite doesn't have a specific type for emails and then we've got available date or date. 85 00:06:44,560 --> 00:06:50,710 So this is a date that the candidates, the job candidate wants to start the new job. 86 00:06:50,710 --> 00:06:56,020 Now this is going to be db.date and remove that. 87 00:06:56,230 --> 00:07:00,970 Also the parentheses so db.date not with parentheses. 88 00:07:01,900 --> 00:07:05,170 Another duplication and this will be occupation. 89 00:07:06,310 --> 00:07:09,400 This will also be a string. 90 00:07:09,520 --> 00:07:16,440 So we'll store here unemployed or employed or self-employed or students that string. 91 00:07:16,450 --> 00:07:17,740 Let's set it at. 92 00:07:18,660 --> 00:07:21,030 80 characters maximum. 93 00:07:21,030 --> 00:07:25,110 So that's our database model. 94 00:07:25,380 --> 00:07:34,200 Now we need to execute this class somehow so that we create the table in this file, which is also going 95 00:07:34,200 --> 00:07:39,390 to be created because it doesn't exist yet here in our project directory. 96 00:07:39,970 --> 00:07:42,790 To create the database from this model. 97 00:07:42,790 --> 00:07:45,070 We should go here to the very end. 98 00:07:45,100 --> 00:07:49,780 And first, let's add this routine here. 99 00:07:49,780 --> 00:07:56,800 If name is equal to Main, I've explained this several times what this means, so you need to intend 100 00:07:56,800 --> 00:07:57,970 that there. 101 00:07:57,970 --> 00:08:05,350 You also need to add something else with app dot app context with parentheses and you indent that to 102 00:08:05,350 --> 00:08:06,610 one more time. 103 00:08:06,610 --> 00:08:10,960 And before that you say DB dot create all. 104 00:08:12,350 --> 00:08:21,560 So what this will do is it will create a database because DB points to this variable, right? 105 00:08:21,800 --> 00:08:23,030 So let's try this. 106 00:08:23,030 --> 00:08:24,080 Let's run the app. 107 00:08:24,080 --> 00:08:25,790 So this is app.py. 108 00:08:26,000 --> 00:08:27,260 I'm going to run it. 109 00:08:29,720 --> 00:08:30,980 Reload the page. 110 00:08:31,940 --> 00:08:32,990 Go here. 111 00:08:33,890 --> 00:08:41,720 And if you expand the project directory now, you'll see that an instance directory has been automatically 112 00:08:41,720 --> 00:08:49,730 created and inside that instance you should see the data dot DB file which corresponds to this file 113 00:08:49,730 --> 00:08:50,240 name here. 114 00:08:50,270 --> 00:08:51,350 Data dot db. 115 00:08:51,770 --> 00:08:56,960 So when we run the Flask app, this database was created. 116 00:08:57,080 --> 00:09:05,270 If you run the Flask app for a second time, the database will not be recreated because this method 117 00:09:05,270 --> 00:09:12,650 basically checks if there exists a database with that name, with that URI, and if it exists, it will 118 00:09:12,650 --> 00:09:17,030 not overwrite it, but it will use the existing database. 119 00:09:17,270 --> 00:09:21,020 Now, what is inside this database, you might ask? 120 00:09:21,020 --> 00:09:27,590 Well, to check that you need a third party program DB browser for SQLite. 121 00:09:27,600 --> 00:09:32,010 You can search for that program online and install it. 122 00:09:32,570 --> 00:09:34,700 It's easy to install that program. 123 00:09:34,850 --> 00:09:42,050 And so then you open it and you should see something like this, and then you could go to open database 124 00:09:42,050 --> 00:09:47,600 and locate that particular FLASK app. 125 00:09:47,600 --> 00:09:51,560 So this is my project app 16 flask form. 126 00:09:51,560 --> 00:09:58,970 If I go to instance, I locate the data dot DB file, I open it and then you want to go to browse and 127 00:09:58,970 --> 00:10:01,700 you want to select the form table here. 128 00:10:01,700 --> 00:10:08,960 So you see it has created a table named form with an ID, first name, last name, email, date and 129 00:10:08,960 --> 00:10:10,730 occupation fields. 130 00:10:11,150 --> 00:10:14,690 So that's where we are so far. 131 00:10:16,430 --> 00:10:26,330 Using the SQL Alchemy class, we were able to get this class, this database model, and create a database 132 00:10:26,330 --> 00:10:27,500 out of that. 133 00:10:27,770 --> 00:10:34,610 So this class is connected to SQL Alchemy using this DB dot model. 134 00:10:34,820 --> 00:10:44,900 And so then you say DB, DB dot create all and that class will be called and those columns will be added 135 00:10:44,900 --> 00:10:45,980 to the database. 136 00:10:45,980 --> 00:10:50,990 So this is quite a high level usage of interacting with databases. 137 00:10:50,990 --> 00:10:59,110 So with this, this lecture is complete and next we are ready to store the user data to the database 138 00:10:59,130 --> 00:11:01,370 that we created in this video. 139 00:11:01,370 --> 00:11:03,020 So let's do that in the next one.