Share this article:
✉ Send Feedback for this Article.

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

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

  2. 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>')
    
  3. Update the last line in the code with the IP address where Flask is installed.
  4. 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>
    
  5. 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
  6. Install the flask dependencies by executing the following command:
    $./requirements.txt
  7. 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 &
  8. After the command is executed successfully, the message * Running on http://<FlaskServer_IP_Address>:5000” appears as follows:

  9. Open the link http://<FlaskServer_IP_Address>:5000/. The flask framework displays the home.html web page with data from the Vertica server.

For More Information

Share this article: