WEBVTT 1 00:00:00.150 --> 00:00:01.360 Hi Guys, and Welcome back. 2 00:00:01.360 --> 00:00:04.570 In this video, we're going to be changing our database.py 3 00:00:04.570 --> 00:00:07.230 to use Postgres instead of sqLite. 4 00:00:07.230 --> 00:00:09.000 Remember, one of the key benefits 5 00:00:09.000 --> 00:00:10.610 of having database interactions 6 00:00:10.610 --> 00:00:13.100 and user interactions in two separate files 7 00:00:13.100 --> 00:00:15.290 is that app.py, we're not gonna touch it, 8 00:00:15.290 --> 00:00:17.120 it shouldn't change at all. 9 00:00:17.120 --> 00:00:21.800 However, we do have to make three changes in database.py. 10 00:00:21.800 --> 00:00:23.080 The first one, as I mentioned earlier, 11 00:00:23.080 --> 00:00:24.890 we can't do connection.execute, 12 00:00:24.890 --> 00:00:27.290 we have to create our cursors. 13 00:00:27.290 --> 00:00:32.120 Second thing, we can't do integer primary key in Postgres, 14 00:00:32.120 --> 00:00:35.240 and expect auto incrementing values by default. 15 00:00:35.240 --> 00:00:38.950 So whereas in sqlite, this was an allais for row id, 16 00:00:38.950 --> 00:00:40.680 and automatically increase the value 17 00:00:40.680 --> 00:00:42.520 every time we enter a new row. 18 00:00:42.520 --> 00:00:44.760 In Postgres, we actually have to give it 19 00:00:44.760 --> 00:00:47.210 a type of serial instead. 20 00:00:47.210 --> 00:00:49.180 In the next video, we have more information 21 00:00:49.180 --> 00:00:52.210 about what serial is and how it works behind the scenes, 22 00:00:52.210 --> 00:00:53.390 but for now, this is gonna give you 23 00:00:53.390 --> 00:00:55.890 an auto incrementing value. 24 00:00:55.890 --> 00:00:57.690 So that's the first change there. 25 00:00:57.690 --> 00:01:00.680 Then another change is that whereas in sqlite, 26 00:01:00.680 --> 00:01:03.840 we were using question marks for the arguments, 27 00:01:03.840 --> 00:01:05.920 in Postgres, we use percent s. 28 00:01:05.920 --> 00:01:07.590 So that's just a change that we have to go 29 00:01:07.590 --> 00:01:10.960 and copy percent s and find every question mark in here 30 00:01:10.960 --> 00:01:13.890 and replace it for percent s. 31 00:01:13.890 --> 00:01:15.680 So as you can see, pretty straightforward. 32 00:01:15.680 --> 00:01:16.830 Just make sure to not miss any, 33 00:01:16.830 --> 00:01:18.480 otherwise you're gonna get some errors later on. 34 00:01:18.480 --> 00:01:19.313 But I think that's it. 35 00:01:19.313 --> 00:01:20.480 You can always press Ctrl + F 36 00:01:20.480 --> 00:01:22.000 or Command F, if you're on a Mac, 37 00:01:22.000 --> 00:01:23.470 and find the question mark. 38 00:01:23.470 --> 00:01:25.790 No matches, so we're good to go. 39 00:01:25.790 --> 00:01:27.810 So the next thing is we have to create our cursors. 40 00:01:27.810 --> 00:01:28.890 And I showed you earlier on 41 00:01:28.890 --> 00:01:30.090 that you can just create a cursor 42 00:01:30.090 --> 00:01:32.930 with cursor equal connection.cursor. 43 00:01:32.930 --> 00:01:37.930 But actually, if you do with connection.cursor as cursor, 44 00:01:39.830 --> 00:01:42.160 making sure to spell things correctly, 45 00:01:42.160 --> 00:01:43.620 this is gonna be a little bit better. 46 00:01:43.620 --> 00:01:47.120 Because that way, any resources associated with the cursor 47 00:01:47.120 --> 00:01:48.500 are automatically released 48 00:01:48.500 --> 00:01:50.473 at the end of this context manager. 49 00:01:51.630 --> 00:01:54.030 This is a little bit different from what happened in sqlite, 50 00:01:54.030 --> 00:01:55.810 where we didn't have to close cursors. 51 00:01:55.810 --> 00:01:57.920 So just a couple of small changes there. 52 00:01:57.920 --> 00:01:59.700 We're gonna have to do this for basically 53 00:01:59.700 --> 00:02:01.620 every function that we've got here. 54 00:02:01.620 --> 00:02:03.500 So I'm gonna copy this. 55 00:02:03.500 --> 00:02:06.990 And then make sure to not miss any. 56 00:02:06.990 --> 00:02:08.730 Here, as you can see, we're already creating a cursor 57 00:02:08.730 --> 00:02:10.690 so we don't necessarily have to create our own, 58 00:02:10.690 --> 00:02:12.630 but it's better to use the context manager, 59 00:02:12.630 --> 00:02:16.040 so that we can release any resources that we already had. 60 00:02:16.040 --> 00:02:17.640 And so we're just gonna do that. 61 00:02:18.750 --> 00:02:19.800 Then here, we're gonna do this. 62 00:02:19.800 --> 00:02:22.140 Oh, by the way, I'm forgetting to use the cursor, 63 00:02:22.140 --> 00:02:24.150 we're gonna come back and do that in just a moment. 64 00:02:24.150 --> 00:02:25.870 But we're gonna paste all that in. 65 00:02:25.870 --> 00:02:28.173 Here we're gonna do the same. 66 00:02:29.810 --> 00:02:30.810 This one is okay. 67 00:02:30.810 --> 00:02:33.400 And this one is okay too. 68 00:02:33.400 --> 00:02:35.250 Now, we have to go up to the top 69 00:02:35.250 --> 00:02:37.260 where we are using connection.execute 70 00:02:37.260 --> 00:02:39.463 and simply replace those by the cursor. 71 00:02:40.540 --> 00:02:42.110 So in this function as well. 72 00:02:42.110 --> 00:02:44.680 This one is okay because it was already using a cursor. 73 00:02:44.680 --> 00:02:46.800 This one is not because it was using connection.execute, 74 00:02:46.800 --> 00:02:48.610 and so is this one, 75 00:02:48.610 --> 00:02:49.820 and the rest are okay. 76 00:02:49.820 --> 00:02:51.500 So as you can see, pretty straightforward. 77 00:02:51.500 --> 00:02:52.920 It's mostly mechanical changes 78 00:02:52.920 --> 00:02:54.480 just because these two libraries behave 79 00:02:54.480 --> 00:02:55.690 in slightly different ways. 80 00:02:55.690 --> 00:02:57.960 The people who coded the libraries decided that 81 00:02:57.960 --> 00:02:59.470 this is the way it should be done. 82 00:02:59.470 --> 00:03:02.570 In this library, no approach is better than the other. 83 00:03:02.570 --> 00:03:06.180 Although I do like the consistency here in psycopg2, 84 00:03:06.180 --> 00:03:08.720 every function is essentially identical 85 00:03:08.720 --> 00:03:10.090 with the first couple of lines. 86 00:03:10.090 --> 00:03:12.220 So you may think this is a bit of duplication, 87 00:03:12.220 --> 00:03:14.170 I like to think of it as set up a work 88 00:03:14.170 --> 00:03:15.480 that we kinda need to do. 89 00:03:15.480 --> 00:03:17.940 And not every function is gonna need its own cursor. 90 00:03:17.940 --> 00:03:20.860 But for now they do in here. 91 00:03:20.860 --> 00:03:22.800 There are ways to reduce this duplication, 92 00:03:22.800 --> 00:03:25.050 but we're not gonna worry about them for now. 93 00:03:25.980 --> 00:03:29.450 So you can see that app.py didn't have to change at all. 94 00:03:29.450 --> 00:03:31.060 So now is the moment of truth. 95 00:03:31.060 --> 00:03:34.090 We can run app.py and see what happens. 96 00:03:34.090 --> 00:03:35.740 So I'm gonna go ahead and run it. 97 00:03:36.750 --> 00:03:38.330 And you can see we get a small error here, 98 00:03:38.330 --> 00:03:39.850 the column username referenced 99 00:03:39.850 --> 00:03:42.250 in the foreign key constraint does not exist. 100 00:03:42.250 --> 00:03:43.620 Now before you go and wonder 101 00:03:43.620 --> 00:03:45.870 whether there's something wrong with the code. 102 00:03:45.870 --> 00:03:47.990 I mean, you can probably double check the columns 103 00:03:47.990 --> 00:03:50.020 and so forth, to make sure that they do match 104 00:03:50.020 --> 00:03:51.540 in terms of the naming. 105 00:03:51.540 --> 00:03:53.760 Remember that we did create a users table 106 00:03:53.760 --> 00:03:56.150 earlier on in elephantSQL, or at least I did. 107 00:03:56.150 --> 00:03:57.840 So that's why I'm getting this error. 108 00:03:57.840 --> 00:04:00.810 If we go to elephantSQL and drop the users table 109 00:04:00.810 --> 00:04:02.630 and then recreate it with our code, 110 00:04:02.630 --> 00:04:04.820 then everything should work. 111 00:04:04.820 --> 00:04:06.290 So I've come here to elephantSQL, 112 00:04:06.290 --> 00:04:09.441 and we're gonna do drop table users. 113 00:04:09.441 --> 00:04:11.090 And then just execute that. 114 00:04:11.090 --> 00:04:13.120 That's gonna delete our table, there we go. 115 00:04:13.120 --> 00:04:16.430 And then we can go back to Postgre and run this again. 116 00:04:16.430 --> 00:04:17.263 And now it works. 117 00:04:17.263 --> 00:04:21.580 So just make sure that not only can your code be wrong now, 118 00:04:21.580 --> 00:04:23.380 your database structure can also be wrong. 119 00:04:23.380 --> 00:04:24.630 So there's two things to check 120 00:04:24.630 --> 00:04:28.190 and debug when you're doing database apps. 121 00:04:28.190 --> 00:04:31.450 So we've got our code here, you feel free to test it out. 122 00:04:31.450 --> 00:04:33.730 But, I mean, it's really just the same. 123 00:04:33.730 --> 00:04:35.710 It's just sending some queries to a database. 124 00:04:35.710 --> 00:04:38.290 And as long as these queries are not malformed, 125 00:04:38.290 --> 00:04:40.480 and we've got all our percent "s"s and so forth, 126 00:04:40.480 --> 00:04:41.930 this is gonna work just the same way. 127 00:04:41.930 --> 00:04:43.660 So I'll let you guys try it out. 128 00:04:43.660 --> 00:04:45.380 Then you can go to elephantSQL, 129 00:04:45.380 --> 00:04:47.990 and do some selection there and see what's up. 130 00:04:47.990 --> 00:04:50.573 And I'm sure everything is gonna work just fine. 131 00:04:52.010 --> 00:04:53.310 Let's go into the next video. 132 00:04:53.310 --> 00:04:55.660 Thank you for watching, and I'll see you there.