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

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

Greenplum Database
Alibaba Cloud PolarDB for PostgreSQL
Alibaba Cloud AnalyticDB (ADB) for PostgreSQL

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	SQL statement, can contain parameter placeholders. `?` denotes a parameter that requires escaping; `??` denotes a parameter that does not require 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 inserts, updates, or deletes and return the number of affected rows. The parameters are the same as .query(...).

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 upon successful execution; on failure, they automatically roll back and throw an exception.

Helper Methods

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

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

Transactions

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

Method	Description
`.start_trans()`	Start a transaction and return a transaction connection object
`.trans_query(trans_conn, sql, sql_params=None)`	Execute a query in the transaction and return `list[dict]`
`.trans_non_query(trans_conn, sql, sql_params=None)`	Execute non-query SQL in the transaction and return the number of affected rows
`.commit(trans_conn)`	Commit the transaction and close the transaction connection
`.rollback(trans_conn)`	Roll back the transaction and close 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 constructing complex dynamic SQL statements.

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

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

Script Development / Connector Object DFF.CONN / PostgreSQL

.query(...)

.non_query(...)