0
# phoenixdb
1

2
Python database adapter library that enables developers to connect to and interact with Apache Phoenix databases using the remote query server interface. It implements the standard Python DB API 2.0 specification for database connectivity and includes partial SQLAlchemy dialect support, making it compatible with most Python database applications and ORM frameworks.
3

4
## Package Information
5

6
- **Package Name**: phoenixdb
7
- **Language**: Python
8
- **Installation**: `pip install phoenixdb`
9

10
## Core Imports
11

12
```python
13
import phoenixdb
14
```
15

16
Common for working with cursors:
17

18
```python
19
import phoenixdb.cursor
20
from phoenixdb.cursor import DictCursor
21
```
22

23
For SQLAlchemy integration:
24

25
```python
26
from phoenixdb.sqlalchemy_phoenix import PhoenixDialect
27
```
28

29
## Basic Usage
30

31
```python
32
import phoenixdb
33

34
# Connect to Phoenix query server
35
database_url = 'http://localhost:8765/'
36
conn = phoenixdb.connect(database_url, autocommit=True)
37

38
# Execute queries
39
cursor = conn.cursor()
40
cursor.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, username VARCHAR)")
41
cursor.execute("UPSERT INTO users VALUES (?, ?)", (1, 'admin'))
42
cursor.execute("SELECT * FROM users")
43
results = cursor.fetchall()
44
print(results)
45

46
# Use dictionary cursor for named access
47
dict_cursor = conn.cursor(cursor_factory=phoenixdb.cursor.DictCursor)
48
dict_cursor.execute("SELECT * FROM users WHERE id=1")
49
user = dict_cursor.fetchone()  # Returns dict instead of tuple
50
print(user['USERNAME'])
51

52
# Close resources
53
cursor.close()
54
conn.close()
55
```
56

57
Context manager usage:
58

59
```python
60
import phoenixdb
61

62
with phoenixdb.connect('http://localhost:8765/', autocommit=True) as conn:
63
    with conn.cursor() as cursor:
64
        cursor.execute("SELECT * FROM users")
65
        for row in cursor:  # Cursor supports iteration
66
            print(row)
67
```
68

69
SQLAlchemy integration:
70

71
```python
72
from sqlalchemy import create_engine, text
73

74
# Create engine for Phoenix
75
engine = create_engine('phoenix://localhost:8765')
76

77
# Execute queries
78
with engine.connect() as conn:
79
    result = conn.execute(text("SELECT * FROM users"))
80
    for row in result:
81
        print(row)
82
```
83

84
## Architecture
85

86
phoenixdb follows the DB API 2.0 specification with these key components:
87

88
- **Connection Management**: Handles Phoenix query server communication via Apache Avatica protocol
89
- **Cursor Operations**: Provides result set iteration, prepared statements, and bulk operations
90
- **Type System**: Converts between Python and Phoenix/Java types with full precision support
91
- **Authentication**: Supports multiple authentication mechanisms (BASIC, DIGEST, SPNEGO/Kerberos)
92
- **Error Handling**: Complete DB API 2.0 exception hierarchy with Phoenix-specific error details
93

94
## DB API 2.0 Compliance
95

96
```python { .api }
97
# Module-level attributes
98
apilevel = "2.0"           # DB API version
99
threadsafety = 1           # Thread safety level
100
paramstyle = 'qmark'       # Parameter placeholder style (?)
101
```
102

103
## Capabilities
104

105
### Connection Management
106

107
Database connection establishment, configuration, and lifecycle management with support for various authentication mechanisms and connection parameters.
108

109
```python { .api }
110
def connect(url, max_retries=None, auth=None, authentication=None,
111
           avatica_user=None, avatica_password=None, truststore=None,
112
           verify=None, do_as=None, user=None, password=None,
113
           extra_headers=None, **kwargs):
114
    """
115
    Connects to a Phoenix query server.
116

117
    Parameters:
118
    - url (str): URL to Phoenix query server (e.g., 'http://localhost:8765/')
119
    - autocommit (bool): Switch connection to autocommit mode
120
    - readonly (bool): Switch connection to readonly mode
121
    - max_retries (int): Maximum number of retries for connection errors
122
    - cursor_factory: Default cursor factory class
123
    - auth: Authentication configuration object
124
    - authentication (str): Authentication mechanism ('BASIC', 'DIGEST', 'SPNEGO', 'NONE')
125
    - avatica_user (str): Username for BASIC/DIGEST authentication
126
    - avatica_password (str): Password for BASIC/DIGEST authentication
127
    - truststore (str): Path to PEM file for server certificate verification
128
    - verify: Certificate verification configuration
129
    - do_as (str): Username to impersonate (Hadoop doAs)
130
    - user (str): Alias for avatica_user or do_as depending on authentication
131
    - password (str): Alias for avatica_password
132
    - extra_headers (dict): Additional HTTP headers
133

134
    Returns:
135
    Connection: Database connection object
136
    """
137
```
138

139
[Connection Management](./connection.md)
140

141
### Query Execution
142

143
Cursor-based query execution with support for prepared statements, parameter binding, result set iteration, and bulk operations.
144

145
```python { .api }
146
class Cursor:
147
    def execute(self, operation, parameters=None): ...
148
    def executemany(self, operation, seq_of_parameters): ...
149
    def fetchone(self): ...
150
    def fetchmany(self, size=None): ...
151
    def fetchall(self): ...
152
```
153

154
[Query Execution](./cursor.md)
155

156
### Type System
157

158
Comprehensive type system for converting between Python and Phoenix data types, including support for date/time, numeric, binary, and array types.
159

160
```python { .api }
161
# Type constructor functions
162
def Date(year, month, day): ...
163
def Time(hour, minute, second): ...
164
def Timestamp(year, month, day, hour, minute, second): ...
165
def Binary(value): ...
166

167
# Type constants for comparison
168
STRING: ColumnType
169
BINARY: ColumnType
170
NUMBER: ColumnType
171
DATETIME: ColumnType
172
BOOLEAN: ColumnType
173
```
174

175
[Type System](./types.md)
176

177
### Error Handling
178

179
Complete DB API 2.0 exception hierarchy with Phoenix-specific error information including SQL state codes and detailed error messages.
180

181
```python { .api }
182
class Error(Exception):
183
    @property
184
    def message(self): ...
185
    @property
186
    def code(self): ...
187
    @property
188
    def sqlstate(self): ...
189
    @property
190
    def cause(self): ...
191
```
192

193
[Error Handling](./errors.md)
194

195
### Database Metadata
196

197
Access to Phoenix database metadata including catalogs, schemas, tables, columns, primary keys, and indexes through JDBC-compatible interface.
198

199
```python { .api }
200
class Meta:
201
    def get_catalogs(self): ...
202
    def get_schemas(self, catalog=None, schemaPattern=None): ...
203
    def get_tables(self, catalog=None, schemaPattern=None, tableNamePattern=None, typeList=None): ...
204
    def get_columns(self, catalog=None, schemaPattern=None, tableNamePattern=None, columnNamePattern=None): ...
205
```
206

207
[Database Metadata](./meta.md)
208

209
### SQLAlchemy Integration
210

211
Partial SQLAlchemy dialect implementation supporting textual SQL execution and basic database operations within SQLAlchemy applications.
212

213
```python { .api }
214
class PhoenixDialect(DefaultDialect):
215
    name = "phoenix"
216
    driver = "phoenixdb"
217
```
218

219
[SQLAlchemy Integration](./sqlalchemy.md)