0
# ConnectorX
1

2
ConnectorX is a high-performance data loading library that enables users to efficiently transfer data from various databases directly into Python dataframes. Built in Rust with Python bindings, it follows a zero-copy principle to achieve significant performance improvements - up to 21x faster than traditional solutions while using 3x less memory. The library supports multiple database sources and dataframe formats with features like automatic parallelization through partition-based loading and federated queries across multiple databases.
3

4
## Package Information
5

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

10
## Core Imports
11

12
```python
13
import connectorx as cx
14
```
15

16
Common individual imports:
17

18
```python
19
from connectorx import read_sql, get_meta, partition_sql, ConnectionUrl
20
```
21

22
All available imports:
23

24
```python
25
from connectorx import (
26
    read_sql, 
27
    read_sql_pandas, 
28
    get_meta, 
29
    partition_sql, 
30
    ConnectionUrl,
31
    remove_ending_semicolon,
32
    try_import_module,
33
    Protocol,
34
    __version__
35
)
36
```
37

38
## Basic Usage
39

40
```python
41
import connectorx as cx
42

43
# Basic data loading from PostgreSQL
44
postgres_url = "postgresql://username:password@server:port/database"
45
query = "SELECT * FROM lineitem"
46
df = cx.read_sql(postgres_url, query)
47

48
# Parallel loading with partitioning
49
df_parallel = cx.read_sql(
50
    postgres_url, 
51
    query, 
52
    partition_on="l_orderkey", 
53
    partition_num=10
54
)
55

56
# Load to different dataframe formats
57
arrow_table = cx.read_sql(postgres_url, query, return_type="arrow")
58
polars_df = cx.read_sql(postgres_url, query, return_type="polars")
59
```
60

61
## Architecture
62

63
ConnectorX follows a zero-copy architecture built on Rust's performance characteristics:
64

65
- **Rust Core**: High-performance data transfer engine with zero-copy principles
66
- **Python Bindings**: Seamless integration with Python dataframe libraries
67
- **Parallel Processing**: Automatic query partitioning for concurrent data loading
68
- **Protocol Optimization**: Backend-specific protocol selection for optimal performance
69
- **Memory Efficiency**: Direct data transfer from database to target dataframe format
70

71
## Capabilities
72

73
### Data Loading
74

75
Primary functionality for executing SQL queries and loading data into various dataframe formats. Supports single-threaded and parallel execution with automatic partitioning.
76

77
```python { .api }
78
def read_sql(
79
    conn: str | ConnectionUrl | dict[str, str] | dict[str, ConnectionUrl],
80
    query: list[str] | str,
81
    *,
82
    return_type: Literal["pandas", "polars", "arrow", "modin", "dask", "arrow_stream"] = "pandas",
83
    protocol: Protocol | None = None,
84
    partition_on: str | None = None,
85
    partition_range: tuple[int, int] | None = None,
86
    partition_num: int | None = None,
87
    index_col: str | None = None,
88
    strategy: str | None = None,
89
    pre_execution_query: list[str] | str | None = None,
90
    batch_size: int = 10000,
91
    **kwargs
92
) -> pd.DataFrame | mpd.DataFrame | dd.DataFrame | pl.DataFrame | pa.Table | pa.RecordBatchReader
93

94
def read_sql_pandas(
95
    sql: list[str] | str,
96
    con: str | ConnectionUrl | dict[str, str] | dict[str, ConnectionUrl],
97
    index_col: str | None = None,
98
    protocol: Protocol | None = None,
99
    partition_on: str | None = None,
100
    partition_range: tuple[int, int] | None = None,
101
    partition_num: int | None = None,
102
    pre_execution_queries: list[str] | str | None = None,
103
) -> pd.DataFrame
104
```
105

106
[Data Loading](./data-loading.md)
107

108
### Query Partitioning
109

110
Functionality for partitioning SQL queries to enable parallel data loading across multiple threads.
111

112
```python { .api }
113
def partition_sql(
114
    conn: str | ConnectionUrl,
115
    query: str,
116
    partition_on: str,
117
    partition_num: int,
118
    partition_range: tuple[int, int] | None = None,
119
) -> list[str]
120
```
121

122
[Query Partitioning](./query-partitioning.md)
123

124
### Metadata Retrieval
125

126
Retrieve schema information and metadata from SQL queries without loading the full dataset.
127

128
```python { .api }
129
def get_meta(
130
    conn: str | ConnectionUrl,
131
    query: str,
132
    protocol: Protocol | None = None,
133
) -> pd.DataFrame
134
```
135

136
[Metadata Retrieval](./metadata-retrieval.md)
137

138
### Connection Management
139

140
Helper utilities for building and managing database connection strings across different database backends.
141

142
```python { .api }
143
class ConnectionUrl(Generic[_BackendT], str):
144
    # For SQLite databases
145
    def __new__(
146
        cls,
147
        *,
148
        backend: Literal["sqlite"],
149
        db_path: str | Path,
150
    ) -> ConnectionUrl[Literal["sqlite"]]: ...
151
    
152
    # For BigQuery databases  
153
    def __new__(
154
        cls,
155
        *,
156
        backend: Literal["bigquery"],
157
        db_path: str | Path,
158
    ) -> ConnectionUrl[Literal["bigquery"]]: ...
159
    
160
    # For server-based databases
161
    def __new__(
162
        cls,
163
        *,
164
        backend: _ServerBackendT,
165
        username: str,
166
        password: str = "",
167
        server: str,
168
        port: int,
169
        database: str = "",
170
        database_options: dict[str, str] | None = None,
171
    ) -> ConnectionUrl[_ServerBackendT]: ...
172
    
173
    # For raw connection strings
174
    def __new__(
175
        cls,
176
        raw_connection: str,
177
    ) -> ConnectionUrl: ...
178
```
179

180
[Connection Management](./connection-management.md)
181

182
### Federated Queries
183

184
Execute queries across multiple databases in a single statement, with automatic join optimization and query rewriting.
185

186
```python { .api }
187
def read_sql(
188
    conn: dict[str, str] | dict[str, ConnectionUrl],
189
    query: str,
190
    *,
191
    strategy: str | None = None,
192
    **kwargs
193
) -> pd.DataFrame | pl.DataFrame | pa.Table
194
```
195

196
[Federated Queries](./federated-queries.md)
197

198
### Utility Functions
199

200
Helper functions for SQL query processing and module management.
201

202
```python { .api }
203
def remove_ending_semicolon(query: str) -> str:
204
    """Remove trailing semicolon from SQL query if present."""
205

206
def try_import_module(name: str) -> Any:
207
    """Import a module with helpful error message if not found."""
208

209
def rewrite_conn(
210
    conn: str | ConnectionUrl, 
211
    protocol: Protocol | None = None
212
) -> tuple[str, Protocol]:
213
    """Rewrite connection string for backend compatibility."""
214

215
__version__: str  # Package version string
216
```
217

218
## Types
219

220
```python { .api }
221
Protocol = Literal["csv", "binary", "cursor", "simple", "text"]
222

223
# Type variables for connection URL backends
224
_BackendT = TypeVar("_BackendT")
225
_ServerBackendT = TypeVar(
226
    "_ServerBackendT",
227
    bound=Literal[
228
        "redshift",
229
        "clickhouse", 
230
        "postgres",
231
        "postgresql",
232
        "mysql",
233
        "mssql",
234
        "oracle",
235
        "duckdb",
236
    ],
237
)
238

239
# Internal types from Rust bindings
240
_DataframeInfos = dict[str, Any]  # Pandas DataFrame reconstruction info
241
_ArrowInfos = tuple[list[str], list[Any]]  # Arrow table reconstruction info
242

243
# Type checking imports (only available when TYPE_CHECKING is True)
244
if TYPE_CHECKING:
245
    import pandas as pd
246
    import polars as pl  
247
    import modin.pandas as mpd
248
    import dask.dataframe as dd
249
    import pyarrow as pa
250
```
251

252
## Supported Databases
253

254
- **PostgreSQL** (`postgresql://`)
255
- **MySQL** (`mysql://`)
256
- **SQLite** (`sqlite://`)
257
- **Microsoft SQL Server** (`mssql://`)
258
- **Oracle** (`oracle://`)
259
- **BigQuery** (`bigquery://`)
260
- **Redshift** (`redshift://`)
261
- **ClickHouse** (`clickhouse://`)
262
- **Trino/Presto**
263

264
## Supported Dataframe Libraries
265

266
- **pandas** (default)
267
- **PyArrow** (arrow tables and record batch readers)
268
- **Polars**
269
- **Modin** (distributed pandas)
270
- **Dask** (distributed computing)