0
# Database Connections and Cursors
1

2
Core database connectivity functionality providing connection management, cursor operations, transaction handling, and the foundation for all database operations in psycopg2.
3

4
## Capabilities
5

6
### Connection Creation
7

8
Create database connections using connection strings or keyword parameters, with support for connection factories and default cursor types.
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
17
    - connection_factory (callable, optional): Custom connection class
18
    - cursor_factory (callable, optional): Default cursor factory
19
    - **kwargs: Connection parameters (host, port, database, user, password, etc.)
20

21
    Returns:
22
    connection: Database connection object
23
    """
24
```
25

26
**Usage Example:**
27

28
```python
29
import psycopg2
30

31
# Using connection string
32
conn = psycopg2.connect("host=localhost dbname=mydb user=myuser password=mypass")
33

34
# Using keyword arguments
35
conn = psycopg2.connect(
36
    host="localhost",
37
    port=5432,
38
    database="mydb", 
39
    user="myuser",
40
    password="mypass"
41
)
42

43
# With custom cursor factory
44
from psycopg2.extras import DictCursor
45
conn = psycopg2.connect(
46
    host="localhost",
47
    database="mydb",
48
    user="myuser", 
49
    password="mypass",
50
    cursor_factory=DictCursor
51
)
52
```
53

54
### Connection Management
55

56
Manage database connections with transaction control, configuration settings, and resource cleanup.
57

58
```python { .api }
59
class connection:
60
    """Database connection object."""
61
    
62
    def cursor(self, name=None, cursor_factory=None):
63
        """
64
        Create a new cursor.
65
        
66
        Parameters:
67
        - name (str, optional): Server-side cursor name
68
        - cursor_factory (callable, optional): Cursor class
69
        
70
        Returns:
71
        cursor: Database cursor object
72
        """
73
    
74
    def commit(self):
75
        """Commit the current transaction."""
76
    
77
    def rollback(self):
78
        """Rollback the current transaction."""
79
    
80
    def close(self):
81
        """Close the connection."""
82
    
83
    def set_isolation_level(self, level):
84
        """
85
        Set transaction isolation level.
86
        
87
        Parameters:
88
        - level (int): Isolation level constant
89
        """
90
    
91
    def set_client_encoding(self, encoding):
92
        """
93
        Set client encoding.
94
        
95
        Parameters:
96
        - encoding (str): Encoding name
97
        """
98
    
99
    def cancel(self):
100
        """Cancel current operation."""
101
    
102
    def reset(self):
103
        """Reset connection to initial state."""
104
    
105
    def set_session(self, isolation_level=None, readonly=None, deferrable=None, autocommit=None):
106
        """
107
        Set session parameters.
108
        
109
        Parameters:
110
        - isolation_level (int, optional): Transaction isolation level
111
        - readonly (bool, optional): Read-only mode
112
        - deferrable (bool, optional): Deferrable transactions
113
        - autocommit (bool, optional): Autocommit mode
114
        """
115
    
116
    def get_transaction_status(self):
117
        """
118
        Get backend transaction status.
119
        
120
        Returns:
121
        int: Transaction status constant
122
        """
123
    
124
    # Properties
125
    @property
126
    def closed(self) -> int:
127
        """Connection status (0=open, >0=closed)."""
128
    
129
    @property
130
    def status(self) -> int:
131
        """Connection status constant."""
132
    
133
    @property
134
    def autocommit(self) -> bool:
135
        """Autocommit mode."""
136
    
137
    @autocommit.setter
138
    def autocommit(self, value: bool):
139
        """Set autocommit mode."""
140
    
141
    @property
142
    def isolation_level(self) -> int:
143
        """Current isolation level."""
144
    
145
    @property
146
    def encoding(self) -> str:
147
        """Client encoding."""
148
    
149
    @property
150
    def cursor_factory(self):
151
        """Default cursor factory."""
152
    
153
    @cursor_factory.setter
154
    def cursor_factory(self, factory):
155
        """Set default cursor factory."""
156
```
157

158
**Usage Example:**
159

160
```python
161
import psycopg2
162
from psycopg2.extensions import ISOLATION_LEVEL_SERIALIZABLE
163

164
conn = psycopg2.connect("host=localhost dbname=mydb user=myuser")
165

166
# Transaction management
167
try:
168
    with conn.cursor() as cur:
169
        cur.execute("INSERT INTO users (name) VALUES (%s)", ("Alice",))
170
        cur.execute("UPDATE accounts SET balance = balance - 100 WHERE user_id = %s", (1,))
171
    conn.commit()
172
except Exception as e:
173
    conn.rollback()
174
    raise
175

176
# Configuration
177
conn.set_isolation_level(ISOLATION_LEVEL_SERIALIZABLE)
178
conn.autocommit = True
179
print(f"Connection status: {conn.status}")
180
print(f"Encoding: {conn.encoding}")
181

182
conn.close()
183
```
184

185
### Cursor Operations
186

187
Execute SQL queries and fetch results with support for parameterized queries, batch operations, and various result formats.
188

189
```python { .api }
190
class cursor:
191
    """Database cursor object."""
192
    
193
    def execute(self, query, vars=None):
194
        """
195
        Execute a database operation.
196
        
197
        Parameters:
198
        - query (str): SQL query
199
        - vars (sequence, optional): Query parameters
200
        """
201
    
202
    def executemany(self, query, vars_list):
203
        """
204
        Execute query multiple times with different parameters.
205
        
206
        Parameters:
207
        - query (str): SQL query  
208
        - vars_list (sequence): List of parameter tuples
209
        """
210
    
211
    def fetchone(self):
212
        """
213
        Fetch the next row of query result.
214
        
215
        Returns:
216
        tuple or None: Next row or None if no more rows
217
        """
218
    
219
    def fetchmany(self, size=None):
220
        """
221
        Fetch multiple rows of query result.
222
        
223
        Parameters:
224
        - size (int, optional): Number of rows to fetch
225
        
226
        Returns:
227
        list: List of row tuples
228
        """
229
    
230
    def fetchall(self):
231
        """
232
        Fetch all remaining rows of query result.
233
        
234
        Returns:
235
        list: List of all remaining row tuples
236
        """
237
    
238
    def callproc(self, procname, parameters=None):
239
        """
240
        Call a stored procedure.
241
        
242
        Parameters:
243
        - procname (str): Procedure name
244
        - parameters (sequence, optional): Procedure parameters
245
        
246
        Returns:
247
        sequence: Modified parameters
248
        """
249
    
250
    def close(self):
251
        """Close the cursor."""
252
    
253
    def copy_from(self, file, table, sep='\t', null='\\N', size=8192, columns=None):
254
        """
255
        Copy data from file to table.
256
        
257
        Parameters:
258
        - file: File-like object
259
        - table (str): Table name
260
        - sep (str): Field separator
261
        - null (str): NULL representation
262
        - size (int): Buffer size
263
        - columns (sequence, optional): Column names
264
        """
265
    
266
    def copy_to(self, file, table, sep='\t', null='\\N', columns=None):
267
        """
268
        Copy data from table to file.
269
        
270
        Parameters:
271
        - file: File-like object  
272
        - table (str): Table name
273
        - sep (str): Field separator
274
        - null (str): NULL representation
275
        - columns (sequence, optional): Column names
276
        """
277
    
278
    def copy_expert(self, sql, file, size=8192):
279
        """
280
        Execute COPY command with custom SQL.
281
        
282
        Parameters:
283
        - sql (str): COPY SQL command
284
        - file: File-like object
285
        - size (int): Buffer size
286
        """
287
    
288
    def mogrify(self, operation, parameters=None):
289
        """
290
        Return formatted query string.
291
        
292
        Parameters:
293
        - operation (str): SQL query
294
        - parameters (sequence, optional): Query parameters
295
        
296
        Returns:
297
        bytes: Formatted query
298
        """
299
    
300
    def scroll(self, value, mode='relative'):
301
        """
302
        Move cursor position.
303
        
304
        Parameters:
305
        - value (int): Number of rows to move
306
        - mode (str): 'relative' or 'absolute' positioning
307
        """
308
    
309
    def setinputsizes(self, sizes):
310
        """Set input parameter sizes (no-op in psycopg2)."""
311
    
312
    def setoutputsize(self, size, column=None):
313
        """Set output column size (no-op in psycopg2)."""
314
    
315
    # Properties
316
    @property
317
    def description(self):
318
        """Column information for last query."""
319
    
320
    @property
321
    def rowcount(self) -> int:
322
        """Number of rows affected by last operation."""
323
    
324
    @property
325
    def rownumber(self) -> int:
326
        """Current row number."""
327
    
328
    @property
329
    def lastrowid(self):
330
        """Last inserted row ID (PostgreSQL doesn't support this)."""
331
    
332
    @property
333
    def query(self):
334
        """Last executed query."""
335
    
336
    @property
337
    def statusmessage(self) -> str:
338
        """Status message from last operation."""
339
    
340
    @property
341
    def connection(self):
342
        """Connection this cursor belongs to."""
343
    
344
    @property
345
    def name(self) -> str:
346
        """Server-side cursor name."""
347
    
348
    @property
349
    def scrollable(self) -> bool:
350
        """Whether cursor is scrollable."""
351
    
352
    @property
353
    def withhold(self) -> bool:
354
        """Whether cursor is WITH HOLD."""
355
```
356

357
**Usage Example:**
358

359
```python
360
import psycopg2
361

362
conn = psycopg2.connect("host=localhost dbname=mydb user=myuser")
363

364
# Basic query execution
365
with conn.cursor() as cur:
366
    cur.execute("SELECT id, name, email FROM users WHERE age > %s", (25,))
367
    
368
    # Fetch results
369
    row = cur.fetchone()
370
    while row:
371
        print(f"ID: {row[0]}, Name: {row[1]}, Email: {row[2]}")
372
        row = cur.fetchone()
373

374
# Batch operations
375
with conn.cursor() as cur:
376
    users = [
377
        ("Alice", "alice@example.com", 28),
378
        ("Bob", "bob@example.com", 32),
379
        ("Charlie", "charlie@example.com", 24)
380
    ]
381
    cur.executemany(
382
        "INSERT INTO users (name, email, age) VALUES (%s, %s, %s)",
383
        users
384
    )
385
    print(f"Inserted {cur.rowcount} rows")
386

387
# COPY operations
388
import io
389
data = "1\tAlice\talice@example.com\n2\tBob\tbob@example.com\n"
390
with conn.cursor() as cur:
391
    cur.copy_from(
392
        io.StringIO(data),
393
        'users',
394
        columns=('id', 'name', 'email'),
395
        sep='\t'
396
    )
397

398
conn.commit()
399
conn.close()
400
```
401

402
### Server-Side Cursors
403

404
Named cursors that execute on the PostgreSQL server, enabling efficient processing of large result sets without loading all data into memory.
405

406
```python { .api }
407
# Server-side cursor creation
408
def cursor(name, cursor_factory=None, scrollable=None, withhold=False):
409
    """
410
    Create named server-side cursor.
411
    
412
    Parameters:
413
    - name (str): Cursor name
414
    - cursor_factory (callable, optional): Cursor class
415
    - scrollable (bool, optional): Enable scrolling
416
    - withhold (bool): Preserve cursor after transaction
417
    
418
    Returns:
419
    cursor: Server-side cursor
420
    """
421
```
422

423
**Usage Example:**
424

425
```python
426
import psycopg2
427

428
conn = psycopg2.connect("host=localhost dbname=mydb user=myuser")
429

430
# Server-side cursor for large result sets
431
with conn.cursor("large_query") as cur:
432
    cur.execute("SELECT * FROM large_table ORDER BY id")
433
    
434
    # Fetch in chunks
435
    while True:
436
        rows = cur.fetchmany(1000)
437
        if not rows:
438
            break
439
        
440
        for row in rows:
441
            # Process row without loading entire result set
442
            process_row(row)
443

444
conn.close()
445
```
446

447
### Context Managers
448

449
Automatic resource management using Python context managers for connections and cursors.
450

451
**Usage Example:**
452

453
```python
454
import psycopg2
455

456
# Connection context manager
457
with psycopg2.connect("host=localhost dbname=mydb user=myuser") as conn:
458
    with conn.cursor() as cur:
459
        cur.execute("SELECT * FROM users")
460
        users = cur.fetchall()
461
    # Cursor automatically closed
462
    
463
    # Transaction automatically committed if no exception,
464
    # or rolled back if exception occurs
465
# Connection automatically closed
466
```
467

468
## Types
469

470
### Connection Status Constants
471

472
```python { .api }
473
STATUS_SETUP: int  # 0 - Connection being set up
474
STATUS_READY: int  # 1 - Connection ready for queries  
475
STATUS_BEGIN: int  # 2 - Transaction in progress
476
STATUS_SYNC: int   # 3 - Synchronizing connection
477
STATUS_ASYNC: int  # 4 - Asynchronous connection
478
STATUS_PREPARED: int  # 5 - Prepared for async operation
479
STATUS_IN_TRANSACTION: int  # Alias for STATUS_BEGIN
480
```
481

482
### Transaction Status Constants
483

484
```python { .api }
485
TRANSACTION_STATUS_IDLE: int     # 0 - Outside transaction
486
TRANSACTION_STATUS_ACTIVE: int   # 1 - Command in progress
487
TRANSACTION_STATUS_INTRANS: int  # 2 - In transaction block
488
TRANSACTION_STATUS_INERROR: int  # 3 - In failed transaction
489
TRANSACTION_STATUS_UNKNOWN: int  # 4 - Connection bad
490
```
491

492
### Isolation Level Constants
493

494
```python { .api }
495
ISOLATION_LEVEL_AUTOCOMMIT: int         # 0
496
ISOLATION_LEVEL_READ_UNCOMMITTED: int   # 4  
497
ISOLATION_LEVEL_READ_COMMITTED: int     # 1
498
ISOLATION_LEVEL_REPEATABLE_READ: int    # 2
499
ISOLATION_LEVEL_SERIALIZABLE: int       # 3
500
ISOLATION_LEVEL_DEFAULT: None           # None
501
```