Python API Reference

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

chDB/ClickHouse doesn't support stored procedures in the traditional sense.
This method is provided for DB-API 2.0 compliance but doesn't perform
any actual operation. Use execute() for all SQL operations.

Compatibility

This is a placeholder implementation. Traditional stored procedure
features like OUT/INOUT parameters, multiple result sets, and server
variables aren't supported by the underlying ClickHouse engine.

---

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

Close the cursor and free associated resources.

After closing, the cursor becomes unusable and any operation will raise an exception.
Closing a cursor exhausts all remaining data and releases the underlying cursor.

**Syntax**

```
close()
```

---

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

Execute a SQL query with optional parameter binding.

This method executes a single SQL statement with optional parameter substitution.
It supports multiple parameter placeholder styles for flexibility.

**Syntax**

```
execute(query, args=None)
```

**Parameters**

|  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Parameter Type Default Description|  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | | `query` str *required* SQL query to execute|  |  |  |  | | --- | --- | --- | --- | | `args` tuple/list/dict `None` Parameters to bind to placeholders | | | | | | | | | | | |

**Returns**

|  |  |  |  |
| --- | --- | --- | --- |
| Return Type Description|  |  | | --- | --- | | `int` Number of affected rows (-1 if unknown) | | | |

**Parameter Styles**

|  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- |
| Style Example|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | Question mark style `"SELECT * FROM users WHERE id = ?"`| Named style `"SELECT * FROM users WHERE name = %(name)s"`| Format style `"SELECT * FROM users WHERE age = %s"` (legacy) | | | | | | | |

**Examples**

```
>>> # Question mark parameters
>>> cur.execute("SELECT * FROM users WHERE id = ? AND age > ?", (123, 18))
>>>
>>> # Named parameters
>>> cur.execute("SELECT * FROM users WHERE name = %(name)s", {'name': 'Alice'})
>>>
>>> # No parameters
>>> cur.execute("SELECT COUNT(*) FROM users")
```

**Raises**

|  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- |
| Exception Condition|  |  |  |  | | --- | --- | --- | --- | | [`ProgrammingError`](#chdb-dbapi-err-programmingerror) If cursor is closed or query is malformed|  |  | | --- | --- | | [`InterfaceError`](#chdb-dbapi-err-interfaceerror) If database error occurs during execution | | | | | |

---

#### `executemany(query, args)`[​](#chdb-dbapi-cursors-cursor-executemany "Direct link to chdb-dbapi-cursors-cursor-executemany")

Execute a query multiple times with different parameter sets.

This method efficiently executes the same SQL query multiple times with
different parameter values. It’s particularly useful for bulk INSERT operations.

**Syntax**

```
executemany(query, args)
```

**Parameters**

|  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Parameter Type Description|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | `query` str SQL query to execute multiple times|  |  |  | | --- | --- | --- | | `args` sequence Sequence of parameter tuples/dicts/lists for each execution | | | | | | | | |

**Returns**

|  |  |  |  |
| --- | --- | --- | --- |
| Return Type Description|  |  | | --- | --- | | `int` Total number of affected rows across all executions | | | |

**Examples**

```
>>> # Bulk insert with question mark parameters
>>> users_data = [(1, 'Alice'), (2, 'Bob'), (3, 'Charlie')]
>>> cur.executemany("INSERT INTO users VALUES (?, ?)", users_data)
>>>
>>> # Bulk insert with named parameters
>>> users_data = [
...     {'id': 1, 'name': 'Alice'},
...     {'id': 2, 'name': 'Bob'}
... ]
>>> cur.executemany(
...     "INSERT INTO users VALUES (%(id)s, %(name)s)",
...     users_data
... )
```

Note

This method improves performance for multiple-row INSERT and UPDATE operations
by optimizing the query execution process.

---

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

Fetch all remaining rows from the query result.

**Syntax**

```
fetchall()
```

**Returns**

|  |  |  |  |
| --- | --- | --- | --- |
| Return Type Description|  |  | | --- | --- | | `list` List of tuples representing all remaining rows | | | |

**Raises**

|  |  |  |  |
| --- | --- | --- | --- |
| Exception Condition|  |  | | --- | --- | | [`ProgrammingError`](#chdb-dbapi-err-programmingerror) If execute() hasn't been called first | | | |

Warning

This method can consume large amounts of memory for big result sets.
Consider using `fetchmany()` for large datasets.

**Example**

```
>>> cursor.execute("SELECT id, name FROM users")
>>> all_rows = cursor.fetchall()
>>> print(len(all_rows))  # Number of total rows
```

---

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

Fetch multiple rows from the query result.

**Syntax**

```
fetchmany(size=1)
```

**Parameters**

|  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- |
| Parameter Type Default Description|  |  |  |  | | --- | --- | --- | --- | | `size` int `1` Number of rows to fetch. If not specified, uses cursor.arraysize | | | | | | | |

**Returns**

|  |  |  |  |
| --- | --- | --- | --- |
| Return Type Description|  |  | | --- | --- | | `list` List of tuples representing the fetched rows | | | |

**Raises**

|  |  |  |  |
| --- | --- | --- | --- |
| Exception Condition|  |  | | --- | --- | | [`ProgrammingError`](#chdb-dbapi-err-programmingerror) If execute() hasn't been called first | | | |

**Example**

```
>>> cursor.execute("SELECT id, name FROM users")
>>> rows = cursor.fetchmany(3)
>>> print(rows)  # [(1, 'Alice'), (2, 'Bob'), (3, 'Charlie')]
```

---

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

Fetch the next row from the query result.

**Syntax**

```
fetchone()
```

**Returns**

|  |  |  |  |
| --- | --- | --- | --- |
| Return Type Description|  |  | | --- | --- | | `tuple or None` Next row as a tuple, or None if no more rows available | | | |

**Raises**

|  |  |  |  |
| --- | --- | --- | --- |
| Exception Condition|  |  | | --- | --- | | [`ProgrammingError`](#chdb-dbapi-err-programmingerror) If `execute()` hasn't been called first | | | |

**Example**

```
>>> cursor.execute("SELECT id, name FROM users LIMIT 3")
>>> row = cursor.fetchone()
>>> print(row)  # (1, 'Alice')
>>> row = cursor.fetchone()
>>> print(row)  # (2, 'Bob')
```

---

#### `max_stmt_length = 1024000`[​](#max-stmt-length "Direct link to max-stmt-length")

Max statement size which [`executemany()`](#chdb-dbapi-cursors-cursor-executemany) generates.

Default value is 1024000.

---

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

Return the exact query string that would be sent to the database.

This method shows the final SQL query after parameter substitution,
which is useful for debugging and logging purposes.

**Syntax**

```
mogrify(query, args=None)
```

**Parameters**