1 00:00:00,329 --> 00:00:06,480 Great, so we've reached the point where we're able to access the email address 2 00:00:06,480 --> 00:00:13,549 and the height that the user is entering in our form so we were able to print those 3 00:00:13,549 --> 00:00:20,760 two values in the console. Now instead of printing them we'll just go ahead assign 4 00:00:20,760 --> 00:00:28,260 them to the database, so our database currently has no values and you can 5 00:00:28,260 --> 00:00:38,850 perform SQL query to prove that, so you go to SQL and select all from data, so 6 00:00:38,850 --> 00:00:44,160 that's the name of the table and you should be on your current database to be 7 00:00:44,160 --> 00:00:50,399 able to get the values for that table so execute the query and you see that we 8 00:00:50,399 --> 00:00:56,250 have this three fields there, but we don't have any rows, so let's go ahead 9 00:00:56,250 --> 00:01:02,879 and add them. Now to be able to add rows with SQLAlchemy you should point to the 10 00:01:02,879 --> 00:01:10,560 SQLAlchemy objects so which is the DB in our case and basically what you do is 11 00:01:10,560 --> 00:01:19,619 you point to DB and then to the session class and then to the add method and this 12 00:01:19,619 --> 00:01:25,890 add method now expects the values that you want to pass and this is a bit tricky. 13 00:01:25,890 --> 00:01:30,960 It's not values exactly, but something else. But let's try the value first so we 14 00:01:30,960 --> 00:01:38,460 have email and height which are two plain strings, so Python strings and they are 15 00:01:38,460 --> 00:01:45,810 currently being printed out in here and you also need to perform another action 16 00:01:45,810 --> 00:01:53,189 there and then the commit method that commits the session, so it commits the 17 00:01:53,189 --> 00:02:01,680 changes to the database so it's quite few lines of code. And this is not all we 18 00:02:01,680 --> 00:02:05,850 need we need something else there but let's see what happens with this too so 19 00:02:05,850 --> 00:02:11,400 that we understand how the processes is going so I'll press submit here and what 20 00:02:11,400 --> 00:02:18,510 that will do, it will go to this URL as defined in the 21 00:02:18,510 --> 00:02:28,440 index.html page in this action bottom so success decorator there and that will 22 00:02:28,440 --> 00:02:33,420 execute this function and this function will try now to send this data to the 23 00:02:33,420 --> 00:02:43,489 database, submit and yeah we have an error there. By the way note that if you have 24 00:02:43,489 --> 00:02:50,310 debug equal to false you wouldn't get these debug notifications and that means when 25 00:02:50,310 --> 00:02:54,299 you deploy your application online you want to set this to false because you 26 00:02:54,299 --> 00:02:59,549 don't want the user to see these errors and question security in your 27 00:02:59,549 --> 00:03:05,519 application, so let's see what this says. So SQLAlchemy is saying that class 28 00:03:05,519 --> 00:03:09,959 built in string is not mapped. We suggest that we 29 00:03:09,959 --> 00:03:17,209 need to map these strings to something and that something is the database model 30 00:03:17,209 --> 00:03:24,349 which we are inheriting from this class so basically what we need to do here is 31 00:03:24,349 --> 00:03:32,669 create an object instance of our data class and there what we need to pass are 32 00:03:32,669 --> 00:03:42,329 these two parameters so that would be email and height so we are sending the 33 00:03:42,329 --> 00:03:46,440 values so that the user is inputting here you are sending these values to 34 00:03:46,440 --> 00:03:54,900 these two parameters. And what the data class will do with these two is it will 35 00:03:54,900 --> 00:04:01,379 create a database model object which is recognized now by the add method of 36 00:04:01,379 --> 00:04:06,480 SQLAlchemy object so we just pass here the data object that we created 37 00:04:06,480 --> 00:04:13,290 here so data and don't confuse this data with this name. This is a table name, this 38 00:04:13,290 --> 00:04:20,400 is our variable, they don't have to be the same. Good! I saved the string no 39 00:04:20,400 --> 00:04:25,360 and the application restarted so I'll go ahead now and try this again. 40 00:04:25,360 --> 00:04:33,370 Submit and it seems to be working so we got the message there, but let's check 41 00:04:33,370 --> 00:04:42,569 the database, so I'll execute this query again and yeah here is our first record. 42 00:04:42,569 --> 00:04:50,199 Great, and we have the ID here which by the way is four it's not one because 43 00:04:50,199 --> 00:04:55,810 before I recorded this video I was making some tests and so I sent 44 00:04:55,810 --> 00:05:00,039 some data here via Python and then I deleted those data so now I'm sending 45 00:05:00,039 --> 00:05:05,979 again new records there and that starts from where I left it last time. 46 00:05:05,979 --> 00:05:14,050 So here is the height and email address. So what's left now is we were 47 00:05:14,050 --> 00:05:19,449 able to send this data to the database but now we have to send this height that 48 00:05:19,449 --> 00:05:25,300 the user entered there, we have to send it to the users email address which is 49 00:05:25,300 --> 00:05:30,969 the one that is being received together with the height and we also need to 50 00:05:30,969 --> 00:05:37,389 calculate an average out of our table so the average of the height column 51 00:05:37,389 --> 00:05:42,159 which means every time the user sends some data we calculated a new average 52 00:05:42,159 --> 00:05:48,460 over the data we have so far and send that average to the user via email, however 53 00:05:48,460 --> 00:05:55,870 before we do that let's make sure our application is running smoothly yeah we 54 00:05:55,870 --> 00:06:00,430 know that's working correctly almost, but what if I send the same email 55 00:06:00,430 --> 00:06:13,479 address to the database? What happens? We get an error, so it's a psycopg2 56 00:06:13,479 --> 00:06:20,560 duplicate key values violates unique constraint data email. That 57 00:06:20,560 --> 00:06:27,069 happens because here when we created this table field there we set this 58 00:06:27,069 --> 00:06:32,949 parameter to true so we only accept unique values for the email address and 59 00:06:32,949 --> 00:06:38,400 we don't want to have duplicate emails that wouldn't make much sense 60 00:06:38,400 --> 00:06:44,180 but we also don't want this error to be displayed and to break the user page 61 00:06:44,180 --> 00:06:51,150 when the user is pressing submit button for the second time so there may 62 00:06:51,150 --> 00:06:59,570 be various ways to handle this, but what I'm thinking of doing here is you know 63 00:06:59,570 --> 00:07:05,520 let me look at the error again so this is trying to insert data into the table 64 00:07:05,520 --> 00:07:12,449 into these two fields and that means the script stops somewhere at this point 65 00:07:12,449 --> 00:07:18,449 where we add data so we are being able to get the email address and the height 66 00:07:18,449 --> 00:07:24,360 successfully from the HTTP request but then we're not putting them in database. 67 00:07:24,360 --> 00:07:31,729 So what we need to do is we need to check this data this pair of values 68 00:07:31,729 --> 00:07:39,030 against the table rows that we have in the database. That means we need to apply 69 00:07:39,030 --> 00:07:45,990 some sort of conditional to these rows so if that conditional is not satisfied, 70 00:07:45,990 --> 00:07:54,030 then we don't execute this so that means we need to do some if there 71 00:07:54,030 --> 00:08:00,840 and then everything gives underneath so that would be something like you know 72 00:08:00,840 --> 00:08:06,630 query the database, query the database table and send this data actually the 73 00:08:06,630 --> 00:08:10,979 email address and send it to the database table and check if there is 74 00:08:10,979 --> 00:08:20,030 something like that email in the email column of the table data. So to do a query 75 00:08:20,030 --> 00:08:29,039 we would go like DB so we point to the DB object and then to the session and then 76 00:08:29,039 --> 00:08:34,560 to the query method and what we need to pass to the query is always the name of 77 00:08:34,560 --> 00:08:40,469 the class so the blueprint that holds the database model and then we filter 78 00:08:40,469 --> 00:08:47,970 that so we don't want to query everything and we want to, from data we 79 00:08:47,970 --> 00:08:57,110 want the email field which actually is a class variable 80 00:08:57,110 --> 00:09:05,000 so we are accessing this here so that field where the row of that field is 81 00:09:05,000 --> 00:09:13,100 equal to the email variable, so the email variable is this one here. I saved the 82 00:09:13,100 --> 00:09:17,900 script so we currently have a syntax error there because I haven't finished 83 00:09:17,900 --> 00:09:23,440 the query yet we need some column there, so again we are querying this database 84 00:09:27,380 --> 00:09:36,200 model here and then filtering that query where the value of this email column so 85 00:09:36,200 --> 00:09:41,360 this one here is equal to the value of the email variable which should be the 86 00:09:41,360 --> 00:09:47,180 current email address of the current HTTP request, so that's good. We also need 87 00:09:47,180 --> 00:09:52,460 to add something else here but before I do that I would like to show you what 88 00:09:52,460 --> 00:09:55,880 this expression produces so that you understand things better 89 00:09:55,880 --> 00:10:04,370 so let me temporally unindent them, the lines and I'll print this and see what 90 00:10:04,370 --> 00:10:15,560 we get out of that, so I'll save that and rerun the script. Good, we don't have any 91 00:10:15,560 --> 00:10:26,240 syntax error. Reload this and enter the same email address again and we got the 92 00:10:26,240 --> 00:10:31,310 same error there because we still haven't applied that conditional and here we 93 00:10:31,310 --> 00:10:41,770 got a long trace back so let me look, let me find the first print statement there. 94 00:10:41,770 --> 00:10:46,339 So this print statement here, so the 95 00:10:46,339 --> 00:10:53,330 email and the height are being printed out in this line so a at a.com and 96 00:10:53,330 --> 00:10:59,390 height and then under that we should have that kind of object which actually 97 00:10:59,390 --> 00:11:06,579 is an SQL query, so this is making the query to the database 98 00:11:06,579 --> 00:11:14,529 search for that existing email and now we can simplify this by adding here 99 00:11:14,529 --> 00:11:22,730 account method so that is getting a bit complicated but bear with me there, 100 00:11:22,730 --> 00:11:32,749 so let me try this again. Submit. We still get the same error and let me find the print 101 00:11:32,749 --> 00:11:39,110 there, the output of the print functions and yeah, now we see that we have 102 00:11:39,110 --> 00:11:45,559 one there as a result of this print statement which means that the query 103 00:11:45,559 --> 00:11:50,720 found one existing email address, one existing row with that email address in 104 00:11:50,720 --> 00:12:00,610 the database table which actually would be a simple integer, so let me try that. 105 00:12:00,610 --> 00:12:11,720 Check the type of that object, so it's a class integer so it's a plane 106 00:12:11,720 --> 00:12:17,420 Python integer and we can now make use of that and apply a conditional there so 107 00:12:17,420 --> 00:12:23,990 we have this scenario where we get 1 but we also have the other scenario. Let me 108 00:12:23,990 --> 00:12:29,929 remove the type there, and then bracket here. Save the script so we have another 109 00:12:29,929 --> 00:12:35,540 scenario when we get 0 so for an email address that doesn't exist in the 110 00:12:35,540 --> 00:12:45,769 database, so as you see that is a 0 just down here. That means we can apply 111 00:12:45,769 --> 00:12:54,290 conditional just here and we could say something like if this thing is equal to 112 00:12:54,290 --> 00:13:03,679 0 then store those data to the database and also return the success dot HTML 113 00:13:03,679 --> 00:13:13,720 template. If that is not true then we want to return the render 114 00:13:13,720 --> 00:13:19,570 template method so we want to render again, what do you want to render? Well we 115 00:13:19,570 --> 00:13:31,720 want to render index dot HTML, so yeah let's try that. Reload the page and let 116 00:13:31,720 --> 00:13:36,000 me first enter something that I haven't yet entered in the database. 117 00:13:36,000 --> 00:13:46,090 So a.com and 123, that successful. Now let's try something that we have 118 00:13:46,090 --> 00:13:54,130 entered before that was I believe this one or let me check in the database, so I 119 00:13:54,130 --> 00:14:04,570 forgot what we used to enter. Yeah, a@a.com, submit that. That successful. I think 120 00:14:04,570 --> 00:14:14,290 I entered a new email address. A@a.a. Yeah, I should have entered 121 00:14:14,290 --> 00:14:21,880 a@a.com, sorry about that! And a.com this is an existing email 122 00:14:21,880 --> 00:14:27,010 address. Yeah, let's now. Good, so what happens 123 00:14:27,010 --> 00:14:33,070 is that Python rendered for us the index dot HTML page again even though this 124 00:14:33,070 --> 00:14:45,610 looks like we try to access success web page, so again a@a.com, 1 submit, sorry 125 00:14:45,610 --> 00:14:54,270 this should be higher than 50. Yeah, seems to be working almost perfect 126 00:14:54,270 --> 00:14:59,650 except of it's not a great user experience so if you entered an email 127 00:14:59,650 --> 00:15:04,780 address there and then you get the same page loading again you'd think that 128 00:15:04,780 --> 00:15:10,450 application is broken so you'd want to notify the user somehow that he is 129 00:15:10,450 --> 00:15:17,500 entering the same email address, so we could do that by adding here when the 130 00:15:17,500 --> 00:15:23,920 user enters the saintly Mulder's we could add above here a message in some 131 00:15:23,920 --> 00:15:31,620 red color or something and to do that you'd want to first pass 132 00:15:31,620 --> 00:15:40,400 a string there to your index dot HTML template, let's say seems like we've got 133 00:15:40,400 --> 00:15:47,360 something from that email address already. And I'd like to put that down here so 134 00:15:50,240 --> 00:15:56,209 that you can see you things and you know that if you break a line in coma, 135 00:15:56,209 --> 00:16:05,510 after coma that things still work so Python reads this as one line, so good. 136 00:16:05,510 --> 00:16:13,510 So we will send that text to this index dot HTML template but we also need to 137 00:16:13,510 --> 00:16:19,339 say here where do we want to insert that text, so we said we want to insert it 138 00:16:19,339 --> 00:16:26,600 just above the form, so above here and what we could do maybe add 139 00:16:26,600 --> 00:16:36,170 a division there and say class message so we want to select that class later from the 140 00:16:36,170 --> 00:16:48,589 CSS file and then we need to close the div there and here comes the 141 00:16:48,589 --> 00:16:56,269 placeholders so we want to pass text in the safe method. Good, so that 142 00:16:56,269 --> 00:17:00,680 should do it. Now let's see. Make sure you reload this 143 00:17:00,680 --> 00:17:10,400 with ctrl R so you empty the cache, yep, let me try to add that email address 144 00:17:10,400 --> 00:17:14,569 again. Yeah, now we're getting some text there 145 00:17:14,569 --> 00:17:20,030 that let's the user know what they are doing so that's great and so we can 146 00:17:20,030 --> 00:17:26,390 trick that text so the style that by going to our CSS file. Staticmain.css. 147 00:17:26,390 --> 00:17:36,799 And we could point to that class so message, maybe make it bigger so 148 00:17:36,799 --> 00:17:49,250 font size 15 pixels now maybe a color of this kind 149 00:17:49,250 --> 00:17:57,440 I found this color code earlier in the web so that should work now. 150 00:17:57,440 --> 00:18:03,490 Go here and reload the page, yeah, 151 00:18:03,490 --> 00:18:12,140 so that seems to be working. Let me try that again or you could make 152 00:18:12,140 --> 00:18:18,490 the font family the same as this, so… Make sure again you reload with ctr R. 153 00:18:26,480 --> 00:18:35,990 123, yeah, so that seems to be working well obviously I have zoomed 154 00:18:35,990 --> 00:18:42,560 it out so let me zoom at 100%. This is the actual size of the web app and I 155 00:18:42,560 --> 00:18:48,380 believe we've come up with a good application and in the next lecture what 156 00:18:48,380 --> 00:18:53,600 we'll be implementing is send the user the actual results so the average of 157 00:18:53,600 --> 00:19:00,350 these data that you are collecting and we'll implement that using stmplib so 158 00:19:00,350 --> 00:19:04,550 the library that sends email addresses from an email address that we will give to 159 00:19:04,550 --> 00:19:11,090 the script to the email address that the user enters in the form. Yeah, we will do 160 00:19:11,090 --> 00:19:14,000 that in the next lecture. Talk to you there!