Lecture 54

So, continuing forward, let us now create the user query. So, with the user interface what the user is saying that he or she needs to find the information let's say about Afghanistan for the sector let's say agriculture and he or she is trying to capture the details regarding CO2 so these three are the conditions that needs to filter the database for the particular information that are available in the MariaDB database. So, with this background we will try to create a query so let us store our query in a new variable with the name user query and this should be a string and it will the query will be select star from the name of the table that is historical data and we will utilize the where clause where country equals the country name variable here we are trying to utilize this country name variable that has been provided to the user interface form and we have multiple conditions so we need to use the end statement and the next thing we need to choose from the databases sector so this is source name and guess now we need to forward this query to the function that we created earlier to connect with our database so we can write DB underscore query this is the function that we created and we have also imported this function in this script as well and we can and also utilize this function call directly to save some time so the username password hostname port and the name of the database remains the same because we are still working on the same database but the query is now different so the query is user query and this user query is the string that we have defined here now what this function call will do is it will try to search this function in the current script and this function is defined here so using this user query it will create a connection and will try to execute this query and whatever the rows that follow or that are available in the database based on the conditions available in this user query it will try to fetch all those rows and try to print rows in the terminal so let us try to check whether this much of our application is working or not so we can just go to our application and change this to something else try to commit so now you can see that the terminal is saying that the connection is successful why because this function of connecting it is we have already told here that print connection successful if we are successfully connecting to the server the next thing is the query so we have selected CAN as a country energy as a sector and all GHG emissions and these are all the data from 1990 to 2021 that has been provided in this query so there is this one row that is available in the data set okay so now this is something this is the retrieval aspect of our application to move forward we can also perform different kinds of complex modeling on the energy system and instead of doing the complex modeling we are trying to show here a very basic example of how we can manipulate the data in the data set by first fetching it and then trying to find out various statistics based on the data available in the query so let us now edit our DB query function in this script and try to see how we can calculate very basic statistics like minimum value of emission maximum value of emission and mean value of emission so to do that we will again try to edit our query function and instead of printing these rows in the terminal we will try to utilize whatever we are retrieving from the database and try to manipulate it so we can fetch in this function until now what we have done is we have created a cursor based on the successful connection then we have executed the user query and using that cursor and in the rows variable we have all the fetched value right now let us try to fetch the column names so to do that we can store all the column names in the variable column name and we can write a for statement for I in description in cursor.description with each iteration what we need to do is we will capture the 0th position of I so this will hold all the column names and we can we have already fetched all the rows in the rows variable rows is equal to cursor.fetchall next is we can manipulate it in different ways currently we will try to use the pandas package which we have installed so we have imported it using the pandas as pd so whatever function we require from the pandas package we will use the pd alias right so let us convert the fetched value so this is pd dataframe this is a function available inside the pandas package and we will utilize all the rows that we captured by using this statement .fetchall and the name of the columns for this dataframe will be the column name that we have received by running the statement using the for loop okay and store this dataframe into a different variable name as here now we have a dataframe and we can either print the values or we can store them into different variables so print statistics so maximum value of the emission for the required information that has been asked by the user we have a dataframe of all those rows right in this df variable so we need to assess this df variable and try to find out what is the maximum value out of all the emissions value provided since 1990 until 2021 so to do that we can write df.ilog and now what this statement will do on the data frame is it will try to fetch all the columns after the first three columns why because if you remember in our output the first three columns have strings that provides the that provides the information about whose emissions data we have retrieved from the database so all the information after these three columns are useful for our numerical analysis right so this statement will try to get all the numerical values into a new variable and out of all those values we need to find the maximum value so we can simply write np.max so here we are utilizing the numpy package with the alias np similarly we can write minval or the minimum value copy the same thing and just change the function from the numpy package also we can calculate the minval now these values we can return to the basic function call of this db query this will be utilized and will be helpful to showcase this on the actual user interface of the system so we can write here minval and the actual data frame without the columns so now this db query function will not only fetch the data from the database it will also try to calculate these three variables and will return wherever this function has been called it will return these four values so we need to capture these four values as well whenever we are trying to call this function so we are calling this function in the home function and here is our call so we can again write the same variable for the function and since we are only capturing the numerical values from the data frame that we have fetched from the database we can write the final data set as erd data this will let us know that the fourth value contains only the numerical data now this is this also we can store in a new dictionary stats underscore dict this will have a min storing the minval max value so now what we have accomplished is we fetch the data based on the user input and then also we calculated few basic statistics based on the data that we have fetched right now we need to transfer these variables to the user interface so that we can show the user whatever that has been fetched or that has been calculated are available to the user for his or her quick verification so the first thing we want to show is what are the user selections so we can create new variable user selection this is a new argument and we can store the user selection dictionary data so now this render template function will send or the complete dictionary that is available in the user selection deck and this dictionary will store the country name that was selected by the user the source name and the guest name right and the keys is country source and guest so similarly we have some statistics that we have and this is under statistic now now what we are telling this home function of the flask packages if the request method is post then do all of these things and render the template in this way right but if we have not mentioned what to do if the request is not post method so to do that we can write else return so there are multiple return statements because we are we are classifying the workings of this function based on the if else of the request that has been provided by the user so this return function will render the template of name index dot HTML and it will have a basic user selection toggle so I will explain what this variable will do now since this user selection toggle should also be there in this function in this return statement with a different value so these arguments will transfer the data that has been available in these variables to the user interface now let us switch back to the user interface so we have created the two columns in our user interface if you see here this is to the mission forces and user selection so this is the input area for the user and this is the output that has been provided by the user and we can show whatever statistics that we have calculated in this column so to do that we need to modify this column and this column is available in under this column 2 right and this this template is following the Jinja templating engine so we can utilize the if else statement using the Jinja template syntax so all of this has been covered in the previous course and you can refer to those lectures in that course to understand how these Jinja templating index work so now first of all we need to check whether the user has selected something or not so to do that programmatically we need a variable that gets changed whenever a user select something right that is why we have created this variable user selection toggle if this user selection toggle is zero that means the web application has been loaded with the get method only then it will only provide this index or test HTML with user selection toggle as 0 otherwise the value of this will be 1 and if this is 1 then we then it indicates that we have calculated we have passed the data from the database and calculated the statistic so let us now create this if statement based on this templating system we can check if user selection toggle if this is 1 then what we need to do we need to create a paragraph and first of all show the user what he or she has selected so we can write user selection so whatever the user has selected we have stored it in the user selection dictionary and we are fetching the data from that only this is the key of the dictionary similarly we can show other two things as well this is the force of emissions and this is also the key name is also force right next is the gas so the name of the gas the next thing we can show to the user is the emission statistics so there are three different things that we have calculated that is the minimum emission and it is available in stats deck that we are transferring to the user interface and the key for this is min we can copy this the next is maximum the third one is the average okay so now this is the if statement which will run when the user selection toggle will have one value but what if the user selection toggle is still 0 then we need to tell the user interface that if there are no user selections till now then we can write else and provide a warning to the user that select the data fields and we need to close this if statement now let us check what has changed in our user interface so we have not used the codes here that is why it is not identifying that index.html is a template available now there is nothing selected by the user and these are all the default values that has been shown so let us just select randomly so this is the stats deck variable and we have mistyped here these are stats deck so user has not selected anything and let us randomly choose two inputs and now you can see that the user interface showing that user has selected country as ARG source as bunker fuels and gas as CH4 and the emission statistics for this is minimum is this much maximum and average you can change this also let's say agriculture and now the data is changed so this user interface is trying to fetch the data from the MariaDB database and then showing you all these statistics further we can also create a chart based on all the information and try to see the trend of all the data that has been provided in the data set so let us now further modify our script and try to create a chart based on the data set that we are utilizing so if you remember we have included this package named as matplotlib.pyplot so this is a visualization library and we will try to create a line chart so to do that we need to first modify we need to access all the data that we are that we are fetching from the database in the yearly data variable so we define this variable here yearly data but we have not used it so now let us try to utilize this data so instead of creating the chart in this home function itself we can create a different function for visualization so let us create a new function with the name def build underscore chart and it will take an argument as the yearly underscore data the top string function will create a chart based on the data fetched from build database and first let us initialize a figure with x's using the plt that is the pyplot.subplot and let us create the plot on this axis ax.plot with plot function you can create a line chart and what we are trying to plot the x-axis will have the data from 1990 to 2021 so we can write list all the values between the range 1990 to 2021 and what we need to plot on these x-axis the yearly data that we have fetched from the database now let us provide the title of the x.title as annual emissions and x.set label set x label as year and x.set y label now we need to save this figure so we are storing this figure in this fig variable and we can utilize the matplotlib function to save this figure in our local directory and where this will be saved is so currently our app.py is in this directory and to show any kind of static data on the web application we need to create a new folder with the name and whenever we fetch the data the this image will get dynamically created on the local file system inside this static directory so let us now provide the path for this storage of this dynamically generated figure and we can write the name of the figure as annual emissions.png and there are some more arguments that can be provided like vbox.foldchip equals true and dpi equals created and we should return this figure so this function will take yearly data as input and try to create this figure okay now we can call this function here only where we are getting this yearly data here so we can develop this figure you can call this build chart function that we have created and provide the yearly data as an argument to this function and this will return a figure variable which we can capture in the fig variable inside this home function okay now again we can provide one more variable that is chart underscore generator and equals one so whenever a chart gets generated then only this variable will be useful so we can create one more section in the UI of the application we can write class of this division is container flow this is from the bootstrap CSS then again we can create div.class should be row and again div.class should be col-mg-12 so we are utilizing the whole horizontal space here now and the next division is div.class now we have generated our image and stored dynamically in this static folder there is nothing right now but whenever the user will select something the image will be generated here so to show that image into this section we need to first center this image and utilize the image tag of the HTML so we can write image the source is so we are again using the Jinja templating syntax and we will be utilizing the URL of the local file system you are in for the file name which is the annual submission.png the alternative string could be chart and height should be auto but width should be 70% now when this chart should be displayed on the user interface only when when the template is getting the chart gen as 1 otherwise we should not show this chart that is so to do that we can again check the chart and then only show this now let us try to run our application again and let us choose Afghanistan agriculture so we should write 2022 here because the last value which we have is 2021 so due to Python indexing system the last value is excluded that is why we are getting error so now let us try to run this again okay there is one more error here while setting the title it should be set under for title for the file now you can see that when user is selecting these three variables as the country is Afghanistan energy sector and all GHG it is showing the emission statistics and the curve since 1990 until 2021 right now you can check whether it is changing for different inputs or not so let's say India and let's say agriculture all GHG emissions so now you can see that the curve has changed dynamically and these images are dynamically created in under the static folder right so with this I would like to stop here and the complete code will be available as a GitHub repository for the development of the user interface and we can further customize this user interface to create any kind and different kinds of complex energy systems modeling and we can utilize these kinds of user interface so that we can create a complete end-to-end carbon accounting system thank you