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

• schema_version

  Type: string

• authentication

  Type: anyOf

  Options:

• host

  The remote hostname or IP address.

  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: string

• database

  Type: string

• mode

  Type: anyOf

  Options:

• 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

• fast_executemany

  If set to True the executemany method will use the fast_executemany flag. This is faster but might use subtly different datatype conversion.

  Type: boolean

• connect_timeout

  A timeout for connecting to a peer in seconds.

  Type: integer

  Default: 30

• total_timeout

  Total timeout for the request in seconds.

  Type: integer

  Default: 30

Output Schema

Example

import flow_api

def handler(system: flow_api.System, this: flow_api.Execution, inputs: dict):
    mssql_server_version = this.connect(
        connector_type='SQLMSSQL',
        authentication={
            'authentication_method': 'username_password',
            'username': '...',
            'password': '...',
        },
        host='...',
        mode={
            'mode_name': 'fetchval',
            'query': 'SELECT @@VERSION AS 'SQL Server Version';',
        },
    ).get('output_value')['result']
    this.log(mssql_server_version=mssql_server_version)
    return this.success('all done')

More

Insert

import flow_api

def handler(system: flow_api.System, this: flow_api.Execution, inputs: dict):
    this.connect(
        connector_type='SQLMSSQL',
        authentication={
            'authentication_method': 'username_password',
            'username': '...',
            'password': '...',
        },
        host='...',
        port={
            'port_mode': 'port_number',
            'port_number': 1433,
        },
        mode={
            'mode_name': 'execute',
            'query': 'INSERT INTO table (string_col, int_col) VALUES (?, ?)',
            'params': [
                'value1',
                42,
            ],
        },
    )
    return this.success('all done')

Bulk insert

import flow_api

def handler(system: flow_api.System, this: flow_api.Execution, inputs: dict):
    this.connect(
        connector_type='SQLMSSQL',
        authentication={
            'authentication_method': 'username_password',
            'username': '...',
            'password': '...',
        },
        host='...',
        port={
            'port_mode': 'service_name',
            'service_name': 'ms-sql-s',
        },
        database='...',
        mode={
            'mode_name': 'executemany',
            'query': 'INSERT INTO table (string_col, int_col) VALUES (?, ?)',
            'rows': [
                ['value1', 42],
                ['value2', 4711],
            ],
            'batch_size': 100,
        },
    )
    return this.success('all done')

Transactions

import flow_api

def handler(system: flow_api.System, this: flow_api.Execution, inputs: dict):
    results = this.connect(
        connector_type='SQLMSSQL',
        authentication={
            'authentication_method': 'username_password',
            'username': '...',
            'password': '...',
        },
        host='...',
        instance='...',  # connect to named instance, look up the instance port
        database='...',
        mode={
            'mode_name': 'transaction',
            'queries': [
                {
                    'mode_name': 'execute',
                    'query': 'INSERT INTO table (string_col, int_col) VALUES (?, ?)',
                    'params': [
                        'value1',
                        42,
                    ],
                },
                {
                    'mode_name': 'fetchval',
                    'query': 'SELECT SCOPE_IDENTITY()',
                },
            ],
        },
    ).get('output_value')['result']
    this.log(results=results)
    return this.success('all done')