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
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
execution_id	int		The ID of the connection execution
log	list	[]
message	str		The ended message for the connection. If the connection ended with an error, the message will contain information about what went wrong
result	object
status	str		The ended status for the connection. 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 = False ssl_context_inputs = ['check_hostname', 'client_cert', 'client_key', 'server_ca'] version = 1

Methods

execute ()

log (message)

one_of_inputs (options)

run ()

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)