ConnectorTypeSQLMSSQL

class connector_types.connector_type_sqlmssql.ConnectorTypeSQLMSSQL

Interact with a MSSQL database. This connector type 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.

Input Schema

• host

  Type: string

• port

  Type: anyOf

  Options:

• instance

  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 connection inputs.

  Type: anyOf

  Options:

• user

  Type: string

  Default: sa

• password

  Type: anyOf

  Options:

• database

  Type: anyOf

  Options:

• execute

  Execute a query.

  Type: anyOf

  Options:

• executemany

  Execute a query with many rows.

  Type: anyOf

  Options:

• transaction

  Execute several queries in a transaction.

  Type: anyOf

  Options:

• fetchall

  Execute a query and fetch all rows.

  Type: anyOf

  Options:

• fetchone

  Execute a query and fetch the first row.

  Type: anyOf

  Options:

• fetchval

  Execute a query and fetch the first column of the first row.

  Type: anyOf

  Options:

• params

  Type: array

  Items:

• isolation_level

  The transaction isolation level.

  Type: anyOf

  Options:

  Default: READ_COMMITTED

• autocommit

  If set to False transactions are enabled. Certain operations (e.g. CREATE DATABASE) require autocommit=True.

  Type: boolean

Output Schema

• result

  Data

Constants

return_many = False ssl_context_inputs = ['check_hostname', 'client_cert', 'client_key', 'server_ca']

Example

import flow_api

def handler(system: flow_api.System, this: flow_api.Execution, inputs: dict):
    mssql_server_version = this.connect(
        connector_type='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, inputs: dict):
    this.connect(
        connector_type='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, inputs: dict):
    this.connect(
        connector_type='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, inputs: dict):
    inserted_id = this.connect(
        connector_type='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)