WEBVTT 1 00:00:00.250 --> 00:00:01.490 Hi guys, and welcome back. 2 00:00:01.490 --> 00:00:03.280 In this video we're going to be working on 3 00:00:03.280 --> 00:00:08.280 creating new polls, and on the returning key-wording sequel. 4 00:00:08.490 --> 00:00:10.900 First of all though, I'm going to show how we could 5 00:00:10.900 --> 00:00:13.670 create a poll, without the returning key-word 6 00:00:13.670 --> 00:00:15.580 and then I'm going to show you what the returning 7 00:00:15.580 --> 00:00:18.910 key-word really does, and why it can help us here. 8 00:00:18.910 --> 00:00:19.810 Let's get started. 9 00:00:20.650 --> 00:00:23.020 Here we've got the prompt to create 10 00:00:23.020 --> 00:00:24.220 a poll in our application. 11 00:00:24.220 --> 00:00:26.690 We're asking the user for the poll title that they want 12 00:00:26.690 --> 00:00:28.324 to create, as well as who the owner is, 13 00:00:28.324 --> 00:00:31.579 then we've got potentially a list of strings, 14 00:00:31.579 --> 00:00:34.890 One string for each option text. 15 00:00:34.890 --> 00:00:36.937 So for example, they might type "what's better 16 00:00:36.937 --> 00:00:40.190 Python or Java?", the owner might be "Jose", 17 00:00:40.190 --> 00:00:42.527 which is me, and then they might type two options, 18 00:00:42.527 --> 00:00:45.100 "Python", "Java", and then an empty string 19 00:00:45.100 --> 00:00:46.940 which would signal the end of this loop, 20 00:00:46.940 --> 00:00:49.527 and then we would pass in here the connection, 21 00:00:49.527 --> 00:00:53.177 "Python vs Java", "Jose", and then a list with 22 00:00:53.177 --> 00:00:55.540 "Python and Java" in it. 23 00:00:55.540 --> 00:00:56.750 Obviously Python is the right choice, 24 00:00:56.750 --> 00:01:00.040 but you might want a poll for that in case you want to 25 00:01:00.040 --> 00:01:02.400 share it with your friends who don't programme, 26 00:01:02.400 --> 00:01:03.790 or something like that. 27 00:01:03.790 --> 00:01:07.790 So then that would call "create poll", and here we 28 00:01:07.790 --> 00:01:10.640 take the title, the owner, and the different options. 29 00:01:10.640 --> 00:01:14.817 So with what we know so far we can implement this already, 30 00:01:14.817 --> 00:01:17.990 but it's quite sub-optimal. 31 00:01:17.990 --> 00:01:19.500 How might we do this at the moment? 32 00:01:19.500 --> 00:01:22.670 Well right now we would first of all have to insert 33 00:01:22.670 --> 00:01:23.900 into polls, so we would do something like 34 00:01:23.900 --> 00:01:28.170 "cursor.execute" and then "insert into polls" 35 00:01:28.170 --> 00:01:32.017 the values "%s", "%s", and then we would pass in here 36 00:01:32.017 --> 00:01:34.350 the title and the owner. 37 00:01:34.350 --> 00:01:36.360 This would create the poll. 38 00:01:36.360 --> 00:01:38.544 Queries within a transaction, which is what happens 39 00:01:38.544 --> 00:01:42.250 when you deal with connection are isolated 40 00:01:42.250 --> 00:01:45.780 from the rest, but you still have access to the data 41 00:01:45.780 --> 00:01:47.270 that this transaction produces 42 00:01:47.270 --> 00:01:48.870 while running the transaction. 43 00:01:48.870 --> 00:01:51.440 So even after running insert into polls, 44 00:01:51.440 --> 00:01:53.431 this poll is now added to the database, 45 00:01:53.431 --> 00:01:56.050 but only available to this transaction, 46 00:01:56.050 --> 00:01:59.631 which means that we can now go and select from polls. 47 00:01:59.631 --> 00:02:01.570 Why do we want to select from polls? 48 00:02:01.570 --> 00:02:05.080 So we can grab the "poll ID" that is automatically generated 49 00:02:05.080 --> 00:02:07.650 by the database, since we need that in order to 50 00:02:07.650 --> 00:02:10.270 create the options in the options table. 51 00:02:10.270 --> 00:02:15.270 So we would do "cursor.execute" "select id from polls order 52 00:02:15.440 --> 00:02:20.000 order by id" in descending order "limit 1". 53 00:02:20.000 --> 00:02:22.320 This would give us essentially, the latest ID 54 00:02:22.320 --> 00:02:24.210 that has been added to the database. 55 00:02:24.210 --> 00:02:26.650 So now that we've got that, we can say that "poll id" 56 00:02:26.650 --> 00:02:31.060 is equal to "cursor.fetchone" and then access column 0, 57 00:02:31.060 --> 00:02:33.440 which is really the only column returned, 58 00:02:33.440 --> 00:02:34.557 then we could say something like 59 00:02:34.557 --> 00:02:38.689 "option values" is equal to "option text poll id", 60 00:02:38.689 --> 00:02:43.166 or "option text in options", that would give us 61 00:02:43.166 --> 00:02:48.166 this list here which has one topple for each option, 62 00:02:50.000 --> 00:02:52.820 and that topple contains the option text and the poll ID. 63 00:02:52.820 --> 00:02:55.400 Notice that this is the data that we need 64 00:02:55.400 --> 00:02:58.110 for the options table, we need the option text 65 00:02:58.110 --> 00:03:00.250 and the poll ID, the ID is going to be 66 00:03:00.250 --> 00:03:03.100 automatically generated, so this gives us everything 67 00:03:03.100 --> 00:03:07.760 we need, and then we do "for option value in option values" 68 00:03:09.520 --> 00:03:13.820 like that, "cursor.execute", "insert option" 69 00:03:13.820 --> 00:03:17.653 with "option value" like that. 70 00:03:19.070 --> 00:03:22.570 This is what we can do already with what we know. 71 00:03:22.570 --> 00:03:24.660 As long as you remember how to do all of this stuff, 72 00:03:24.660 --> 00:03:27.120 which is you don't, no problem, we've got the e-book 73 00:03:27.120 --> 00:03:28.570 there for you to check out if you want, 74 00:03:28.570 --> 00:03:30.880 or you can revisit the past lectures, 75 00:03:30.880 --> 00:03:33.330 but clearly it's not great. Right? 76 00:03:33.330 --> 00:03:35.560 We've got to do a bunch of stuff here, like 77 00:03:35.560 --> 00:03:39.120 this secondary selecting, then we've got this loop here 78 00:03:39.120 --> 00:03:41.050 to insert all of the different options. 79 00:03:41.050 --> 00:03:42.840 We're going to do much better than that, 80 00:03:42.840 --> 00:03:45.090 but before we can do that we need to learn 81 00:03:45.090 --> 00:03:47.970 about the returning key-word, which is going to allow us 82 00:03:47.970 --> 00:03:50.340 to get rid of this select statement. 83 00:03:50.340 --> 00:03:51.240 Let's take a look. 84 00:03:52.260 --> 00:03:54.651 So real quick, the returning keyword. 85 00:03:54.651 --> 00:03:56.180 What's it for? 86 00:03:56.180 --> 00:03:58.000 Well often when we're modifying a row, 87 00:03:58.000 --> 00:03:59.640 we don't get anything back, 88 00:03:59.640 --> 00:04:01.147 but sometimes it can be handy. 89 00:04:01.147 --> 00:04:03.390 So when you're doing an insert, for example, 90 00:04:03.390 --> 00:04:05.550 you can get the data back, and that means you're going to 91 00:04:05.550 --> 00:04:08.960 see that auto-generated ID field. 92 00:04:08.960 --> 00:04:10.713 So here's what it looks like. 93 00:04:10.713 --> 00:04:15.713 Insert into polls the values of whatever returning ID, 94 00:04:16.560 --> 00:04:19.280 and that can be comma separated columns, 95 00:04:19.280 --> 00:04:21.670 or it can be the star for everything you've inserted 96 00:04:21.670 --> 00:04:22.503 and so on. 97 00:04:22.503 --> 00:04:25.530 So no secret here, nothing particularly tricky, 98 00:04:25.530 --> 00:04:28.060 but nonetheless something worth knowing about. 99 00:04:28.060 --> 00:04:29.260 Let's go back to Python. 100 00:04:31.320 --> 00:04:33.640 All right, so now we know that can simply put 101 00:04:33.640 --> 00:04:37.182 returning ID here, and now we no longer need 102 00:04:37.182 --> 00:04:40.300 this separate select statement because running this 103 00:04:40.300 --> 00:04:43.110 query will both insert into the table as well as 104 00:04:43.110 --> 00:04:46.660 give us back a result set with one row and one column 105 00:04:46.660 --> 00:04:48.270 so when we do "cursor.fetchone" 106 00:04:48.270 --> 00:04:49.850 and we get this zero column there 107 00:04:49.850 --> 00:04:52.200 it's going to give us that value back. 108 00:04:52.200 --> 00:04:54.640 Now remember that something I forgot to do here 109 00:04:54.640 --> 00:04:57.360 essentially is we do need to put the columns 110 00:04:57.360 --> 00:05:00.180 that we want to enter data into, otherwise 111 00:05:00.180 --> 00:05:02.192 we're going to try to put this data into the ID column, 112 00:05:02.192 --> 00:05:03.440 which is not going to work, 113 00:05:03.440 --> 00:05:05.710 and also we're going to be missing one column of data. 114 00:05:05.710 --> 00:05:07.310 So that's just my bad there, but do remember 115 00:05:07.310 --> 00:05:08.840 to put that in. 116 00:05:08.840 --> 00:05:11.500 Then I'm going to copy that and go up to the top 117 00:05:11.500 --> 00:05:16.490 and create a query for that insert poll return ID, 118 00:05:16.490 --> 00:05:18.370 for example, and I'm going to paste that in there, 119 00:05:18.370 --> 00:05:21.440 now I can copy that, come down again and replace it. 120 00:05:21.440 --> 00:05:24.550 Just make sure that our queries are all defined at the top 121 00:05:24.550 --> 00:05:26.040 so if we want to make any changes later on 122 00:05:26.040 --> 00:05:28.310 it's really easy, we don't have to go looking through 123 00:05:28.310 --> 00:05:29.433 these functions. 124 00:05:30.640 --> 00:05:32.028 I also mentioned that I wasn't a big fan of 125 00:05:32.028 --> 00:05:35.920 this for loop here, because we've got a lot of different 126 00:05:35.920 --> 00:05:39.420 cursor executes running for all different pieces of data 127 00:05:39.420 --> 00:05:42.450 and all that, and psycopg2 fortunately comes with 128 00:05:42.450 --> 00:05:43.790 a better way of doing this. 129 00:05:43.790 --> 00:05:46.025 So what we're going to do is go up to the top of the file 130 00:05:46.025 --> 00:05:47.810 and we're going to do an import here. 131 00:05:47.810 --> 00:05:52.810 We're going do "from psycopg2.extras import execute_values" 132 00:05:54.560 --> 00:05:57.610 and now we can grab that, come down to the bottom 133 00:05:57.610 --> 00:06:00.433 and what we're going to do is "execute_values" 134 00:06:01.679 --> 00:06:05.060 and we have to pass in the cursor that we're going to use 135 00:06:05.060 --> 00:06:07.421 to execute all of the different queries, 136 00:06:07.421 --> 00:06:11.920 the query that we want to run, and also a list of topples 137 00:06:11.920 --> 00:06:14.390 so that's our option values linked, 138 00:06:14.390 --> 00:06:17.540 and this is simply a helper to do this. 139 00:06:17.540 --> 00:06:21.120 It's going to take in each of the topples in this list 140 00:06:21.120 --> 00:06:23.720 and pass them through the query 141 00:06:23.720 --> 00:06:25.130 and run them with a cursor. 142 00:06:25.130 --> 00:06:26.788 So it saves us a bit of typing, 143 00:06:26.788 --> 00:06:28.730 and also it's a little bit better. 144 00:06:28.730 --> 00:06:30.070 All right that's everything for this video, 145 00:06:30.070 --> 00:06:31.060 thank you for joining me. 146 00:06:31.060 --> 00:06:32.140 I hope you've learned something, 147 00:06:32.140 --> 00:06:33.790 and I'll see you in the next one.