Python API Reference

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/chdb/api/python.md)

## Core Query Functions[​](#core-query-functions "Direct link to Core Query Functions")

### `chdb.query`[​](#chdb-query "Direct link to chdb-query")

Execute SQL query using chDB engine.

This is the main query function that executes SQL statements using the embedded
ClickHouse engine. Supports various output formats and can work with in-memory
or file-based databases.

**Syntax**

```
chdb.query(sql, output_format='CSV', path='', udf_path='')
```

**Parameters**

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Parameter Type Default Description|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `sql` str *required* SQL query string to execute|  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `output_format` str `"CSV"` Output format for results. Supported formats: • `"CSV"` - Comma-separated values • `"JSON"` - JSON format • `"Arrow"` - Apache Arrow format • `"Parquet"` - Parquet format • `"DataFrame"` - Pandas DataFrame • `"ArrowTable"` - PyArrow Table • `"Debug"` - Enable verbose logging| `path` str `""` Database file path. Defaults to in-memory database. Can be a file path or `":memory:"` for in-memory database| `udf_path` str `""` Path to User-Defined Functions directory | | | | | | | | | | | | | | | | | | | |

**Returns**

Returns the query result in the specified format:

|  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Return Type Condition|  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | | `str` For text formats like CSV, JSON|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | `pd.DataFrame` When `output_format` is `"DataFrame"` or `"dataframe"`| `pa.Table` When `output_format` is `"ArrowTable"` or `"arrowtable"`| chdb result object For other formats | | | | | | | | | |

**Raises**

|  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- |
| Exception Condition|  |  |  |  | | --- | --- | --- | --- | | `ChdbError` If the SQL query execution fails|  |  | | --- | --- | | `ImportError` If required dependencies are missing for DataFrame/Arrow formats | | | | | |

**Examples**

```
>>> # Basic CSV query
>>> result = chdb.query("SELECT 1, 'hello'")
>>> print(result)
"1,hello"
```

```
>>> # Query with DataFrame output
>>> df = chdb.query("SELECT 1 as id, 'hello' as msg", "DataFrame")
>>> print(df)
   id    msg
0   1  hello
```

```
>>> # Query with file-based database
>>> result = chdb.query("CREATE TABLE test (id INT) ENGINE = Memory", path="mydb.chdb")
```

```
>>> # Query with UDF
>>> result = chdb.query("SELECT my_udf('test')", udf_path="/path/to/udfs")
```

---

### `chdb.sql`[​](#chdb_sql "Direct link to chdb_sql")

Execute SQL query using chDB engine.

This is the main query function that executes SQL statements using the embedded
ClickHouse engine. Supports various output formats and can work with in-memory
or file-based databases.

**Syntax**

```
chdb.sql(sql, output_format='CSV', path='', udf_path='')
```

**Parameters**

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Parameter Type Default Description|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `sql` str *required* SQL query string to execute|  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `output_format` str `"CSV"` Output format for results. Supported formats: • `"CSV"` - Comma-separated values • `"JSON"` - JSON format • `"Arrow"` - Apache Arrow format • `"Parquet"` - Parquet format • `"DataFrame"` - Pandas DataFrame • `"ArrowTable"` - PyArrow Table • `"Debug"` - Enable verbose logging| `path` str `""` Database file path. Defaults to in-memory database. Can be a file path or `":memory:"` for in-memory database| `udf_path` str `""` Path to User-Defined Functions directory | | | | | | | | | | | | | | | | | | | |

**Returns**

Returns the query result in the specified format:

|  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Return Type Condition|  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | | `str` For text formats like CSV, JSON|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | `pd.DataFrame` When `output_format` is `"DataFrame"` or `"dataframe"`| `pa.Table` When `output_format` is `"ArrowTable"` or `"arrowtable"`| chdb result object For other formats | | | | | | | | | |

**Raises**

|  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- |
| Exception Condition|  |  |  |  | | --- | --- | --- | --- | | [`ChdbError`](#chdberror) If the SQL query execution fails|  |  | | --- | --- | | `ImportError` If required dependencies are missing for DataFrame/Arrow formats | | | | | |

**Examples**

```
>>> # Basic CSV query
>>> result = chdb.query("SELECT 1, 'hello'")
>>> print(result)
"1,hello"
```

```
>>> # Query with DataFrame output
>>> df = chdb.query("SELECT 1 as id, 'hello' as msg", "DataFrame")
>>> print(df)
   id    msg
0   1  hello
```

```
>>> # Query with file-based database
>>> result = chdb.query("CREATE TABLE test (id INT) ENGINE = Memory", path="mydb.chdb")
```

```
>>> # Query with UDF
>>> result = chdb.query("SELECT my_udf('test')", udf_path="/path/to/udfs")
```

---

### `chdb.to_arrowTable`[​](#chdb-state-sqlitelike-to_arrowtable "Direct link to chdb-state-sqlitelike-to_arrowtable")

Convert query result to PyArrow Table.

Converts a chDB query result to a PyArrow Table for efficient columnar data processing.
Returns an empty table if the result is empty.

**Syntax**

```
chdb.to_arrowTable(res)
```

**Parameters**

|  |  |  |  |
| --- | --- | --- | --- |
| Parameter Description|  |  | | --- | --- | | `res` chDB query result object containing binary Arrow data | | | |

**Returns**

|  |  |  |  |
| --- | --- | --- | --- |
| Return type Description|  |  | | --- | --- | | `pa.Table` PyArrow Table containing the query results | | | |

**Raises**

|  |  |  |  |
| --- | --- | --- | --- |
| Error type Description|  |  | | --- | --- | | `ImportError` If pyarrow or pandas aren't installed | | | |

**Example**

```
>>> result = chdb.query("SELECT 1 as id, 'hello' as msg", "Arrow")
>>> table = chdb.to_arrowTable(result)
>>> print(table.to_pandas())
   id    msg
0   1  hello
```

---

### `chdb.to_df`[​](#chdb_to_df "Direct link to chdb_to_df")

Convert query result to pandas DataFrame.

Converts a chDB query result to a pandas DataFrame by first converting to
PyArrow Table and then to pandas using multi-threading for better performance.

**Syntax**

```
chdb.to_df(r)
```

**Parameters**

|  |  |  |  |
| --- | --- | --- | --- |
| Parameter Description|  |  | | --- | --- | | `r` chDB query result object containing binary Arrow data | | | |

**Returns**

|  |  |  |  |
| --- | --- | --- | --- |
| Return Type Description|  |  | | --- | --- | | `pd.DataFrame` pandas DataFrame containing the query results | | | |

**Raises**

|  |  |  |  |
| --- | --- | --- | --- |
| Exception Condition|  |  | | --- | --- | | `ImportError` If pyarrow or pandas aren't installed | | | |

**Example**

```
>>> result = chdb.query("SELECT 1 as id, 'hello' as msg", "Arrow")
>>> df = chdb.to_df(result)
>>> print(df)
   id    msg
0   1  hello
```

## Connection and Session Management[​](#connection-session-management "Direct link to Connection and Session Management")

The following Session Functions are available:

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

Create a connection to chDB background server.