0
# DuckDB Engine
1

2
A comprehensive SQLAlchemy driver for DuckDB that enables seamless integration between Python applications and DuckDB's analytical database engine. The driver extends PostgreSQL dialect functionality to accommodate DuckDB's specific features while maintaining compatibility with SQLAlchemy 1.3+ and Python 3.9+.
3

4
## Package Information
5

6
- **Package Name**: duckdb-engine
7
- **Language**: Python
8
- **Installation**: `pip install duckdb-engine`
9
- **SQLAlchemy Version**: 1.3.22+
10
- **Python Version**: 3.9+
11
- **DuckDB Version**: 0.5.0+
12

13
## Core Imports
14

15
```python
16
from duckdb_engine import Dialect, ConnectionWrapper, CursorWrapper, DBAPI, DuckDBEngineWarning
17
```
18

19
For SQLAlchemy integration:
20

21
```python
22
from sqlalchemy import create_engine
23
```
24

25
For PostgreSQL-style INSERT operations with DuckDB compatibility:
26

27
```python
28
from duckdb_engine import insert  # Re-export of sqlalchemy.dialects.postgresql.insert
29
```
30

31
## Basic Usage
32

33
```python
34
from sqlalchemy import create_engine, Column, Integer, String, MetaData, Table
35
from sqlalchemy.orm import sessionmaker
36

37
# Create engine with DuckDB
38
engine = create_engine('duckdb:///:memory:')
39

40
# Define a simple table
41
metadata = MetaData()
42
users = Table('users', metadata,
43
    Column('id', Integer, primary_key=True),
44
    Column('name', String(50)),
45
    Column('email', String(100))
46
)
47

48
# Create table
49
metadata.create_all(engine)
50

51
# Insert data
52
with engine.connect() as conn:
53
    conn.execute(users.insert().values(name='John Doe', email='john@example.com'))
54
    conn.commit()
55
    
56
    # Query data
57
    result = conn.execute(users.select()).fetchall()
58
    print(result)
59
```
60

61
## Architecture
62

63
DuckDB Engine implements the SQLAlchemy dialect interface with several key components:
64

65
- **Dialect**: Main SQLAlchemy dialect extending PostgreSQL compatibility
66
- **Connection Management**: Connection and cursor wrappers for DuckDB integration
67
- **Type System**: Comprehensive type mapping including DuckDB-specific types
68
- **Schema Inspection**: Database reflection and metadata capabilities
69
- **Transaction Handling**: Transaction management with DuckDB-specific optimizations
70

71
## Capabilities
72

73
### Core SQLAlchemy Integration
74

75
Main dialect class and connection management providing SQLAlchemy compatibility for DuckDB databases, including connection pooling, transaction management, and schema operations.
76

77
```python { .api }
78
class Dialect(PGDialect_psycopg2):
79
    name = "duckdb"
80
    driver = "duckdb_engine"
81
    
82
    def connect(self, *cargs, **cparams): ...
83
    def create_connect_args(self, url): ...
84
    def get_pool_class(cls, url): ...
85
```
86

87
[Core Integration](./core-integration.md)
88

89
### Data Types and Type System
90

91
Comprehensive type system supporting DuckDB's native types including integers, complex types (STRUCT, MAP, UNION), and extension types with proper SQLAlchemy mapping.
92

93
```python { .api }
94
class Struct(TypeEngine):
95
    def __init__(self, fields=None): ...
96

97
class Map(TypeEngine):
98
    def __init__(self, key_type, value_type): ...
99

100
class Union(TypeEngine):
101
    def __init__(self, fields): ...
102
```
103

104
[Data Types](./data-types.md)
105

106
### Connection and Cursor Management
107

108
Connection and cursor wrapper classes that adapt DuckDB's Python API to SQLAlchemy's expected interface, handling special DuckDB operations and DataFrame registration.
109

110
```python { .api }
111
class ConnectionWrapper:
112
    def __init__(self, c): ...
113
    def cursor(self): ...
114
    def close(self): ...
115

116
class CursorWrapper:
117
    def __init__(self, c, connection_wrapper): ...
118
    def execute(self, statement, parameters=None, context=None): ...
119
    def executemany(self, statement, parameters=None, context=None): ...
120
```
121

122
[Connection Management](./connection-management.md)
123

124
### Configuration and Extensions
125

126
Configuration management for DuckDB settings, extension loading, and filesystem registration with support for MotherDuck and custom configuration options.
127

128
```python { .api }
129
def get_core_config():
130
    """Get set of core DuckDB configuration parameters."""
131
    
132
def apply_config(dialect, conn, ext):
133
    """Apply configuration settings to DuckDB connection."""
134
```
135

136
[Configuration](./configuration.md)
137

138
## Types
139

140
```python { .api }
141
# Core SQLAlchemy types (for reference)
142
from sqlalchemy.engine.interfaces import Dialect as RootDialect
143
from sqlalchemy.dialects.postgresql.base import PGDialect_psycopg2
144
from sqlalchemy.sql.type_api import TypeEngine
145
from sqlalchemy.engine.url import URL
146
from sqlalchemy import pool
147
from typing import Type, Dict, List, Optional, Any
148

149
# DuckDB connection type
150
DuckDBPyConnection = Any  # duckdb.DuckDBPyConnection
151

152
class DBAPI:
153
    paramstyle: str
154
    apilevel: str
155
    threadsafety: int
156
    Error: Type[Exception]
157
    TransactionException: Type[Exception]
158
    ParserException: Type[Exception]
159
    
160
    @staticmethod
161
    def Binary(x): ...
162

163
class DuckDBEngineWarning(Warning):
164
    pass
165

166
# Version and feature detection constants
167
__version__: str  # Current duckdb-engine version
168
sqlalchemy_version: str  # SQLAlchemy version in use  
169
duckdb_version: str  # DuckDB version in use
170
supports_attach: bool  # Whether DuckDB supports ATTACH (v0.7.0+)
171
supports_user_agent: bool  # Whether DuckDB supports custom user agent (v0.9.2+)
172
```