TaskSQLMSSQL

class tasks.task_sqlmssql.TaskSQLMSSQL

Interact with a MSSQL database. This task supports the execute, fetchall, fetchone, and fetchval commands. Each command expects an SQL query and returns the status, list, record or field value respectively.

Consult the Transact-SQL Reference at https://docs.microsoft.com/en-us/sql/t-sql/language-reference for more information.

Inputs

Name	Type	Default	Description
database	`str`	`None`
execute	`str`	`None`
executemany	`str`	`None`
fetchall	`str`	`None`
fetchone	`str`	`None`
fetchval	`str`	`None`
host	`str`
instance	`str`	`None`	If set, the `SQL Server Browser` on port 1434 will be contacted to query the TCP port of the named instance. The port returned by the `SQL Server Browser` will take precedence to the one specified in the task inputs.
isolation_level	`str`	`READ_COMMITTED`	The transaction isolation level. One of `READ_UNCOMMITTED`, `READ_COMMITTED`, `REPEATABLE_READ`, or `SERIALIZABLE`
params	`list`	`[]`
password	`str`	`None`
port	`int`	`1433`
transaction	`list`	`None`
user	`str`	`sa`

Outputs

Name	Type	Default	Description
log	`list`	`[]`
result	`object`
execution_id	`int`		The ID of the task execution
message	`str`		The ended message for the task. If the task ended with an error, the message will contain information about what went wrong
status	`str`		The ended status for the task. Either "success" or "error".

Constants

input_list = ['database', 'execute', 'executemany', 'fetchall', 'fetchone', 'fetchval', 'host', 'instance', 'isolation_level', 'params', 'password', 'port', 'transaction', 'user']output_list = ['log', 'result']return_many = Falseversion = 1

Methods

run ()

Example

import flow_api
def handler(system: flow_api.System, this: flow_api.Execution):
    mssql_server_version = this.task(
        'SQLMSSQL',
        host='my-mssql-server',
        user='my-user',
        password='secret',
        fetchval='''SELECT @@VERSION AS 'SQL Server Version';''',
    ).get('output_value')['result']
    this.log(mssql_server_version=mssql_server_version)

Insert

import flow_api
def handler(system: flow_api.System, this: flow_api.Execution):
    this.task(
        'SQLMSSQL',
        host='my-mssql-server',
        user='my-user',
        password='secret',
        execute='INSERT INTO table (a, b) VALUES (?, ?)',
        params=['value1', 'value2'],
    )

Bulk insert

import flow_api
def handler(system: flow_api.System, this: flow_api.Execution):
    this.task(
        'SQLMSSQL',
        host='my-mssql-server',
        user='my-user',
        password='secret',
        executemany='INSERT INTO table (a, b) VALUES (?, ?)',
        params=[
            ['row1value1', 'row1value2']
            ['row2value1', 'row2value2']
        ],
    )

Transactions

import flow_api
def handler(system: flow_api.System, this: flow_api.Execution):
    inserted_id = this.task(
        'SQLMSSQL',
        host='my-mssql-server',
        user='my-user',
        password='secret',
        transaction=[
            {
                'execute': 'INSERT INTO table (col1, col2) VALUES (?, ?)',
                'params': ['spam', 'eggs'],
            },
            {
                'fetchval': 'SELECT SCOPE_IDENTITY()',
            },
        ],
    ).get('output_value')['result']
    this.log(inserted_id=inserted_id)

TaskSOAP

TaskSQLORACLE