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.

Inputs

Name	Type	Default	Description
autocommit	bool	False	If set to False transactions are enabled. Certain operations (e.g. CREATE DATABASE) require autocommit=True.
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 connection 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

Constants

input_list = ['autocommit', 'database', 'execute', 'executemany', 'fetchall', 'fetchone', 'fetchval', 'host', 'instance', 'isolation_level', 'params', 'password', 'port', 'transaction', 'user'] output_list = ['log', 'result'] return_many = False ssl_context_inputs = ['check_hostname', 'client_cert', 'client_key', 'server_ca'] version = 1

Methods

execute

details

log

details

one_of_inputs

details

run

details

Example

import flow_api

def handler(system: flow_api.System, this: flow_api.Execution):
    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):
    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):
    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):
    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)