Skip to main content
Version: 10 - TBD

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

  • 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

  • user

    Type: string

    Default: sa

  • password

    Type: anyOf

  • database

    Type: anyOf

  • execute

    Execute a query.

    Type: anyOf

  • executemany

    Execute a query with many rows.

    Type: anyOf

  • transaction

    Execute several queries in a transaction.

    Type: anyOf

  • fetchall

    Execute a query and fetch all rows.

    Type: anyOf

  • fetchone

    Execute a query and fetch the first row.

    Type: anyOf

  • fetchval

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

    Type: anyOf

  • params

    Type: array

  • isolation_level

    The transaction isolation level.

    Type: anyOf

    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)