Script Development / Connector Object DFF.CONN / Oracle Database

The Oracle database connector operation object mainly provides operation methods for Oracle databases.

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

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

.query(...)

Executes SQL statements and returns query results in list[dict] format, with the parameters as follows:

Parameter	Type	Required / Default	Description
sql	str	Required	SQL statement, which can contain parameter placeholders. ? indicates a parameter that needs to be escaped; ?? indicates a parameter that does not need to be escaped
sql_params	list	None	SQL parameters

sql = 'SELECT * FROM ?? WHERE seq > ?'
sql_params = ['demo', 1]
result = db.query(sql, sql_params=sql_params)

.non_query(...)

Executes SQL statements such as INSERT, UPDATE, or DELETE and returns the number of affected rows. The parameters are the same as .query(...).

sql = 'DELETE FROM ?? WHERE id = ?'
sql_params = ['demo', 1]
affected_rows = db.non_query(sql, sql_params=sql_params)

After .query(...) and .non_query(...) execute successfully, the transaction is automatically committed; on failure, it is automatically rolled back and an exception is thrown.

Transactions

When you need to execute multiple SQL statements in the same transaction, use the following methods:

Method	Description
.start_trans()	Starts a transaction and returns the transaction connection object
.trans_query(trans_conn, sql, sql_params=None)	Executes a query within the transaction and returns list[dict]
.trans_non_query(trans_conn, sql, sql_params=None)	Executes non-query SQL within the transaction and returns the number of affected rows
.commit(trans_conn)	Commits the transaction and closes the transaction connection
.rollback(trans_conn)	Rolls back the transaction and closes the transaction connection

If an exception occurs during transaction execution, call .rollback(trans_conn); after the same transaction connection object has been committed or rolled back, it cannot be used again.

Dynamic SQL Statements

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

For example, when the number of values in WHERE IN (...) is uncertain, or when INSERT INTO ... VALUES ... is used for batch data writing.

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