vertica-python

library1.4.0pypypi✓ verified 25d ago

vertica-python is the official native Python client for the Vertica Analytics Database. It provides a DB-API 2.0 compliant interface for connecting to and interacting with Vertica, supporting features like query execution, data retrieval, and bulk loading. Currently at version 1.4.0, the library maintains an active development pace with frequent minor releases addressing bug fixes, performance improvements, and new features.

database

pip install vertica-python

github ↗package ↗homepage ↗

INSTALL

IMPORT

SIG · VERTICA-PYTHON

vertica-python

databasepythonv1.4.0

Install

1.8s avg

Import

186ms

Disk

18MB

Pass rate

10/ 10

Env Coverage10 / 10

glibc

3.9–3.13

musl

3.9–3.13

Install & Compatibility

Where this runs

tested against v1.4.0 · pip install

no network on importno background threads

Install × environment matrix

Each cell = how many times install + import succeeded across repeated harness runs. Partial = flaky.
glibc = Debian/Ubuntu slim · musl = Alpine Linux

musl

py 3.10–3.95 runs

installs and imports cleanly · install 0.0s · import 0.194s · 20MB

glibc

py 3.10–3.95 runs

installs and imports cleanly · install 1.8s · import 0.178s · 20MB

18MB installed

● package 18MB

Code

Verified usage

Verified import paths — ran on the pinned version, not inferred.

vertica_python

✓ import vertica_python

connect

✓ from vertica_python import connect

Error

✓ from vertica_python import Error, DatabaseError, ProgrammingError

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}")

Debug

Known issues

breakingStarting 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.

affects: >=1.3.0

deprecatedThe `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).

affects: >=1.3.0

deprecatedThe `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.

affects: >=1.4.0

gotchaWhen 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.

affects: All

gotchaErrors 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.

affects: All

gotchaVertica'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').

affects: All

Errors

Common errors & fixes

ModuleNotFoundError: No module named 'vertica_python'

The `vertica-python` package has not been installed in the current Python environment.

fix

pip install vertica-python

vertica_python.errors.QueryError: Authentication failed

The username or password provided in the connection parameters is incorrect or the specified user lacks necessary database permissions.

fix

conn_info = {'host': 'your_host', 'port': 5432, 'user': 'correct_user', 'password': 'correct_password', 'database': 'your_db'}

ssl.SSLError: [SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed

The client failed to verify the Vertica server's SSL certificate, often because the necessary CA root certificate is not trusted or provided.

fix

conn_info = {'host': 'your_host', 'port': 5432, 'user': 'user', 'password': 'password', 'database': 'db', 'ssl': True, 'ssl_verify_certificate': False} # Note: ssl_verify_certificate=False bypasses security and is not recommended for production.

vertica_python.errors.ConnectionError: ('Cannot connect to the server at', ('your_host', 5433), 'check your connection parameters.')

The Vertica server is either not running, the hostname or port in the connection string is incorrect, or a network firewall is blocking the connection.

fix

conn_info = {'host': 'correct_host', 'port': correct_port, 'user': 'user', 'password': 'password', 'database': 'db'} # Verify host, port, server status, and firewall rules.

TypeError: expected a file-like object for input

The `cursor.copy()` method requires a file-like object (e.g., `io.StringIO` or `io.BytesIO`) or an iterator for its `data` parameter, not a plain string.

fix

import io
import vertica_python

data_to_copy = 'col1_val\tcol2_val\nnext_row_val1\tnext_row_val2'
with vertica_python.connect(**conn_info) as connection:
    cursor = connection.cursor()
    with io.StringIO(data_to_copy) as data_stream:
        cursor.copy("COPY my_table FROM STDIN DELIMITER E'\\t'", data_stream)
    connection.commit()

Upgrade

Version history

1.4.0latest on PyPI · released Jul 22, 2024

Audit

Dependencies

kerberosoptionalRequired for Kerberos authentication support on Unix-like systems.

Agent activity

19 hits · last 30 days

node