Vertica and Flask: Technical Exploration

About this Document

The goal of this document is to integrate Vertica with Flask. We used the vertica-python driver to connect Vertica and Flask. This integration enables you to populate data, update and delete data from the Vertica database.

Apache Flask Overview

Flask is a micro web framework developed in python which is used for developing web applications easily. The Flask framework can be installed on any operating system which has python 3 installed on it.

Test Environment

For this exploration, we used the following environment and versions:

Operating System: RHEL 8.4
Python Version: 3.6.8
pip latest version
Flask Version: 2.0.2

Prerequisite

Before you connect Flask to Vertica, you need to install the vertica-python driver. To do this, run the following command in the RHEL command line:

pip3.6 install vertica-python

Connecting Flask to Vertica

Create the flask_sqlalchemy_vertica_python directory and add the files as shown in the following image:

In the EmployeeData.py file, add the following code. Update SQLALCHEMY_DATABASE_URI with the Vertica database username, password, server IP and database name.

import os
from flask import Flask
from flask import render_template
from flask import request
from flask import redirect
from flask_sqlalchemy import SQLAlchemy


app = Flask(__name__)
app.config.update(
 SECRET_KEY='',
 SQLALCHEMY_DATABASE_URI='vertica+vertica_python://dbadmin:<password>@<Vertica_IP_Address>:5433/VMart',
 SQLALCHEMY_TRACK_MODIFICATIONS= False
 )
db = SQLAlchemy(app)
class Employee(db.Model):
    emp_name = db.Column(db.String(80), unique=True, nullable=False, primary_key=True)
    def __repr__(self):
        return "<Employeename: {}>".format(self.emp_name)
@app.route("/", methods=["GET", "POST"])
def home():
    employees = None
    if request.form:
        try:
            employee = Employee(emp_name=request.form.get("emp_name"))
            db.session.add(employee)
            db.session.commit()
        except Exception as e:
            print("Failed to show employee name ")
            print(e)
    employees = Employee.query.all()
    return render_template("home.html", employees=employees)
@app.route("/update", methods=["POST"])
def update():
    new_name = request.form.get("newname")
    #old_name = request.form.get("oldname")
    employees = Employee.query.filter_by(emp_name=request.form.get("oldname")).first()

    if employees:
        db.session.delete(employees)
        db.session.commit()
    employees = Employee(emp_name=new_name)
    db.session.add(employees)
    db.session.commit()
    return redirect("/")
@app.route("/delete", methods=["POST"])
def delete():
    emp_name = request.form.get("delname")
    employees = Employee.query.filter_by(emp_name=emp_name).first()
    db.session.delete(employees)
    db.session.commit()
    return redirect("/")

if __name__ == "__main__":
    db.create_all()
    app.run(host='<FlaskServer_IP_Address>')

Update the last line in the code with the IP address where Flask is installed.

In the home.html file include the following script:

<html>
<style>
  table#addnew{
   table-layout: fixed;
   width: 50%;
   font-family: Arial, Helvetica, sans-serif;
  }
   
  table#showdetails,td, th, td {
    table-layout: fixed;
    width: 50%;
    font-family: Arial, Helvetica, sans-serif;
  }
   
  h1,h3 {
    color: blue;
    font-family: Arial, Helvetica, sans-serif;
  }
   
    div#f{
      border: 3px solid black;
      width: 100%;
      height: 500px;
  }
</style>
  <body>
  <h1><center>Flask Vertica Integration Demo</center></h1>
  <div id="f">
    <div id="flask">
         
        <div id="a">
            <table id='addnew' align="center">  
            <div>
                <tr>
                    <form method="POST" action="/">
                    <h3><center>Add New Employee</center></h3>          
                </tr>
            </div>
        <div id="b">  
             <tr>
                    <td>Enter Employee Name</td>
                    <td><input type="text" name="emp_name"></td>
                    <td><input type="submit" value="Add"></td>
              </tr>
              </table>
             </form>
        </div>  
    </div>
     
    <div id="c">
         <table id='showdetails' align="center">
         <tr></tr>
         <tr>
          <h3><center>Employees Data</center></h3>
         </tr>        
         <tr align="left">       
           <th>Old Name</th><th>New Name</th><th>   </th>          
         </tr>
         <tr ><td>{% for employee in employees %}</td></tr>
         <tr>
            <td>
            {{employee.emp_name}}
            </td>
             <form method="POST" action="./update" style="display: inline">
             <td>                
                    <input type="hidden" value="{{employee.emp_name}}" name="oldname">
                    <input type="text" value="{{employee.emp_name}}" name="newname">
              </td>          
              <td align="center">
                    <input type="submit" value="Update">
              </td>
              </form>
              <td>
                 <form method="POST" action="./delete" style="display: inline">
                 <input type="hidden" value="{{employee.emp_name}}" name="delname">
                 <input type="submit" value="Delete">
                 </form>
             </td>
         {% endfor %}   
        </tr>    
       </table>
    </div>
 </div>
 </div>
  </body>
</html>

In the requirements.txt file include the following commands:

pip3.6 install flask

pip3.6 install flask-sqlalchemy

pip3.6 install sqlalchemy-vertica

pip3.6 install sqlalchemy-vertica-python

Install the flask dependencies by executing the following command:
```
$./requirements.txt
```
Execute the following command to run the flask application. The application logs will be logged into flask.log file.
```
$python3.6 EmployeeData.py > flask.log &
```
After the command is executed successfully, the message * Running on http://<FlaskServer_IP_Address>:5000” appears as follows:
Open the link http://<FlaskServer_IP_Address>:5000/. The flask framework displays the home.html web page with data from the Vertica server.