1 00:00:00,625 --> 00:00:02,393 - [Instructor] Hi and welcome back to the course. 2 00:00:02,393 --> 00:00:04,071 In this video we're going to continue 3 00:00:04,071 --> 00:00:07,410 and start using SQLAlchemy in our models, 4 00:00:07,410 --> 00:00:09,880 which we've done to some extent. 5 00:00:09,880 --> 00:00:13,048 We've told it the columns and we've extended db.Model 6 00:00:13,048 --> 00:00:15,525 in our classes, but we're not yet using 7 00:00:15,525 --> 00:00:18,930 the power of SQLAlchemy to retrieve data 8 00:00:18,930 --> 00:00:20,874 and to insert data into a database. 9 00:00:20,874 --> 00:00:24,793 We're gonna be doing just that and you're going to love it. 10 00:00:24,793 --> 00:00:26,254 The first thing we have to do however 11 00:00:26,254 --> 00:00:29,671 is make sure that the create_table script 12 00:00:31,005 --> 00:00:33,271 creates the tables properly. 13 00:00:33,271 --> 00:00:38,178 Remember that our item model here has an ID column 14 00:00:38,178 --> 00:00:41,475 we have to make sure before we can use SQLAlchemy 15 00:00:41,475 --> 00:00:46,076 to retrieve and insert data that this column exists. 16 00:00:46,076 --> 00:00:48,852 So that's not been a problem because so far 17 00:00:48,852 --> 00:00:52,331 we have used SQLite directly, but as soon as 18 00:00:52,331 --> 00:00:54,863 we start using SQLAlchemy to retrieve and insert data 19 00:00:54,863 --> 00:00:57,642 we're going to need the table to have the correct format. 20 00:00:57,642 --> 00:01:00,140 So let's go to the create_tables and being sure 21 00:01:00,140 --> 00:01:03,473 to put in here ID, INTEGER, PRIMARY KEY. 22 00:01:04,579 --> 00:01:08,506 And then all you have to do is go to your terminal 23 00:01:08,506 --> 00:01:12,786 and make sure to go into your code folder. 24 00:01:12,786 --> 00:01:15,093 So right now I'm no longer in set section6, 25 00:01:15,093 --> 00:01:19,260 now I'm in set code and here run python create_tables.py. 26 00:01:20,835 --> 00:01:24,852 Now, this goes against what we looked at before, 27 00:01:24,852 --> 00:01:28,472 but in order to work with SQLAlchemy, 28 00:01:28,472 --> 00:01:32,639 we have to create the data.db file inside the code folder. 29 00:01:34,878 --> 00:01:36,476 If we create it outside unfortunately, 30 00:01:36,476 --> 00:01:38,979 things won't work quite as we expect, 31 00:01:38,979 --> 00:01:41,609 the way that it's taught in this video, 32 00:01:41,609 --> 00:01:44,832 we can make it work, but for simplicity reasons, 33 00:01:44,832 --> 00:01:48,453 it's better to stick to creating the tables inside 34 00:01:48,453 --> 00:01:50,476 the code folder for now. 35 00:01:50,476 --> 00:01:52,367 Afterwards we'll look at how to change that, 36 00:01:52,367 --> 00:01:55,511 it's really quite simple, but just for simplicity. 37 00:01:55,511 --> 00:01:58,421 And that's it, so now let's go back in 38 00:01:58,421 --> 00:02:01,178 and that'll now be correct. 39 00:02:01,178 --> 00:02:03,585 Okay, so now that we've done that 40 00:02:03,585 --> 00:02:06,485 we are going to go and start simplifying our code. 41 00:02:06,485 --> 00:02:07,942 I've been talking about simplifying 42 00:02:07,942 --> 00:02:09,142 the code for a while now. 43 00:02:09,142 --> 00:02:11,519 We've not really seen much simplification 44 00:02:11,519 --> 00:02:13,102 so let's get on it. 45 00:02:15,953 --> 00:02:19,278 We've got the item model and the first simplification 46 00:02:19,278 --> 00:02:23,099 we're gonna do is in the find_by_name method. 47 00:02:23,099 --> 00:02:27,266 SQLAlchemy handles all of this connection to the database 48 00:02:28,731 --> 00:02:30,532 all the cursor creation, hell, 49 00:02:30,532 --> 00:02:33,615 even the queries it can build for us. 50 00:02:35,137 --> 00:02:39,304 And not only that, but if we use SQLAlchemy to find data 51 00:02:41,965 --> 00:02:45,851 it doesn't find a row it automatically converts 52 00:02:45,851 --> 00:02:49,225 that row to and object if it can. 53 00:02:49,225 --> 00:02:52,228 So we're gonna delete the entire contents of find_by_name 54 00:02:52,228 --> 00:02:55,509 and you're gonna be amazed at how simple it becomes 55 00:02:55,509 --> 00:02:57,842 to find an item by its name. 56 00:02:59,079 --> 00:03:02,246 We're going to return ItemModel.query. 57 00:03:05,406 --> 00:03:08,597 And now, .query is not something we've defined 58 00:03:08,597 --> 00:03:11,724 it's something that comes from db.Model 59 00:03:11,724 --> 00:03:14,210 it's something that comes from SQLAlchemy. 60 00:03:14,210 --> 00:03:17,548 Now with this query, which is a query builder, 61 00:03:17,548 --> 00:03:20,027 we can build queries as its name says. 62 00:03:20,027 --> 00:03:24,110 We can do things like filter_by name equals name. 63 00:03:26,516 --> 00:03:30,495 Okay, so we've got the item model, which is the class 64 00:03:30,495 --> 00:03:33,495 which is a type of SQLAlchemy model. 65 00:03:34,978 --> 00:03:37,960 Then we say we want to query the model 66 00:03:37,960 --> 00:03:39,613 and now SQLAlchemy knows we're gonna 67 00:03:39,613 --> 00:03:42,367 be building a query on the database. 68 00:03:42,367 --> 00:03:45,823 Then we say filter_by name equals name. 69 00:03:45,823 --> 00:03:49,990 So what this is doing is saying SELECT * FROM __tablename__ 70 00:03:51,845 --> 00:03:55,845 which is items, which is WHERE name equals name. 71 00:03:56,801 --> 00:04:00,884 And this second name here is this argument there. 72 00:04:01,913 --> 00:04:04,066 Isn't that great, but it does that 73 00:04:04,066 --> 00:04:07,302 without us having to do any of the connecting, 74 00:04:07,302 --> 00:04:11,559 cursoring, iterating over rows, et cetera. 75 00:04:11,559 --> 00:04:16,248 Of course this isn't exactly what we had before, 76 00:04:16,248 --> 00:04:20,080 because before we selected the first row 77 00:04:20,080 --> 00:04:23,913 of the table after we filtered it in order to, 78 00:04:24,785 --> 00:04:28,888 find only the one element that matches the name. 79 00:04:28,888 --> 00:04:31,962 But, fortunately, the query builder, 80 00:04:31,962 --> 00:04:34,629 which filter_by is also a query builder, 81 00:04:34,629 --> 00:04:37,296 allows us to continue filtering. 82 00:04:38,226 --> 00:04:40,906 So we could technically do filter_by 83 00:04:40,906 --> 00:04:44,656 ID equals one here, so we can say .filter_by, 84 00:04:46,639 --> 00:04:49,622 .filter_by, .filter_by, et cetera, if we want 85 00:04:49,622 --> 00:04:52,602 to filter by multiple things it's always better 86 00:04:52,602 --> 00:04:55,181 to do this as we can filter 87 00:04:55,181 --> 00:04:57,823 by multiple arguments simultaneously. 88 00:04:57,823 --> 00:04:59,977 But the interesting part is that 89 00:04:59,977 --> 00:05:03,902 we can say something like this .first, 90 00:05:03,902 --> 00:05:08,419 and what that does is it says SELECT * FROM items 91 00:05:08,419 --> 00:05:10,752 WHERE name is name LIMIT one 92 00:05:12,374 --> 00:05:15,374 and that returns the first row only. 93 00:05:18,048 --> 00:05:21,302 This line of code here, directly translates 94 00:05:21,302 --> 00:05:25,422 to some SQL code, and this is the SQL code 95 00:05:25,422 --> 00:05:27,169 that it translates to. 96 00:05:27,169 --> 00:05:29,371 And it also does a bunch of more things of course. 97 00:05:29,371 --> 00:05:34,163 Then, this data also gets converted to an item model object. 98 00:05:34,163 --> 00:05:38,696 So what this is returning is an ItemModel object 99 00:05:38,696 --> 00:05:41,529 that has self.name and self.price. 100 00:05:43,419 --> 00:05:45,361 Now sure, because there is a classmethod 101 00:05:45,361 --> 00:05:49,528 we don't have to use ItemModel we can just use cls. 102 00:05:53,209 --> 00:05:56,042 Now, that has been quite a change. 103 00:05:57,201 --> 00:05:59,141 I'm just gonna go back so you can see 104 00:05:59,141 --> 00:06:03,141 the sort of difference that we have implemented. 105 00:06:04,822 --> 00:06:08,616 This is the code before and this is the code 106 00:06:08,616 --> 00:06:12,783 at the end which is pretty impressive if you ask me. 107 00:06:13,964 --> 00:06:16,964 SQLAlchemy makes things really easy. 108 00:06:18,273 --> 00:06:21,606 Now, let's move on to the insert method. 109 00:06:22,449 --> 00:06:24,308 This insert method, all it's doing, 110 00:06:24,308 --> 00:06:28,433 is it's saving the model to the database. 111 00:06:28,433 --> 00:06:32,600 Now, SQLAlchemy can directly translate from object 112 00:06:34,326 --> 00:06:36,778 to row in a database, so we don't 113 00:06:36,778 --> 00:06:40,111 have to tell it what row data to insert, 114 00:06:41,338 --> 00:06:43,003 we just have to tell it to insert 115 00:06:43,003 --> 00:06:45,512 this object into the database. 116 00:06:45,512 --> 00:06:48,236 Remember, this object that we're currently dealing with 117 00:06:48,236 --> 00:06:52,403 is self, so all we do is we say db.session.add(self) 118 00:06:55,743 --> 00:06:57,483 and then we commit as well, 119 00:06:57,483 --> 00:06:59,917 so it gets saved to the database. 120 00:06:59,917 --> 00:07:04,084 The session in this instance is a collection of objects 121 00:07:05,368 --> 00:07:07,305 that we're going to write to the database. 122 00:07:07,305 --> 00:07:09,980 We an add multiple objects to the session 123 00:07:09,980 --> 00:07:12,058 and then write them all at one, and that's more efficient 124 00:07:12,058 --> 00:07:14,794 but in this case because we're only inserting 125 00:07:14,794 --> 00:07:16,815 one object we're just doing the adding 126 00:07:16,815 --> 00:07:20,137 and the committing straight after. 127 00:07:20,137 --> 00:07:23,554 Now, as you can imagine, when we retrieve 128 00:07:24,503 --> 00:07:28,670 an object from the database that has a particular ID, 129 00:07:31,145 --> 00:07:34,145 then we can change the object's name 130 00:07:35,074 --> 00:07:38,521 and all we have to do, is we have to add it 131 00:07:38,521 --> 00:07:40,165 to the session and commit it again. 132 00:07:40,165 --> 00:07:44,332 And SQLAlchemy will do an update instead of an insert. 133 00:07:45,693 --> 00:07:48,562 So this method here actually is useful 134 00:07:48,562 --> 00:07:51,479 for both the update and the insert. 135 00:07:53,139 --> 00:07:54,929 So what we're gonna do, is we're going 136 00:07:54,929 --> 00:07:57,792 to rename it to save_to_db because now 137 00:07:57,792 --> 00:07:58,968 that's a more appropriate name. 138 00:07:58,968 --> 00:08:00,999 It's no longer inserting data, 139 00:08:00,999 --> 00:08:04,726 now it's just updating or upserting 140 00:08:04,726 --> 00:08:06,893 as that's normally called. 141 00:08:08,940 --> 00:08:12,386 The update method is no longer going to exist 142 00:08:12,386 --> 00:08:15,207 we're going to rename it to delete_from_db 143 00:08:15,207 --> 00:08:17,453 because that's sometimes useful 144 00:08:17,453 --> 00:08:21,620 and you may guess at what this is gonna look like, 145 00:08:22,835 --> 00:08:27,002 fairly simple, db.session.delete(self) 146 00:08:28,377 --> 00:08:32,630 and then we commit it and that gets removed. 147 00:08:32,630 --> 00:08:34,315 Now as you can see this code has 148 00:08:34,315 --> 00:08:36,732 been substantially simplified 149 00:08:38,012 --> 00:08:41,054 and we no longer need to import SQLite3 150 00:08:41,054 --> 00:08:42,554 at the top either. 151 00:08:44,553 --> 00:08:48,801 Now let's go to the item resource because now there's 152 00:08:48,801 --> 00:08:52,968 a bunch of things that we have to change here of course. 153 00:08:53,824 --> 00:08:57,433 The ItemModel.find_by_name still exists. 154 00:08:57,433 --> 00:09:01,589 We still have to return item.json sadly, 155 00:09:01,589 --> 00:09:05,156 we cannot return the object only yet 156 00:09:05,156 --> 00:09:09,025 we will learn how to do that later on. 157 00:09:09,025 --> 00:09:13,108 We have to rename item.insert to item.save_to_db. 158 00:09:16,253 --> 00:09:19,647 We're also going to change the delete method 159 00:09:19,647 --> 00:09:23,349 quite substantially because now we are going 160 00:09:23,349 --> 00:09:26,275 to use the model to do the deletion. 161 00:09:26,275 --> 00:09:30,434 All we have to do is first find the item by its name, 162 00:09:30,434 --> 00:09:34,601 so item is Item.find_by_name and then if the item exists 163 00:09:35,998 --> 00:09:39,165 we're going to do item.delete_from_db. 164 00:09:40,395 --> 00:09:42,895 So, as you can see this is a lot simpler. 165 00:09:42,895 --> 00:09:45,005 We still have to return a message just so users 166 00:09:45,005 --> 00:09:48,922 of our API know that the item has been deleted. 167 00:09:51,813 --> 00:09:53,393 Finally, we're also going to update 168 00:09:53,393 --> 00:09:57,036 the put method to make it a bit simpler. 169 00:09:57,036 --> 00:09:58,851 The first thing we want to do is we 170 00:09:58,851 --> 00:10:02,817 are going to get rid of this updated item there. 171 00:10:02,817 --> 00:10:05,282 And then we're gonna do the following, 172 00:10:05,282 --> 00:10:09,449 if the item is a none, then we want to create the item. 173 00:10:12,376 --> 00:10:15,126 We wanna save it to the database. 174 00:10:15,987 --> 00:10:17,925 So what we're gonna do is we're going to say 175 00:10:17,925 --> 00:10:21,617 if the item is none here, we are going to say 176 00:10:21,617 --> 00:10:23,450 that item is ItemModel 177 00:10:27,468 --> 00:10:30,484 with the name and data price, 178 00:10:30,484 --> 00:10:33,912 so we're essentially, because this found nothing, 179 00:10:33,912 --> 00:10:38,079 we're just creating a new one and calling it the same 180 00:10:39,903 --> 00:10:44,182 and if the item did exist, then what we're gonna do 181 00:10:44,182 --> 00:10:46,849 is say item.price is data price. 182 00:10:49,965 --> 00:10:54,132 Because this item is uniquely identified by its ID 183 00:10:55,348 --> 00:10:57,764 that means all we have to do at the end 184 00:10:57,764 --> 00:11:02,575 is say item.save_to_db, and SQLAlchemy will update 185 00:11:02,575 --> 00:11:06,409 if the price has changed, or it will insert a new one 186 00:11:06,409 --> 00:11:09,972 if there wasn't one there already. 187 00:11:09,972 --> 00:11:14,055 Of course at the end we have to return item.json. 188 00:11:17,422 --> 00:11:19,004 So a bunch of changes that we've made, 189 00:11:19,004 --> 00:11:20,927 and we still have to make one more. 190 00:11:20,927 --> 00:11:22,696 We're not gonna touch the item list yet, 191 00:11:22,696 --> 00:11:26,580 but in the app.py we have to now tell SQLAlchemy 192 00:11:26,580 --> 00:11:29,163 where to find the data.db file. 193 00:11:32,559 --> 00:11:36,305 So, we're gonna tell it just that at app.config 194 00:11:36,305 --> 00:11:40,388 SQLALCHEMY_DATABASE_URI is gonna be the following 195 00:11:42,383 --> 00:11:46,550 and this is important, sqllite3:///data.db. 196 00:11:52,349 --> 00:11:56,516 Okay, so what we are saying is that the SQLAlchemy database 197 00:11:57,702 --> 00:12:01,702 is gonna live at the root folder of our project. 198 00:12:03,239 --> 00:12:05,916 Essentially exactly the same as it has been done. 199 00:12:05,916 --> 00:12:08,644 Nothing is going to change in terms of how we run the files 200 00:12:08,644 --> 00:12:10,470 or anything like that. 201 00:12:10,470 --> 00:12:13,868 SQLAlchemy with this code is going to read the data.db 202 00:12:13,868 --> 00:12:16,522 that we've already created. 203 00:12:16,522 --> 00:12:18,925 An interesting thing here though that a lot 204 00:12:18,925 --> 00:12:21,558 of people find very useful, and I do as well, 205 00:12:21,558 --> 00:12:25,070 is that it doesn't have to be SQLite. 206 00:12:25,070 --> 00:12:27,769 It can be MySQL, it can be PostgreSQL, 207 00:12:27,769 --> 00:12:30,138 it can be Oracle, it can be anything. 208 00:12:30,138 --> 00:12:32,305 SQLAlchemy will just work. 209 00:12:34,741 --> 00:12:38,661 And if that sounds too good to believe, 210 00:12:38,661 --> 00:12:42,114 but honestly, that's the way it is. 211 00:12:42,114 --> 00:12:45,301 I've worked with MySQL and PostgreSQL and SQLite, 212 00:12:45,301 --> 00:12:49,131 using SQLAlchemy without changing a single bit of the code 213 00:12:49,131 --> 00:12:52,103 except, of course, this line here. 214 00:12:52,103 --> 00:12:54,315 So that's really nice. 215 00:12:54,315 --> 00:12:57,547 So now all we have to do is go over to Postman 216 00:12:57,547 --> 00:13:00,513 and we're going to be trying things out, 217 00:13:00,513 --> 00:13:03,033 see if everything works. 218 00:13:03,033 --> 00:13:06,910 So let's first register and it does help 219 00:13:06,910 --> 00:13:08,993 if your server is running 220 00:13:11,489 --> 00:13:13,572 that is usually required. 221 00:13:14,656 --> 00:13:19,033 Let's register, and it says we have registered successfully 222 00:13:19,033 --> 00:13:21,888 we can authenticate successfully 223 00:13:21,888 --> 00:13:25,721 and let's now create an item, and there we go. 224 00:13:27,465 --> 00:13:30,339 Created, one of the tests has failed though. 225 00:13:30,339 --> 00:13:32,744 Response time is less than 200 milliseconds 226 00:13:32,744 --> 00:13:36,315 so as you can see slightly slower than before, 227 00:13:36,315 --> 00:13:40,482 and that can be due to some SQLAlchemy and SQLite slowness 228 00:13:42,818 --> 00:13:45,680 or it could just be due to SQLAlchemy being slightly slower 229 00:13:45,680 --> 00:13:49,181 than writing things to the database directly. 230 00:13:49,181 --> 00:13:53,393 But, don't worry, because it does get faster 231 00:13:53,393 --> 00:13:57,560 if everything's deployed nicely and if you're using 232 00:13:58,990 --> 00:14:02,657 a non SQLite database which is a bit slower. 233 00:14:05,721 --> 00:14:09,141 Okay, so everything seems to work. 234 00:14:09,141 --> 00:14:11,533 Which is pretty nice. 235 00:14:11,533 --> 00:14:14,743 Now, we found a bug here, 'Item' has no attribute 236 00:14:14,743 --> 00:14:18,257 'find_by_name' so we may have messed something up 237 00:14:18,257 --> 00:14:21,174 in the model, in the resource here. 238 00:14:23,784 --> 00:14:26,617 Yeah, the delete Item.find_by_name 239 00:14:28,424 --> 00:14:31,080 that should be ItemModel.find_by_name. 240 00:14:31,080 --> 00:14:35,247 Easy mistake to make, let's go back to Postman, try again. 241 00:14:36,770 --> 00:14:38,831 Remember your app restarts automatically 242 00:14:38,831 --> 00:14:41,307 and if it doesn't you can start it manually in the terminal 243 00:14:41,307 --> 00:14:42,973 and there's the item deleted. 244 00:14:42,973 --> 00:14:45,155 Notice how the database stays there 245 00:14:45,155 --> 00:14:46,837 and all the items stay there when 246 00:14:46,837 --> 00:14:48,241 you restart your app because now 247 00:14:48,241 --> 00:14:52,267 this is persistently being stored on the disc. 248 00:14:52,267 --> 00:14:54,880 So everything seems to work barring that one minor bug 249 00:14:54,880 --> 00:14:58,169 which we've just fixed so we are ready to move on 250 00:14:58,169 --> 00:15:00,794 to the following videos and do remember 251 00:15:00,794 --> 00:15:04,618 that we've only modified the item model to use SQLAlchemy, 252 00:15:04,618 --> 00:15:07,031 we still have to do the same with the user model 253 00:15:07,031 --> 00:15:10,109 and we still have to do the same with the item list as well. 254 00:15:10,109 --> 00:15:12,957 So there's gonna be a few more things to change here. 255 00:15:12,957 --> 00:15:14,509 So that's it for this video, thanks for watching, 256 00:15:14,509 --> 00:15:16,690 and I'll see you on the next one.