1 00:00:00,743 --> 00:00:02,808 - Hi and welcome back to the course. 2 00:00:02,808 --> 00:00:03,641 In this video, 3 00:00:03,641 --> 00:00:08,026 we are finally going to get started using SQLAlchemy. 4 00:00:08,026 --> 00:00:10,377 Now SQLAlchemy is going to make a lot of things 5 00:00:10,377 --> 00:00:11,943 a lot easier. 6 00:00:11,943 --> 00:00:15,736 But it does require a couple of minutes setting it up. 7 00:00:15,736 --> 00:00:16,807 So the first thing we're going to do 8 00:00:16,807 --> 00:00:19,415 is we're going to create a new file 9 00:00:19,415 --> 00:00:23,833 that is going to host our SQLAlchemy object. 10 00:00:23,833 --> 00:00:26,842 So let's create a new file in the base code folder, 11 00:00:26,842 --> 00:00:29,927 so not inside the models folder or anywhere else. 12 00:00:29,927 --> 00:00:32,231 And I'm going to call it db.py. 13 00:00:32,231 --> 00:00:34,010 You can call it whatever you want 14 00:00:34,010 --> 00:00:36,856 but I'm going to go for db. 15 00:00:36,856 --> 00:00:40,215 And what we're gonna do here is we're going to import 16 00:00:40,215 --> 00:00:41,946 the SQLAlchemy object. 17 00:00:41,946 --> 00:00:46,113 So from flask_sqlalchemy we're going to import SQLAlchemy. 18 00:00:48,167 --> 00:00:50,339 And now I'm going to initialise a variable called db 19 00:00:50,339 --> 00:00:51,922 to be a SQLAlchemy. 20 00:00:53,001 --> 00:00:55,480 Now let me explain what's going on here. 21 00:00:55,480 --> 00:00:58,730 We've got a object which is SQLAlchemy. 22 00:01:00,634 --> 00:01:01,551 And that is 23 00:01:03,224 --> 00:01:07,656 essentially a thing that is going to link to our app, 24 00:01:07,656 --> 00:01:09,159 our flask app. 25 00:01:09,159 --> 00:01:13,187 And it's going to look at all of the objects 26 00:01:13,187 --> 00:01:15,034 that we tell it to. 27 00:01:15,034 --> 00:01:18,392 And then it's going to allow us to map those objects 28 00:01:18,392 --> 00:01:20,790 to rows in a database. 29 00:01:20,790 --> 00:01:21,878 For example, 30 00:01:21,878 --> 00:01:24,102 it will allow us to do something like 31 00:01:24,102 --> 00:01:26,486 when we create an item model object 32 00:01:26,486 --> 00:01:30,935 that has a column called name and a column called price, 33 00:01:30,935 --> 00:01:34,200 it's going to allow us to very easily put that object 34 00:01:34,200 --> 00:01:36,167 into our database. 35 00:01:36,167 --> 00:01:37,929 Naturally, putting an object into a database, 36 00:01:37,929 --> 00:01:40,711 all that is is saving the object's properties 37 00:01:40,711 --> 00:01:41,863 into the database. 38 00:01:41,863 --> 00:01:44,407 And that's what SQLAlchemy excels at 39 00:01:44,407 --> 00:01:46,118 and it makes things really easy. 40 00:01:46,118 --> 00:01:48,119 It sounds like something not very useful 41 00:01:48,119 --> 00:01:51,959 but you'll see how simpler it makes our code. 42 00:01:51,959 --> 00:01:55,031 Now that we've printed a db variable, 43 00:01:55,031 --> 00:01:57,719 we're going to go over to the app.py 44 00:01:57,719 --> 00:01:59,287 and we're going to import it 45 00:01:59,287 --> 00:02:01,239 to make sure that we can use it. 46 00:02:01,239 --> 00:02:04,055 Now normally we import things at the top. 47 00:02:04,055 --> 00:02:05,750 But in this specific scenario, 48 00:02:05,750 --> 00:02:09,081 we're going to import the db variable down here 49 00:02:09,081 --> 00:02:11,624 inside the if statement. 50 00:02:11,624 --> 00:02:15,289 So we're going to do from db import db 51 00:02:15,289 --> 00:02:18,206 and then we're gonna do db.init_app 52 00:02:19,112 --> 00:02:22,774 and the app we're gonna pass in is our flask app. 53 00:02:22,774 --> 00:02:25,691 Now why are we importing this here? 54 00:02:27,270 --> 00:02:31,437 Well, it's because of a thing called circular imports. 55 00:02:33,792 --> 00:02:34,709 Our user... 56 00:02:35,813 --> 00:02:38,309 Well our item models and things 57 00:02:38,309 --> 00:02:41,317 are going to import db as well. 58 00:02:41,317 --> 00:02:43,817 So if we import db at the top, 59 00:02:45,174 --> 00:02:49,653 and we're also going to import the models at the top, 60 00:02:49,653 --> 00:02:51,510 when we import the model, 61 00:02:51,510 --> 00:02:53,316 the model's going to import the db 62 00:02:53,316 --> 00:02:56,708 and the db is going to be here in app 63 00:02:56,708 --> 00:02:58,870 and then it's going to create a circular import. 64 00:02:58,870 --> 00:03:00,039 I'll explain in a moment, 65 00:03:00,039 --> 00:03:02,420 once we've finalised setting things up 66 00:03:02,420 --> 00:03:04,170 why it wouldn't work. 67 00:03:05,940 --> 00:03:08,133 But in order for us to proceed, 68 00:03:08,133 --> 00:03:10,300 we have to make our models 69 00:03:11,742 --> 00:03:15,492 extend this db that we've got going on there. 70 00:03:16,710 --> 00:03:18,549 So what we're going to do is we're going to 71 00:03:18,549 --> 00:03:19,632 import the db 72 00:03:20,964 --> 00:03:23,157 and then both the user and the item model 73 00:03:23,157 --> 00:03:26,196 are going to extend db.model. 74 00:03:26,196 --> 00:03:27,743 And what that's gonna do is it's going to 75 00:03:27,743 --> 00:03:30,327 tell the SQLAlchemy entity 76 00:03:30,327 --> 00:03:33,061 that these classes here, 77 00:03:33,061 --> 00:03:34,598 this item model 78 00:03:34,598 --> 00:03:37,508 and the user model that we're about to 79 00:03:37,508 --> 00:03:40,308 make and extend as well, 80 00:03:40,308 --> 00:03:44,519 are things that we are going to be saving to a database 81 00:03:44,519 --> 00:03:46,149 and retrieving from a database. 82 00:03:46,149 --> 00:03:48,551 So it's going to create that mapping 83 00:03:48,551 --> 00:03:51,801 between the database and these objects. 84 00:03:53,236 --> 00:03:57,669 Okay, so all we've done is we've imported the db variable 85 00:03:57,669 --> 00:03:59,285 from the db file 86 00:03:59,285 --> 00:04:02,294 and then we've made the classes extend db model 87 00:04:02,294 --> 00:04:03,783 in both cases. 88 00:04:03,783 --> 00:04:06,740 Item model and user model. 89 00:04:06,740 --> 00:04:09,462 The next thing we have to do is tell SQLAlchemy 90 00:04:09,462 --> 00:04:14,339 the table name where these models are going to be stored. 91 00:04:14,339 --> 00:04:16,037 So as we see in our queries, 92 00:04:16,037 --> 00:04:17,923 we're accessing the user's table, 93 00:04:17,923 --> 00:04:19,911 so that's the table we're gonna be using. 94 00:04:19,911 --> 00:04:21,524 And the way we tell SQLAlchemy 95 00:04:21,524 --> 00:04:24,118 is we create a new variable called 96 00:04:24,118 --> 00:04:28,180 underscore underscore tablename underscore underscore 97 00:04:28,180 --> 00:04:29,959 and we make that equal to 98 00:04:29,959 --> 00:04:32,775 users, which is a table. 99 00:04:32,775 --> 00:04:34,577 And we also have to tell it 100 00:04:34,577 --> 00:04:37,174 what columns the table contains 101 00:04:37,174 --> 00:04:40,836 or what columns we want the table to contain. 102 00:04:40,836 --> 00:04:43,419 In this case the user has an id 103 00:04:44,454 --> 00:04:46,954 which is going to be db.Column 104 00:04:48,229 --> 00:04:49,146 db.Integer, 105 00:04:50,389 --> 00:04:53,306 primary underscore key equals true. 106 00:04:54,421 --> 00:04:57,109 So all we're doing here is we're telling SQLAlchemy 107 00:04:57,109 --> 00:04:59,972 that there is a column called id 108 00:04:59,972 --> 00:05:02,054 and that that's of type integer 109 00:05:02,054 --> 00:05:03,844 and that that's the primary key. 110 00:05:03,844 --> 00:05:04,964 Remember the primary key, 111 00:05:04,964 --> 00:05:07,955 all that means is that this is unique 112 00:05:07,955 --> 00:05:10,499 and it's going to create an index based on it. 113 00:05:10,499 --> 00:05:12,358 We've not looked at indexes yet. 114 00:05:12,358 --> 00:05:14,611 But essentially all that makes it, 115 00:05:14,611 --> 00:05:17,861 it makes it easy to search based on id. 116 00:05:19,411 --> 00:05:22,291 The next thing we're gonna have is the username, 117 00:05:22,291 --> 00:05:25,604 which is also gonna be a column. 118 00:05:25,604 --> 00:05:28,102 This time it's going to be a string. 119 00:05:28,102 --> 00:05:30,995 And now we can optionally put here something like 120 00:05:30,995 --> 00:05:35,428 80, in order to limit the size of the username, 121 00:05:35,428 --> 00:05:38,517 to make it 80 characters maximum. 122 00:05:38,517 --> 00:05:40,582 And it's usually a good idea to put limitations 123 00:05:40,582 --> 00:05:41,862 in your columns. 124 00:05:41,862 --> 00:05:44,486 Don't be too limiting with your sizes 125 00:05:44,486 --> 00:05:46,278 but also don't allow any sizes 126 00:05:46,278 --> 00:05:49,195 or else some users might go mental. 127 00:05:50,532 --> 00:05:52,199 And also a password. 128 00:05:56,868 --> 00:05:57,701 Like so. 129 00:05:58,630 --> 00:06:00,483 So now what we've done is we've told SQLAlchemy 130 00:06:00,483 --> 00:06:04,436 the three columns that this model is going to have. 131 00:06:04,436 --> 00:06:06,502 And when it comes to saving it to the database, 132 00:06:06,502 --> 00:06:10,291 it's only going to look for these three properties. 133 00:06:10,291 --> 00:06:11,157 And as you can see, 134 00:06:11,157 --> 00:06:14,613 id, username, and password must match, 135 00:06:14,613 --> 00:06:16,742 or rather the other way around, 136 00:06:16,742 --> 00:06:17,621 these properties, 137 00:06:17,621 --> 00:06:19,155 self.id, self.username, 138 00:06:19,155 --> 00:06:21,843 self.password must match the columns 139 00:06:21,843 --> 00:06:24,672 for them to be saved to the database. 140 00:06:24,672 --> 00:06:27,005 We can have other properties 141 00:06:28,624 --> 00:06:31,136 and this property won't be saved to the database. 142 00:06:31,136 --> 00:06:33,568 And it also won't give us an error. 143 00:06:33,568 --> 00:06:35,920 It will exist in the object 144 00:06:35,920 --> 00:06:38,548 but it won't be in any way related to the database. 145 00:06:38,548 --> 00:06:39,539 It won't be stored, 146 00:06:39,539 --> 00:06:41,859 it won't be read from the database. 147 00:06:41,859 --> 00:06:42,692 Okay. 148 00:06:43,870 --> 00:06:46,719 Now let's do the same thing with the item. 149 00:06:46,719 --> 00:06:49,983 We have to specify the table name, 150 00:06:49,983 --> 00:06:51,358 which is items. 151 00:06:51,358 --> 00:06:55,103 And we also have to specify the columns. 152 00:06:55,103 --> 00:06:59,088 In this case, the id column doesn't exist for our items. 153 00:06:59,088 --> 00:07:02,049 Our items have never used ids. 154 00:07:02,049 --> 00:07:04,161 But we're going to start doing that 155 00:07:04,161 --> 00:07:08,671 because having an id for each entity is usually very useful 156 00:07:08,671 --> 00:07:12,925 as we learn in the later stages of this section. 157 00:07:12,925 --> 00:07:13,889 So once again, 158 00:07:13,889 --> 00:07:16,607 the process is db.column 159 00:07:16,607 --> 00:07:18,351 to tell it that we want a column, 160 00:07:18,351 --> 00:07:20,272 then we tell it the data type, 161 00:07:20,272 --> 00:07:21,189 db.Integer, 162 00:07:22,081 --> 00:07:26,483 and finally we say if we want it to be a foreign key or not, 163 00:07:26,483 --> 00:07:28,909 which we do because this is the... 164 00:07:28,909 --> 00:07:30,731 Sorry, not the foreign key, primary key. 165 00:07:30,731 --> 00:07:31,997 Apologies. 166 00:07:31,997 --> 00:07:33,005 Don't know what I'm thinking about. 167 00:07:33,005 --> 00:07:34,076 Did I say foreign key here? 168 00:07:34,076 --> 00:07:35,373 No, I said primary key. 169 00:07:35,373 --> 00:07:37,206 Okay, the primary key. 170 00:07:38,044 --> 00:07:39,452 Yeah, the foreign key is something else. 171 00:07:39,452 --> 00:07:42,303 And we'll look at foreign keys very soon. 172 00:07:42,303 --> 00:07:44,636 And then of course the name. 173 00:07:49,804 --> 00:07:52,095 It would help if I can type, 174 00:07:52,095 --> 00:07:55,069 and the price which is gonna be another column. 175 00:07:55,069 --> 00:07:59,132 But in this case it is going to be a float. 176 00:07:59,132 --> 00:08:01,725 And we can also specify a precision. 177 00:08:01,725 --> 00:08:05,629 That's the number of numbers after the decimal point. 178 00:08:05,629 --> 00:08:08,349 And currencies are always at two decimal places. 179 00:08:08,349 --> 00:08:11,100 Actually, most of the time they're two decimal places. 180 00:08:11,100 --> 00:08:14,222 And so we're gonna stick with precision, two. 181 00:08:14,222 --> 00:08:15,835 And all that does is it says, 182 00:08:15,835 --> 00:08:19,085 this column is a floating point number, 183 00:08:20,124 --> 00:08:21,541 a decimal number. 184 00:08:22,397 --> 00:08:23,230 Okay. 185 00:08:24,159 --> 00:08:26,399 Now the last thing that we have to do as well, 186 00:08:26,399 --> 00:08:27,566 is in our app, 187 00:08:28,716 --> 00:08:29,549 we have to 188 00:08:31,007 --> 00:08:33,708 go ahead and make sure that we specify 189 00:08:33,708 --> 00:08:36,456 a configuration property 190 00:08:36,456 --> 00:08:38,123 which is app.config, 191 00:08:39,212 --> 00:08:43,379 SQLAlchemy underscore TRACK underscore MODIFICATIONS. 192 00:08:44,605 --> 00:08:46,272 It's gonna be false. 193 00:08:48,095 --> 00:08:50,140 So the reason why we say this 194 00:08:50,140 --> 00:08:51,743 is slightly more advanced. 195 00:08:51,743 --> 00:08:53,076 But essentially, 196 00:08:54,956 --> 00:08:55,873 in order to 197 00:08:57,180 --> 00:09:00,541 know when an object had changed 198 00:09:00,541 --> 00:09:03,391 but not been saved to the database, 199 00:09:03,391 --> 00:09:05,934 the extension flask SQLAlchemy 200 00:09:05,934 --> 00:09:08,541 was tracking every change that we made 201 00:09:08,541 --> 00:09:10,623 to the SQLAlchemy session, 202 00:09:10,623 --> 00:09:13,149 and that took some resources. 203 00:09:13,149 --> 00:09:15,325 Now we're turning it off because 204 00:09:15,325 --> 00:09:18,620 SQLAlchemy itself, the main library, 205 00:09:18,620 --> 00:09:21,278 has its own modification tracker 206 00:09:21,278 --> 00:09:23,006 which is a bit better. 207 00:09:23,006 --> 00:09:27,810 So this turns off the flask SQLAlchemy modification tracker. 208 00:09:27,810 --> 00:09:31,407 It does not turn off the SQLAlchemy modification tracker. 209 00:09:31,407 --> 00:09:34,705 So this is only changing the extensions behaviours 210 00:09:34,705 --> 00:09:38,912 and not the underlying SQLAlchemy behaviour. 211 00:09:38,912 --> 00:09:40,209 Okay. 212 00:09:40,209 --> 00:09:41,058 And that's everything. 213 00:09:41,058 --> 00:09:45,152 All that we've done now is we have told our app 214 00:09:45,152 --> 00:09:48,431 that we have two models that are coming from 215 00:09:48,431 --> 00:09:50,079 tables in our database. 216 00:09:50,079 --> 00:09:52,015 The users table and the items table. 217 00:09:52,015 --> 00:09:56,546 And we've told SQLAlchemy how it can read these items 218 00:09:56,546 --> 00:09:59,439 by just looking at the columns. 219 00:09:59,439 --> 00:10:01,056 And when it does look at the columns, 220 00:10:01,056 --> 00:10:02,976 it's going to see the name and the price 221 00:10:02,976 --> 00:10:06,671 and it's going to pump them in straight to the init method 222 00:10:06,671 --> 00:10:08,608 and it's going to be able to create an object 223 00:10:08,608 --> 00:10:10,843 for each row in our database. 224 00:10:10,843 --> 00:10:13,322 The id method will also be passed in 225 00:10:13,322 --> 00:10:16,970 but because there's no id parameter here it won't be used. 226 00:10:16,970 --> 00:10:19,220 Okay, and that's it really. 227 00:10:20,522 --> 00:10:24,906 But of course we still haven't used SQLAlchemy 228 00:10:24,906 --> 00:10:26,664 in any of our methods. 229 00:10:26,664 --> 00:10:28,665 These are still going straight through 230 00:10:28,665 --> 00:10:32,425 to the SQL, sorry to the SQLite database. 231 00:10:32,425 --> 00:10:35,163 So these are not using SQLAlchemy at all. 232 00:10:35,163 --> 00:10:38,457 We're going to be working on that in the next video. 233 00:10:38,457 --> 00:10:40,362 For now I'd encourage you to try all of this 234 00:10:40,362 --> 00:10:42,281 with Postman to make sure it works 235 00:10:42,281 --> 00:10:43,885 and then move on to the next video. 236 00:10:43,885 --> 00:10:45,279 So I'll see you there.