Skip to main content

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

NameTypeDefaultDescription
databasestrNone
executestrNone
executemanystrNone
fetchallstrNone
fetchonestrNone
fetchvalstrNone
hoststr
instancestrNoneIf 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_levelstrREAD_COMMITTEDThe transaction isolation level. One of READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, or SERIALIZABLE
paramslist[]
passwordstrNone
portint1433
transactionlistNone
userstrsa

Outputs

NameTypeDefaultDescription
execution_idintThe ID of the connection execution
loglist[]
messagestrThe ended message for the connection. If the connection ended with an error, the message will contain information about what went wrong
resultobject
statusstrThe 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 = Falseversion = 1

Methods

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)