0
# Core Engine and Connectivity
1
2
Database engine creation, connection management, URL handling, connection pooling, and transaction management. The Core Engine provides the foundation for all SQLAlchemy database operations.
3
4
## Capabilities
5
6
### Engine Creation
7
8
Create database engines for connection management and SQL execution.
9
10
```python { .api }
11
def create_engine(url, **kwargs):
12
"""
13
Create a database engine.
14
15
Parameters:
16
- url: str or URL, database connection URL
17
- echo: bool, log all SQL statements (default False)
18
- pool_size: int, connection pool size (default 5)
19
- max_overflow: int, maximum pool overflow (default 10)
20
- pool_timeout: int, connection timeout in seconds (default 30)
21
- pool_recycle: int, connection recycle time (default -1)
22
- isolation_level: str, transaction isolation level
23
24
Returns:
25
Engine: Database engine instance
26
"""
27
28
def create_mock_engine(url, executor, **kwargs):
29
"""
30
Create a mock engine for testing SQL generation.
31
32
Parameters:
33
- url: str or URL, database URL for dialect selection
34
- executor: callable, function to handle SQL execution
35
36
Returns:
37
Engine: Mock engine that captures SQL without database connection
38
"""
39
40
def engine_from_config(configuration, prefix='sqlalchemy.', **kwargs):
41
"""
42
Create engine from configuration dictionary.
43
44
Parameters:
45
- configuration: dict, configuration parameters
46
- prefix: str, prefix for configuration keys
47
48
Returns:
49
Engine: Configured database engine
50
"""
51
```
52
53
### URL Construction and Parsing
54
55
Handle database connection URLs with parsing and construction utilities.
56
57
```python { .api }
58
def make_url(name_or_url):
59
"""
60
Create URL object from string or existing URL.
61
62
Parameters:
63
- name_or_url: str or URL, connection specification
64
65
Returns:
66
URL: Parsed URL object
67
"""
68
69
class URL:
70
"""Database connection URL representation."""
71
72
def __init__(self, drivername, username=None, password=None,
73
host=None, port=None, database=None, query=None):
74
"""
75
Create URL object.
76
77
Parameters:
78
- drivername: str, database driver name
79
- username: str, database username
80
- password: str, database password
81
- host: str, database host
82
- port: int, database port
83
- database: str, database name
84
- query: dict, additional query parameters
85
"""
86
87
def render_as_string(self, hide_password=True):
88
"""
89
Render URL as connection string.
90
91
Parameters:
92
- hide_password: bool, mask password in output
93
94
Returns:
95
str: Connection string representation
96
"""
97
```
98
99
### Engine Operations
100
101
Core engine functionality for connection management and SQL execution.
102
103
```python { .api }
104
class Engine:
105
"""Database engine providing connection management and execution."""
106
107
def connect(self):
108
"""
109
Create new database connection.
110
111
Returns:
112
Connection: New database connection
113
"""
114
115
def execute(self, statement, parameters=None):
116
"""
117
Execute SQL statement with automatic connection management.
118
119
Parameters:
120
- statement: str or executable, SQL statement
121
- parameters: dict or sequence, bound parameters
122
123
Returns:
124
Result: Query results
125
"""
126
127
def begin(self):
128
"""
129
Begin transaction with automatic connection management.
130
131
Returns:
132
Transaction: Transaction context manager
133
"""
134
135
def dispose(self):
136
"""Close all connections and dispose of connection pool."""
137
138
@property
139
def dialect(self):
140
"""Database dialect for this engine."""
141
142
@property
143
def pool(self):
144
"""Connection pool for this engine."""
145
```
146
147
### Connection Management
148
149
Direct connection handling with transaction support.
150
151
```python { .api }
152
class Connection:
153
"""Database connection with transaction support."""
154
155
def execute(self, statement, parameters=None):
156
"""
157
Execute SQL statement on this connection.
158
159
Parameters:
160
- statement: str or executable, SQL statement
161
- parameters: dict or sequence, bound parameters
162
163
Returns:
164
Result: Query results
165
"""
166
167
def begin(self):
168
"""
169
Begin transaction on this connection.
170
171
Returns:
172
Transaction: Transaction object
173
"""
174
175
def commit(self):
176
"""Commit current transaction."""
177
178
def rollback(self):
179
"""Rollback current transaction."""
180
181
def close(self):
182
"""Close this connection."""
183
184
def scalar(self, statement, parameters=None):
185
"""
186
Execute statement and return scalar result.
187
188
Parameters:
189
- statement: str or executable, SQL statement
190
- parameters: dict or sequence, bound parameters
191
192
Returns:
193
Any: Single scalar value
194
"""
195
```
196
197
### Transaction Management
198
199
Transaction handling with context manager support and savepoints.
200
201
```python { .api }
202
class Transaction:
203
"""Database transaction with rollback support."""
204
205
def commit(self):
206
"""Commit this transaction."""
207
208
def rollback(self):
209
"""Rollback this transaction."""
210
211
def close(self):
212
"""Close transaction (rollback if not committed)."""
213
214
class NestedTransaction(Transaction):
215
"""Nested transaction using savepoints."""
216
217
def rollback(self):
218
"""Rollback to savepoint."""
219
220
class TwoPhaseTransaction(Transaction):
221
"""Two-phase commit transaction."""
222
223
def prepare(self):
224
"""Prepare transaction for two-phase commit."""
225
```
226
227
### Result Handling
228
229
Query result processing with multiple access patterns.
230
231
```python { .api }
232
class Result:
233
"""Query result with row iteration and data access."""
234
235
def fetchone(self):
236
"""
237
Fetch next row.
238
239
Returns:
240
Row or None: Next row or None if no more rows
241
"""
242
243
def fetchmany(self, size=None):
244
"""
245
Fetch multiple rows.
246
247
Parameters:
248
- size: int, number of rows to fetch
249
250
Returns:
251
List[Row]: List of rows
252
"""
253
254
def fetchall(self):
255
"""
256
Fetch all remaining rows.
257
258
Returns:
259
List[Row]: All remaining rows
260
"""
261
262
def scalar(self):
263
"""
264
Fetch scalar value from first column of first row.
265
266
Returns:
267
Any: Scalar value or None
268
"""
269
270
def mappings(self):
271
"""
272
Return result as mapping-like objects.
273
274
Returns:
275
MappingResult: Result with dict-like row access
276
"""
277
278
class Row:
279
"""Individual result row with column access."""
280
281
def __getitem__(self, key):
282
"""Get column value by index or name."""
283
284
def __getattr__(self, name):
285
"""Get column value by attribute access."""
286
287
def _asdict(self):
288
"""
289
Convert row to dictionary.
290
291
Returns:
292
dict: Row data as dictionary
293
"""
294
```
295
296
### Connection Pooling
297
298
Connection pool management with various pooling strategies.
299
300
```python { .api }
301
class Pool:
302
"""Base connection pool class."""
303
304
def connect(self):
305
"""Get connection from pool."""
306
307
def dispose(self):
308
"""Dispose all connections in pool."""
309
310
class QueuePool(Pool):
311
"""Thread-safe connection pool with overflow and timeout."""
312
313
def __init__(self, creator, pool_size=5, max_overflow=10,
314
timeout=30, **kwargs):
315
"""
316
Create queue-based connection pool.
317
318
Parameters:
319
- creator: callable, function to create connections
320
- pool_size: int, base pool size
321
- max_overflow: int, maximum overflow connections
322
- timeout: int, connection timeout in seconds
323
"""
324
325
class StaticPool(Pool):
326
"""Single connection pool for lightweight scenarios."""
327
328
class NullPool(Pool):
329
"""No pooling - create new connection for each request."""
330
331
class SingletonThreadPool(Pool):
332
"""One connection per thread pool."""
333
```
334
335
### Database Inspection
336
337
Database schema inspection and reflection capabilities.
338
339
```python { .api }
340
def inspect(subject):
341
"""
342
Inspect database engine, connection, or mapped object.
343
344
Parameters:
345
- subject: Engine, Connection, or mapped class
346
347
Returns:
348
Inspector: Appropriate inspector object
349
"""
350
351
class Inspector:
352
"""Database schema inspector."""
353
354
def get_schema_names(self):
355
"""
356
Get list of schema names.
357
358
Returns:
359
List[str]: Available schema names
360
"""
361
362
def get_table_names(self, schema=None):
363
"""
364
Get list of table names in schema.
365
366
Parameters:
367
- schema: str, schema name (default schema if None)
368
369
Returns:
370
List[str]: Table names in schema
371
"""
372
373
def get_columns(self, table_name, schema=None):
374
"""
375
Get column information for table.
376
377
Parameters:
378
- table_name: str, table name
379
- schema: str, schema name
380
381
Returns:
382
List[dict]: Column information dictionaries
383
"""
384
385
def get_primary_keys(self, table_name, schema=None):
386
"""
387
Get primary key columns for table.
388
389
Parameters:
390
- table_name: str, table name
391
- schema: str, schema name
392
393
Returns:
394
List[str]: Primary key column names
395
"""
396
397
def get_foreign_keys(self, table_name, schema=None):
398
"""
399
Get foreign key constraints for table.
400
401
Parameters:
402
- table_name: str, table name
403
- schema: str, schema name
404
405
Returns:
406
List[dict]: Foreign key constraint information
407
"""
408
```
409
410
## Usage Examples
411
412
### Basic Engine Usage
413
414
```python
415
from sqlalchemy import create_engine, text
416
417
# Create engine
418
engine = create_engine("postgresql://user:pass@localhost/dbname")
419
420
# Execute simple query
421
with engine.connect() as conn:
422
result = conn.execute(text("SELECT version()"))
423
print(result.scalar())
424
425
# Transaction usage
426
with engine.begin() as conn:
427
conn.execute(text("INSERT INTO users (name) VALUES ('John')"))
428
# Automatically committed
429
```
430
431
### Connection Pool Configuration
432
433
```python
434
from sqlalchemy import create_engine
435
436
engine = create_engine(
437
"postgresql://user:pass@localhost/dbname",
438
pool_size=20, # Base pool size
439
max_overflow=0, # No overflow connections
440
pool_timeout=30, # 30 second timeout
441
pool_recycle=3600 # Recycle connections after 1 hour
442
)
443
```
444
445
### Database Inspection
446
447
```python
448
from sqlalchemy import create_engine, inspect
449
450
engine = create_engine("sqlite:///example.db")
451
inspector = inspect(engine)
452
453
# Get table information
454
tables = inspector.get_table_names()
455
for table in tables:
456
columns = inspector.get_columns(table)
457
print(f"Table {table}: {[col['name'] for col in columns]}")
458
```