Python API Reference

```
>>> # File-based database
>>> conn = Connection('mydata.db')
>>> with conn.cursor() as cur:
...     cur.execute("CREATE TABLE users (id INT, name STRING) ENGINE = MergeTree() order by id")
...     cur.execute("INSERT INTO users VALUES (1, 'Alice')")
>>> conn.close()
```

```
>>> # Context manager usage
>>> with Connection() as cur:
...     cur.execute("SELECT version()")
...     version = cur.fetchone()
```

Note

ClickHouse doesn't support traditional transactions, so commit() and rollback()
operations are no-ops but provided for DB-API compliance.

---

#### `close`[​](#dbapi-connection-close "Direct link to dbapi-connection-close")

Close the database connection.

Closes the underlying chDB connection and marks this connection as closed.
Subsequent operations on this connection will raise an Error.

**Syntax**

```
close()
```

**Raises**

|  |  |  |  |
| --- | --- | --- | --- |
| Exception Condition|  |  | | --- | --- | | [`err.Error`](#chdb-dbapi-err-error) If connection is already closed | | | |

---

#### `commit`[​](#dbapi-commit "Direct link to dbapi-commit")

Commit the current transaction.

**Syntax**

```
commit()
```

Note

This is a no-op for chDB/ClickHouse as it doesn’t support traditional
transactions. Provided for DB-API 2.0 compliance.

---

#### `cursor`[​](#dbapi-cursor "Direct link to dbapi-cursor")

Create a new cursor for executing queries.

**Syntax**

```
cursor(cursor=None)
```

**Parameters**

|  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- |
| Parameter Type Description|  |  |  | | --- | --- | --- | | `cursor` - Ignored, provided for compatibility | | | | | |

**Returns**

|  |  |  |  |
| --- | --- | --- | --- |
| Return Type Description|  |  | | --- | --- | | `Cursor` New cursor object for this connection | | | |

**Raises**

|  |  |  |  |
| --- | --- | --- | --- |
| Exception Condition|  |  | | --- | --- | | [`err.Error`](#chdb-dbapi-err-error) If connection is closed | | | |

**Example**

```
>>> conn = Connection()
>>> cur = conn.cursor()
>>> cur.execute("SELECT 1")
>>> result = cur.fetchone()
```

---

#### `escape`[​](#escape "Direct link to escape")

Escape a value for safe inclusion in SQL queries.

**Syntax**

```
escape(obj, mapping=None)
```

**Parameters**

|  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Parameter Type Description|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | `obj` - Value to escape (string, bytes, number, etc.)|  |  |  | | --- | --- | --- | | `mapping` - Optional character mapping for escaping | | | | | | | | |

**Returns**

|  |  |  |  |
| --- | --- | --- | --- |
| Return Type Description|  |  | | --- | --- | | - Escaped version of the input suitable for SQL queries | | | |

**Example**

```
>>> conn = Connection()
>>> safe_value = conn.escape("O'Reilly")
>>> query = f"SELECT * FROM users WHERE name = {safe_value}"
```

---

#### `escape_string`[​](#escape-string "Direct link to escape-string")

Escape a string value for SQL queries.

**Syntax**

```
escape_string(s)
```

**Parameters**

|  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- |
| Parameter Type Description|  |  |  | | --- | --- | --- | | `s` str String to escape | | | | | |

**Returns**

|  |  |  |  |
| --- | --- | --- | --- |
| Return Type Description|  |  | | --- | --- | | `str` Escaped string safe for SQL inclusion | | | |

---

#### `property open`[​](#property-open "Direct link to property-open")

Check if the connection is open.

**Returns**

|  |  |  |  |
| --- | --- | --- | --- |
| Return Type Description|  |  | | --- | --- | | `bool` True if connection is open, False if closed | | | |

---

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

Execute a SQL query directly and return raw results.

This method bypasses the cursor interface and executes queries directly.
For standard DB-API usage, prefer using cursor() method.

**Syntax**

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

**Parameters:**

|  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Parameter Type Default Description|  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | | `sql` str or bytes *required* SQL query to execute|  |  |  |  | | --- | --- | --- | --- | | `fmt` str `"CSV"` Output format. Supported formats include "CSV", "JSON", "Arrow", "Parquet", etc. | | | | | | | | | | | |

**Returns**

|  |  |  |  |
| --- | --- | --- | --- |
| Return Type Description|  |  | | --- | --- | | - Query result in the specified format | | | |

**Raises**

|  |  |  |  |
| --- | --- | --- | --- |
| Exception Condition|  |  | | --- | --- | | [`err.InterfaceError`](#chdb-dbapi-err-interfaceerror) If connection is closed or query fails | | | |

**Example**

```
>>> conn = Connection()
>>> result = conn.query("SELECT 1, 'hello'", "CSV")
>>> print(result)
"1,hello\n"
```

---

#### `property resp`[​](#property-resp "Direct link to property-resp")

Get the last query response.

**Returns**

|  |  |  |  |
| --- | --- | --- | --- |
| Return Type Description|  |  | | --- | --- | | - The raw response from the last query() call | | | |

Note

This property is updated each time query() is called directly.
It doesn't reflect queries executed through cursors.

---

#### `rollback`[​](#rollback "Direct link to rollback")

Roll back the current transaction.

**Syntax**

```
rollback()
```

Note

This is a no-op for chDB/ClickHouse as it doesn’t support traditional
transactions. Provided for DB-API 2.0 compliance.

---

### Cursor Class[​](#cursor-class "Direct link to Cursor Class")

#### **class** `chdb.dbapi.cursors.Cursor`[​](#chdb-dbapi-cursors-cursor "Direct link to chdb-dbapi-cursors-cursor")

Bases: `object`

DB-API 2.0 cursor for executing queries and fetching results.

The cursor provides methods for executing SQL statements, managing query results,
and navigating through result sets. It supports parameter binding, bulk operations,
and follows DB-API 2.0 specifications.

Don't create Cursor instances directly. Use `Connection.cursor()` instead.

```
class chdb.dbapi.cursors.Cursor(connection)
```

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Variable Type Description|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `description` tuple Column metadata for the last query result|  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `rowcount` int Number of rows affected by the last query (-1 if unknown)|  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `arraysize` int Default number of rows to fetch at once (default: 1)|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | `lastrowid` - ID of the last inserted row (if applicable)|  |  |  | | --- | --- | --- | | `max_stmt_length` int Maximum statement size for executemany() (default: 1024000) | | | | | | | | | | | | | | | | | |

**Examples**

```
>>> conn = Connection()
>>> cur = conn.cursor()
>>> cur.execute("SELECT 1 as id, 'test' as name")
>>> result = cur.fetchone()
>>> print(result)  # (1, 'test')
>>> cur.close()
```

Note

See [DB-API 2.0 Cursor Objects](https://www.python.org/dev/peps/pep-0249/#cursor-objects)
for complete specification details.

---

#### `callproc`[​](#callproc "Direct link to callproc")

Execute a stored procedure (placeholder implementation).

**Syntax**

```
callproc(procname, args=())
```

**Parameters**

|  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Parameter Type Description|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | `procname` str Name of stored procedure to execute|  |  |  | | --- | --- | --- | | `args` sequence Parameters to pass to the procedure | | | | | | | | |

**Returns**

|  |  |  |  |
| --- | --- | --- | --- |
| Return Type Description|  |  | | --- | --- | | `sequence` The original args parameter (unmodified) | | | |

Note