0
# Core Database Operations
1

2
Essential database connectivity functionality that forms the foundation of the DB-API 2.0 interface. These operations provide connection establishment, query execution, result fetching, and transaction management capabilities required for all database interactions.
3

4
## Capabilities
5

6
### Connection Establishment
7

8
Creates database connections with comprehensive configuration options supporting various authentication methods, networking configurations, and Redshift-specific features.
9

10
```python { .api }
11
def connect(
12
    user: str = None,
13
    database: str = None,
14
    password: str = None,
15
    port: int = None,
16
    host: str = None,
17
    source_address: str = None,
18
    unix_sock: str = None,
19
    ssl: bool = None,
20
    sslmode: str = None,
21
    timeout: int = None,
22
    max_prepared_statements: int = None,
23
    tcp_keepalive: bool = None,
24
    tcp_keepalive_idle: int = None,
25
    tcp_keepalive_interval: int = None,
26
    tcp_keepalive_count: int = None,
27
    application_name: str = None,
28
    replication: str = None,
29
    # IAM Authentication Parameters
30
    idp_host: str = None,
31
    db_user: str = None,
32
    app_id: str = None,
33
    app_name: str = None,
34
    preferred_role: str = None,
35
    principal_arn: str = None,
36
    access_key_id: str = None,
37
    secret_access_key: str = None,
38
    session_token: str = None,
39
    profile: str = None,
40
    credentials_provider: str = None,
41
    region: str = None,
42
    cluster_identifier: str = None,
43
    iam: bool = None,
44
    # Identity Provider Parameters
45
    client_id: str = None,
46
    idp_tenant: str = None,
47
    client_secret: str = None,
48
    partner_sp_id: str = None,
49
    idp_response_timeout: int = None,
50
    listen_port: int = None,
51
    login_to_rp: str = None,
52
    login_url: str = None,
53
    auto_create: bool = None,
54
    db_groups: list[str] = None,
55
    force_lowercase: bool = None,
56
    allow_db_user_override: bool = None,
57
    # Protocol and Configuration Parameters
58
    client_protocol_version: int = None,
59
    database_metadata_current_db_only: bool = None,
60
    ssl_insecure: bool = None,
61
    web_identity_token: str = None,
62
    role_session_name: str = None,
63
    role_arn: str = None,
64
    iam_disable_cache: bool = None,
65
    auth_profile: str = None,
66
    endpoint_url: str = None,
67
    provider_name: str = None,
68
    scope: str = None,
69
    numeric_to_float: bool = False,
70
    # Serverless Parameters
71
    is_serverless: bool = False,
72
    serverless_acct_id: str = None,
73
    serverless_work_group: str = None,
74
    group_federation: bool = None,
75
    identity_namespace: str = None,
76
    idc_client_display_name: str = None,
77
    idc_region: str = None,
78
    issuer_url: str = None,
79
    token: str = None,
80
    token_type: str = None,
81
) -> Connection:
82
    """
83
    Establishes a Connection to an Amazon Redshift cluster.
84
    
85
    Parameters:
86
    - user: Username for authentication
87
    - database: Database name to connect to
88
    - password: Password for authentication
89
    - host: Hostname of the Redshift cluster
90
    - port: Port number (default 5439)
91
    - ssl: Enable SSL (default True)
92
    - iam: Enable IAM authentication (default False)
93
    - cluster_identifier: Redshift cluster identifier for IAM
94
    - And 60+ additional parameters for various authentication and configuration options
95
    
96
    Returns:
97
    Connection object for database operations
98
    """
99
```
100

101
### Connection Management
102

103
Database connection objects providing transaction control, cursor creation, and connection lifecycle management.
104

105
```python { .api }
106
class Connection:
107
    """Database connection object implementing DB-API 2.0 interface."""
108
    
109
    def cursor(self) -> Cursor:
110
        """Create and return a new Cursor object."""
111
    
112
    def commit(self) -> None:
113
        """Commit the current transaction."""
114
    
115
    def rollback(self) -> None:
116
        """Roll back the current transaction."""
117
    
118
    def close(self) -> None:
119
        """Close the connection."""
120
    
121
    def run(self, sql: str, stream=None, **params) -> tuple:
122
        """Execute SQL statement and return results as tuple."""
123
    
124
    # Context manager support
125
    def __enter__(self) -> 'Connection': ...
126
    def __exit__(self, exc_type, exc_value, traceback) -> None: ...
127
    
128
    # Connection properties
129
    @property
130
    def autocommit(self) -> bool:
131
        """Get/set autocommit mode."""
132
    
133
    @autocommit.setter  
134
    def autocommit(self, value: bool) -> None: ...
135
    
136
    # Metadata properties
137
    def client_os_version(self) -> str:
138
        """Return client OS version string."""
139
    
140
    def is_single_database_metadata(self) -> bool:
141
        """Return whether metadata queries are limited to current database."""
142
    
143
    @property
144
    def description(self) -> list | None:
145
        """Return description of last query result."""
146
```
147

148
### Query Execution and Cursors
149

150
Cursor objects for executing SQL statements, managing prepared statements, and retrieving query results.
151

152
```python { .api }
153
class Cursor:
154
    """Database cursor for executing queries and fetching results."""
155
    
156
    def execute(self, operation: str, args=None, stream=None, merge_socket_read: bool = False) -> 'Cursor':
157
        """
158
        Execute a SQL statement.
159
        
160
        Parameters:
161
        - operation: SQL statement to execute
162
        - args: Parameters for the SQL statement
163
        - stream: Optional stream for result processing
164
        - merge_socket_read: Optimize socket reading for large results
165
        
166
        Returns:
167
        Self for method chaining
168
        """
169
    
170
    def executemany(self, operation: str, param_sets) -> 'Cursor':
171
        """
172
        Execute a SQL statement multiple times with different parameter sets.
173
        
174
        Parameters:
175
        - operation: SQL statement to execute
176
        - param_sets: Sequence of parameter tuples/dicts
177
        
178
        Returns:
179
        Self for method chaining
180
        """
181
    
182
    def callproc(self, procname: str, parameters=None):
183
        """
184
        Call a stored procedure.
185
        
186
        Parameters:
187
        - procname: Name of the stored procedure
188
        - parameters: Procedure parameters
189
        
190
        Returns:
191
        Procedure result
192
        """
193
    
194
    # Context manager support
195
    def __enter__(self) -> 'Cursor': ...
196
    def __exit__(self, exc_type, exc_value, traceback) -> None: ...
197
    
198
    def close(self) -> None:
199
        """Close the cursor."""
200
```
201

202
### Result Fetching
203

204
Methods for retrieving query results in various formats with support for large result sets and streaming.
205

206
```python { .api }
207
class Cursor:
208
    def fetchone(self) -> list | None:
209
        """
210
        Fetch the next row of a query result set.
211
        
212
        Returns:
213
        Next row as a list, or None if no more rows available
214
        """
215
    
216
    def fetchmany(self, num: int = None) -> tuple:
217
        """
218
        Fetch a specified number of rows from the query result.
219
        
220
        Parameters:
221
        - num: Number of rows to fetch (default: arraysize)
222
        
223
        Returns:
224
        Tuple of rows, each row as a list
225
        """
226
    
227
    def fetchall(self) -> tuple:
228
        """
229
        Fetch all remaining rows of a query result set.
230
        
231
        Returns:
232
        Tuple of all remaining rows, each row as a list
233
        """
234
    
235
    # Iterator support
236
    def __iter__(self) -> 'Cursor': ...
237
    def __next__(self) -> list: ...
238
    
239
    # Cursor properties
240
    @property
241
    def connection(self) -> Connection | None:
242
        """Reference to the associated Connection object."""
243
    
244
    @property
245
    def rowcount(self) -> int:
246
        """Number of rows affected by the last execute() call."""
247
    
248
    @property
249
    def redshift_rowcount(self) -> int:
250
        """Redshift-specific row count information."""
251
    
252
    @property
253
    def description(self) -> list[tuple] | None:
254
        """
255
        Description of query result columns.
256
        
257
        Returns:
258
        List of 7-tuples (name, type_code, display_size, internal_size, precision, scale, null_ok)
259
        """
260
    
261
    @property
262
    def arraysize(self) -> int:
263
        """Number of rows to fetch at a time with fetchmany()."""
264
    
265
    @arraysize.setter
266
    def arraysize(self, size: int) -> None: ...
267
```
268

269
### Bulk Operations
270

271
High-performance bulk data operations for efficient data loading and processing.
272

273
```python { .api }
274
class Cursor:
275
    def insert_data_bulk(
276
        self,
277
        table: str,
278
        column_names: list[str], 
279
        data_rows: list[list]
280
    ) -> None:
281
        """
282
        Perform bulk insert operation.
283
        
284
        Parameters:
285
        - table: Target table name
286
        - column_names: List of column names  
287
        - data_rows: List of data rows to insert
288
        """
289
```
290

291
### Two-Phase Commit Support
292

293
Distributed transaction support for applications requiring two-phase commit protocol.
294

295
```python { .api }
296
class Connection:
297
    def xid(self, format_id: int, global_transaction_id: str, branch_qualifier: str) -> tuple:
298
        """Create a transaction ID for two-phase commit."""
299
    
300
    def tpc_begin(self, xid: tuple) -> None:
301
        """Begin a two-phase commit transaction."""
302
    
303
    def tpc_prepare(self) -> None:
304
        """Prepare the current two-phase commit transaction."""
305
    
306
    def tpc_commit(self, xid: tuple = None) -> None:
307
        """Commit a prepared two-phase commit transaction."""
308
    
309
    def tpc_rollback(self, xid: tuple = None) -> None:
310
        """Roll back a prepared two-phase commit transaction."""
311
    
312
    def tpc_recover(self) -> list[tuple]:
313
        """Return a list of pending transaction IDs."""
314
```
315

316
### Parameter Style Configuration
317

318
Configurable parameter binding styles for SQL statement parameterization.
319

320
```python { .api }
321
# Module-level paramstyle configuration
322
paramstyle: str = "format"  # Default parameter style
323

324
# Set paramstyle at module level (affects all new cursors)
325
redshift_connector.paramstyle = 'qmark'
326

327
# Set paramstyle on individual cursor
328
cursor.paramstyle = 'named'
329

330
# Supported parameter styles:
331
# - 'format': WHERE name=%s
332
# - 'qmark': WHERE name=?  
333
# - 'numeric': WHERE name=:1
334
# - 'named': WHERE name=:param_name
335
# - 'pyformat': WHERE name=%(param_name)s
336
```
337

338
### Connection Validation
339

340
Utility functions for validating connection parameters and testing connectivity.
341

342
```python { .api }
343
def validate_keepalive_values(idle: int, interval: int, count: int) -> None:
344
    """
345
    Validate TCP keepalive parameter values.
346
    
347
    Parameters:
348
    - idle: Seconds before sending keepalive probes
349
    - interval: Seconds between keepalive probes  
350
    - count: Number of failed probes before considering connection dead
351
    
352
    Raises:
353
    ValueError: If any parameter is invalid
354
    """
355
```