0
# Database Connections and Cursors
1
2
Core database connectivity functionality providing connection management, cursor operations, transaction control, and SQL execution. This implements the DB API 2.0 specification with psycopg2-specific enhancements.
3
4
## Capabilities
5
6
### Connection Creation
7
8
Create database connections using various parameter formats and connection factories.
9
10
```python { .api }
11
def connect(dsn=None, connection_factory=None, cursor_factory=None, **kwargs):
12
"""
13
Create a new database connection.
14
15
Parameters:
16
- dsn (str, optional): Connection string in PostgreSQL format
17
- connection_factory (callable, optional): Custom connection class
18
- cursor_factory (callable, optional): Default cursor factory for this connection
19
- **kwargs: Connection parameters (host, port, database, user, password, etc.)
20
21
Returns:
22
connection: Database connection object
23
"""
24
```
25
26
Usage examples:
27
28
```python
29
# Using connection string
30
conn = psycopg2.connect("host=localhost dbname=test user=postgres")
31
32
# Using keyword arguments
33
conn = psycopg2.connect(
34
host="localhost",
35
port=5432,
36
database="mydb",
37
user="postgres",
38
password="secret"
39
)
40
41
# With custom cursor factory
42
conn = psycopg2.connect(
43
host="localhost",
44
database="mydb",
45
user="postgres",
46
password="secret",
47
cursor_factory=psycopg2.extras.DictCursor
48
)
49
```
50
51
### Connection Utilities
52
53
Utility functions for connection string handling, identifier quoting, and password encryption.
54
55
```python { .api }
56
def parse_dsn(dsn):
57
"""
58
Parse connection string into keyword dictionary.
59
60
Parameters:
61
- dsn (str): Connection string in PostgreSQL format
62
63
Returns:
64
dict: Dictionary of connection parameters
65
66
Raises:
67
ProgrammingError: If DSN is not valid
68
"""
69
70
def make_dsn(dsn=None, **kwargs):
71
"""
72
Convert keywords into connection string.
73
74
Parameters:
75
- dsn (str, optional): Base connection string
76
- **kwargs: Connection parameters to include
77
78
Returns:
79
str: Formatted connection string
80
"""
81
82
def quote_ident(name, scope):
83
"""
84
Quote SQL identifier according to PostgreSQL rules.
85
86
Parameters:
87
- name (str): Identifier to quote
88
- scope (connection/cursor): Connection or cursor for encoding
89
90
Returns:
91
str: Quoted identifier
92
"""
93
94
def encrypt_password(password, user, scope=None, algorithm=None):
95
"""
96
Encrypt password for PostgreSQL authentication.
97
98
Parameters:
99
- password (str): Cleartext password
100
- user (str): Username
101
- scope (connection/cursor, optional): Connection scope
102
- algorithm (str, optional): Encryption algorithm ('md5', 'scram-sha-256')
103
104
Returns:
105
str: Encrypted password
106
"""
107
```
108
109
Usage examples:
110
111
```python
112
from psycopg2.extensions import parse_dsn, make_dsn, quote_ident, encrypt_password
113
114
# Parse connection string
115
params = parse_dsn('dbname=test user=postgres password=secret')
116
print(params) # {'dbname': 'test', 'user': 'postgres', 'password': 'secret'}
117
118
# Parse connection URI
119
uri_params = parse_dsn("postgresql://user@host/db?connect_timeout=10")
120
121
# Build connection string
122
dsn = make_dsn(host='localhost', database='mydb', user='postgres')
123
print(dsn) # "host=localhost database=mydb user=postgres"
124
125
# Override existing DSN
126
new_dsn = make_dsn('host=localhost dbname=test', user='admin')
127
128
# Quote SQL identifiers safely
129
table_name = quote_ident('user-table', conn)
130
print(table_name) # "user-table"
131
132
# Handle embedded quotes
133
complex_name = quote_ident('table"with"quotes', conn)
134
print(complex_name) # "table""with""quotes"
135
136
# Encrypt passwords for storage
137
encrypted = encrypt_password('mypassword', 'username', algorithm='md5')
138
print(encrypted) # md5<hash>
139
140
# Use connection's algorithm setting
141
encrypted = encrypt_password('mypassword', 'username', conn)
142
143
# SCRAM-SHA-256 encryption (requires libpq >= 10)
144
encrypted = encrypt_password('mypassword', 'username', conn, 'scram-sha-256')
145
```
146
147
### Connection Object
148
149
Database connection providing transaction management, cursor creation, and connection control.
150
151
```python { .api }
152
class connection:
153
def cursor(self, name=None, cursor_factory=None, scrollable=None, withhold=False):
154
"""
155
Create a new cursor for this connection.
156
157
Parameters:
158
- name (str, optional): Server-side cursor name
159
- cursor_factory (callable, optional): Cursor class to instantiate
160
- scrollable (bool, optional): Server cursor scrollability
161
- withhold (bool, optional): Server cursor withhold capability
162
163
Returns:
164
cursor: New cursor object
165
"""
166
167
def commit(self):
168
"""Commit current transaction."""
169
170
def rollback(self):
171
"""Rollback current transaction."""
172
173
def close(self):
174
"""Close the connection."""
175
176
def set_isolation_level(self, level):
177
"""
178
Set transaction isolation level.
179
180
Parameters:
181
- level (int): Isolation level constant
182
"""
183
184
def set_client_encoding(self, encoding):
185
"""
186
Set client encoding.
187
188
Parameters:
189
- encoding (str): Encoding name
190
"""
191
192
def cancel(self):
193
"""Cancel current operation."""
194
195
def reset(self):
196
"""Reset connection to initial state."""
197
198
# Properties
199
@property
200
def closed(self):
201
"""Connection closed status (0=open, >0=closed)."""
202
203
@property
204
def status(self):
205
"""Connection status constant."""
206
207
@property
208
def encoding(self):
209
"""Current client encoding."""
210
211
@property
212
def isolation_level(self):
213
"""Current isolation level."""
214
215
@property
216
def autocommit(self):
217
"""Autocommit mode status."""
218
219
@autocommit.setter
220
def autocommit(self, value):
221
"""Set autocommit mode."""
222
```
223
224
### Cursor Object
225
226
Database cursor for executing SQL statements and fetching results.
227
228
```python { .api }
229
class cursor:
230
def execute(self, query, vars=None):
231
"""
232
Execute SQL statement.
233
234
Parameters:
235
- query (str): SQL statement with optional placeholders
236
- vars (sequence/dict, optional): Parameter values
237
"""
238
239
def executemany(self, query, vars_list):
240
"""
241
Execute SQL statement multiple times.
242
243
Parameters:
244
- query (str): SQL statement with placeholders
245
- vars_list (sequence): Sequence of parameter tuples/dicts
246
"""
247
248
def fetchone(self):
249
"""
250
Fetch next row.
251
252
Returns:
253
tuple/None: Next row or None if no more rows
254
"""
255
256
def fetchmany(self, size=None):
257
"""
258
Fetch multiple rows.
259
260
Parameters:
261
- size (int, optional): Number of rows to fetch
262
263
Returns:
264
list: List of row tuples
265
"""
266
267
def fetchall(self):
268
"""
269
Fetch all remaining rows.
270
271
Returns:
272
list: List of all remaining row tuples
273
"""
274
275
def close(self):
276
"""Close the cursor."""
277
278
def callproc(self, procname, parameters=None):
279
"""
280
Call stored procedure.
281
282
Parameters:
283
- procname (str): Procedure name
284
- parameters (sequence, optional): Procedure parameters
285
286
Returns:
287
sequence: Modified parameters
288
"""
289
290
def copy_from(self, file, table, sep='\t', null='\\N', size=8192, columns=None):
291
"""
292
Copy data from file to table.
293
294
Parameters:
295
- file: File-like object to read from
296
- table (str): Target table name
297
- sep (str): Field separator
298
- null (str): NULL representation
299
- size (int): Buffer size
300
- columns (sequence, optional): Column names
301
"""
302
303
def copy_to(self, file, table, sep='\t', null='\\N', columns=None):
304
"""
305
Copy table data to file.
306
307
Parameters:
308
- file: File-like object to write to
309
- table (str): Source table name
310
- sep (str): Field separator
311
- null (str): NULL representation
312
- columns (sequence, optional): Column names
313
"""
314
315
def copy_expert(self, sql, file, size=8192):
316
"""
317
Execute COPY command with expert control.
318
319
Parameters:
320
- sql (str): COPY command
321
- file: File-like object
322
- size (int): Buffer size
323
"""
324
325
def mogrify(self, operation, parameters=None):
326
"""
327
Return formatted query string.
328
329
Parameters:
330
- operation (str): SQL statement with placeholders
331
- parameters (sequence/dict, optional): Parameter values
332
333
Returns:
334
bytes: Formatted query string
335
"""
336
337
def setinputsizes(self, sizes):
338
"""Set input sizes (DB API 2.0 compliance - no-op)."""
339
340
def setoutputsize(self, size, column=None):
341
"""Set output size (DB API 2.0 compliance - no-op)."""
342
343
# Properties
344
@property
345
def description(self):
346
"""
347
Cursor result description.
348
349
Returns:
350
list/None: List of column descriptors or None
351
"""
352
353
@property
354
def rowcount(self):
355
"""Number of rows affected by last execute."""
356
357
@property
358
def rownumber(self):
359
"""Current 0-based row number."""
360
361
@property
362
def lastrowid(self):
363
"""Last inserted row ID (not supported by PostgreSQL)."""
364
365
@property
366
def query(self):
367
"""Last executed query as bytes."""
368
369
@property
370
def statusmessage(self):
371
"""Status message from last command."""
372
373
@property
374
def closed(self):
375
"""Cursor closed status."""
376
377
@property
378
def name(self):
379
"""Server-side cursor name."""
380
381
@property
382
def scrollable(self):
383
"""Server cursor scrollability."""
384
385
@property
386
def withhold(self):
387
"""Server cursor withhold capability."""
388
```
389
390
### Transaction Management
391
392
```python
393
# Basic transaction control
394
conn = psycopg2.connect(...)
395
396
# Explicit transaction
397
conn.commit() # Commit current transaction
398
conn.rollback() # Rollback current transaction
399
400
# Autocommit mode
401
conn.autocommit = True # Enable autocommit
402
conn.autocommit = False # Disable autocommit (default)
403
404
# Isolation levels
405
conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_SERIALIZABLE)
406
407
# Context manager (automatic commit/rollback)
408
with conn:
409
cur = conn.cursor()
410
cur.execute("INSERT INTO table VALUES (%s)", (value,))
411
# Automatic commit on success, rollback on exception
412
```
413
414
### Server-Side Cursors
415
416
```python
417
# Named cursor for large result sets
418
cur = conn.cursor('my_cursor') # Server-side cursor
419
cur.execute("SELECT * FROM large_table")
420
421
# Iterate through results without loading all into memory
422
for row in cur:
423
process_row(row)
424
425
# Scrollable server cursor
426
cur = conn.cursor('scrollable_cursor', scrollable=True)
427
cur.execute("SELECT * FROM table ORDER BY id")
428
cur.scroll(100) # Skip 100 rows
429
row = cur.fetchone()
430
431
# Cursor with holdability
432
cur = conn.cursor('hold_cursor', withhold=True)
433
```
434
435
### Asynchronous Connections
436
437
```python
438
# Create async connection
439
conn = psycopg2.connect(..., async_=True)
440
441
# Check connection state
442
if conn.poll() == psycopg2.extensions.POLL_OK:
443
print("Connection ready")
444
445
# Async query execution
446
cur = conn.cursor()
447
cur.execute("SELECT * FROM table")
448
449
# Poll for completion
450
while True:
451
state = conn.poll()
452
if state == psycopg2.extensions.POLL_OK:
453
results = cur.fetchall()
454
break
455
elif state == psycopg2.extensions.POLL_READ:
456
# Wait for read
457
select.select([conn.fileno()], [], [])
458
elif state == psycopg2.extensions.POLL_WRITE:
459
# Wait for write
460
select.select([], [conn.fileno()], [])
461
```
462
463
## Types
464
465
### Connection Status Constants
466
467
```python { .api }
468
STATUS_SETUP: int = 0 # Connection being set up
469
STATUS_READY: int = 1 # Connection ready for commands
470
STATUS_BEGIN: int = 2 # Connection in transaction block
471
STATUS_IN_TRANSACTION: int = 2 # Alias for STATUS_BEGIN
472
STATUS_PREPARED: int = 5 # Connection with prepared transaction
473
```
474
475
### Polling Constants
476
477
```python { .api }
478
POLL_OK: int = 0 # Operation completed
479
POLL_READ: int = 1 # Wait for read
480
POLL_WRITE: int = 2 # Wait for write
481
POLL_ERROR: int = 3 # Error occurred
482
```
483
484
### Transaction Status Constants
485
486
```python { .api }
487
TRANSACTION_STATUS_IDLE: int = 0 # Not in a transaction
488
TRANSACTION_STATUS_ACTIVE: int = 1 # Command in progress
489
TRANSACTION_STATUS_INTRANS: int = 2 # In transaction block
490
TRANSACTION_STATUS_INERROR: int = 3 # In failed transaction
491
TRANSACTION_STATUS_UNKNOWN: int = 4 # Connection bad
492
```
493
494
### Column Description
495
496
```python { .api }
497
ColumnDescription = tuple[
498
str, # name - column name
499
int, # type_code - PostgreSQL type OID
500
int, # display_size - display size (not used)
501
int, # internal_size - internal size in bytes
502
int, # precision - numeric precision
503
int, # scale - numeric scale
504
bool # null_ok - nullable flag
505
]
506
```