Vertica Example

vertica_example.py

"""
To install the vertica driver would need to install via conda

conda install -c conda-forge vertica-python

Import the vertica python library
"""
import vertica_python
import json


# Get credentials from kubernetes. The credentials were setup as a dictionary
credentials = None
with open('/var/run/secrets/user_credentials/vertica_credentials') as f:
    credentials = json.load(f)
    
# Check and make sure the credentials were pulled correctly
if credentials:
    # Setup connection dictionary based on secrets obtained above
    connection_info = {
        'host': credentials.get('hostname'),
        'port': 5433,
        'user': credentials.get('username'),
        'password': credentials.get('password'),
        'database': 'VMart'
    }

    # Use a with statement that auto closes the connection once out of the loop
    with vertica_python.connect(**connection_info) as connection:
        cursor = connection.cursor()
        
        # Setup example query for the sample dataset that is being used
        example_query = (
            "SELECT DISTINCT s.product_key, p.product_description "
            "FROM store.store_sales_fact s, public.product_dimension p "
            "WHERE s.product_key = p.product_key "
            "AND s.product_version = p.product_version AND s.store_key IN ("
            "  SELECT store_key "
            "  FROM store.store_dimension "
            "  WHERE store_state = 'MA') "
            "ORDER BY s.product_key LIMIT 50;"
        )
        # Execute the query
        cursor.execute(example_query)
        # Iterate through the cursor and print each row
        for row in cursor.iterate():
            print(row)