Close
    logoCloudomation Docs

    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

    NameTypeDefaultDescription
    databasestrNone
    executestrNone
    executemanystrNone
    fetchallstrNone
    fetchonestrNone
    fetchvalstrNone
    hoststr
    instancestrNoneIf 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_levelstrREAD_COMMITTEDThe transaction isolation level. One of READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, or SERIALIZABLE
    paramslist[]
    passwordstrNone
    portint1433
    transactionlistNone
    userstrsa

    Outputs

    NameTypeDefaultDescription
    loglist[]
    resultobject
    execution_idintThe ID of the task execution
    messagestrThe ended message for the task. If the task ended with an error, the message will contain information about what went wrong
    statusstrThe 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)

    More

    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)
    Previous
    TaskSMTP
    Next
    TaskSQLORACLE