Python API Reference

This class extends frozenset to support DB-API 2.0 type comparison semantics.
It allows for flexible type checking where individual items can be compared
against the set using both equality and inequality operators.

This is used for type constants like STRING, BINARY, NUMBER, etc. to enable
comparisons like “field\_type == STRING” where field\_type is a single type value.

**Examples**

```
>>> string_types = DBAPISet([FIELD_TYPE.STRING, FIELD_TYPE.VAR_STRING])
>>> FIELD_TYPE.STRING == string_types  # Returns True
>>> FIELD_TYPE.INT != string_types     # Returns True
>>> FIELD_TYPE.BLOB in string_types    # Returns False
```

---

#### `chdb.dbapi.TIMESTAMP = frozenset({7, 12})`[​](#timestamp-type "Direct link to timestamp-type")

Extended frozenset for DB-API 2.0 type comparison.

This class extends frozenset to support DB-API 2.0 type comparison semantics.
It allows for flexible type checking where individual items can be compared
against the set using both equality and inequality operators.

This is used for type constants like STRING, BINARY, NUMBER, etc. to enable
comparisons like “field\_type == STRING” where field\_type is a single type value.

**Examples**

```
>>> string_types = DBAPISet([FIELD_TYPE.STRING, FIELD_TYPE.VAR_STRING])
>>> FIELD_TYPE.STRING == string_types  # Returns True
>>> FIELD_TYPE.INT != string_types     # Returns True
>>> FIELD_TYPE.BLOB in string_types    # Returns False
```

#### `chdb.dbapi.DATETIME = frozenset({7, 12})`[​](#datetime-type "Direct link to datetime-type")

Extended frozenset for DB-API 2.0 type comparison.

This class extends frozenset to support DB-API 2.0 type comparison semantics.
It allows for flexible type checking where individual items can be compared
against the set using both equality and inequality operators.

This is used for type constants like STRING, BINARY, NUMBER, etc. to enable
comparisons like “field\_type == STRING” where field\_type is a single type value.

**Examples**

```
>>> string_types = DBAPISet([FIELD_TYPE.STRING, FIELD_TYPE.VAR_STRING])
>>> FIELD_TYPE.STRING == string_types  # Returns True
>>> FIELD_TYPE.INT != string_types     # Returns True
>>> FIELD_TYPE.BLOB in string_types    # Returns False
```

---

#### `chdb.dbapi.ROWID = frozenset({})`[​](#rowid-type "Direct link to rowid-type")

Extended frozenset for DB-API 2.0 type comparison.

This class extends frozenset to support DB-API 2.0 type comparison semantics.
It allows for flexible type checking where individual items can be compared
against the set using both equality and inequality operators.

This is used for type constants like STRING, BINARY, NUMBER, etc. to enable
comparisons like “field\_type == STRING” where field\_type is a single type value.

**Examples**

```
>>> string_types = DBAPISet([FIELD_TYPE.STRING, FIELD_TYPE.VAR_STRING])
>>> FIELD_TYPE.STRING == string_types  # Returns True
>>> FIELD_TYPE.INT != string_types     # Returns True
>>> FIELD_TYPE.BLOB in string_types    # Returns False
```

**Usage Examples**

Basic Query Example:

```
import chdb.dbapi as dbapi

print("chdb driver version: {0}".format(dbapi.get_client_info()))

# Create connection and cursor
conn = dbapi.connect()
cur = conn.cursor()

# Execute query
cur.execute('SELECT version()')
print("description:", cur.description)
print("data:", cur.fetchone())

# Clean up
cur.close()
conn.close()
```

Working with Data:

```
import chdb.dbapi as dbapi

conn = dbapi.connect()
cur = conn.cursor()

# Create table
cur.execute("""
    CREATE TABLE employees (
        id UInt32,
        name String,
        department String,
        salary Decimal(10,2)
    ) ENGINE = Memory
""")

# Insert data
cur.execute("""
    INSERT INTO employees VALUES
    (1, 'Alice', 'Engineering', 75000.00),
    (2, 'Bob', 'Marketing', 65000.00),
    (3, 'Charlie', 'Engineering', 80000.00)
""")

# Query data
cur.execute("SELECT * FROM employees WHERE department = 'Engineering'")

# Fetch results
print("Column names:", [desc[0] for desc in cur.description])
for row in cur.fetchall():
    print(row)

conn.close()
```

Connection Management:

```
import chdb.dbapi as dbapi

# In-memory database (default)
conn1 = dbapi.connect()

# Persistent database file
conn2 = dbapi.connect("./my_database.chdb")

# Connection with parameters
conn3 = dbapi.connect("./my_database.chdb?log-level=debug&verbose")

# Read-only connection
conn4 = dbapi.connect("./my_database.chdb?mode=ro")

# Automatic connection cleanup
with dbapi.connect("test.chdb") as conn:
    cur = conn.cursor()
    cur.execute("SELECT count() FROM numbers(1000)")
    result = cur.fetchone()
    print(f"Count: {result[0]}")
    cur.close()
```

**Best Practices**

1. **Connection Management**: Always close connections and cursors when done
2. **Context Managers**: Use `with` statements for automatic cleanup
3. **Batch Processing**: Use `fetchmany()` for large result sets
4. **Error Handling**: Wrap database operations in try-except blocks
5. **Parameter Binding**: Use parameterized queries when possible
6. **Memory Management**: Avoid `fetchall()` for very large datasets

Note

- chDB’s DB-API 2.0 interface is compatible with most Python database tools
- The interface provides Level 1 thread safety (threads may share modules but not connections)
- Connection strings support the same parameters as chDB sessions
- All standard DB-API 2.0 exceptions are supported

Warning

- Always close cursors and connections to avoid resource leaks
- Large result sets should be processed in batches
- Parameter binding syntax follows format style: `%s`

## User-Defined Functions (UDF)[​](#user-defined-functions "Direct link to User-Defined Functions (UDF)")

User-defined functions module for chDB.

This module provides functionality for creating and managing user-defined functions (UDFs)
in chDB. It allows you to extend chDB’s capabilities by writing custom Python functions
that can be called from SQL queries.

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

Decorator for chDB Python UDF(User Defined Function).

**Syntax**

```
chdb.udf.chdb_udf(return_type='String')
```

**Parameters**

|  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- |
| Parameter Type Default Description|  |  |  |  | | --- | --- | --- | --- | | `return_type` str `"String"` Return type of the function. Should be one of the ClickHouse data types | | | | | | | |

**Notes**

1. The function should be stateless. Only UDFs are supported, not UDAFs.
2. Default return type is String. The return type should be one of the ClickHouse data types.
3. The function should take in arguments of type String. All arguments are strings.
4. The function will be called for each line of input.
5. The function should be pure python function. Import all modules used IN THE FUNCTION.
6. Python interpreter used is the same as the one used to run the script.

**Example**

```
@chdb_udf()
def sum_udf(lhs, rhs):
    return int(lhs) + int(rhs)

@chdb_udf()
def func_use_json(arg):
    import json
    # ... use json module
```

---

### `chdb.udf.generate_udf`[​](#generate-udf "Direct link to generate-udf")

Generate UDF configuration and executable script files.

This function creates the necessary files for a User Defined Function (UDF) in chDB:

1. A Python executable script that processes input data
2. An XML configuration file that registers the UDF with ClickHouse

**Syntax**

```
chdb.udf.generate_udf(func_name, args, return_type, udf_body)
```

**Parameters**