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


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 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__)
    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:
                employee = Employee(emp_name=request.form.get("emp_name"))
            except Exception as e:
                print("Failed to show employee name ")
        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:
        employees = Employee(emp_name=new_name)
        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()
        return redirect("/")
    if __name__ == "__main__":
  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:
       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;
          border: 3px solid black;
          width: 100%;
          height: 500px;
      <h1><center>Flask Vertica Integration Demo</center></h1>
      <div id="f">
        <div id="flask">
            <div id="a">
                <table id='addnew' align="center">  
                        <form method="POST" action="/">
                        <h3><center>Add New Employee</center></h3>          
            <div id="b">  
                        <td>Enter Employee Name</td>
                        <td><input type="text" name="emp_name"></td>
                        <td><input type="submit" value="Add"></td>
        <div id="c">
             <table id='showdetails' align="center">
              <h3><center>Employees Data</center></h3>
             <tr align="left">       
               <th>Old Name</th><th>New Name</th><th>   </th>          
             <tr ><td>{% for employee in employees %}</td></tr>
                 <form method="POST" action="./update" style="display: inline">
                        <input type="hidden" value="{{employee.emp_name}}" name="oldname">
                        <input type="text" value="{{employee.emp_name}}" name="newname">
                  <td align="center">
                        <input type="submit" value="Update">
                     <form method="POST" action="./delete" style="display: inline">
                     <input type="hidden" value="{{employee.emp_name}}" name="delname">
                     <input type="submit" value="Delete">
             {% endfor %}   
  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:
  7. Execute the following command to run the flask application. The application logs will be logged into flask.log file.
    $python3.6 > 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