0
# Database API
1

2
Complete Python DB API 2.0 implementation providing standard database connectivity for SQL queries against diverse data sources. Supports connections, cursors, transactions, and all standard database operations.
3

4
## Capabilities
5

6
### Connection Management
7

8
Create and manage database connections with support for multiple adapters and backends.
9

10
```python { .api }
11
def connect(path, adapters=None, adapter_kwargs=None, safe=False, isolation_level=None, apsw_connection_kwargs=None, schema="main"):
12
    """
13
    Create a database connection.
14
    
15
    Parameters:
16
    - path (str): Database path (":memory:" for in-memory database)
17
    - adapters (list, optional): List of adapter entry point names to enable
18
    - adapter_kwargs (dict, optional): Configuration for specific adapters
19
    - safe (bool): Enable safe mode (limited SQL operations, default: False)
20
    - isolation_level (str, optional): Transaction isolation level
21
    - apsw_connection_kwargs (dict, optional): Additional APSW connection parameters
22
    - schema (str): Database schema name (default: "main")
23
    
24
    Returns:
25
    APSWConnection: Database connection object
26
    """
27
```
28

29
Usage example:
30

31
```python
32
from shillelagh.backends.apsw.db import connect
33

34
# Basic connection (in-memory database)
35
connection = connect(":memory:")
36

37
# Connection with specific adapters
38
connection = connect(
39
    ":memory:",
40
    adapters=['CSVFile', 'GSheetsAPI'], 
41
    adapter_kwargs={
42
        'gsheetsapi': {'service_account_file': '/path/to/creds.json'}
43
    }
44
)
45

46
# Safe mode connection (restricted SQL)
47
safe_connection = connect(":memory:", safe=True)
48
```
49

50
### Connection Class
51

52
Database connection implementing Python DB API 2.0 specification.
53

54
```python { .api }
55
class Connection:
56
    """Database connection."""
57
    
58
    def cursor(self):
59
        """
60
        Return a new Cursor object using the connection.
61
        
62
        Returns:
63
        Cursor object
64
        """
65
    
66
    def execute(self, operation, parameters=None):
67
        """
68
        Execute a query on a cursor.
69
        
70
        Parameters:
71
        - operation (str): SQL query string
72
        - parameters (tuple, optional): Query parameters
73
        
74
        Returns:
75
        Cursor object with results
76
        """
77
    
78
    def close(self):
79
        """Close the connection."""
80
    
81
    def commit(self):
82
        """Commit any pending transaction to the database."""
83
    
84
    def rollback(self):
85
        """Rollback any transactions."""
86
    
87
    # Context manager support
88
    def __enter__(self): ...
89
    def __exit__(self, exc_type, exc_val, exc_tb): ...
90
```
91

92
### Cursor Class
93

94
Database cursor for executing queries and fetching results.
95

96
```python { .api }
97
class Cursor:
98
    """Connection cursor."""
99
    
100
    # Cursor properties
101
    arraysize: int  # Number of rows to fetch with fetchmany() (default: 1)
102
    description: tuple  # Column description after execute()
103
    rowcount: int  # Number of affected/returned rows
104
    
105
    def execute(self, operation, parameters=None):
106
        """
107
        Execute a query using the cursor.
108
        
109
        Parameters:
110
        - operation (str): SQL query string  
111
        - parameters (tuple, optional): Query parameters using ? placeholders
112
        
113
        Returns:
114
        Cursor object (self)
115
        """
116
    
117
    def executemany(self, operation, seq_of_parameters=None):
118
        """
119
        Execute multiple statements.
120
        Currently not supported - raises NotSupportedError.
121
        """
122
    
123
    def fetchone(self):
124
        """
125
        Fetch the next row of a query result set.
126
        
127
        Returns:
128
        tuple: Single row as tuple, or None when no more data available
129
        """
130
    
131
    def fetchmany(self, size=None):
132
        """
133
        Fetch the next set of rows of a query result.
134
        
135
        Parameters:
136
        - size (int, optional): Number of rows to fetch (defaults to arraysize)
137
        
138
        Returns:
139
        list: List of tuples, empty list when no more rows available
140
        """
141
    
142
    def fetchall(self):
143
        """
144
        Fetch all remaining rows of a query result.
145
        
146
        Returns:
147
        list: List of tuples containing all remaining rows
148
        """
149
    
150
    def close(self):
151
        """Close the cursor."""
152
    
153
    def setinputsizes(self, sizes):
154
        """Predefine memory areas for parameters (not supported)."""
155
    
156
    def setoutputsizes(self, sizes):
157
        """Set column buffer size for large columns (not supported)."""
158
    
159
    # Iterator support
160
    def __iter__(self): ...
161
    def __next__(self): ...
162
```
163

164
### DB API Constants
165

166
Standard DB API 2.0 module-level constants and information.
167

168
```python { .api }
169
# API compliance level
170
apilevel = "2.0"
171

172
# Thread safety level (2 = threads may share module and connections)
173
threadsafety = 2
174

175
# Parameter style for SQL queries (qmark = ? placeholders)
176
paramstyle = "qmark"
177
```
178

179
### Type Constructors
180

181
Standard DB API 2.0 type constructors for handling different data types.
182

183
```python { .api }
184
def Binary(value):
185
    """Construct binary value."""
186

187
def Date(year, month, day):
188
    """Construct date value."""
189

190
def Time(hour, minute, second):
191
    """Construct time value."""
192

193
def Timestamp(year, month, day, hour, minute, second):
194
    """Construct timestamp value."""
195

196
def DateFromTicks(ticks):
197
    """Construct date from timestamp."""
198

199
def TimeFromTicks(ticks):
200
    """Construct time from timestamp."""
201

202
def TimestampFromTicks(ticks):
203
    """Construct timestamp from timestamp."""
204
```
205

206
### Type Constants
207

208
Standard DB API 2.0 type constants for column type identification.
209

210
```python { .api }
211
STRING: str     # String column type
212
BINARY: str     # Binary column type  
213
NUMBER: str     # Numeric column type
214
DATETIME: str   # DateTime column type
215
ROWID: str      # Row ID column type
216
```
217

218
## Usage Examples
219

220
### Basic Query Execution
221

222
```python
223
from shillelagh.backends.apsw.db import connect
224

225
# Create connection and cursor
226
connection = connect(":memory:")
227
cursor = connection.cursor()
228

229
# Execute query
230
cursor.execute("SELECT * FROM 'data.csv' WHERE age > ?", (25,))
231

232
# Fetch results
233
for row in cursor.fetchall():
234
    print(row)
235

236
# Clean up
237
cursor.close()
238
connection.close()
239
```
240

241
### Context Manager Usage
242

243
```python
244
from shillelagh.backends.apsw.db import connect
245

246
with connect(":memory:") as connection:
247
    cursor = connection.cursor()
248
    cursor.execute("SELECT count(*) FROM 'https://api.example.com/data'")
249
    count = cursor.fetchone()[0]
250
    print(f"Total rows: {count}")
251
    # Connection automatically closed
252
```
253

254
### Transaction Handling
255

256
```python
257
from shillelagh.backends.apsw.db import connect
258

259
connection = connect(":memory:")
260
try:
261
    cursor = connection.cursor()
262
    cursor.execute("INSERT INTO memory_table VALUES (1, 'test')")
263
    cursor.execute("UPDATE memory_table SET value = 'updated' WHERE id = 1")
264
    connection.commit()  # Commit changes
265
except Exception as e:
266
    connection.rollback()  # Rollback on error
267
    print(f"Error: {e}")
268
finally:
269
    connection.close()
270
```