Python API Reference

```
>>> # Using with context manager
>>> with session.send_query("SELECT COUNT(*) FROM big_table") as stream:
...     for result in stream:
...         print(f"Count result: {result}")
```

**See also**

- [`query()`](#chdb-session-session-query) - For non-streaming query execution
- `chdb.state.sqlitelike.StreamingResult` - Streaming result iterator

---

#### `sql`[​](#chdb-session-session-sql "Direct link to chdb-session-session-sql")

Execute a SQL query and return the results.

This method executes a SQL query against the session’s database and returns
the results in the specified format. The method supports various output formats
and maintains session state between queries.

**Syntax**

```
sql(sql, fmt='CSV', udf_path='')
```

**Parameters**

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Parameter Type Default Description|  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `sql` str *required* SQL query string to execute|  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | | `fmt` str `"CSV"` Output format for results. Available formats: • `"CSV"` - Comma-separated values • `"JSON"` - JSON format • `"TabSeparated"` - Tab-separated values • `"Pretty"` - Pretty-printed table format • `"JSONCompact"` - Compact JSON format • `"Arrow"` - Apache Arrow format • `"Parquet"` - Parquet format| `udf_path` str `""` Path to user-defined functions. If not specified, uses the UDF path from session initialization | | | | | | | | | | | | | | | |

**Returns**

Returns query results in the specified format.
The exact return type depends on the format parameter:

- String formats (CSV, JSON, etc.) return str
- Binary formats (Arrow, Parquet) return bytes

**Raises:**

|  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- |
| Exception Condition|  |  |  |  | | --- | --- | --- | --- | | `RuntimeError` If the session is closed or invalid|  |  | | --- | --- | | `ValueError` If the SQL query is malformed | | | | | |

Note

The “Debug” format isn't supported and will be automatically converted
to “CSV” with a warning. For debugging, use connection string parameters
instead.

Warning

This method executes the query synchronously and loads all results into
memory.
For large result sets, consider using [`send_query()`](#chdb-session-session-send_query) for streaming results.

**Examples**

```
>>> session = Session("test.db")
>>>
>>> # Basic query with default CSV format
>>> result = session.query("SELECT 1 as number")
>>> print(result)
number
1
```

```
>>> # Query with JSON format
>>> result = session.query("SELECT 1 as number", fmt="JSON")
>>> print(result)
{"number": "1"}
```

```
>>> # Complex query with table creation
>>> session.query("CREATE TABLE test (id INT, name String) ENGINE = MergeTree() order by id")
>>> session.query("INSERT INTO test VALUES (1, 'Alice'), (2, 'Bob')")
>>> result = session.query("SELECT * FROM test ORDER BY id")
>>> print(result)
id,name
1,Alice
2,Bob
```

**See also**

- [`send_query()`](#chdb-session-session-send_query) - For streaming query execution
- [`sql`](#chdb-session-session-sql) - Alias for this method

## State Management[​](#chdb-state-management "Direct link to State Management")

### `chdb.state.connect`[​](#chdb_state_connect "Direct link to chdb_state_connect")

Create a [Connection](#chdb-state-sqlitelike-connection) to the chDB background server.

This function establishes a connection to the chDB (ClickHouse) database engine.
Only one open connection is allowed per process. Multiple calls with the same
connection string will return the same connection object.

**Syntax**

```
chdb.state.connect(connection_string: str = ':memory:') → Connection
```

**Parameters**

|  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- |
| Parameter Type Default Description|  |  |  |  | | --- | --- | --- | --- | | `connection_string(str, optional)` str `":memory:"` Database connection string. See formats below. | | | | | | | |

**Basic formats**

Supported connection string formats:

|  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Format Description|  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `":memory:"` In-memory database (default)|  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | | `"test.db"` Relative path database file|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | `"file:test.db"` Same as relative path|  |  |  |  | | --- | --- | --- | --- | | `"/path/to/test.db"` Absolute path database file|  |  | | --- | --- | | `"file:/path/to/test.db"` Same as absolute path | | | | | | | | | | | |

**With query parameters**

|  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- |
| Format Description|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | `"file:test.db?param1=value1&param2=value2"` Relative path with params|  |  |  |  | | --- | --- | --- | --- | | `"file::memory:?verbose&log-level=test"` In-memory with params|  |  | | --- | --- | | `"///path/to/test.db?param1=value1&param2=value2"` Absolute path with params | | | | | | | |

**Query parameter handling**

Query parameters are passed to ClickHouse engine as startup arguments.
Special parameter handling:

|  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Special Parameter Becomes Description|  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `mode=ro` `--readonly=1` Read-only mode|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | `verbose` (flag) Enables verbose logging|  |  |  | | --- | --- | --- | | `log-level=test` (setting) Sets logging level | | | | | | | | | | | |

For a complete parameter list, see `clickhouse local --help --verbose`

**Returns**

|  |  |  |  |
| --- | --- | --- | --- |
| Return Type Description|  |  | | --- | --- | | `Connection` Database connection object that supports: • Creating cursors with `Connection.cursor()` • Direct queries with `Connection.query()` • Streaming queries with `Connection.send_query()` • Context manager protocol for automatic cleanup | | | |

**Raises**

|  |  |  |  |
| --- | --- | --- | --- |
| Exception Condition|  |  | | --- | --- | | `RuntimeError` If connection to database fails | | | |

Warning

Only one connection per process is supported.
Creating a new connection will close any existing connection.

**Examples**

```
>>> # In-memory database
>>> conn = connect()
>>> conn = connect(":memory:")
>>>
>>> # File-based database
>>> conn = connect("my_data.db")
>>> conn = connect("/path/to/data.db")
>>>
>>> # With parameters
>>> conn = connect("data.db?mode=ro")  # Read-only mode
>>> conn = connect(":memory:?verbose&log-level=debug")  # Debug logging
>>>
>>> # Using context manager for automatic cleanup
>>> with connect("data.db") as conn:
...     result = conn.query("SELECT 1")
...     print(result)
>>> # Connection automatically closed
```

**See also**

- `Connection` - Database connection class
- `Cursor` - Database cursor for DB-API 2.0 operations

### **class** `chdb.state.sqlitelike.Connection`[​](#chdb-state-sqlitelike-connection "Direct link to chdb-state-sqlitelike-connection")

Bases: `object`

**Syntax**

```
class chdb.state.sqlitelike.Connection(connection_string: str)
```

---

#### `close`[​](#chdb-session-session-close "Direct link to chdb-session-session-close")

Close the connection and cleanup resources.

This method closes the database connection and cleans up any associated
resources including active cursors. After calling this method, the
connection becomes invalid and can't be used for further operations.

**Syntax**

```
close() → None
```

Note

This method is idempotent - calling it multiple times is safe.

Warning

Any ongoing streaming queries will be cancelled when the connection
is closed. Ensure all important data is processed before closing.

**Examples**