Create Python Flask Web-page for Data operations- Part 1

The Objective of this article is to understand the implementation of a basic HTML web-page to perform DML operations on Database records. In this article we will design an HTML webpage, Python API and a Backend using python for DB connectivity.

Application Frontend

Frontend is using HTML and some JavaScript part has been used to add dynamic feel to the web-page. Role of HTML form is to provide end-user a functionality to add employee details which can be processed by python backend.

<!--etl_insert.html -->
<h1>Enter New Employee Details</h1>
<br> 


<script>
//javascript method to populate dropdown values into textfield defined in
//wtf_flask form field name deptno 
 function addDept() { 
 var mylist = document.getElementById(“myList”); 
 document.getElementById(“deptno”).value = mylist.options[mylist.selectedIndex].text;
} 
</script>
 
<form action = “/insert” method = “POST” enctype = “multipart/form-data”>
 {{ form.empno.label }} {{ form.empno }} 
 <br> <br>
 {{ form.ename.label }} {{ form.ename }}
 <br> <br>
 {{ form.sal.label }} {{ form.sal }}
 <br> <br>
 {{ form.deptno.label }}

 <!--below section defines the dropdown menu in html-->
 <label for=”myList”></label>
 <select name=”dept” id=”myList” onchange = “addDept()”>
 <option value=”{{list_dept}}” selected>DeptNo</option>
 
<!--list_dept is a python list-->
<!--in below piece of code we are iterating over list using
    flask jinja template syntax to populate the dropdown-->

 {% for d in list_dept %} 
 <option value=”{{d}}”>{{d}}</option>
 {% endfor %}
 </select>

 <!--dropdown logic ends-->

 <p>Selected Department ID value is 
 {{ form.deptno }}
 </p>
 <br> <br>
 {{ form.enter}}
</form>

Output:

Form is defined using Flask wtforms library and that form is being passed to HTML using render_template() method of flask (defined in API class), where we are passing a form components or fields such as EmployeeID, EmployeeName , Salary, DepartmentID TextFields and a python list which populates the dropdown in HTML. This whole process is explained in below API section.

Dropdown functionality is designed in dynamic way using Javascript. When we select a DeptNo value from dropdown it dynamically populates the {{ form.deptno }} in HTML form field.

As of now I haven’t touched the CSS, so the look of this HTML form is raw and I had used Python Flask wtforms framework to design the form elements.

Python API

Python API is basically a Flask Application that contains the method calls to backend systems. For this article we are going to insert records into DB.

#etl_api_call.py

from flask import Flask, jsonify, request, render_template, redirect,url_for 
from flask_wtf import FlaskForm
from wtforms import FileField, SubmitField, StringField,TextField, FieldList, BooleanField, IntegerField
from etl import etl_data_obj
import random

app=Flask(__name__)
app.config[‘SECRET_KEY’]='vivek2677'

#form definition class, all these values 
class insertEmpDetails(FlaskForm):
   empno=TextField(“Enter EmployeeID”)
   ename=TextField(“Enter Employee Name”)
   sal=TextField(“Enter Salary”)
   deptno=TextField(“Enter DeptNo”)
   enter=SubmitField(“Enter”)
list_dept=[10,20,30,40]

@app.route(‘/index’)
def etl_process():
   return ‘APIfy the ETL method’

@app.route(‘/etl’,methods=[‘GET’])
def get_data():
   if request.method == ‘GET’:
   d=etl_data_obj.get_details(random.choice(list_dept))
   print(‘value returned’,d)
   return jsonify(d)

@app.route(‘/insert’, methods=[‘GET’,’POST’]) 
def post_data():
   empd={} 
   form=insertEmpDetails()
   if request.method == ‘GET’:
       return render_template(‘etl_insert.html’,form=form,list_dept=list_dept)
   if request.method == ‘POST’:
       empno=form.empno.data
       ename=form.ename.data
       sal=form.sal.data
       deptno=form.deptno.data
       empd[‘empno’]=empno
       empd[‘ename’]=ename
       empd[‘sal’]=sal
       empd[‘deptno’]=deptno
       print(‘emp dict’,empd)
       print(etl_data_obj.post_details(empd))
       return redirect(url_for(‘post_data’))

if __name__ == “__main__”:
 app.run(host=”0.0.0.0", port=5000, debug = True)

Code Explanation:

class definition:- class insertEmpDetails contains various form elements such as TextFields and Button as below:

   empno=TextField(“Enter EmployeeID”)
   ename=TextField(“Enter Employee Name”)
   sal=TextField(“Enter Salary”)
   deptno=TextField(“Enter DeptNo”)
   enter=SubmitField(“Enter”)

post_data(), method == ‘GET’ this method is used to invoke the HTML to share the form objects defined in class insertEmpDetails and a python list. html page is rendered/called using flask render_template() method where we are passing a form object form=insertEmpDetails() and a list list_dept that populates HTML dropdown.

post_data(), method == ‘POST’, this method posts or returns the form values back to API and that eventually is processed by the python backend. values returned by form are accessed via form.empno.data and assigned to a variable. all the column values are structured into a dictionary and backend method etl_data_obj.post_details() is called for DB related operations.

Application Backend

Backend is purely a python class that contains various methods to perform DB related operations.

#etl_api_call.py

import pandas as pd
from sqlalchemy import create_engine
engine = create_engine(‘oracle://scott:scott@oracle’, echo=False)

class etl_data:
 def __init__(self,dept) -> None:
     self.dept=dept
     print(‘dept value’,self.dept)

 def get_details(self,dept):
     print(‘dataframe value’,dept)
     emp_df=pd.read_sql_query(‘select empno,ename,sal,deptno from emp_api’,engine)
     emp_df.head(10)
     emp_df=emp_df[emp_df[‘deptno’]==dept]
     print(emp_df.head(5))
     emp_dict=emp_df.to_dict(orient=’index’)
     return emp_dict 

 def post_details(self,empd):
     df_ins=pd.DataFrame([empd],columns=empd.keys())
     print(df_ins.head(5))
     df_ins[‘empno’]=pd.to_numeric(df_ins[‘empno’])
     df_ins[‘sal’]=pd.to_numeric(df_ins[‘sal’])
     df_ins[‘deptno’]=pd.to_numeric(df_ins[‘deptno’])
     #insert df into table
     df_ins.to_sql(‘emp_api’,con=engine,if_exists=’append’,index=False)

#creating object of class
etl_data_obj=etl_data(‘00’)

post_details() method is called from Python API as etl_data_obj.post_details(empd) to insert the values that are grabbed from HTML form.

Execution Flow is as below:-

1. Run the Flask application using: python etl_api_call.py

This output confirms that application is now up and running and can be checked via URL 127.0.0.1:5000/insert

2. Enter the New Employee Details:

When we submit the values using ENTER button at the bottom of page, then execution flow will go to python API and calls POST method.

@app.route(‘/insert’, methods=[‘GET’,’POST’]) POST method

if request.method == ‘POST’:
       empno=form.empno.data
       ename=form.ename.data
       sal=form.sal.data
       deptno=form.deptno.data
       empd[‘empno’]=empno
       empd[‘ename’]=ename
       empd[‘sal’]=sal
       empd[‘deptno’]=deptno
       print(‘emp dict’,empd)
       print(etl_data_obj.post_details(empd))
       return redirect(url_for(‘post_data’))

which eventually calls the Backend method etl_data_obj.post_details(empd) to perform table insert operation.

Once, values are submitted via enter button, these field values are inserted into DB table emp_api. Issue a select query to validate the records in table:

Data is now available in DB table emp_api.

Summary:

· Flask wtforms to define form components.

· Render html pages from flask APIs.

· Python Flask application to initiate backend calls.

· Python Backend application for DB connectivity.