Skip to content

Script Development / Connector Object DFF.CONN / ClickHouse

The ClickHouse connector operation object provides methods for querying and writing to ClickHouse.

DFF.CONN(...) parameters are as follows:

Parameter Type Required / Default Description
connector_id str Required Connector ID
database str None Specifies the database

.query(...)

Executes an SQL statement and returns the query results. Each row of results is converted into a dict keyed by column names, and all results are returned as a list.

Parameter Type Required / Default Description
sql str Required SQL statement, which may contain parameter placeholders.
? indicates a parameter that needs escaping;
?? indicates a parameter that does not need escaping
sql_params list None SQL parameters
Example
1
2
3
sql = 'SELECT * FROM ?? WHERE age > ?'
sql_params = ['demo_table', 50]
result = helper.query(sql, sql_params=sql_params)

.non_query(...)

Executes SQL statements such as inserts, updates, or deletes, and returns the number of affected rows reported by the driver. The parameters are the same as .query(...).

Example
1
2
3
sql = 'INSERT INTO ?? (name, age) VALUES (?, ?)'
sql_params = ['demo_table', 'Tom', 20]
affected_rows = helper.non_query(sql, sql_params=sql_params)

Both .query(...) and .non_query(...) automatically commit the transaction after successful execution; if execution fails, an exception is thrown.

Dynamic SQL Statements

query(...) and non_query(...) internally use DFF.SQL(...) to construct SQL statements and support building complex dynamic SQL statements.

For example, WHERE IN (...) with an unspecified number of values, or INSERT INTO ... VALUES ... for batch data insertion, etc.

For details, please refer to Script Development / SQL Construction DFF.SQL