Vertica Python Client

Warnings

• breaking Starting with version 1.3.0, vertica-python dropped support for Python 2.x. The library now explicitly requires Python 3.7 or higher.
  Fix: Upgrade your Python environment to 3.7 or newer. If you must use Python 2, you will need to pin vertica-python to a version prior to 1.3.0, which is not recommended as it is no longer maintained.
• deprecated The `crypt` package, which was used for certain authentication mechanisms, has been deprecated in version 1.3.0. While still functional, users should be aware that its use is discouraged.
  Fix: Review your authentication configuration. If you were relying on the `crypt` package's functionality, consider migrating to newer, more secure authentication methods supported by Vertica and vertica-python (e.g., OAuth 2.0 or Kerberos if applicable).
• deprecated The `ssl` connection option for enabling TLS/SSL has been deprecated. Users should now use the `tlsmode` connection option for more granular control over TLS security levels (e.g., 'disable', 'require', 'verify-ca', 'verify-full).
  Fix: Replace `ssl: True/False` in your `conn_info` dictionary with `tlsmode: 'require'`, `tlsmode: 'verify-ca'`, or `tlsmode: 'verify-full'` as appropriate for your security requirements. Ensure any necessary `tls_cafile`, `tls_certfile`, and `tls_keyfile` paths are also provided for certificate verification modes.
• gotcha When executing multiple SQL statements in a single `cursor.execute()` call, only the results from the first statement are immediately accessible. To retrieve results from subsequent statements, you must explicitly call `cursor.nextset()`.
  Fix: After `cursor.execute()`, if you expect results from multiple statements, iterate through them using a loop with `while cursor.nextset():` and then `cursor.fetchall()` for each set.
• gotcha Errors occurring in later statements of a multiple-statement query (or some other specific cases) might not raise an exception immediately during `cursor.execute()`. Instead, they might only be raised when `cursor.fetchone()`, `cursor.fetchmany()`, or `cursor.fetchall()` is called.
  Fix: It is recommended to always call a `fetch` method (e.g., `cursor.fetchall()`) after `cursor.execute()` to ensure all errors are captured, especially when dealing with multiple statements.
• gotcha Vertica's VARCHAR length is defined in bytes, not characters. When working with multi-byte UTF-8 characters (e.g., emojis or certain international characters), a VARCHAR(10) might only be able to store a few characters, leading to 'String data right truncation' errors if not accounted for.
  Fix: Define VARCHAR column lengths with sufficient buffer for multi-byte characters, or consider using `LONG VARCHAR` for potentially large or unpredictable string data. Ensure your Python strings are correctly encoded (vertica-python uses UTF-8 by default for unicode_error='strict').

Install

• pip install vertica-python Install stable version

Imports

• vertica_python

  import vertica_python

• connect

  from vertica_python import connect

• Error

  from vertica_python import Error, DatabaseError, ProgrammingError

Quickstart

This quickstart demonstrates how to establish a connection to a Vertica database, create a table, insert data, and query it using the `vertica-python` client. It leverages environment variables for sensitive connection details and uses a `with` statement for proper connection and cursor management. Error handling is included to catch common database exceptions.

import vertica_python
import os

# Configure connection details using environment variables for security
conn_info = {
    'host': os.environ.get('VERTICA_HOST', '127.0.0.1'),
    'port': int(os.environ.get('VERTICA_PORT', 5433)),
    'user': os.environ.get('VERTICA_USER', 'dbadmin'),
    'password': os.environ.get('VERTICA_PASSWORD', 'password'),
    'database': os.environ.get('VERTICA_DATABASE', 'verticadb'),
    'read_timeout': 600, # 10 minutes timeout on queries
    'unicode_error': 'strict', # default throw error on invalid UTF-8 results
    'ssl': False # SSL is disabled by default, consider 'tlsmode' for production
}

try:
    # Using 'with' statement for automatic connection closing
    with vertica_python.connect(**conn_info) as connection:
        print("Successfully connected to Vertica.")
        with connection.cursor() as cursor:
            # Execute a DDL statement
            cursor.execute("DROP TABLE IF EXISTS my_test_table;")
            cursor.execute("CREATE TABLE my_test_table (id INT, name VARCHAR(100));")
            print("Table created.")

            # Insert data
            cursor.execute("INSERT INTO my_test_table (id, name) VALUES (1, 'Alice');")
            cursor.execute("INSERT INTO my_test_table (id, name) VALUES (2, 'Bob');")
            print("Data inserted.")

            # Query data
            cursor.execute("SELECT id, name FROM my_test_table;")
            rows = cursor.fetchall()
            print("Retrieved data:")
            for row in rows:
                print(f"  ID: {row[0]}, Name: {row[1]}")

except vertica_python.Error as e:
    print(f"An error occurred: {e}")
except Exception as e:
    print(f"An unexpected error occurred: {e}")