0
# Database API
1

2
Python DB API 2.0 compliant interface for SQL-based interaction with Druid, providing standard database connectivity patterns that work with existing Python database tools and frameworks.
3

4
## Capabilities
5

6
### Connection Management
7

8
Establish and manage database connections to Druid.
9

10
```python { .api }
11
def connect(
12
    host: str = "localhost",
13
    port: int = 8082,
14
    path: str = "/druid/v2/sql/",
15
    scheme: str = "http",
16
    user: str = None,
17
    password: str = None,
18
    context: dict = None,
19
    header: bool = False,
20
    ssl_verify_cert: bool = True,
21
    ssl_client_cert: str = None,
22
    proxies: dict = None,
23
    jwt: str = None
24
) -> Connection:
25
    """
26
    Create a connection to Druid database.
27
    
28
    Parameters:
29
    - host: Druid broker hostname
30
    - port: Druid broker port  
31
    - path: SQL endpoint path
32
    - scheme: Connection scheme ('http' or 'https')
33
    - user: Username for authentication (optional)
34
    - password: Password for authentication (optional)
35
    - context: Query context parameters (optional)
36
    - header: Whether to request column headers in responses
37
    - ssl_verify_cert: Whether to verify SSL certificates
38
    - ssl_client_cert: Path to client SSL certificate (optional)
39
    - proxies: Proxy configuration dictionary (optional)
40
    - jwt: JWT token for authentication (optional)
41
    
42
    Returns:
43
    Connection object for executing queries
44
    """
45
```
46

47
### Connection Interface
48

49
DB API 2.0 compliant connection object.
50

51
```python { .api }
52
class Connection:
53
    """Database connection implementing DB API 2.0 interface."""
54
    
55
    def close(self) -> None:
56
        """Close the connection and free resources."""
57
    
58
    def commit(self) -> None:
59
        """
60
        Commit pending transactions.
61
        Note: Druid does not support transactions, so this is a no-op.
62
        """
63
    
64
    def cursor(self) -> 'Cursor':
65
        """Create a new cursor object for executing queries."""
66
    
67
    def execute(self, operation: str, parameters: dict = None) -> 'Cursor':
68
        """
69
        Execute SQL query directly on connection.
70
        
71
        Parameters:
72
        - operation: SQL query string
73
        - parameters: Query parameters for parameterized queries (optional)
74
        
75
        Returns:
76
        Cursor object with query results
77
        """
78
```
79

80
### Cursor Interface
81

82
DB API 2.0 compliant cursor for query execution and result retrieval.
83

84
```python { .api }
85
class Cursor:
86
    """Database cursor implementing DB API 2.0 interface."""
87
    
88
    # Properties
89
    rowcount: int = None  # Number of rows affected by last operation
90
    description: list = None  # Column description tuples
91
    arraysize: int = 1  # Default number of rows to fetch with fetchmany()
92
    
93
    def close(self) -> None:
94
        """Close the cursor and free resources."""
95
    
96
    def execute(self, operation: str, parameters: dict = None) -> None:
97
        """
98
        Execute SQL query.
99
        
100
        Parameters:
101
        - operation: SQL query string  
102
        - parameters: Query parameters for parameterized queries (optional)
103
                     Uses pyformat style: {'param': value}
104
        """
105
    
106
    def fetchone(self) -> tuple | None:
107
        """
108
        Fetch next row from query results.
109
        
110
        Returns:
111
        Tuple representing single row, or None if no more rows
112
        """
113
    
114
    def fetchmany(self, size: int = None) -> list:
115
        """
116
        Fetch multiple rows from query results.
117
        
118
        Parameters:
119
        - size: Number of rows to fetch (defaults to arraysize)
120
        
121
        Returns:
122
        List of tuples representing rows
123
        """
124
    
125
    def fetchall(self) -> list:
126
        """
127
        Fetch all remaining rows from query results.
128
        
129
        Returns:
130
        List of tuples representing all remaining rows
131
        """
132
    
133
    def __iter__(self):
134
        """Allow iteration over cursor results."""
135
        return self
136
    
137
    def __next__(self):
138
        """Get next row when iterating over cursor."""
139
```
140

141
### Data Types
142

143
Data type constants for column description.
144

145
```python { .api }
146
class Type:
147
    """Data type constants for DB API compatibility."""
148
    STRING: int = 1
149
    NUMBER: int = 2  
150
    BOOLEAN: int = 3
151
```
152

153
### Exception Hierarchy
154

155
DB API 2.0 compliant exception hierarchy.
156

157
```python { .api }
158
# Base exception
159
class Error(Exception):
160
    """Base class for all database exceptions."""
161

162
# Warning
163
class Warning(Exception):
164
    """Exception for important warnings."""
165

166
# Interface errors
167
class InterfaceError(Error):
168
    """Exception for database interface errors."""
169

170
# Compile errors
171
class CompileError(Error):
172
    """Exception for query compilation errors."""
173

174
# Database errors
175
class DatabaseError(Error):
176
    """Exception for database-related errors."""
177

178
class InternalError(DatabaseError):
179
    """Exception for internal database errors."""
180

181
class OperationalError(DatabaseError):
182
    """Exception for operational database errors."""
183

184
class ProgrammingError(DatabaseError):
185
    """Exception for programming errors in SQL."""
186

187
class IntegrityError(DatabaseError):
188
    """Exception for data integrity errors."""
189

190
class DataError(DatabaseError):
191
    """Exception for data processing errors."""
192

193
class NotSupportedError(CompileError):
194
    """Exception for unsupported database operations."""
195
```
196

197
### Authentication Support
198

199
Additional authentication mechanisms.
200

201
```python { .api }
202
class BearerAuth:
203
    """Bearer token authentication for HTTP requests."""
204
    
205
    def __init__(self, token: str) -> None:
206
        """
207
        Initialize bearer authentication.
208
        
209
        Parameters:
210
        - token: Bearer token string
211
        """
212
```
213

214
## Usage Examples
215

216
### Basic Usage
217

218
```python
219
from pydruid.db import connect
220

221
# Connect to Druid
222
conn = connect(host='localhost', port=8082, path='/druid/v2/sql/', scheme='http')
223
cursor = conn.cursor()
224

225
# Execute SQL query
226
cursor.execute("""
227
    SELECT place,
228
           CAST(REGEXP_EXTRACT(place, '(.*),', 1) AS FLOAT) AS lat,
229
           CAST(REGEXP_EXTRACT(place, ',(.*)', 1) AS FLOAT) AS lon
230
      FROM places
231
     LIMIT 10
232
""")
233

234
# Fetch results
235
for row in cursor:
236
    print(row)
237

238
# Or fetch all at once
239
cursor.execute("SELECT COUNT(*) FROM places")
240
result = cursor.fetchone()
241
print(f"Total places: {result[0]}")
242

243
# Close resources
244
cursor.close()
245
conn.close()
246
```
247

248
### Parameterized Queries
249

250
```python
251
from pydruid.db import connect
252

253
conn = connect(host='localhost', port=8082)
254
cursor = conn.cursor()
255

256
# Use parameterized queries (pyformat style)
257
cursor.execute("""
258
    SELECT user_name, COUNT(*) as tweet_count
259
      FROM twitterstream
260
     WHERE user_lang = %(language)s
261
       AND __time >= %(start_time)s
262
     GROUP BY user_name
263
     ORDER BY tweet_count DESC
264
     LIMIT %(limit)s
265
""", {
266
    'language': 'en',
267
    'start_time': '2014-03-01',
268
    'limit': 10
269
})
270

271
results = cursor.fetchall()
272
for row in results:
273
    print(f"User: {row[0]}, Tweets: {row[1]}")
274
```
275

276
### Error Handling
277

278
```python
279
from pydruid.db import connect, DatabaseError, ProgrammingError
280

281
try:
282
    conn = connect(host='localhost', port=8082)
283
    cursor = conn.cursor()
284
    
285
    cursor.execute("SELECT * FROM nonexistent_table")
286
    results = cursor.fetchall()
287
    
288
except ProgrammingError as e:
289
    print(f"SQL error: {e}")
290
except DatabaseError as e:
291
    print(f"Database error: {e}")
292
finally:
293
    if cursor:
294
        cursor.close()
295
    if conn:
296
        conn.close()
297
```
298

299
### Connection with Authentication
300

301
```python
302
from pydruid.db import connect
303

304
# Basic authentication
305
conn = connect(
306
    host='druid.example.com',
307
    port=8082,
308
    scheme='https',
309
    user='username',
310
    password='password',
311
    ssl_verify_cert=True
312
)
313

314
# JWT authentication  
315
conn = connect(
316
    host='druid.example.com',
317
    port=8082,
318
    scheme='https',
319
    jwt='your-jwt-token-here'
320
)
321

322
# With query context
323
conn = connect(
324
    host='localhost',
325
    port=8082,
326
    context={'timeout': 60000, 'maxScatterGatherBytes': 1000000}
327
)
328
```
329

330
## DB API Compliance
331

332
This implementation follows the Python Database API Specification v2.0 (PEP 249):
333

334
- **apilevel**: "2.0" - Supports DB API 2.0
335
- **threadsafety**: 2 - Threads may share the module and connections
336
- **paramstyle**: "pyformat" - Uses %(name)s parameter style
337

338
The implementation provides standard database connectivity that works with existing Python database tools, ORMs, and frameworks that expect DB API 2.0 compliant interfaces.