0
# Connection Management
1
2
Core database connection functionality for establishing, managing, and terminating connections to Vertica databases. Supports various authentication methods, secure connections via TLS/SSL, connection pooling, and comprehensive parameter configuration.
3
4
## Capabilities
5
6
### Connection Functions
7
8
#### connect(**kwargs)
9
10
Creates a new connection to a Vertica database.
11
12
```python { .api }
13
def connect(**kwargs) -> Connection:
14
"""
15
Opens a new connection to a Vertica database.
16
17
Parameters:
18
- host (str): Database server hostname (default: 'localhost')
19
- port (int): Database server port (default: 5433)
20
- user (str): Username for authentication
21
- password (str): Password for authentication (default: '')
22
- database (str): Database name to connect to (default: '')
23
- autocommit (bool): Enable autocommit mode (default: False)
24
- ssl (bool): Use SSL/TLS connection (default: False)
25
- tlsmode (str): TLS mode ('disable', 'allow', 'prefer', 'require', 'verify-ca', 'verify-full')
26
- backup_server_node (list): List of backup server nodes for failover
27
- binary_transfer (bool): Use binary protocol transfer (default: False)
28
- request_complex_types (bool): Request complex type information (default: True)
29
- log_level (int): Logging level (default: logging.WARNING)
30
- log_path (str): Log file path (default: 'vertica_python.log')
31
- oauth_access_token (str): OAuth access token for authentication
32
- workload (str): Workload identifier for resource management
33
- kerberos_service_name (str): Kerberos service name (default: 'vertica')
34
- unicode_error (str): Unicode error handling mode
35
- ssl_cert_file (str): Path to client certificate file
36
- ssl_key_file (str): Path to client private key file
37
- ssl_ca_file (str): Path to CA certificate file
38
39
Returns:
40
Connection: New database connection instance
41
42
Raises:
43
ConnectionError: If connection cannot be established
44
"""
45
```
46
47
#### parse_dsn(dsn)
48
49
Parses a connection string (DSN) into a dictionary of connection parameters.
50
51
```python { .api }
52
def parse_dsn(dsn: str) -> dict:
53
"""
54
Parse connection string (DSN) into a dictionary of keywords and values.
55
56
Parameters:
57
- dsn (str): Connection string in format
58
'vertica://user:password@host:port/database?param1=value1¶m2=value2'
59
60
Returns:
61
dict: Dictionary containing connection parameters
62
63
Raises:
64
ValueError: If DSN format is invalid
65
"""
66
```
67
68
### Connection Class
69
70
The Connection class represents a database connection and provides methods for transaction control, cursor creation, and connection management.
71
72
```python { .api }
73
class Connection:
74
"""
75
Database connection object implementing DB-API 2.0 interface.
76
"""
77
78
def __init__(self, options: dict):
79
"""
80
Initialize connection with connection parameters.
81
82
Parameters:
83
- options (dict): Connection configuration dictionary
84
"""
85
86
def close(self) -> None:
87
"""
88
Close the connection and free associated resources.
89
Connection becomes unusable after this call.
90
"""
91
92
def commit(self) -> None:
93
"""
94
Commit any pending transaction to the database.
95
96
Raises:
97
DatabaseError: If commit fails
98
"""
99
100
def rollback(self) -> None:
101
"""
102
Roll back pending transaction to the start of the transaction.
103
104
Raises:
105
DatabaseError: If rollback fails
106
"""
107
108
def cursor(self, cursor_type=None) -> 'Cursor':
109
"""
110
Return a new cursor object using the connection.
111
112
Parameters:
113
- cursor_type (str, optional): Type of cursor to create ('list', 'dict', etc.)
114
115
Returns:
116
Cursor: New cursor instance
117
118
Raises:
119
InterfaceError: If connection is closed
120
"""
121
122
def cancel(self) -> None:
123
"""
124
Cancel the current database operation.
125
126
Raises:
127
OperationalError: If cancellation fails
128
"""
129
130
def opened(self) -> bool:
131
"""
132
Check if connection is open.
133
134
Returns:
135
bool: True if connection is open, False otherwise
136
"""
137
138
def closed(self) -> bool:
139
"""
140
Check if connection is closed.
141
142
Returns:
143
bool: True if connection is closed, False otherwise
144
"""
145
146
def ssl(self) -> bool:
147
"""
148
Check if connection is using SSL/TLS encryption.
149
150
Returns:
151
bool: True if connection uses SSL/TLS, False otherwise
152
"""
153
154
@property
155
def autocommit(self) -> bool:
156
"""
157
Get or set autocommit mode.
158
159
Returns:
160
bool: Current autocommit setting
161
"""
162
163
@autocommit.setter
164
def autocommit(self, value: bool) -> None:
165
"""
166
Set autocommit mode.
167
168
Parameters:
169
- value (bool): Enable (True) or disable (False) autocommit
170
"""
171
172
@property
173
def parameters(self) -> dict:
174
"""
175
Get connection parameters.
176
177
Returns:
178
dict: Current connection parameters
179
"""
180
181
@property
182
def unicode_error(self) -> str:
183
"""
184
Get or set unicode error handling mode.
185
186
Returns:
187
str: Current unicode error handling mode
188
"""
189
190
@unicode_error.setter
191
def unicode_error(self, value: str) -> None:
192
"""
193
Set unicode error handling mode.
194
195
Parameters:
196
- value (str): Error handling mode ('strict', 'ignore', 'replace')
197
"""
198
199
def __enter__(self) -> 'Connection':
200
"""
201
Enter context manager.
202
203
Returns:
204
Connection: Self for context manager protocol
205
"""
206
207
def __exit__(self, exc_type, exc_val, exc_tb) -> None:
208
"""
209
Exit context manager and close connection.
210
211
Parameters:
212
- exc_type: Exception type (if any)
213
- exc_val: Exception value (if any)
214
- exc_tb: Exception traceback (if any)
215
"""
216
```
217
218
## Connection Parameters
219
220
### Authentication Parameters
221
222
- **user**: Database username
223
- **password**: Database password
224
- **oauth_access_token**: OAuth token for OAuth authentication
225
- **kerberos_service_name**: Service name for Kerberos authentication
226
227
### Network Parameters
228
229
- **host**: Server hostname or IP address
230
- **port**: Server port (default: 5433)
231
- **backup_server_node**: List of backup servers for load balancing/failover
232
233
### Security Parameters
234
235
- **ssl**: Enable SSL/TLS encryption
236
- **tlsmode**: TLS connection mode ('disable', 'allow', 'prefer', 'require', 'verify-ca', 'verify-full')
237
- **ssl_cert_file**: Client certificate file path
238
- **ssl_key_file**: Client private key file path
239
- **ssl_ca_file**: CA certificate file path
240
241
### Behavior Parameters
242
243
- **database**: Target database name
244
- **autocommit**: Enable autocommit mode
245
- **binary_transfer**: Use binary protocol for better performance
246
- **request_complex_types**: Request metadata for complex types
247
- **unicode_error**: Unicode error handling ('strict', 'ignore', 'replace')
248
- **workload**: Workload identifier for resource management
249
250
### Logging Parameters
251
252
- **log_level**: Python logging level (logging.DEBUG, logging.INFO, etc.)
253
- **log_path**: Path to log file
254
255
## Usage Examples
256
257
### Basic Connection
258
259
```python
260
import vertica_python
261
262
# Simple connection
263
conn = vertica_python.connect(
264
host='myserver.example.com',
265
port=5433,
266
user='myuser',
267
password='mypassword',
268
database='mydb'
269
)
270
271
# Use connection
272
cursor = conn.cursor()
273
cursor.execute("SELECT version()")
274
result = cursor.fetchone()
275
print(result[0])
276
277
# Clean up
278
cursor.close()
279
conn.close()
280
```
281
282
### Context Manager Usage
283
284
```python
285
with vertica_python.connect(
286
host='myserver.example.com',
287
user='myuser',
288
password='mypassword',
289
database='mydb'
290
) as conn:
291
with conn.cursor() as cur:
292
cur.execute("SELECT COUNT(*) FROM my_table")
293
count = cur.fetchone()[0]
294
print(f"Row count: {count}")
295
# Cursor automatically closed
296
# Connection automatically closed
297
```
298
299
### SSL Connection
300
301
```python
302
conn = vertica_python.connect(
303
host='secure.example.com',
304
user='myuser',
305
password='mypassword',
306
database='mydb',
307
ssl=True,
308
tlsmode='verify-full',
309
ssl_ca_file='/path/to/ca-cert.pem'
310
)
311
```
312
313
### Connection String (DSN) Usage
314
315
```python
316
dsn = "vertica://myuser:mypassword@myserver.example.com:5433/mydb?ssl=true&autocommit=true"
317
params = vertica_python.parse_dsn(dsn)
318
conn = vertica_python.connect(**params)
319
```
320
321
### Load Balancing with Backup Servers
322
323
```python
324
conn = vertica_python.connect(
325
host='primary.example.com',
326
backup_server_node=[
327
'backup1.example.com:5433',
328
'backup2.example.com:5433'
329
],
330
user='myuser',
331
password='mypassword',
332
database='mydb'
333
)
334
```