1 00:00:00,030 --> 00:00:06,120 Good, now we have access to the email and to the height values that the user is 2 00:00:06,120 --> 00:00:11,190 entering in the frontend interface and now that we have access to these two values 3 00:00:11,190 --> 00:00:17,850 we'll go ahead and store these values in a PostgreSQL database and that means you 4 00:00:17,850 --> 00:00:21,900 need to have a PostgreSQL database server installed in your computer and 5 00:00:21,900 --> 00:00:26,070 you need to create a table in that database and then you need to create an 6 00:00:26,070 --> 00:00:31,830 email and a height column in that table and once we have those, than instead of 7 00:00:31,830 --> 00:00:38,010 printing these two values we'll send them in the table of your database and they 8 00:00:38,010 --> 00:00:44,129 will be stored as rows and each submission will be stored in a row, in a 9 00:00:44,129 --> 00:00:51,270 single row so what you need is you need to have PostreSQL installed and I 10 00:00:51,270 --> 00:00:58,020 explained that how to install PostgreSQL in one of our previous lectures where we 11 00:00:58,020 --> 00:01:03,180 built this bookstore application so there we use PostgreSQL to store book 12 00:01:03,180 --> 00:01:08,939 data in the database and so if you have problems with installing it, just locate 13 00:01:08,939 --> 00:01:14,520 the lecture that is about installing PostgreSQL and you should be good to go. 14 00:01:14,520 --> 00:01:21,420 And one way to create database tables and to access PostgreSQL in general is 15 00:01:21,420 --> 00:01:28,350 to use pgAdmin 3 so that was install with your PostgreSQL so when you install 16 00:01:28,350 --> 00:01:34,409 PostgreSQL, PostgreSQL will ask you to provide password so you need to remember 17 00:01:34,409 --> 00:01:42,000 that and then you open pgAdmin which is installed with PostgreSQL and you connect to 18 00:01:42,000 --> 00:01:49,799 PostgreSQL server so connect and then you enter your password minus 19 00:01:49,799 --> 00:01:54,780 Postgre123. And then you have your databases 20 00:01:54,780 --> 00:01:59,700 there I have four here but you should have only Postgres if you have just 21 00:01:59,700 --> 00:02:04,469 installed PostgreSQL and I'll go ahead and create a new database for our 22 00:02:04,469 --> 00:02:12,709 application so new database. let's call that height 23 00:02:12,709 --> 00:02:20,220 collector. you need to specify the owner so Postgres in my case that's the user 24 00:02:20,220 --> 00:02:28,739 of the database and ok. And your database is created but you have no tables in 25 00:02:28,739 --> 00:02:34,920 there so if you expand this you see that tables is zero here and so we will 26 00:02:34,920 --> 00:02:42,630 create a table but the way we will create that is by using Python. Now when 27 00:02:42,630 --> 00:02:49,590 we build our bookstore application with tkinter and psycopg2 so we used 28 00:02:49,590 --> 00:02:55,590 psycopg2 to access PostgreSQL database and psycopg2 is a library 29 00:02:55,590 --> 00:03:00,329 that allows you to set SQL statements to your database so you can query data 30 00:03:00,329 --> 00:03:06,390 create tables and so on. Now we can use the same library for Flask as well but a 31 00:03:06,390 --> 00:03:12,780 more commonly used library for operating with PostgreSQL databases in Flask 32 00:03:12,780 --> 00:03:20,790 is SQLAlchemy and SQLAlchemy compared to psycopg2, SQLAlchemy is 33 00:03:20,790 --> 00:03:28,859 more higher level than psycopg2 so in general with SQLAlchemy you apply 34 00:03:28,859 --> 00:03:33,150 operations with less lines of code for instance you don't have to establish 35 00:03:33,150 --> 00:03:38,790 a connection using SQLAlchemy and then commit the changes and close that 36 00:03:38,790 --> 00:03:44,130 connection so if you don't want to use this repeated code than it's good to use 37 00:03:44,130 --> 00:03:49,530 SQLAlchemy and many people are using that with Flask. It's good, it's a 38 00:03:49,530 --> 00:03:54,690 good idea to also use it because later you may have to work with other people 39 00:03:54,690 --> 00:04:00,329 when developing the application so it is good that you learn SQLAlchemy. Now SQL 40 00:04:00,329 --> 00:04:07,470 Alchemy and let me stop this and so I'm in the virtual environment here. 41 00:04:07,470 --> 00:04:13,680 SQLAlchemy I was saying that it is based on psycopg2 so that means you 42 00:04:13,680 --> 00:04:22,410 need to install psycopg2 first and be careful I'm 43 00:04:22,410 --> 00:04:28,050 installing this in my virtual environment and this faces a problem with 44 00:04:28,050 --> 00:04:34,130 installation and what I can do here I'll first try to upgrade pip so with 45 00:04:34,130 --> 00:04:46,380 Python and pip install upgrade pip. So if you are on a Linux or 46 00:04:46,380 --> 00:04:50,430 Mac you probably don't have this problem but on Windows you often have 47 00:04:50,430 --> 00:04:56,160 problems. Also when you deploy the application on a server, on a live server 48 00:04:56,160 --> 00:05:00,840 that server, that server is most likely a Linux server so you most likely not have this 49 00:05:00,840 --> 00:05:06,570 kind of problem anyway let me try this again, so pip install psycopg2. The same 50 00:05:06,570 --> 00:05:14,880 problem so I probably mean need a precompiled Python library so that is 51 00:05:14,880 --> 00:05:28,580 available in this page. So I'm looking for psycopg2, here we go. 52 00:05:28,580 --> 00:05:35,190 So I have Python 3.5 so I probably need this, here, sorry if you 53 00:05:35,190 --> 00:05:40,530 are on a Mac and Linux so bear with me here. If you are on Windows you may be 54 00:05:40,530 --> 00:05:50,730 interested on this. So I'll go now to my app folder and I'll just put this 55 00:05:50,730 --> 00:05:56,460 file in here, go to the terminal again and this time I want to point to this 56 00:05:56,460 --> 00:06:01,890 file so I'm in this directory and point to that file and install it with pip. 57 00:06:01,890 --> 00:06:07,730 That was quick so now I am ready to go and install the SQLAlchemy library 58 00:06:07,730 --> 00:06:16,860 again with pip, so pip install, but that will come as a Flask extension so 59 00:06:16,860 --> 00:06:27,090 alchemy, so make sure we are typing it like this. Install and that was 60 00:06:27,090 --> 00:06:34,080 successful as well so now we go to our python script and maybe import SQL 61 00:06:34,080 --> 00:06:45,710 alchemy so from Flask you need to do a trick here. Extensions SQLAlchemy, import 62 00:06:45,710 --> 00:06:55,680 SQLAlchemy. This statement means that SQL Alchemy was installed among the folders 63 00:06:55,680 --> 00:07:04,949 of your Flask library so which should be somewhere in here, and so Flask 64 00:07:04,949 --> 00:07:12,990 ext and then SQLAlchemy so that should be inside this holder so this is a way 65 00:07:12,990 --> 00:07:18,449 to actually call SQL alchemy from within Flask so it's not a must to know but 66 00:07:18,449 --> 00:07:28,139 just for curiosity. Yeah, let me close this and good. Now the idea is that once 67 00:07:28,139 --> 00:07:34,529 we have a database we need to create a model for our database which is a table 68 00:07:34,529 --> 00:07:40,020 with columns and then we need to specify what types of columns we'll also we want 69 00:07:40,020 --> 00:07:45,270 integers or floats or text in these columns and so we will write this model 70 00:07:45,270 --> 00:07:53,219 with Python and specifically we will be writing a class here which means that 71 00:07:53,219 --> 00:07:58,139 our model will be the object so we will create object instances out of this 72 00:07:58,139 --> 00:08:06,089 blueprint so let's call the class data and this class will be a subclass of 73 00:08:06,089 --> 00:08:13,469 another class that is constructed by SQL alchemy so there's the blueprint that is 74 00:08:13,469 --> 00:08:18,509 designed to interact with the PostgreSQL database and we need to make 75 00:08:18,509 --> 00:08:23,819 use of this blueprint which is already written in SQLAlchemy. What you do 76 00:08:23,819 --> 00:08:32,830 is you create a DB variable there you'll store SQLAlchemy and the name of 77 00:08:32,830 --> 00:08:38,950 your app so you are creating an SQL Alchemy object for your Flask 78 00:08:38,950 --> 00:08:43,870 application which is this one here. However at this point you you're still 79 00:08:43,870 --> 00:08:49,029 not specifying the connection to your database so your flask application 80 00:08:49,029 --> 00:08:54,370 still doesn't know what the database to connect with so you want to configure 81 00:08:54,370 --> 00:09:02,080 that using the config method and here you pass a list which will have this 82 00:09:02,080 --> 00:09:13,960 name SQLAlchemy database URI. You're specifying the URI of your 83 00:09:13,960 --> 00:09:20,080 database which is basically the address of your database in the computer where 84 00:09:20,080 --> 00:09:29,589 the app is running so that would be a string like PostgreSQL in the form of a 85 00:09:29,589 --> 00:09:37,210 URL almost and Postgres so you need to pass the user so my username is Postgres 86 00:09:37,210 --> 00:09:43,060 then a column and then the password Postgres123, that's my 87 00:09:43,060 --> 00:09:48,610 password and then the server address which is local host in my case and you 88 00:09:48,610 --> 00:09:52,180 should write the same whether you are on a Mac or Linux here you write the 89 00:09:52,180 --> 00:09:59,160 localhost there and then lastly the label the database so mine was 90 00:10:01,610 --> 00:10:13,140 height collector. So I had to refresh it for that to show. Collector. Yeah, that's 91 00:10:13,140 --> 00:10:17,220 that should do it. I have brackets there now. There shouldn't 92 00:10:17,220 --> 00:10:24,270 be brackets there, just like that. So basically you're setting the value of 93 00:10:24,270 --> 00:10:30,600 this dictionary key to this value so that your app knows where to look for a 94 00:10:30,600 --> 00:10:35,910 database and then you create an SQL alchemy object and then what you want to 95 00:10:35,910 --> 00:10:44,180 do here is from this SQLAlchemy object you want to access the model class so 96 00:10:44,180 --> 00:10:50,480 with this class we are inheriting from the model class of the SQLAlchemy 97 00:10:50,480 --> 00:10:55,470 object and then you need to tell the name of your table that you want to 98 00:10:55,470 --> 00:11:00,150 create so we will create this class blueprint first and then we will call 99 00:11:00,150 --> 00:11:04,710 the class and that operation so when we call the class a table will be created 100 00:11:04,710 --> 00:11:09,780 with columns so now we are just giving instructions here. Table name is the 101 00:11:09,780 --> 00:11:14,100 special name that will be equal to the name that we want to give to the table 102 00:11:14,100 --> 00:11:20,130 so let's say in data and once you create the table then you want to create table 103 00:11:20,130 --> 00:11:25,140 fields so columns. It would be a good idea to have an ID first 104 00:11:25,140 --> 00:11:34,080 so ID and DB dot column method DB dot integer so that would be the data type 105 00:11:34,080 --> 00:11:41,340 of the ID column. And that would be a primary key so you want to set that to 106 00:11:41,340 --> 00:11:49,140 true and then we have two more fields there, so the email field. Again DB 107 00:11:49,140 --> 00:12:00,150 column, DB that would be a string and maybe set a limit there 120 so will not 108 00:12:00,150 --> 00:12:07,190 be accepting values that are more than 120 characters long. 109 00:12:07,920 --> 00:12:12,990 We want to email address to be unique. It doesn't make sense to have duplicate 110 00:12:12,990 --> 00:12:25,260 email addresses, so that's good and height equal to DB dot column so don't 111 00:12:25,260 --> 00:12:33,300 confuse these variables with these ones here, email, height so these are local 112 00:12:33,300 --> 00:12:39,120 variables for this function and these also are local variables for this class. 113 00:12:39,120 --> 00:12:44,550 Or even better to discriminate I could put some underscore for these 114 00:12:44,550 --> 00:12:56,490 variables, yeah, that's it. Good! So column DB. That would be an integer, yeah, that's it. 115 00:12:56,490 --> 00:13:02,430 And once you do that, you want to initialize the variables of your objects 116 00:13:02,430 --> 00:13:07,160 so the instance variables so that would be an init function where we pass self, 117 00:13:07,160 --> 00:13:18,720 email, height and yeah, that's it. Than you say self, the usual self.email. That will 118 00:13:18,720 --> 00:13:23,180 be equal to the email parameter. Same for height. 119 00:13:23,180 --> 00:13:31,470 Self.height equals to the height parameter so this one here, so this is 120 00:13:31,470 --> 00:13:37,050 initializing our instance variables because this method is the first to be 121 00:13:37,050 --> 00:13:43,440 executed when you call the class instance. Now what you have and create an 122 00:13:43,440 --> 00:13:49,709 instance of this class and how can we do that? Well, we need to call the class. But 123 00:13:49,709 --> 00:13:54,449 where do we call the class? Well, we could call the class like in this script, we 124 00:13:54,449 --> 00:13:58,860 could say it's something like Delta, the name of the class and brackets and so on, 125 00:13:58,860 --> 00:14:06,060 but that would execute the flask app so a better idea would be to go to the 126 00:14:06,060 --> 00:14:13,290 command line and trigger a Python session where we import the DB object from this 127 00:14:13,290 --> 00:14:20,040 script. This is where this last lines come in handy so when you import something 128 00:14:20,040 --> 00:14:23,740 from the script is not executed because the name of the 129 00:14:23,740 --> 00:14:32,170 script will not be main but it'll be app when you execute the script. Great, so 130 00:14:32,170 --> 00:14:39,220 Python and then from app import DB and then you point to the DB object 131 00:14:39,220 --> 00:14:47,800 and create all method so what this will do it will create these tables and 132 00:14:47,800 --> 00:14:58,750 fields using this model. Yeah, basically that's it. Let's now go to PG 133 00:14:58,750 --> 00:15:08,620 admin and check if we have a table. Let's refresh the database. Tables is 134 00:15:08,620 --> 00:15:16,450 still 0. There should be a problem there. Let's see. 135 00:15:16,450 --> 00:15:23,200 Well this took me a bit of time for me to figure out where the problem was and 136 00:15:23,200 --> 00:15:27,820 this is one of those scenarios that you really hate. You don't get an error 137 00:15:27,820 --> 00:15:32,230 from your programming language so you have to figure it out manually to 138 00:15:32,230 --> 00:15:36,370 see you where you have some typos or anything like that and if we had a 139 00:15:36,370 --> 00:15:43,779 problem in here we would get an error from Python, but problem was in this line. 140 00:15:43,779 --> 00:15:49,600 If we had a problem in this line we would probably get a connection error to 141 00:15:49,600 --> 00:15:56,790 the database, but the problem, my problem place was that I mistyped this slq 142 00:15:56,790 --> 00:16:04,269 alchemy so it should have been SQL alchemy. I think this is good for you to 143 00:16:04,269 --> 00:16:10,230 know if you do the same thing. Just make sure you check this dictionary key first 144 00:16:10,230 --> 00:16:17,769 so it should be SQLAlchemy database URI. Later I will do a feature request to 145 00:16:17,769 --> 00:16:23,019 the authors of SQLAlchemy to include some error handling when you mistype 146 00:16:23,019 --> 00:16:28,819 something in this part. Okay, SQLAlchemy and save, 147 00:16:28,819 --> 00:16:37,729 and go ahead and try it out again. Here's a tip. When you change 148 00:16:37,729 --> 00:16:42,679 something in your script it's good to exit first your Python console so you're 149 00:16:42,679 --> 00:16:46,759 Python sessions and then enter Python again because it might happen that when 150 00:16:46,759 --> 00:16:53,419 you import the DB object you might get the outdated object there without the 151 00:16:53,419 --> 00:16:58,669 change that you just made so it's good to exit Python and then from app import 152 00:16:58,669 --> 00:17:08,600 DB again and let's execute the create all method, so that should execute these 153 00:17:08,600 --> 00:17:14,569 line of code for us and should create a table with columns but who knows, so 154 00:17:14,569 --> 00:17:20,029 let's see. Height collector this of database it's zero at the moment, for 155 00:17:20,029 --> 00:17:31,179 tables so refresh, yeah, now we have data have three columns ID email and height. 156 00:17:31,179 --> 00:17:39,340 And at the moment they should have zero rows there so we can do as query select 157 00:17:39,340 --> 00:17:48,639 all from data so data is the name of our table and yeah. 158 00:17:48,639 --> 00:17:53,269 We don't have any rows otherwise they would have been listed in 159 00:17:53,269 --> 00:18:08,840 here, great. We have a table now and next is we want to send these two values two 160 00:18:08,840 --> 00:18:15,259 the database so we'll be applying some queries some SQLAlchemy queries, but in 161 00:18:15,259 --> 00:18:17,000 the next lecture, so see you later.