0
# Connection and Cursor Management
1
2
Connection and cursor wrapper classes that adapt DuckDB's Python API to SQLAlchemy's expected interface. These classes handle special DuckDB operations, DataFrame registration, and provide compatibility with SQLAlchemy's connection and cursor protocols.
3
4
## Capabilities
5
6
### Connection Wrapper
7
8
Wraps DuckDB connections to provide SQLAlchemy-compatible interface and behavior.
9
10
```python { .api }
11
class ConnectionWrapper:
12
"""
13
Wraps DuckDB connection for SQLAlchemy compatibility.
14
15
Provides the interface SQLAlchemy expects while delegating to
16
the underlying DuckDB connection for actual operations.
17
"""
18
19
autocommit = None # DuckDB doesn't support setting autocommit
20
closed: bool = False
21
notices: List[str]
22
23
def __init__(self, c):
24
"""
25
Initialize connection wrapper.
26
27
Parameters:
28
- c (DuckDBPyConnection): DuckDB connection to wrap
29
"""
30
31
def cursor(self):
32
"""
33
Create a cursor for executing statements.
34
35
Returns:
36
CursorWrapper: Wrapped DuckDB cursor
37
"""
38
39
def close(self):
40
"""
41
Close the connection and mark as closed.
42
"""
43
44
def __getattr__(self, name):
45
"""
46
Delegate attribute access to underlying DuckDB connection.
47
48
Parameters:
49
- name (str): Attribute name
50
51
Returns:
52
Any: Attribute value from DuckDB connection
53
"""
54
```
55
56
### Cursor Wrapper
57
58
Wraps DuckDB cursors to provide SQLAlchemy-compatible interface with special handling for DuckDB operations.
59
60
```python { .api }
61
class CursorWrapper:
62
"""
63
Wraps DuckDB cursor for SQLAlchemy compatibility.
64
65
Handles SQL execution, parameter binding, and special DuckDB
66
operations like DataFrame registration.
67
"""
68
69
def __init__(self, c, connection_wrapper):
70
"""
71
Initialize cursor wrapper.
72
73
Parameters:
74
- c (DuckDBPyConnection): DuckDB connection
75
- connection_wrapper (ConnectionWrapper): Parent connection wrapper
76
"""
77
78
def execute(self, statement, parameters=None, context=None):
79
"""
80
Execute SQL statement with parameter binding.
81
82
Handles special cases for DuckDB including:
83
- COMMIT statements for ipython-sql compatibility
84
- REGISTER statements for DataFrame registration
85
- Standard SQL with parameter binding
86
87
Parameters:
88
- statement (str): SQL statement to execute
89
- parameters (Tuple, optional): Parameter values for binding
90
- context (Any, optional): Execution context (unused)
91
92
Raises:
93
NotImplementedError: For unsupported DuckDB operations
94
RuntimeError: For other DuckDB errors
95
"""
96
97
def executemany(self, statement, parameters=None, context=None):
98
"""
99
Execute SQL statement multiple times with different parameters.
100
101
Parameters:
102
- statement (str): SQL statement to execute
103
- parameters (List[Dict], optional): List of parameter dictionaries
104
- context (Any, optional): Execution context (unused)
105
"""
106
107
def fetchmany(self, size=None):
108
"""
109
Fetch multiple rows from query results.
110
111
Parameters:
112
- size (int, optional): Number of rows to fetch
113
114
Returns:
115
List: Fetched rows
116
"""
117
118
def close(self):
119
"""
120
Close cursor (no-op for DuckDB cursors).
121
"""
122
123
@property
124
def connection(self):
125
"""
126
Get parent connection wrapper.
127
128
Returns:
129
ConnectionWrapper: Parent connection
130
"""
131
132
def __getattr__(self, name):
133
"""
134
Delegate attribute access to underlying DuckDB connection.
135
136
Parameters:
137
- name (str): Attribute name
138
139
Returns:
140
Any: Attribute value from DuckDB connection
141
"""
142
```
143
144
### Database API Compatibility
145
146
DBAPI 2.0 compatibility layer providing standard database interface constants and exceptions.
147
148
```python { .api }
149
class DBAPI:
150
"""
151
Database API compatibility class providing DBAPI 2.0 interface.
152
153
Defines standard database interface constants and exception classes
154
for SQLAlchemy compatibility.
155
"""
156
157
paramstyle: str # "numeric_dollar" for SQLAlchemy 2.0+, "qmark" for 1.x
158
apilevel: str # From duckdb.apilevel
159
threadsafety: int # From duckdb.threadsafety
160
161
# Exception classes
162
Error: Type[Exception] # Base exception class
163
TransactionException: Type[Exception] # Transaction-related errors
164
ParserException: Type[Exception] # SQL parsing errors
165
166
@staticmethod
167
def Binary(x):
168
"""
169
Construct binary data type (pass-through for DuckDB).
170
171
Parameters:
172
- x (Any): Data to treat as binary
173
174
Returns:
175
Any: Input data unchanged
176
"""
177
```
178
179
## Usage Examples
180
181
### Direct Connection Usage
182
183
```python
184
from duckdb_engine import ConnectionWrapper, CursorWrapper
185
import duckdb
186
187
# Create wrapped connection
188
raw_conn = duckdb.connect(':memory:')
189
conn = ConnectionWrapper(raw_conn)
190
191
# Use cursor for operations
192
cursor = conn.cursor()
193
cursor.execute("CREATE TABLE test (id INTEGER, name VARCHAR)")
194
cursor.execute("INSERT INTO test VALUES (?, ?)", (1, 'Alice'))
195
196
# Fetch results
197
cursor.execute("SELECT * FROM test")
198
results = cursor.fetchall()
199
print(results)
200
201
conn.close()
202
```
203
204
### DataFrame Registration
205
206
```python
207
import pandas as pd
208
209
# Create DataFrame
210
df = pd.DataFrame({
211
'id': [1, 2, 3],
212
'value': ['a', 'b', 'c']
213
})
214
215
# Register DataFrame with DuckDB (special REGISTER handling)
216
cursor.execute("REGISTER", ('my_df', df))
217
218
# Query the registered DataFrame
219
cursor.execute("SELECT * FROM my_df")
220
results = cursor.fetchall()
221
```
222
223
### SQLAlchemy Integration
224
225
```python
226
from sqlalchemy import create_engine
227
228
# Engine automatically uses ConnectionWrapper/CursorWrapper
229
engine = create_engine('duckdb:///:memory:')
230
231
with engine.connect() as conn:
232
# All operations go through wrapper classes
233
result = conn.execute("SELECT 1 as test")
234
print(result.fetchone())
235
```
236
237
### Error Handling
238
239
```python
240
from duckdb_engine import DBAPI
241
242
try:
243
cursor.execute("INVALID SQL")
244
except DBAPI.ParserException as e:
245
print(f"SQL parsing error: {e}")
246
except DBAPI.Error as e:
247
print(f"Database error: {e}")
248
```
249
250
### Connection State Management
251
252
```python
253
conn = ConnectionWrapper(duckdb.connect(':memory:'))
254
255
# Check connection state
256
print(f"Closed: {conn.closed}")
257
print(f"Notices: {conn.notices}")
258
259
# Close connection
260
conn.close()
261
print(f"Closed: {conn.closed}")
262
```