Skip to content

Script Development / SQL Construction DFF.SQL

Using DFF.SQL(...) allows for convenient generation of dynamic SQL statements, avoiding SQL injection issues caused by manual string concatenation.

Parameter Type Required / Default Description
sql str Required 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

Most SQL-type connectors have this feature built-in

Most connection objects created via DFF.CONN(...) for SQL-type connectors already have this functionality built-in and can be used directly.

You can directly use conn.query(sql, sql_params) without needing to call this method separately.

This method is primarily useful for conveniently DEBUGGING SQL statements, allowing you to observe the actual executed SQL via print(...).

Example
1
2
3
sql = 'SELECT * FROM ?? WHERE id = ?'
sql_params = [ 'users', 'user-001' ]
print(DFF.SQL(sql, sql_params))
Example Output
1
SELECT * FROM users WHERE id = 'user-001'

Parameter Expansion

To facilitate dynamic SQL generation, this method performs parameter expansion based on the value types within sql_params.

It allows users to package an uncertain number of items as a single parameter, which the system automatically expands.

When using it, simply pass an array or dictionary for a specific parameter placeholder, for example:

Array Expansion

Parameters that are arrays are automatically expanded into the following form:

Array Parameter Expansion
1
p1, p2, p3, ...
Example 1
1
2
3
4
5
6
7
8
9
sql = 'SELECT * FROM ?? WHERE status IN (?)'
sql_params = [
    # Corresponds to the first parameter placeholder ??
    'demo',

    # Corresponds to the second parameter placeholder ?, the parameter value is an array, automatically expanded into multiple values
    [ 'error', 'warning' ]
]
result = db.query(sql, sql_params=sql_params)
Actual Executed SQL
1
SELECT * FROM demo WHERE status IN ('error', 'warning')

If a parameter is an array and its elements are also arrays, a second-level expansion is automatically performed into the following form:

Two-dimensional Array Parameter Expansion
1
(p1_1, p1_2, p1_3, ...), (p2_1, p2_2, p2_3, ...), (p3_1, p3_2, p3_3, ...), ...`.
Example 2
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
sql = 'INSERT INTO ?? (id, name, value) VALUES ?'
sql_params = [
    # Corresponds to the first parameter placeholder ??
    'demo',

    # Corresponds to the second parameter placeholder ?, the parameter value is an array, automatically expanded into multiple values (i.e., multiple database records)
    [
        # Elements within the parameter value array are also arrays, automatically further expanded (i.e., multiple fields for each database record)
        [ 1, 'zhang3', 100 ],
        [ 1, 'li4', 200 ],
    ]
]
affected_rows = db.non_query(sql, sql_params=sql_params)
Actual Executed SQL
1
INSERT INTO demo (id, name, value) VALUES (1, 'zhang3', 100), (1, 'li4', 200)

Dictionary Expansion

Example
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
sql = 'INSERT INTO ?? SET ?'
sql_params = [
    # Corresponds to the first parameter placeholder ??
    'demo',

    # Corresponds to the second parameter placeholder ?, the dictionary is automatically expanded into multiple assignment syntaxes
    {
      'id'   : 1,
      'name' : 'zhang3',
      'value': 100,
    }
]
affected_rows = db.non_query(sql, sql_params=sql_params)
Actual Executed SQL
1
INSERT INTO demo SET id = 1, name = 'zhang3', value = 100