0
# Vertica-Python
1

2
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.
3

4
## Package Information
5

6
- **Package Name**: vertica-python
7
- **Language**: Python
8
- **Installation**: `pip install vertica-python`
9

10
## Core Imports
11

12
```python
13
import vertica_python
14
```
15

16
For database connections:
17

18
```python
19
from vertica_python import connect, Connection
20
```
21

22
For exception handling:
23

24
```python
25
from vertica_python import Error, DatabaseError, ProgrammingError
26
```
27

28
## Basic Usage
29

30
```python
31
import vertica_python
32

33
# Connect to database
34
connection = vertica_python.connect(
35
    host='localhost',
36
    port=5433,
37
    user='dbadmin',
38
    password='password',
39
    database='mydb'
40
)
41

42
# Create cursor and execute query
43
with connection.cursor() as cursor:
44
    cursor.execute("SELECT * FROM my_table LIMIT 10")
45
    rows = cursor.fetchall()
46
    for row in rows:
47
        print(row)
48

49
# Connection cleanup
50
connection.close()
51

52
# Alternative: Using context manager
53
with vertica_python.connect(
54
    host='localhost',
55
    port=5433,
56
    user='dbadmin',
57
    password='password',
58
    database='mydb'
59
) as conn:
60
    with conn.cursor() as cur:
61
        cur.execute("SELECT COUNT(*) FROM my_table")
62
        count = cur.fetchone()[0]
63
        print(f"Table has {count} rows")
64
```
65

66
## Architecture
67

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

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

75
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.
76

77
## Capabilities
78

79
### Connection Management
80

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

83
```python { .api }
84
def connect(**kwargs) -> Connection: ...
85
def parse_dsn(dsn: str) -> dict: ...
86

87
class Connection:
88
    def close(self) -> None: ...
89
    def commit(self) -> None: ...
90
    def rollback(self) -> None: ...
91
    def cursor(self, cursor_type=None) -> Cursor: ...
92
    def cancel(self) -> None: ...
93
    def opened(self) -> bool: ...
94
    def closed(self) -> bool: ...
95
    def ssl(self) -> bool: ...
96
```
97

98
[Connection Management](./connection-management.md)
99

100
### Query Execution and Cursors
101

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

104
```python { .api }
105
class Cursor:
106
    def execute(self, operation: str, parameters=None, **kwargs) -> None: ...
107
    def executemany(self, operation: str, seq_of_parameters, **kwargs) -> None: ...
108
    def fetchone(self) -> tuple: ...
109
    def fetchmany(self, size=None) -> list: ...
110
    def fetchall(self) -> list: ...
111
    def copy(self, sql: str, data, **kwargs) -> None: ...
112
```
113

114
[Query Execution](./query-execution.md)
115

116
### Exception Handling
117

118
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.
119

120
```python { .api }
121
class Error(Exception): ...
122
class DatabaseError(Error): ...
123
class ProgrammingError(DatabaseError): ...
124
class QueryError(ProgrammingError):
125
    @property
126
    def sql(self) -> str: ...
127
    @classmethod
128
    def from_error_response(cls, error_response, sql): ...
129
```
130

131
[Exception Handling](./exception-handling.md)
132

133
### Data Types and Type Conversion
134

135
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.
136

137
```python { .api }
138
def Date(year: int, month: int, day: int): ...
139
def Time(hour: int, minute: int, second: int): ...
140
def Timestamp(year: int, month: int, day: int, hour: int, minute: int, second: int): ...
141
def Binary(string): ...
142

143
class VerticaType:
144
    # Type constants for all Vertica data types
145
    UNKNOWN: int
146
    BOOL: int
147
    INT8: int
148
    FLOAT8: int
149
    # ... (all type constants)
150
```
151

152
[Data Types](./data-types.md)
153

154
## Module Constants
155

156
```python { .api }
157
PROTOCOL_VERSION: int  # 196624 (3.16)
158
version_info: tuple  # (1, 4, 0)
159
__version__: str  # "1.4.0"
160
apilevel: float  # 2.0
161
threadsafety: int  # 1
162
paramstyle: str  # "named"
163
```
164

165
### Type Objects
166

167
DB-API 2.0 type objects for column type comparison:
168

169
```python { .api }
170
STRING: VerticaType
171
BINARY: VerticaType
172
NUMBER: VerticaType
173
DATETIME: VerticaType
174
ROWID: VerticaType
175
```