Script Development / Connector Object `DFF.CONN` / MySQL

The MySQL connector operation object mainly provides methods for operating MySQL. This connector is compatible with the following databases:

MariaDB
Percona Server for MySQL
Alibaba Cloud PolarDB for MySQL
Alibaba Cloud OceanBase
Alibaba Cloud AnalyticDB (ADB) for MySQL

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

Parameter	Type	Required / Default	Description
`connector_id`	str	Required	Connector ID
`database`	str	`None`	Specified database

`.query(...)`

Execute an SQL statement and return query results in list[dict] format. The parameters are as follows:

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

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

`.non_query(...)`

Execute SQL statements such as insert, delete, and update, and return the number of affected rows. The parameters are as follows:

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

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

Both .query(...) and .non_query(...) automatically commit the transaction after successful execution; on failure, they automatically roll back and raise an exception.

Helper Methods

Method	Description
`.query_raw(sql, sql_params=None)`	Executes raw query SQL without going through `DFF.SQL(...)` construction
`.non_query_raw(sql, sql_params=None)`	Executes raw non-query SQL without going through `DFF.SQL(...)` construction
`.format_sql(sql, sql_params=None, pretty=False)`	Generates the final SQL string, suitable for debugging
`.tables()`	Returns the list of table names in the current database
`.create_sql_builder(raw_sql=None)`	Creates an SQL Builder

For routine dynamic SQL, prefer using the sql_params of .query(...) or .non_query(...). Only use the raw SQL methods when the SQL has already been fully constructed by trusted code.

Transactions

To execute multiple SQL statements in the same transaction, use the following methods:

Method	Description
`.start_trans()`	Starts a transaction and returns a transaction connection object
`.trans_query(trans_conn, sql, sql_params=None)`	Executes a query in the transaction and returns `list[dict]`
`.trans_non_query(trans_conn, sql, sql_params=None)`	Executes non-query SQL in 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

Example
trans_conn = db.start_trans()
db.trans_non_query(trans_conn, 'UPDATE ?? SET status = ? WHERE id = ?', ['demo', 'done', 1])
rows = db.trans_query(trans_conn, 'SELECT * FROM ?? WHERE id = ?', ['demo', 1])
db.commit(trans_conn)

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 Statement

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

For example, an uncertain number of values in WHERE IN (...), or batch writing data with INSERT INTO ... VALUES ..., etc.

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

Script Development / Connector Object DFF.CONN / MySQL

.query(...)

.non_query(...)