Script Development / Connector Object DFF.CONN / Microsoft SQL Server

The Microsoft SQL Server connector operation object mainly provides operation methods for Microsoft SQL Server.

The 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. Parameters are as follows:

Parameter	Type	Required / Default	Description
sql	str	Required	SQL statement, may contain parameter placeholders. ? indicates a parameter that needs escaping; ?? indicates a parameter that does not need escaping
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(...)

Execute SQL statements such as insertion, update, or deletion. Parameters are the same as .query(...).

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

The current implementation directly returns the value returned by pymssql.Cursor.execute(...), which is usually None, so it cannot be used to get the affected row count.

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

Transaction

When multiple SQL statements need to be executed 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, usually returns None
.commit(trans_conn)	Commits the transaction and closes the transaction connection
.rollback(trans_conn)	Rolls back the transaction and closes the transaction connection

When an exception occurs during transaction execution, you should 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 building complex dynamic SQL statements.

Such as an uncertain number of values in WHERE IN (...), or batch data insertion via INSERT INTO ... VALUES ..., etc.

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