0
# Core SQLAlchemy Integration
1
2
Main dialect class and connection management providing SQLAlchemy compatibility for DuckDB databases. This module implements the core SQLAlchemy dialect interface with DuckDB-specific optimizations and features.
3
4
## Capabilities
5
6
### Main Dialect Class
7
8
The primary SQLAlchemy dialect class that extends PostgreSQL dialect functionality for DuckDB compatibility.
9
10
```python { .api }
11
class Dialect(PGDialect_psycopg2):
12
"""
13
Main SQLAlchemy dialect for DuckDB.
14
15
Extends PostgreSQL dialect to provide DuckDB-specific functionality
16
while maintaining SQLAlchemy compatibility.
17
"""
18
name = "duckdb"
19
driver = "duckdb_engine"
20
supports_statement_cache = False
21
supports_comments: bool
22
supports_sane_rowcount = False
23
supports_server_side_cursors = False
24
div_is_floordiv = False
25
26
def __init__(self, *args, **kwargs): ...
27
```
28
29
### Connection Management
30
31
Methods for creating and managing DuckDB connections through SQLAlchemy.
32
33
```python { .api }
34
def connect(self, *cargs, **cparams):
35
"""
36
Create a DuckDB connection with configuration.
37
38
Parameters:
39
- *cargs: Positional arguments for DuckDB connection
40
- **cparams: Keyword arguments including:
41
- preload_extensions: List of extensions to load
42
- register_filesystems: List of filesystems to register
43
- config: Dictionary of DuckDB configuration settings
44
- url_config: Configuration from connection URL query parameters
45
46
Returns:
47
ConnectionWrapper: Wrapped DuckDB connection
48
"""
49
50
def create_connect_args(self, url):
51
"""
52
Parse SQLAlchemy URL and create DuckDB connection arguments.
53
54
Parameters:
55
- url (URL): SQLAlchemy connection URL
56
57
Returns:
58
tuple: (args, kwargs) for DuckDB connection
59
"""
60
61
@classmethod
62
def get_pool_class(cls, url):
63
"""
64
Return appropriate connection pool class based on database type.
65
66
Parameters:
67
- url (URL): SQLAlchemy connection URL
68
69
Returns:
70
Type[Pool]: SingletonThreadPool for :memory:, QueuePool for files
71
"""
72
```
73
74
### Database API Compatibility
75
76
DBAPI compatibility layer providing standard database interface.
77
78
```python { .api }
79
@staticmethod
80
def dbapi(**kwargs):
81
"""
82
Return DBAPI compatibility class.
83
84
Returns:
85
Type[DBAPI]: Database API compatibility class
86
"""
87
88
@classmethod
89
def import_dbapi(cls):
90
"""
91
Import and return DBAPI class.
92
93
Returns:
94
Type[DBAPI]: Database API class
95
"""
96
```
97
98
### Schema Operations
99
100
Methods for database schema inspection and table operations.
101
102
```python { .api }
103
def get_table_names(self, connection, schema=None, **kw):
104
"""
105
Get list of table names in schema.
106
107
Parameters:
108
- connection: Database connection
109
- schema (str, optional): Schema name, supports database.schema format
110
111
Returns:
112
List[str]: Table names
113
"""
114
115
def get_view_names(self, connection, schema=None, include=None, **kw):
116
"""
117
Get list of view names in schema.
118
119
Parameters:
120
- connection: Database connection
121
- schema (str, optional): Schema name
122
- include: Filter for views to include
123
124
Returns:
125
List[str]: View names
126
"""
127
128
def get_schema_names(self, connection, **kw):
129
"""
130
Get list of schema names with database prefixes.
131
132
Returns unquoted database_name.schema_name unless either contains
133
spaces or double quotes, in which case they are properly quoted.
134
135
Parameters:
136
- connection: Database connection
137
138
Returns:
139
List[str]: Schema names in database.schema format
140
"""
141
142
def has_table(self, connection, table_name, schema=None, **kw):
143
"""
144
Check if table exists in schema.
145
146
Parameters:
147
- connection: Database connection
148
- table_name (str): Name of table to check
149
- schema (str, optional): Schema name
150
151
Returns:
152
bool: True if table exists, False otherwise
153
"""
154
155
def get_table_oid(self, connection, table_name, schema=None, **kw):
156
"""
157
Get table OID for schema operations.
158
159
Parameters:
160
- connection: Database connection
161
- table_name (str): Table name
162
- schema (str, optional): Schema name
163
164
Returns:
165
int: Table OID
166
167
Raises:
168
NoSuchTableError: If table does not exist
169
"""
170
```
171
172
### Index Operations
173
174
Index-related operations (currently limited in DuckDB).
175
176
```python { .api }
177
def get_indexes(self, connection, table_name, schema=None, **kw):
178
"""
179
Get table indexes (returns empty list, indexes not yet supported).
180
181
Parameters:
182
- connection: Database connection
183
- table_name (str): Table name
184
- schema (str, optional): Schema name
185
186
Returns:
187
List: Empty list (indexes not supported)
188
"""
189
190
def get_multi_indexes(self, connection, schema=None, filter_names=None, **kw):
191
"""
192
Get multiple table indexes (returns empty iterator).
193
194
Parameters:
195
- connection: Database connection
196
- schema (str, optional): Schema name
197
- filter_names (Collection[str], optional): Filter for index names
198
199
Returns:
200
Iterable[Tuple]: Empty iterator
201
"""
202
```
203
204
### Transaction Management
205
206
Transaction handling with DuckDB-specific error handling.
207
208
```python { .api }
209
def do_begin(self, connection):
210
"""
211
Begin a transaction.
212
213
Parameters:
214
- connection: Database connection
215
"""
216
217
def do_rollback(self, connection):
218
"""
219
Rollback transaction with DuckDB-specific error handling.
220
221
Parameters:
222
- connection: Database connection
223
"""
224
```
225
226
### Column Operations
227
228
Multi-column reflection for complex schema operations.
229
230
```python { .api }
231
def get_multi_columns(self, connection, schema=None, filter_names=None, scope=None, kind=None, **kw):
232
"""
233
Get column information for multiple tables.
234
235
Advanced method for retrieving column metadata across multiple tables,
236
with optimizations for DuckDB's cursor handling.
237
238
Parameters:
239
- connection: Database connection
240
- schema (str, optional): Schema name to filter by
241
- filter_names (Set[str], optional): Set of table names to include
242
- scope (str, optional): Scope for column retrieval
243
- kind (Tuple[str, ...], optional): Table kinds to include
244
- **kw: Additional keyword arguments
245
246
Returns:
247
List: Column information for matching tables
248
"""
249
```
250
251
### Type System Integration
252
253
Type descriptor and column specification methods.
254
255
```python { .api }
256
def type_descriptor(self, typeobj):
257
"""
258
Convert SQLAlchemy types to DuckDB-compatible types.
259
260
Parameters:
261
- typeobj (Type[TypeEngine]): SQLAlchemy type class
262
263
Returns:
264
TypeEngine: DuckDB-compatible type instance
265
"""
266
267
# Class attributes
268
colspecs: Dict[Type[TypeEngine], TypeEngine]
269
ischema_names: Dict[str, Type[TypeEngine]]
270
```
271
272
### Identifier Preparation
273
274
SQL identifier quoting and preparation for DuckDB.
275
276
```python { .api }
277
class DuckDBIdentifierPreparer(PGIdentifierPreparer):
278
"""
279
Handles SQL identifier quoting and preparation for DuckDB.
280
281
Extends PostgreSQL identifier preparation with DuckDB-specific
282
reserved word handling and database.schema name separation.
283
"""
284
285
def __init__(self, dialect, **kwargs): ...
286
287
def format_schema(self, name):
288
"""
289
Prepare a quoted schema name with database prefix support.
290
291
Parameters:
292
- name (str): Schema name, optionally with database prefix
293
294
Returns:
295
str: Properly quoted schema identifier
296
"""
297
298
def quote_schema(self, schema, force=None):
299
"""
300
Conditionally quote a schema name.
301
302
Parameters:
303
- schema (str): Schema name to quote
304
- force: Unused parameter for compatibility
305
306
Returns:
307
str: Quoted schema name
308
"""
309
```
310
311
### Database Inspection
312
313
Schema inspection capabilities for DuckDB databases.
314
315
```python { .api }
316
class DuckDBInspector(PGInspector):
317
"""
318
Database schema inspection for DuckDB.
319
320
Extends PostgreSQL inspector with DuckDB-specific adaptations
321
for constraint and metadata handling.
322
"""
323
324
def get_check_constraints(self, table_name, schema=None, **kw):
325
"""
326
Get check constraints for table (limited DuckDB support).
327
328
Parameters:
329
- table_name (str): Table name
330
- schema (str, optional): Schema name
331
- **kw: Additional arguments
332
333
Returns:
334
List[Dict[str, Any]]: Check constraint information
335
336
Raises:
337
NotImplementedError: If DuckDB doesn't support the operation
338
"""
339
```
340
341
### Custom Type Handling
342
343
DuckDB-specific type handling for null types and JSON processing.
344
345
```python { .api }
346
class DuckDBNullType(sqltypes.NullType):
347
"""
348
Custom null type with JSON processing support.
349
350
Extends SQLAlchemy's NullType to handle DuckDB-specific
351
type processing, particularly for JSON columns.
352
"""
353
354
def result_processor(self, dialect, coltype):
355
"""
356
Process null type results with JSON support.
357
358
Parameters:
359
- dialect (RootDialect): SQLAlchemy dialect
360
- coltype (TypeEngine): Column type
361
362
Returns:
363
Optional[Callable]: Result processor function
364
"""
365
```
366
367
## Usage Examples
368
369
### Basic Connection
370
371
```python
372
from sqlalchemy import create_engine
373
from duckdb_engine import Dialect
374
375
# Create engine
376
engine = create_engine('duckdb:///mydb.db')
377
378
# With configuration
379
engine = create_engine(
380
'duckdb:///mydb.db',
381
connect_args={
382
'config': {'threads': 4, 'memory_limit': '2GB'},
383
'preload_extensions': ['spatial', 'json'],
384
'register_filesystems': [my_filesystem]
385
}
386
)
387
```
388
389
### Schema Operations
390
391
```python
392
# Get table names
393
with engine.connect() as conn:
394
tables = engine.dialect.get_table_names(conn)
395
views = engine.dialect.get_view_names(conn)
396
schemas = engine.dialect.get_schema_names(conn)
397
398
# Check if table exists
399
exists = engine.dialect.has_table(conn, 'my_table')
400
```