0
# Connection Management
1

2
Core database connection functionality supporting both high-level PG-API and standard DB-API 2.0 interfaces, with flexible connection parameters and authentication methods.
3

4
## Capabilities
5

6
### Primary Connection Function
7

8
The main connection interface for creating PostgreSQL database connections using URI format or keyword parameters.
9

10
```python { .api }
11
def open(iri=None, prompt_title=None, **kw):
12
    """
13
    Create a postgresql.api.Connection to the server referenced by the given IRI.
14
    
15
    Parameters:
16
    - iri (str, optional): PostgreSQL connection IRI in format 'pq://user:password@host:port/database'
17
    - prompt_title (str, optional): Ignored, included for compatibility
18
    - **kw: Connection parameters (host, port, user, password, database, unix, etc.)
19
    
20
    Returns:
21
    Connection: Active database connection
22
    
23
    Raises:
24
    ConnectionError: If connection cannot be established
25
    AuthenticationError: If authentication fails
26
    """
27
```
28

29
**Usage Examples:**
30

31
```python
32
import postgresql
33

34
# URI format connection
35
db = postgresql.open('pq://user:password@localhost:5432/mydb')
36

37
# Keyword parameters
38
db = postgresql.open(
39
    host='localhost',
40
    port=5432,
41
    user='user',
42
    password='password',
43
    database='mydb'
44
)
45

46
# Local socket connection
47
db = postgresql.open(unix='/var/run/postgresql/.s.PGSQL.5432', database='mydb')
48

49
# Return connector instead of connection
50
connector = postgresql.open('&pq://user:password@localhost/mydb')
51
connection = connector()
52
connection.connect()
53
```
54

55
### Connection Interface
56

57
The primary connection interface providing database operations, transaction management, and prepared statement support.
58

59
```python { .api }
60
class Connection:
61
    """
62
    Database connection providing query execution, transaction management,
63
    and prepared statement support.
64
    """
65
    
66
    def prepare(statement):
67
        """
68
        Create a prepared statement for repeated execution.
69
        
70
        Parameters:
71
        - statement (str): SQL statement with $1, $2, etc. parameter placeholders
72
        
73
        Returns:
74
        Statement: Prepared statement object
75
        """
76
    
77
    def execute(statement, *parameters):
78
        """
79
        Execute a statement with parameters.
80
        
81
        Parameters:
82
        - statement (str): SQL statement
83
        - *parameters: Parameter values
84
        
85
        Returns:
86
        Command result or row data
87
        """
88
    
89
    def query(statement, *parameters):
90
        """
91
        Execute a query and return all results.
92
        
93
        Parameters:
94
        - statement (str): SQL query
95
        - *parameters: Parameter values
96
        
97
        Returns:
98
        List of result rows
99
        """
100
    
101
    def xact():
102
        """
103
        Create a transaction context manager.
104
        
105
        Returns:
106
        Transaction: Transaction context manager
107
        """
108
    
109
    def reset():
110
        """
111
        Reset the connection to its initial state.
112
        
113
        Clears any temporary settings, deallocates prepared statements,
114
        and resets the connection to its default configuration.
115
        """
116
    
117
    def do(language, source):
118
        """
119
        Execute source code in the specified procedural language.
120
        
121
        Parameters:
122
        - language (str): Procedural language name (e.g., 'plpgsql', 'python')
123
        - source (str): Source code to execute
124
        
125
        Returns:
126
        Execution result
127
        """
128
    
129
    def connect():
130
        """Establish connection to the database."""
131
    
132
    def close():
133
        """Close the database connection."""
134
    
135
    def clone():
136
        """
137
        Create a new connection with the same parameters.
138
        
139
        Returns:
140
        Connection: New connection instance
141
        """
142
```
143

144
### Connector Interface
145

146
Factory for creating connections with pre-configured parameters.
147

148
```python { .api }
149
class Connector:
150
    """
151
    Connection factory with pre-configured credentials and settings.
152
    """
153
    
154
    def __call__():
155
        """
156
        Create a new connection instance.
157
        
158
        Returns:
159
        Connection: New connection (not yet connected)
160
        """
161
    
162
    def fit(**parameters):
163
        """
164
        Create a new connector with additional parameters.
165
        
166
        Parameters:
167
        - **parameters: Additional connection parameters
168
        
169
        Returns:
170
        Connector: New connector with merged parameters
171
        """
172
```
173

174
### Connection Parameters
175

176
Key connection parameters supported by py-postgresql:
177

178
```python { .api }
179
# Connection parameters (used with open() **kw or Connector.fit())
180
host: str          # Database server hostname
181
port: int          # Database server port (default: 5432)
182
user: str          # Database username
183
password: str      # Database password
184
database: str      # Database name
185
unix: str          # Unix domain socket path
186
sslmode: str       # SSL mode ('disable', 'allow', 'prefer', 'require')
187
connect_timeout: int  # Connection timeout in seconds
188
application_name: str # Application name for logging
189
```
190

191
**Usage Example:**
192

193
```python
194
import postgresql
195

196
# Create connector with base parameters
197
base_connector = postgresql.open('&pq://user@localhost/template1')
198

199
# Create specific database connections
200
db1_connector = base_connector.fit(database='database1')
201
db2_connector = base_connector.fit(database='database2')
202

203
# Connect to databases
204
db1 = db1_connector()
205
db1.connect()
206

207
db2 = db2_connector()
208
db2.connect()
209

210
# Use connections
211
users = db1.query("SELECT * FROM users")
212
orders = db2.query("SELECT * FROM orders")
213

214
db1.close()
215
db2.close()
216
```
217

218
### Connection Utilities
219

220
Utility functions for connection parameter handling and resolution.
221

222
```python { .api }
223
# From postgresql.clientparameters module
224
def collect(prompt_title=None):
225
    """
226
    Collect standard connection parameters from environment.
227
    
228
    Returns:
229
    dict: Connection parameters from environment variables
230
    """
231

232
def normalize(parameters):
233
    """
234
    Normalize connection parameters to standard format.
235
    
236
    Parameters:
237
    - parameters (list): List of parameter tuples
238
    
239
    Returns:
240
    dict: Normalized connection parameters
241
    """
242

243
def resolve_password(parameters):
244
    """
245
    Resolve password from various sources (environment, pgpass file, etc.).
246
    
247
    Parameters:
248
    - parameters (dict): Connection parameters (modified in-place)
249
    """
250
```
251

252
### Driver Interface
253

254
Access to the default driver for advanced connection control.
255

256
```python { .api }
257
# From postgresql.driver module
258
default: Driver    # Default driver instance
259

260
def connect(*args, **kw):
261
    """
262
    Create connection using default driver.
263
    
264
    Returns:
265
    Connection: Database connection
266
    """
267

268
class Driver:
269
    """Database driver implementation."""
270
    
271
    def fit(**parameters):
272
        """
273
        Create connector with specified parameters.
274
        
275
        Returns:
276
        Connector: Connection factory
277
        """
278
```
279

280
## Connection Examples
281

282
### Basic Connection Patterns
283

284
```python
285
import postgresql
286

287
# Simple local connection
288
db = postgresql.open('localhost/mydb')
289

290
# Full URI with credentials
291
db = postgresql.open('pq://user:pass@server.example.com:5432/production_db')
292

293
# Using keyword parameters
294
db = postgresql.open(
295
    host='localhost',
296
    database='mydb',
297
    user='postgres',
298
    sslmode='require'
299
)
300
```
301

302
### Connection Pool Pattern
303

304
```python
305
import postgresql
306

307
# Create connector for reuse
308
connector = postgresql.open('&pq://user:pass@localhost/mydb')
309

310
# Function to get new connection
311
def get_connection():
312
    conn = connector()
313
    conn.connect()
314
    return conn
315

316
# Use in application
317
def process_data():
318
    with get_connection() as db:
319
        return db.query("SELECT * FROM data WHERE active = $1", True)
320
```
321

322
### Environment-based Connection
323

324
```python
325
import postgresql
326
import postgresql.clientparameters as params
327

328
# Use environment variables (PGHOST, PGPORT, PGUSER, PGPASSWORD, PGDATABASE)
329
env_params = params.collect()
330
db = postgresql.open(**env_params)
331

332
# Or let open() handle environment automatically
333
db = postgresql.open()  # Uses environment variables
334
```