0
# Database Connections
1

2
Complete connection management for PostgreSQL databases with support for both synchronous and asynchronous operations, connection pooling, transaction control, two-phase commit, and comprehensive server configuration access.
3

4
## Capabilities
5

6
### Connection Creation
7

8
Create database connections using connection strings or individual parameters, with support for SSL, connection pooling, and various authentication methods.
9

10
```python { .api }
11
class Connection:
12
    @classmethod
13
    def connect(
14
        cls,
15
        conninfo: str = "",
16
        *,
17
        autocommit: bool = False,
18
        prepare_threshold: int | None = 5,
19
        row_factory: RowFactory | None = None,
20
        cursor_factory: type[Cursor] | None = None,
21
        host: str | None = None,
22
        port: int | None = None,
23
        dbname: str | None = None,
24
        user: str | None = None,
25
        password: str | None = None,
26
        sslmode: str | None = None,
27
        connect_timeout: int | None = None,
28
        application_name: str | None = None,
29
        **kwargs
30
    ) -> Connection:
31
        """
32
        Create a new database connection.
33
        
34
        Args:
35
            conninfo: PostgreSQL connection string
36
            autocommit: Enable auto-commit mode
37
            prepare_threshold: Number of executions before auto-preparing statements
38
            row_factory: Default row factory for cursors
39
            cursor_factory: Default cursor factory class
40
            host: Database host address
41
            port: Database port number  
42
            dbname: Database name
43
            user: Username for authentication
44
            password: Password for authentication
45
            sslmode: SSL connection mode ('disable', 'require', 'prefer', etc.)
46
            connect_timeout: Connection timeout in seconds
47
            application_name: Application name for connection tracking
48
            
49
        Returns:
50
            Connection: Active database connection
51
        """
52

53
class AsyncConnection:
54
    @classmethod
55
    async def connect(
56
        cls,
57
        conninfo: str = "",
58
        *,
59
        autocommit: bool = False,
60
        prepare_threshold: int | None = 5,
61
        row_factory: RowFactory | None = None,
62
        cursor_factory: type[AsyncCursor] | None = None,
63
        host: str | None = None,
64
        port: int | None = None,
65
        dbname: str | None = None,
66
        user: str | None = None,
67
        password: str | None = None,
68
        sslmode: str | None = None,
69
        connect_timeout: int | None = None,
70
        application_name: str | None = None,
71
        **kwargs
72
    ) -> AsyncConnection:
73
        """Async version of Connection.connect() with same parameters"""
74
```
75

76
#### Usage Examples
77

78
```python
79
# Simple connection string
80
conn = psycopg.connect("dbname=mydb user=postgres host=localhost")
81

82
# Individual parameters
83
conn = psycopg.connect(
84
    host="localhost",
85
    port=5432,
86
    dbname="mydb",
87
    user="postgres",
88
    password="secret"
89
)
90

91
# SSL connection
92
conn = psycopg.connect(
93
    host="secure-db.example.com",
94
    dbname="production",
95
    user="app_user",
96
    password="secure_password",
97
    sslmode="require"
98
)
99

100
# Async connection
101
conn = await psycopg.AsyncConnection.connect("dbname=mydb user=postgres")
102
```
103

104
### Connection State Management
105

106
Monitor and control connection state, including checking connection status, handling broken connections, and managing connection lifecycle.
107

108
```python { .api }
109
class BaseConnection:
110
    @property
111
    def closed(self) -> int:
112
        """Connection status: 0=open, >0=closed"""
113
    
114
    @property  
115
    def broken(self) -> bool:
116
        """True if connection is broken and unusable"""
117
    
118
    def close(self) -> None:
119
        """Close the connection"""
120
        
121
    def cancel(self) -> None:
122
        """Cancel any running operation"""
123
```
124

125
### Transaction Management
126

127
Control database transactions with support for different isolation levels, savepoints, and explicit transaction boundaries.
128

129
```python { .api }
130
class Connection:
131
    def commit(self) -> None:
132
        """Commit current transaction"""
133
        
134
    def rollback(self) -> None:
135
        """Rollback current transaction"""
136
    
137
    @property
138
    def autocommit(self) -> bool:
139
        """Auto-commit mode status"""
140
        
141
    @autocommit.setter  
142
    def autocommit(self, value: bool) -> None:
143
        """Enable/disable auto-commit mode"""
144
    
145
    @property
146
    def isolation_level(self) -> IsolationLevel | None:
147
        """Current transaction isolation level"""
148
        
149
    @isolation_level.setter
150
    def isolation_level(self, value: IsolationLevel | None) -> None:
151
        """Set transaction isolation level"""
152
    
153
    @property
154
    def read_only(self) -> bool | None:
155
        """Read-only transaction mode"""
156
        
157
    @read_only.setter
158
    def read_only(self, value: bool | None) -> None:
159
        """Set read-only transaction mode"""
160
    
161
    def transaction(
162
        self,
163
        *,
164
        savepoint_name: str | None = None,
165
        isolation_level: IsolationLevel | None = None,
166
        read_only: bool | None = None,
167
        deferrable: bool | None = None
168
    ) -> Transaction:
169
        """Create transaction context manager"""
170
```
171

172
#### Transaction Usage Examples
173

174
```python
175
# Auto-commit mode
176
conn.autocommit = True
177
conn.execute("CREATE TABLE test (id serial, name text)")
178

179
# Manual transaction control
180
conn.autocommit = False
181
try:
182
    conn.execute("INSERT INTO users (name) VALUES (%s)", ("Alice",))
183
    conn.execute("INSERT INTO users (name) VALUES (%s)", ("Bob",))
184
    conn.commit()
185
except Exception:
186
    conn.rollback()
187
    raise
188

189
# Transaction context manager
190
with conn.transaction():
191
    conn.execute("INSERT INTO users (name) VALUES (%s)", ("Charlie",))
192
    # Automatically commits or rolls back
193

194
# Savepoint
195
with conn.transaction(savepoint_name="sp1"):
196
    conn.execute("INSERT INTO users (name) VALUES (%s)", ("Dave",))
197
    # Can rollback to this savepoint
198
```
199

200
### Two-Phase Commit
201

202
Support for distributed transactions using PostgreSQL's two-phase commit protocol for coordination across multiple databases.
203

204
```python { .api }
205
class Connection:
206
    def tpc_begin(self, xid: Xid) -> None:
207
        """Begin two-phase commit transaction"""
208
        
209
    def tpc_prepare(self) -> None:
210
        """Prepare two-phase commit transaction"""
211
        
212
    def tpc_commit(self, xid: Xid | None = None) -> None:
213
        """Commit prepared transaction"""
214
        
215
    def tpc_rollback(self, xid: Xid | None = None) -> None:
216
        """Rollback prepared transaction"""
217
        
218
    def tpc_recover(self) -> list[Xid]:
219
        """Get list of prepared transaction IDs"""
220

221
class Xid:
222
    def __init__(
223
        self,
224
        format_id: int,
225
        gtrid: str,
226
        bqual: str = ""
227
    ):
228
        """
229
        Transaction identifier for two-phase commit.
230
        
231
        Args:
232
            format_id: Format identifier
233
            gtrid: Global transaction identifier
234
            bqual: Branch qualifier
235
        """
236
        
237
    @property
238
    def format_id(self) -> int: ...
239
    @property
240
    def gtrid(self) -> str: ...
241
    @property
242
    def bqual(self) -> str: ...
243
```
244

245
### Query Execution Methods
246

247
Execute queries directly on connections with automatic cursor management and result handling.
248

249
```python { .api }
250
class Connection:
251
    def execute(
252
        self,
253
        query,
254
        params=None,
255
        *,
256
        prepare: bool | None = None,
257
        binary: bool | None = None
258
    ) -> Cursor:
259
        """
260
        Execute query and return cursor with results.
261
        
262
        Args:
263
            query: SQL query string or sql.Composable
264
            params: Query parameters
265
            prepare: Use prepared statements
266
            binary: Use binary format for results
267
            
268
        Returns:
269
            Cursor with query results
270
        """
271

272
class AsyncConnection:
273
    async def execute(
274
        self,
275
        query,
276
        params=None,
277
        *,
278
        prepare: bool | None = None,
279
        binary: bool | None = None
280
    ) -> AsyncCursor:
281
        """Async version of Connection.execute()"""
282
```
283

284
### Connection Information Access
285

286
Access detailed connection and server information including database parameters, server version, and connection status.
287

288
```python { .api }
289
@property
290
def info(self) -> ConnectionInfo:
291
    """Connection information and server details"""
292

293
class ConnectionInfo:
294
    @property
295
    def dsn(self) -> str:
296
        """Data source name (connection string)"""
297
        
298
    @property
299
    def status(self) -> int:
300
        """Connection status code"""
301
        
302
    @property
303
    def transaction_status(self) -> int:
304
        """Current transaction status"""
305
        
306
    @property
307
    def pipeline_status(self) -> int:
308
        """Pipeline mode status"""
309
        
310
    @property
311
    def encoding(self) -> str:
312
        """Client encoding name"""
313
        
314
    @property
315
    def server_version(self) -> int:
316
        """PostgreSQL server version number"""
317
        
318
    @property
319
    def backend_pid(self) -> int:
320
        """Server backend process ID"""
321
        
322
    @property
323
    def secret_key(self) -> int:
324
        """Secret key for connection cancellation"""
325
        
326
    @property
327
    def timezone(self) -> str | None:
328
        """Server timezone setting"""
329
        
330
    @property
331
    def host(self) -> str | None:
332
        """Database host"""
333
        
334
    @property
335
    def hostaddr(self) -> str | None:
336
        """Database host IP address"""
337
        
338
    @property
339
    def port(self) -> int | None:
340
        """Database port"""
341
        
342
    @property
343
    def dbname(self) -> str | None:
344
        """Database name"""
345
        
346
    @property
347
    def user(self) -> str | None:
348
        """Database user"""
349
        
350
    @property
351
    def password(self) -> str | None:  
352
        """Database password (masked)"""
353
        
354
    def get_parameters(self) -> dict[str, str]:
355
        """Get all server configuration parameters"""
356
        
357
    def parameter_status(self, name: str) -> str | None:
358
        """Get specific server parameter value"""
359
```
360

361
### Notification Handling
362

363
Handle PostgreSQL LISTEN/NOTIFY messages for real-time communication between database clients.
364

365
```python { .api }
366
class Connection:
367
    def add_notify_handler(self, callback: Callable[[Notify], None]) -> None:
368
        """Add notification message handler"""
369
        
370
    def remove_notify_handler(self, callback: Callable[[Notify], None]) -> None:
371
        """Remove notification message handler"""
372
        
373
    def notifies(self) -> list[Notify]:
374
        """Get pending notification messages"""
375

376
class Notify:
377
    @property
378
    def channel(self) -> str:
379
        """Notification channel name"""
380
        
381
    @property
382
    def payload(self) -> str:
383
        """Notification message payload"""
384
        
385
    @property
386
    def pid(self) -> int:
387
        """Process ID of notifying backend"""
388
```
389

390
### Adapter Configuration
391

392
Configure type adapters and customize how Python objects are converted to/from PostgreSQL data types.
393

394
```python { .api }
395
@property
396
def adapters(self) -> AdaptersMap:
397
    """Type adapters registry for this connection"""
398

399
class AdaptersMap:
400
    def register_loader(self, oid: int, loader: Callable) -> None:
401
        """Register custom type loader"""
402
        
403
    def register_dumper(self, cls: type, dumper: Callable) -> None:
404
        """Register custom type dumper"""
405
```
406

407
## Enumeration Types
408

409
```python { .api }
410
from enum import IntEnum
411

412
class IsolationLevel(IntEnum):
413
    READ_UNCOMMITTED = 1
414
    READ_COMMITTED = 2  
415
    REPEATABLE_READ = 3
416
    SERIALIZABLE = 4
417
```