1 00:00:05,360 --> 00:00:06,410 Welcome back. 2 00:00:06,410 --> 00:00:10,670 We just talked about in general using Python and databases. 3 00:00:10,670 --> 00:00:17,180 Now we're going to dive into the details of actually working of databases with flask to begin working 4 00:00:17,180 --> 00:00:19,070 of databases with Python and flask. 5 00:00:19,070 --> 00:00:21,620 We essentially follow three main steps. 6 00:00:21,620 --> 00:00:26,240 The first one is to set up a sequel like database in our flask application. 7 00:00:26,240 --> 00:00:32,290 The next step is create a model inside a flask application and the last step is to perform basic crud 8 00:00:32,360 --> 00:00:33,220 on our model. 9 00:00:33,320 --> 00:00:36,840 And cruds stands for create read update and delete. 10 00:00:36,950 --> 00:00:41,570 Those are the four main actions you're going to ever do on a table in your database. 11 00:00:41,570 --> 00:00:47,250 Creating new rows reading your rows updating a row and then deleting a row. 12 00:00:47,280 --> 00:00:52,380 So let's start with that first main components step which is to set up the sequel like database in flask. 13 00:00:52,380 --> 00:00:56,820 So to create that we first create our flask application as we've always done by saying app is equal 14 00:00:56,820 --> 00:01:01,120 to a flask name then reconfigure our flask out for Sequel alchemy. 15 00:01:01,230 --> 00:01:05,990 That's going to be really similar to the way we configured a secret key when working the forms. 16 00:01:06,150 --> 00:01:09,590 And then we pass our application into the sequel alchemy class call. 17 00:01:09,810 --> 00:01:10,790 So all of these steps. 18 00:01:10,820 --> 00:01:15,780 It's just essentially four or five lines of Python code and those lines of Python code are going to 19 00:01:15,780 --> 00:01:20,000 be the same every time you set up a school like database for your flask application. 20 00:01:20,160 --> 00:01:25,050 So it's relatively straightforward because of the abstraction libraries we're using that flask sequel 21 00:01:25,080 --> 00:01:29,850 alchemy allows us to do all of this in just a few lines of Python code and we never have to get our 22 00:01:29,850 --> 00:01:33,560 hands dirty with the sequel database. 23 00:01:33,570 --> 00:01:39,240 Now the next step after that is actually create a model in flask and the term model directly links to 24 00:01:39,240 --> 00:01:41,130 a table in sequel database. 25 00:01:41,130 --> 00:01:42,940 So one will work with a python and flask. 26 00:01:43,020 --> 00:01:44,690 We refer to models. 27 00:01:44,880 --> 00:01:50,940 But on the actual back end of our database a model is just a table inside of our database with rows 28 00:01:50,940 --> 00:01:51,770 and columns. 29 00:01:51,840 --> 00:01:56,190 So keep that in mind whenever we say model we're just referring to a table in our database. 30 00:01:56,430 --> 00:02:01,020 And luckily because again of these python abstraction libraries we don't need to go manually create 31 00:02:01,020 --> 00:02:02,410 the table with sequel. 32 00:02:02,430 --> 00:02:06,530 Instead we simply create a model class in Python that generates the table for us. 33 00:02:06,540 --> 00:02:10,360 So pretty much a life saver here because we can do it all with Python. 34 00:02:10,560 --> 00:02:13,400 Now actually creating that model class is also quite simple. 35 00:02:13,890 --> 00:02:17,230 It's going to feel really similar to the way we created a flask form. 36 00:02:17,250 --> 00:02:19,220 Except in this case we're doing it for a model. 37 00:02:19,410 --> 00:02:24,020 So we just create a model class we inherit from db that model it's optional. 38 00:02:24,030 --> 00:02:29,130 You can provide a table name the sequel alchemy will actually automatically generate a table and for 39 00:02:29,130 --> 00:02:32,970 you depending on what you call your class but you can overwrite it and we'll show you how to do that 40 00:02:32,970 --> 00:02:33,760 as well. 41 00:02:33,810 --> 00:02:39,120 And then you just add in table columns as attributes and then you also add in methods for in it and 42 00:02:39,330 --> 00:02:43,380 our EPR Reaper and we're going to talk about that in more detail when we actually code it out. 43 00:02:43,380 --> 00:02:48,800 And just a little bit after that we're going to walk through a basic example of performing credit which 44 00:02:48,800 --> 00:02:53,570 is creating reading updating and deleting from that model or that table. 45 00:02:54,990 --> 00:02:59,120 So we're going to show our code operations being performed manually and adopt by script. 46 00:02:59,130 --> 00:03:01,320 Keep in mind this is just to understand the syntax. 47 00:03:01,320 --> 00:03:04,470 Typically a lot of this is actually going to be automated with a flask. 48 00:03:04,560 --> 00:03:08,050 In real life we're not going to be manually doing crud operations ourselves. 49 00:03:08,160 --> 00:03:12,810 That's going to be all handled automatically for this lecture we just need to understand the syntax 50 00:03:12,990 --> 00:03:18,370 of how we perform crud that we can write that same syntax code in scripts later in the future. 51 00:03:18,390 --> 00:03:20,370 OK so there's a lot to cover here. 52 00:03:20,370 --> 00:03:21,400 Let's get started. 53 00:03:21,690 --> 00:03:27,540 OK here I am at the text editor I have an empty basic thought py file and I'm going to start by doing 54 00:03:27,540 --> 00:03:28,950 a couple of imports. 55 00:03:28,950 --> 00:03:35,100 First off I'm going to import O S and this is allowing us to actually grab directory names and file 56 00:03:35,100 --> 00:03:38,120 pathnames programmatically with Python. 57 00:03:38,130 --> 00:03:39,830 We'll show you what that means in just a second. 58 00:03:40,200 --> 00:03:49,770 But then I'm also going say from flask import flask and then from flask underscore sequel alchemy import 59 00:03:50,310 --> 00:03:51,490 sequel alchemy. 60 00:03:51,910 --> 00:03:58,660 OK so those are all the imports we need for right now and then we want to set up our sequel like database. 61 00:03:58,730 --> 00:04:04,310 So for our base or database we need to choose a directory of words to actually put it. 62 00:04:04,310 --> 00:04:05,730 So what I'm going to do here is the following. 63 00:04:05,750 --> 00:04:09,470 I will say base directory and I'm going to collapse my side bars. 64 00:04:09,470 --> 00:04:11,090 We get a little more room here. 65 00:04:11,210 --> 00:04:21,020 I'll say based Diyar based on victory is equal to OS that path that absolute path for ABS path and then 66 00:04:21,080 --> 00:04:31,760 O S the path directory name and then underscore underscore file underscore underscore again just in 67 00:04:31,760 --> 00:04:39,150 case you made a typo here you can copy and paste this from the actual files you provide for you. 68 00:04:39,160 --> 00:04:41,730 Let's quickly break down what this line is actually doing. 69 00:04:41,740 --> 00:04:46,630 In case you're not familiar of the OS library assent to what's happening here is we have this underscore 70 00:04:46,630 --> 00:04:52,560 underscore file underscore underscore that is essentially what a module is loaded in Python. 71 00:04:52,630 --> 00:04:58,080 This file variable is built in and that's set to the name of the actual file. 72 00:04:58,090 --> 00:05:03,880 So in your code underscore in the score file is going to be automatically set to the name of the file. 73 00:05:03,910 --> 00:05:10,360 In this case that's going to mean that it's set to basic not pi because that's what I call my dot PI 74 00:05:10,360 --> 00:05:11,020 script. 75 00:05:11,270 --> 00:05:15,230 Then I want to get the actual directory name for where the file is. 76 00:05:15,250 --> 00:05:16,390 So that's what this is doing. 77 00:05:16,500 --> 00:05:18,020 Oh fastpath rectory name. 78 00:05:18,220 --> 00:05:25,690 It's grabbing the directory name for basic that Pies so that could be something like my flask examples 79 00:05:25,930 --> 00:05:27,620 for basic about pi. 80 00:05:27,790 --> 00:05:33,140 So that's grabbing the directory name and then I want the absolute path for that directory name. 81 00:05:33,310 --> 00:05:38,410 So that's essentially going to fall out or give the full directory path for this py file so that could 82 00:05:38,410 --> 00:05:40,980 be something like for my computer. 83 00:05:41,170 --> 00:05:45,530 Maybe C users Jose. 84 00:05:45,840 --> 00:05:48,740 I think it's on my desktop for slash my fossick examples. 85 00:05:48,750 --> 00:05:50,370 Basic pi etc.. 86 00:05:50,460 --> 00:05:56,520 So here I have this really long file path that instead of having to write out manually as a string I 87 00:05:56,520 --> 00:06:01,180 can use the OS library that's already built into Python to grab that myself. 88 00:06:01,230 --> 00:06:06,780 And the reason it's really important to use OS is that of manually typing up the strings yourself is 89 00:06:06,780 --> 00:06:07,320 one. 90 00:06:07,320 --> 00:06:09,080 It saves you a lot of work because you don't need it. 91 00:06:09,080 --> 00:06:15,180 Look all this up and secondly now it doesn't matter what operating system I'm on whether I'm on Linux 92 00:06:15,240 --> 00:06:17,400 Mac OS or Windows. 93 00:06:17,460 --> 00:06:23,680 This os library is going to take that into account and provide the correct format for this base directory. 94 00:06:24,500 --> 00:06:30,770 And if you want you can even say something like Prince base directory to see what this looks like. 95 00:06:30,780 --> 00:06:35,830 So going to save that and then let's say Python basic thought PI enter. 96 00:06:36,040 --> 00:06:36,530 We'll see. 97 00:06:36,540 --> 00:06:39,000 Now it's showing me that entire based on victory. 98 00:06:39,000 --> 00:06:42,320 So I see C users desktop flask examples. 99 00:06:42,390 --> 00:06:47,090 So in this case is just getting the absolute path to where my file is located. 100 00:06:47,190 --> 00:06:51,450 And notice here I'm not actually saying forward slash basic about pi. 101 00:06:51,510 --> 00:06:57,080 So the reason we now have the space directory this is where I want to build my actual database. 102 00:06:58,170 --> 00:06:59,940 So we have our base directory ready to go. 103 00:06:59,940 --> 00:07:04,770 Let's go ahead and continue and we create my flask application by saying app. 104 00:07:04,770 --> 00:07:06,290 Let me zoom in here just a little bit. 105 00:07:07,280 --> 00:07:10,460 Well say Apple is equal to flask. 106 00:07:11,230 --> 00:07:14,130 Pasand the main variable we've seen before. 107 00:07:14,320 --> 00:07:20,050 And then we need to connect our flask application to our database and we do this on that configuration 108 00:07:20,050 --> 00:07:20,910 step we discussed. 109 00:07:20,920 --> 00:07:22,900 We say app config. 110 00:07:23,530 --> 00:07:35,070 And here the key we're going to grab is siecle alchemy in all caps underscore database underscore. 111 00:07:35,070 --> 00:07:46,600 You are I and then we set that equal to the following sequel White's colon three forward slashes plus. 112 00:07:46,670 --> 00:07:55,480 And then we say oh yes stop path join and then we're going to say the base directory and then we'll 113 00:07:55,480 --> 00:07:58,580 say data Eskew a light. 114 00:07:58,630 --> 00:08:04,260 So what that line does for us is it basically sets up the database location. 115 00:08:04,300 --> 00:08:09,220 So it tells the app our database is going to be set up there and then the last configuration we need 116 00:08:09,220 --> 00:08:14,440 to do app that config is say sequel alchemy. 117 00:08:15,800 --> 00:08:20,090 Underscore track underscore modifications. 118 00:08:20,220 --> 00:08:24,900 I'm going to set that equal to false because we don't want to have to track every single modification 119 00:08:25,230 --> 00:08:26,510 in our database. 120 00:08:26,760 --> 00:08:28,560 By default it could do that for you. 121 00:08:28,560 --> 00:08:31,270 But instead it's often just set to false. 122 00:08:31,410 --> 00:08:34,450 That way in case there's an update you don't need to track all the changes. 123 00:08:34,480 --> 00:08:36,380 Now you could track modifications if you want. 124 00:08:36,390 --> 00:08:39,660 It's really up to you as you become more experienced with databases. 125 00:08:39,750 --> 00:08:41,100 Right now we're keeping things simple. 126 00:08:41,130 --> 00:08:45,240 We're just setting up our path and then saying track modifications equal to false. 127 00:08:45,240 --> 00:08:50,910 Once you've set up your configurations the last thing you need to do is say D-B is equal to and then 128 00:08:50,910 --> 00:08:54,960 we're going to call sequel alchemy and pass in our application. 129 00:08:55,430 --> 00:08:56,070 OK. 130 00:08:56,210 --> 00:09:00,360 So that essentially finishes up finishes up setting up our sequel like database. 131 00:09:00,470 --> 00:09:05,930 These simple lines of code here specifically lines 5 9 10 and 12 here. 132 00:09:05,930 --> 00:09:11,220 Those actually create a sequel like database for us purely through python. 133 00:09:11,220 --> 00:09:15,680 Now we're going to put a bunch of comments here it's kind of separate this the next step. 134 00:09:15,680 --> 00:09:21,260 It's time we actually create our first model so to do this we set up a class we're going to say class 135 00:09:22,180 --> 00:09:25,130 and remember model is just setting up a table in our database. 136 00:09:25,130 --> 00:09:32,120 So what we do here is you decide on the name of your model or call it puppy and then you inherit from 137 00:09:32,120 --> 00:09:34,790 D-B dot model. 138 00:09:35,490 --> 00:09:41,220 So when you pass on your application through sequel alchemy this D-B is now going to have a model class 139 00:09:41,220 --> 00:09:41,520 for it. 140 00:09:41,520 --> 00:09:48,780 So your database model is going to go into the class of your choosing and then a default table name 141 00:09:48,780 --> 00:09:51,340 will be provided based off your class name. 142 00:09:51,450 --> 00:09:57,630 But if you don't want that you can override it by saying underscore underscore table name underscore 143 00:09:57,660 --> 00:10:03,560 underscore is equal to puppies or whatever you want the table name. 144 00:10:03,560 --> 00:10:05,960 So this is how you manually overwrite the table names. 145 00:10:05,960 --> 00:10:11,480 This is a manual table name choice and don't worry if you forget this line. 146 00:10:11,630 --> 00:10:16,580 Basically sequel alchemy will automatically take your class name and it automatically adds an s to it 147 00:10:16,660 --> 00:10:19,800 and just makes the table name OK. 148 00:10:19,840 --> 00:10:22,430 So that's a manual table name choice we'll call it puppies. 149 00:10:22,690 --> 00:10:25,600 And then up next we're going to create the columns for the table. 150 00:10:25,840 --> 00:10:31,240 So I'm going to create a primary key column which is essentially going to be a unique ID for each puppie 151 00:10:31,240 --> 00:10:36,810 will say ID is equal to and they've been called D-B Culp's. 152 00:10:36,810 --> 00:10:38,480 That's not what I wanted. 153 00:10:38,650 --> 00:10:43,350 D-B column and then we say this is an integer. 154 00:10:43,520 --> 00:10:51,470 So say D-B integer and we'll set primary underscore key is equal to true. 155 00:10:51,480 --> 00:10:57,680 OK so what we're doing here is within our puppy class which is a model we have an attribute called ID. 156 00:10:57,850 --> 00:11:01,930 And we're setting that equal to a column inside this table. 157 00:11:01,930 --> 00:11:04,310 And then the actual column type is the first argument. 158 00:11:04,390 --> 00:11:09,340 And we set it to an integer type later and we'll explore other types so we can have. 159 00:11:09,340 --> 00:11:11,600 And then we set primary key equal to true. 160 00:11:11,800 --> 00:11:17,440 And what a primary key means is that is a unique identifier for the actual rows. 161 00:11:17,440 --> 00:11:18,990 So what does that actually mean then. 162 00:11:19,030 --> 00:11:22,240 Let's imagine that we have a table full of puppies. 163 00:11:22,240 --> 00:11:26,700 Now there are some really popular names out there for puppies such as Rufus or no. 164 00:11:26,770 --> 00:11:29,500 Spot is another really popular name. 165 00:11:29,500 --> 00:11:34,420 So what happens is you may get confused if you have two spots inside your database. 166 00:11:34,420 --> 00:11:41,500 So what you want to do is make sure they have at least one column be a primary identifier for the puppies 167 00:11:41,500 --> 00:11:42,570 in our database. 168 00:11:42,580 --> 00:11:47,490 So this idea is going to be unique for each puppy and it's essentially just an integer. 169 00:11:47,500 --> 00:11:50,730 So the first puppy in the database gets an idea of zero. 170 00:11:50,800 --> 00:11:53,050 Second puppy idea of one and so on. 171 00:11:54,000 --> 00:11:54,240 OK. 172 00:11:54,250 --> 00:11:59,620 So we have these here and we'll just check in a little bit whether it starts at zero or 1 off the remember 173 00:12:00,000 --> 00:12:03,430 full say name is equal to the name of the puppies. 174 00:12:03,520 --> 00:12:13,320 D-B call them and then we'll say D-B text so we have text there and they will say age is equal to D-B 175 00:12:14,400 --> 00:12:20,750 column and here will passen again integer because it should be an integer type. 176 00:12:20,800 --> 00:12:25,000 And like I said We'll discuss these various types so we can pass them to columns in more detail later 177 00:12:25,000 --> 00:12:25,420 on. 178 00:12:25,660 --> 00:12:31,360 But these are three main columns a unique ID the name of the puppy and then all the puppies so name 179 00:12:31,360 --> 00:12:43,630 and age then we want to do is set up an init method for our class and we'll say self name and age. 180 00:12:43,630 --> 00:12:48,080 Notice how we don't need to pass an e ID that's going to actually going to be auto created for us later. 181 00:12:48,160 --> 00:12:56,150 So we don't need to add it here and we'll say self-taught name physical to name and then self-taught 182 00:12:57,050 --> 00:12:59,740 age is equal to age. 183 00:13:00,560 --> 00:13:07,690 And then after that we're going to say DCF underscore underscore and you'll type R E P R underscore 184 00:13:07,700 --> 00:13:13,510 underscore self and this is going to give you the string representation of the object. 185 00:13:13,550 --> 00:13:19,120 So what that actually means is often when you're debugging stuff you want to print out a row of the 186 00:13:19,130 --> 00:13:23,990 column and if you just set it up like this there's actually going to be a string representation that 187 00:13:23,990 --> 00:13:24,710 exists. 188 00:13:24,920 --> 00:13:28,710 So what I'm going to do here is return one of my own choosing. 189 00:13:29,890 --> 00:13:38,470 So we'll say we'll use a string here will say Papy and then say self thought name is and then we'll 190 00:13:38,470 --> 00:13:46,690 say self the age years old. 191 00:13:46,710 --> 00:13:47,050 All right. 192 00:13:47,220 --> 00:13:51,240 So that's a string representation of printing out a puppy from our table. 193 00:13:51,270 --> 00:13:58,290 We'll just say puppy Rufus is seven years old or something similar to that and we say that and that's 194 00:13:58,290 --> 00:13:59,730 our main file here. 195 00:14:01,230 --> 00:14:02,390 Let's do a quick review. 196 00:14:02,490 --> 00:14:06,660 And then the next lecture or we're going to do is actually set up the database and add some information 197 00:14:06,660 --> 00:14:08,620 to it and then go through crud. 198 00:14:08,850 --> 00:14:11,030 But I want to quickly review have done so far. 199 00:14:11,190 --> 00:14:14,620 We did our imports including oDesk and flask with alchemy. 200 00:14:14,670 --> 00:14:20,280 We set up the base directory essentially asking for whatever folder I was located in with my flask application. 201 00:14:20,280 --> 00:14:23,010 We set up these two configurations for our application. 202 00:14:23,220 --> 00:14:28,470 We passed in our application into the secret alchemy class creating the D-B object which essentially 203 00:14:28,470 --> 00:14:32,580 stands for a database and then from there instead of a database we're going to be creating models or 204 00:14:32,580 --> 00:14:33,330 tables. 205 00:14:33,510 --> 00:14:35,230 We manually set up the table name. 206 00:14:35,310 --> 00:14:41,070 We set up the columns ID name and age and then we have this method that's going to take in the name 207 00:14:41,070 --> 00:14:42,140 and age of a puppy. 208 00:14:42,330 --> 00:14:44,660 And then also the string representation of a puppy. 209 00:14:44,880 --> 00:14:47,340 So that's all in basic pie. 210 00:14:47,550 --> 00:14:52,730 The next step that we need to do is create a new file right next to basic thought pie. 211 00:14:52,890 --> 00:15:00,040 So I'm going to say instead of my flask examples folder I'll say a new file and I'll call this set up 212 00:15:01,150 --> 00:15:04,750 database that pi and I hit enter. 213 00:15:04,750 --> 00:15:10,750 So notice again basic about pi is in the same folder location as set up databased up PI. 214 00:15:10,780 --> 00:15:16,570 So going to be using a different thought Paice group in order to actually import this D-B object. 215 00:15:16,570 --> 00:15:21,220 And this puppy class but that's just really to separate the certain steps that we're going through right 216 00:15:21,220 --> 00:15:22,010 here. 217 00:15:22,030 --> 00:15:28,090 So if you want to do any checks against the notes this file base about pi It's called basic model app 218 00:15:28,510 --> 00:15:32,430 inside of our notes under the model and crowd basics folder. 219 00:15:32,440 --> 00:15:32,950 OK. 220 00:15:33,010 --> 00:15:37,990 Coming up next we're going to set up this database and this set a database up pie will also show you 221 00:15:37,990 --> 00:15:41,160 how to do basic code in another python script. 222 00:15:41,260 --> 00:15:42,220 We'll see at the next lecture.