WEBVTT 1 00:00:00.180 --> 00:00:01.620 Hi guys, and welcome back. 2 00:00:01.620 --> 00:00:03.230 In this quick video I just wanted to tell you 3 00:00:03.230 --> 00:00:06.333 about SQL String composition using Psycopg2. 4 00:00:07.480 --> 00:00:08.870 Here's how we've been passing arguments 5 00:00:08.870 --> 00:00:11.210 to a query for now. 6 00:00:11.210 --> 00:00:13.330 We've done something like select star from users 7 00:00:13.330 --> 00:00:15.670 where name equal percent S. 8 00:00:15.670 --> 00:00:18.500 And then when we did cursor dot execute, 9 00:00:18.500 --> 00:00:22.580 we passed in a value that would replace the percent S. 10 00:00:22.580 --> 00:00:24.640 But what if you wanted to pass in a value 11 00:00:24.640 --> 00:00:26.580 that would replace the table name, 12 00:00:26.580 --> 00:00:28.000 so instead of selecting from users 13 00:00:28.000 --> 00:00:31.610 you can select from something else that the user decided? 14 00:00:31.610 --> 00:00:32.790 Can you do this? 15 00:00:32.790 --> 00:00:37.000 Select star from percent S where name equal percent S? 16 00:00:37.000 --> 00:00:39.490 What about select percent S from percent S 17 00:00:39.490 --> 00:00:41.313 where name equal percent S? 18 00:00:42.230 --> 00:00:43.063 No. 19 00:00:43.063 --> 00:00:45.130 No, you cannot do that. 20 00:00:45.130 --> 00:00:47.800 And the reason, I'm not quiet sure. 21 00:00:47.800 --> 00:00:49.490 It's probably got to do with the fact 22 00:00:49.490 --> 00:00:52.330 that values that you are passing in 23 00:00:52.330 --> 00:00:57.330 are not the same concept as identifiers in SQL. 24 00:00:57.450 --> 00:00:59.920 You know, column and table names. 25 00:00:59.920 --> 00:01:04.300 So this is why we have to use SQL string composition. 26 00:01:04.300 --> 00:01:05.680 This is how we do it. 27 00:01:05.680 --> 00:01:08.740 First of all, from Psycopg2 we import SQL 28 00:01:09.690 --> 00:01:11.440 and then let's say that we want to ask the user 29 00:01:11.440 --> 00:01:14.830 for a table name, a column name, and a search value 30 00:01:14.830 --> 00:01:15.870 that they're looking for. 31 00:01:15.870 --> 00:01:18.650 So we just ask them with the input function. 32 00:01:18.650 --> 00:01:21.590 And then we're going to construct our query, 33 00:01:21.590 --> 00:01:25.460 and we do SQL dot SQL with uppercase, 34 00:01:25.460 --> 00:01:26.960 and then we type in our query. 35 00:01:26.960 --> 00:01:28.510 Select star from, 36 00:01:28.510 --> 00:01:31.670 and then table where column equal percent S. 37 00:01:31.670 --> 00:01:35.810 Note that the curly braces with table and column 38 00:01:35.810 --> 00:01:38.410 are not Psycopg2 specific. 39 00:01:38.410 --> 00:01:42.330 This is simply how we do formatting in Python. 40 00:01:42.330 --> 00:01:44.320 Here we're gonna use the format function 41 00:01:44.320 --> 00:01:48.440 to put values into those interpolation strings. 42 00:01:48.440 --> 00:01:51.620 So then we do query equal query dot format, 43 00:01:51.620 --> 00:01:55.870 and then for the table we're gonna pass in a SQL identifier 44 00:01:55.870 --> 00:01:58.020 which is the table name. 45 00:01:58.020 --> 00:02:01.730 And that is going to allow Psycopg2 to to construct 46 00:02:01.730 --> 00:02:03.570 the identifier in a way 47 00:02:03.570 --> 00:02:05.633 that will be understandable by Postgres. 48 00:02:06.690 --> 00:02:08.880 Similarly with a column as well. 49 00:02:08.880 --> 00:02:10.280 So at the end of the day, 50 00:02:10.280 --> 00:02:13.920 what you've got is a query string 51 00:02:13.920 --> 00:02:16.660 which is really a SQL object 52 00:02:16.660 --> 00:02:19.170 and inside it you've got the values that you want 53 00:02:19.170 --> 00:02:21.500 but it is safe to include them there. 54 00:02:21.500 --> 00:02:24.060 So this protects you against any attacks 55 00:02:24.060 --> 00:02:27.950 and also you can put in there your table and column names. 56 00:02:27.950 --> 00:02:31.160 So at the end, you would simply do cursor dot execute, 57 00:02:31.160 --> 00:02:33.070 query, and the search value. 58 00:02:33.070 --> 00:02:36.960 Let's take a look at how to pass variable fields to a query. 59 00:02:36.960 --> 00:02:38.900 We can ask the user the fields 60 00:02:38.900 --> 00:02:41.230 that they want to retrieve from the table. 61 00:02:41.230 --> 00:02:44.070 Then we're gonna grab them and put them into a list. 62 00:02:44.070 --> 00:02:46.340 Let's say that they entered a comma separated list. 63 00:02:46.340 --> 00:02:49.470 What we're doing is we're simply stripping any white space 64 00:02:49.470 --> 00:02:52.690 from the start on the end, just in case they made a mistake, 65 00:02:52.690 --> 00:02:55.180 and then we're splitting on the comma. 66 00:02:55.180 --> 00:02:59.380 And then we're gonna construct a list of SQL identifiers 67 00:02:59.380 --> 00:03:03.223 where each identifier is one field that they typed. 68 00:03:04.170 --> 00:03:07.590 Then we'll construct our queries, select fields from users, 69 00:03:07.590 --> 00:03:11.470 again using that format method syntax, 70 00:03:11.470 --> 00:03:14.810 and at the end we are going to do a query dot format, 71 00:03:14.810 --> 00:03:19.140 fields equal, and then SQL dot SQL with the comma. 72 00:03:19.140 --> 00:03:21.360 Remember this is very similar to a string, 73 00:03:21.360 --> 00:03:22.300 so what we're gonna do then 74 00:03:22.300 --> 00:03:25.480 is we're gonna use the join method from strings 75 00:03:25.480 --> 00:03:30.480 to join the SQL fields using the secure comma string there. 76 00:03:30.560 --> 00:03:33.810 Just in case it had any, you know, any SQL injection attacks 77 00:03:33.810 --> 00:03:36.140 or things like that is gonna escape things for you 78 00:03:36.140 --> 00:03:38.150 and handle them so that it is safe. 79 00:03:38.150 --> 00:03:40.080 So that's why we use the comma string there 80 00:03:40.080 --> 00:03:44.150 but cast as a SQL object. 81 00:03:44.150 --> 00:03:45.870 So really the key takeaway from this lecture 82 00:03:45.870 --> 00:03:49.390 is that whenever you want to do any SQL string construction 83 00:03:49.390 --> 00:03:54.390 using Psycopg2, you do not use plain strings. 84 00:03:54.550 --> 00:03:59.430 You use the uppercase SQL class for your strings. 85 00:03:59.430 --> 00:04:03.610 You use your SQL identifier for your columns and your tables 86 00:04:03.610 --> 00:04:05.670 and there's also a couple more things you can use. 87 00:04:05.670 --> 00:04:07.610 So, other module classes. 88 00:04:07.610 --> 00:04:08.480 You've got Identifier 89 00:04:08.480 --> 00:04:10.780 for tables and columns as we've discussed. 90 00:04:10.780 --> 00:04:13.030 You've got SQL dot Literal for any values 91 00:04:13.030 --> 00:04:15.510 that you wanna hard code into the query. 92 00:04:15.510 --> 00:04:18.080 And finally you've got SQL dot Placeholder 93 00:04:18.080 --> 00:04:19.880 for values to be passed in later 94 00:04:19.880 --> 00:04:22.773 or you can just use percent S if you prefer. 95 00:04:23.680 --> 00:04:25.240 All right, that's everything for this video. 96 00:04:25.240 --> 00:04:26.570 Just wanted to tell you about it. 97 00:04:26.570 --> 00:04:28.480 The official documentation is also really good, 98 00:04:28.480 --> 00:04:30.820 so I would encourage you to give it a read as well. 99 00:04:30.820 --> 00:04:32.590 That is linked in the resources section 100 00:04:32.590 --> 00:04:34.800 of this video, as usual. 101 00:04:34.800 --> 00:04:36.230 Thank you guys for joining me in this video, 102 00:04:36.230 --> 00:04:38.930 thanks for watching, and I'll see you in the next one.