or run

npx @tessl/cli init
Log in

Version

Tile

Overview

Evals

Files

docs

connection-management.mddata-types.mdexception-handling.mdindex.mdquery-execution.md
tile.json

tessl/pypi-vertica-python

Official native Python client for the Vertica database.

Workspace
tessl
Visibility
Public
Created
Last updated
Describes
pypipkg:pypi/vertica-python@1.4.x

To install, run

npx @tessl/cli install tessl/pypi-vertica-python@1.4.0

index.mddocs/

Vertica-Python

Official native Python client for the Vertica database. This package provides comprehensive database connectivity features including secure connections via TLS/SSL, authentication methods (Kerberos, OAuth), connection pooling and load balancing, prepared statements for SQL injection prevention, and efficient data transfer in both text and binary formats. It implements the DB-API v2.0 protocol standard and supports advanced Vertica-specific features like COPY operations for bulk data loading, complex data types, streaming query results, and customizable data type conversions.

Package Information

  • Package Name: vertica-python
  • Language: Python
  • Installation: pip install vertica-python

Core Imports

import vertica_python

For database connections:

from vertica_python import connect, Connection

For exception handling:

from vertica_python import Error, DatabaseError, ProgrammingError

Basic Usage

import vertica_python

# Connect to database
connection = vertica_python.connect(
    host='localhost',
    port=5433,
    user='dbadmin',
    password='password',
    database='mydb'
)

# Create cursor and execute query
with connection.cursor() as cursor:
    cursor.execute("SELECT * FROM my_table LIMIT 10")
    rows = cursor.fetchall()
    for row in rows:
        print(row)

# Connection cleanup
connection.close()

# Alternative: Using context manager
with vertica_python.connect(
    host='localhost',
    port=5433,
    user='dbadmin',
    password='password',
    database='mydb'
) as conn:
    with conn.cursor() as cur:
        cur.execute("SELECT COUNT(*) FROM my_table")
        count = cur.fetchone()[0]
        print(f"Table has {count} rows")

Architecture

The vertica-python client follows the Python DB-API 2.0 specification and uses a layered architecture:

  • Connection Layer: Manages database connections, authentication, and transaction control
  • Cursor Layer: Handles query execution, result fetching, and data type conversion
  • Protocol Layer: Implements Vertica's native binary protocol for efficient communication
  • Type System: Provides comprehensive data type mapping between Python and Vertica types

This design enables thread-safe module usage (threadsafety level 1), supports both prepared and dynamic statements, and provides extensive customization options for data type handling and connection parameters.

Capabilities

Connection Management

Core database connection functionality including connection establishment, authentication, transaction control, and connection pooling. Supports various authentication methods and secure connections.

def connect(**kwargs) -> Connection: ...
def parse_dsn(dsn: str) -> dict: ...

class Connection:
    def close(self) -> None: ...
    def commit(self) -> None: ...
    def rollback(self) -> None: ...
    def cursor(self, cursor_type=None) -> Cursor: ...
    def cancel(self) -> None: ...
    def opened(self) -> bool: ...
    def closed(self) -> bool: ...
    def ssl(self) -> bool: ...

Connection Management

Query Execution and Cursors

Database cursor operations for executing SQL statements, fetching results, and handling bulk operations. Includes support for prepared statements, parameter binding, and streaming results.

class Cursor:
    def execute(self, operation: str, parameters=None, **kwargs) -> None: ...
    def executemany(self, operation: str, seq_of_parameters, **kwargs) -> None: ...
    def fetchone(self) -> tuple: ...
    def fetchmany(self, size=None) -> list: ...
    def fetchall(self) -> list: ...
    def copy(self, sql: str, data, **kwargs) -> None: ...

Query Execution

Exception Handling

Complete exception hierarchy following DB-API 2.0 standards plus Vertica-specific error types. Includes automatic error classification based on SQL states and detailed error information.

class Error(Exception): ...
class DatabaseError(Error): ...
class ProgrammingError(DatabaseError): ...
class QueryError(ProgrammingError):
    @property
    def sql(self) -> str: ...
    @classmethod
    def from_error_response(cls, error_response, sql): ...

Exception Handling

Data Types and Type Conversion

Comprehensive data type support including DB-API 2.0 type constructors, Vertica-specific types, and customizable type conversion system. Handles complex types like arrays, sets, and custom data structures.

def Date(year: int, month: int, day: int): ...
def Time(hour: int, minute: int, second: int): ...
def Timestamp(year: int, month: int, day: int, hour: int, minute: int, second: int): ...
def Binary(string): ...

class VerticaType:
    # Type constants for all Vertica data types
    UNKNOWN: int
    BOOL: int
    INT8: int
    FLOAT8: int
    # ... (all type constants)

Data Types

Module Constants

PROTOCOL_VERSION: int  # 196624 (3.16)
version_info: tuple  # (1, 4, 0)
__version__: str  # "1.4.0"
apilevel: float  # 2.0
threadsafety: int  # 1
paramstyle: str  # "named"

Type Objects

DB-API 2.0 type objects for column type comparison:

STRING: VerticaType
BINARY: VerticaType
NUMBER: VerticaType
DATETIME: VerticaType
ROWID: VerticaType