Lecture 52

Hello everyone, welcome to the course on Carbon Accounting and Sustainable Designs in Product Life Cycle Management. I am Dr. Prabhupada Singh and we are co-teaching this course with Professor Deepu Philip and Dr. Amandeep Singh. Today we will talk about Carbon Accounting User Interfaces, but before that let us first revise what are the topics that we have majorly covered. So, initially we covered carbon accounting databases, a brief introduction of what are the different types of carbon accounting databases that exist in India and around the world. Then we talked about what are the database management systems, what are their advantages and how they are being used for different kinds of purposes. After that we talked about the database design, the six different processes of designing a database. Then we also talked about the schema, how to develop the database schema while following the design process of the databases, so we talked about database schema. Then we talked about database normalization. Now today we will try to develop user interfaces by incorporating the databases that we have developed. So, the outline for the today majorly covers the first we will talk about the connection between MariaDB and Python. Now we have talked about MariaDB, we have seen in our previous lectures how to create new databases, show what is available in a currently available databases in MariaDB on Windows machine. After that we will also try to talk about what is Python, what is this programming language, how we can utilize this Python to create different kinds of scripts so that we can connect it with our MariaDB databases and then transfer data to and fro between the user and the database. After that we will discuss about a database that is historical emissions dataset. So, we will utilize this dataset to develop our user interface and try to calculate various statistics based on the data. Then we will start developing our user interface by first installing Python, a code editor known as Visual Studio Code and we will try to install various Python packages that we will utilize while developing our user interface. Then we will try to develop a script to connect Python and MariaDB. After that we will start developing our complete user interface for Carbon accounting system. So, let us start by introducing MariaDB and Python. So, we all know about MariaDB based on our previous lectures. Now, Python is a general purpose programming language. So, you can build different kinds of scripts of programs using this Python. So, its use cases could be web development. Most famous use case is in the applications of data science. Then you can also use it for automation. You can create different kinds of scripts that can be utilized to conduct various analysis behind the user that is automatically and various other tasks like game development etc. So, this programming language was first developed in 1991. It was developed by Van Rossum and it executes code line by line. So, it is not a compiled language. It is an interpreted language. It is also a high level language and we have also covered this basic introduction to Python in our previous courses with the name advanced business decision support system. So, you can utilize those resources to get to know more details about the Python programming language. We will not cover these details in this course and we will try to directly develop our Carbon accounting system today. Also, Python allows seamless integration with various kinds of database management systems. So, we have learned about different kinds of DBMS that are available and since today we are using MariaDB, we have a dedicated package named as MariaDB that is available in the Python packaging index PyPI. And anybody can install by using pip install MariaDB as a command to install this connector package that can provide different kinds of functions to connect to a MariaDB database server using Python scripts. So, with these types of connections using different kinds of DBMS system, this Python scripts can provide dynamic content for web application. And we will try to develop our web application today by developing our own user interface for the Carbon accounting system. So, now let us talk about the historical emissions data that we are trying to utilize today for developing our system. So, this data set is sourced from a climate watch project and it provides historical emissions data from 194 countries. Also, this data reports emissions between 1990 and 2021. It contains various sectors like contains data about various could be energy sector, agriculture sector or waste building and there are many more. Also, it provides data on all major GHG gases that is greenhouse gases. Like methane, N2O, CO2, carbon dioxide and other gases. Also, whatever the numerical measures that this data set is reporting, if the gas is a non-carbon dioxide gas, then non-CO2 gases are reported with CO2 equivalent emissions using 100 year global warming potential or GWP. So, we have discussed all these terms in the previous weeks of this course and whatever the data that is available between 1990 and 2021 for different kinds of gases, they are based on the CO2 equivalent emissions. We will see this data set and we have already pre-filled the data set in our MariaDB data for easy process of creating the user interface. So, now to develop a UI, we first need to install Python on our systems. Till now, we have installed MariaDB on our machines. So, it is a prerequisite or requirement for doing every task that we are doing today and today we will install Python. Then, we will install VS Code and then first we will try to connect the database, any database that are available in our MariaDB server with Python script. Then, we will try to get the demo of the complete historical emissions data set. So, this is available in our MariaDB server. We retrieved the data from internet sources and then we developed a MariaDB database and we will show you how this data is available in a table in the database. And for our actual UI development, we require the following packages today. So, we will also install these packages once our Python gets installed on our machines. So, the first package we require is flask. Flask is nothing but a lightweight web applications framework. So, we will heavily utilize this to create our web pages. Then, we will use MariaDB connector package named as MariaDB itself. And this package allows access of MariaDB data set using Python language. Then, we will also utilize a famous library Pandas. It is an analysis and manipulation library. At last, we will utilize comprehensive visualization library known as matplotlib. It is a comprehensive library for creating visualizations. So, now let us switch to our machines and try to first install Python then VS code. So, to install Python, just write download Python and download the latest version which is a stable release not the pre-releases like 3.13. So, the current stable release is 3.12.6. So, once it gets downloaded, just open it and it will start its installer. So, once the installer starts, try to click this checkbox add python.exe to path. So, this variable we have also manually utilized for our MariaDB installation. If you switch back to our previous discussion, we added the path information of MariaDB manually. And this installer allows you to add python.exe to path in this installer. So, just click on install now. Now, our Python is installed and we can check it by opening a terminal window and try to write python version. And you can see that it is showing that Python 3.12.6 is installed. Now, the next thing we need to install is Visual Studio Code. You can write VS code download. So, now we will accept the agreement. Click on next. This is the default directory where it will install. And you can create a desktop icon. Click on these checkboxes. Also, make sure that you click on the add to path checkbox as well. So, our code editor is also installed. And now let me give you a quick overview of the user interface of Visual Studio Code. So, these are all the file menus. Different kinds of menus like file, edit, selection, view. And this is our terminal that is open. We are currently in this directory and we will change this directory right now. Also, these are the different kinds of options available for you like explorer will show you what are the different kinds of files that has been opened. So, now we are in this directory where we will code today's user interface. Now, first of all, we need to create an environment for our packages. So, to do that, let us again check whether we have Python available in this terminal or not. So, you can write Python version. And Python is working fine. So, let us now create our environment for today's session. Python-m vn. This is virtual environment. And create a name for your virtual environment. So, let's say today's session is UIEnv. So, the name of our environment will be UIEnv. It will take some time to create this environment. And now you can see in the code explorer window, you have a folder named as UIEnv. And it has different kinds of folders and files. So, to activate, this environment has been created but it is still not active. So, now once your development environment gets activated, it will be shown as this. So, the name of the environment has been highlighted in different color. And now your environment is active. Now, we need to install different kinds of packages which we talked about. So, first start installing the MariaDB package. pip install mariadb Also, we need to install flask. So, let us now create a new file named as connectors. Now, we can hide our terminal for some time. And we will start coding our connector script that will connect the MariaDB database and fetch some data from the pre-existing datasets. So, let us start by importing the MariaDB package that we just installed. And we will try to create a function in the Python programming language by using the def keyword. So, let us create the name of the function as db underscore query. Because this function can provide different kinds of queries. And the DBMS system will send back the datasets. So, let us also create a docstring for this function. This can connect and manage the connections with MariaDB server. So, whenever we try to connect with our MariaDB server using our terminal, we tend to provide some argument or some information to connect with the correct database. So, these information was like the name of the database. So, db underscore username or db underscore password. The hostname of the server of the database. And the port of the database. So, this port will be useful today which we defined while installing the MariaDB server. The name of the database that we are trying to connect. And the user query that the user wants to run while after creating this connection. So, username will be a string. So, we can mark it as a string. This is also a string, the password. The hostname will be a string as well. The port will be an integer. So, it will be int. The name of the database is also a string. And finally, the user query is again a string. Now, let us try to first open our database, MariaDB database, by running a Windows terminal. We can write here mysql-u. The user is root and hyphen p. So, the password was root123. And you should change these passwords. Now, show. So, we have different kinds of databases available. And we created a dbnormal database in our previous lectures. And this cw underscore emissions database is the dataset which we will try to utilize today. And we have already prefilled this dataset. So, let us peek into this dataset. So, use cw underscore emissions. Now, let us first try to see how many tables are available in this dataset. Show tables. So, there is only one table named as historical data. So, we will try to see the schema of this database table. So, we can write describe historical underscore data. So, there are different kinds of columns available like country which is a string. And sector is also a string. Gas type is also a string. And then these are double precision datasets with different numbers of years from 1990 to 2021. So, this is usually a unnormalized form of the dataset. And this is how we received the dataset from the internet sources. So, we will try to utilize this dataset. Now, let us try to find out how many rows are available in this database that we have created. So, we can write this query select count star from which the table name that is historical data. Historical underscore data. So, we mistyped the name of the database table. So, there are 10,928 rows in this dataset. And we can also try to see the first few rows. So, we can write select star from historical underscore data. Fetch first 5 rows only. So, this is how your dataset looks like. The first column is country. So, this is AFG or Afghanistan. Then we have a sector. So, this is another value. Total excluding LUCF. And for the next row, it is energy or industrial processes or agriculture. And there are different kinds of gases like all GHG gases or CO2. So, if you try to retrieve more rows, we will try to see different kinds of dataset available in each column. Now, we need to connect this dataset with our Python programming script. So, now let us get back to this window again. And to connect this Python script with the mariadb package, we need to first write a try except statement. So, what we need to try? We need to try the sub function available in the mariadb package. mariadb.connect So, this function will try to connect it with the user's database available. So, user is db underscore username. And the next argument is password. It is db underscore post is equal to db underscore post name. Port is equal to db underscore port. And finally, the database which is db underscore name. So, now this code will try to connect with the provided user argument for the database. We can store the output of this new variable name as conn connection. And we can try to print here that the connection is successful. You can also try to log this. We can also use a print statement. So, if this part of the code works, then this print statement will tell you that your connection with the mariadb server is successful. After that, we will try to execute the query. So, we can provide comments by using the pound symbol. So, we can write execute the user query. So, query is saved in the user query argument here. So, we can utilize it directly and we can use. Before executing the user query, we need to first create a cursor on the connection. So, we can write connection dot cursor and then we will execute the query by using cursor dot query user query. So, whatever query the user will provide, this connection will execute it on the server. Now, what if this connection does not happen? That is either mariadb server is not available or the python is not able to connect it to the currently running server. So, we need to write our accept statement as well. So, this should contain accept mariadb dot error as e and we can write print the error. And whatever happens, whether it can connect it or not, you must run this command after to close this connection. So, we can write if there is a connection that is this cursor variable was created by the script. So, if this is available, then we should close this connection. Otherwise else or we can write another if statement. That is a successful connection was there, but we could not developed our cursor query. So, we should close our connection. So, this is a function that will try to connect with the mariadb server and will fetch your data from the server. So, let us try to call this function with our arguments like the username and password and try to see whether it will print something or not. So, the name of the function was db underscore query. So, we can call this function and let us just copy these arguments. So, the username of our server is root. Then the password is root123. Hostname of the server is localhost and port was 3306. So, if you try to remember the previous discussion while installing the mariadb server, this is the default port that was created while installing it. Now, the name of the database. So, it is cw underscore emissions and here we can provide a query. So, let us just write select count star from historical. So, let us open our terminal again and we can run this script by using python connector dot py ksop forward a comma here. So, we can run this script again. So, connection is successful but it is still not printing anything. That is the user query ran successfully but there is nothing printing on the screen. So, what we can do is once the query gets executed successfully, we can write here that let us say the cursor has a sub function known as fetch all. And we can utilize it to get all the information that has been queried from the server into a new variable named as rows. And this row has multiple rows of whatever that we have retrieved. So, we can write for row in rows print. So, now let us try to run this script again and you can see that we have mentioned in our query that we need to count all the total number of rows that are available in this historical data table. That is present in the CW emissions data set. So, there are 10,928 rows that are available and we can confirm this since we have already executed this command in our directly into the MariaDB server and we have found the same number of rows. So, if the user wants to change this query, so it can change the query here while calling this function and the output will be different. So, let us now stop here for a quick pause and we will try to start developing our user interface in our in the next upcoming lecture. And till now we have covered that how to create and connect the connection with our MariaDB server using Python programming language. Thank you.