# SQL Server Tasks


This module contains a collection of tasks for interacting with SQL Server databases via the pyodbc library.

# SqlServerExecute

class

prefect.tasks.sql_server.sql_server.SqlServerExecute

(db_name, user, host, port=1433, driver="ODBC Driver 17 for SQL Server", query=None, data=None, commit=False, **kwargs)[source]

Task for executing a query against a SQL Server database.

Args:

  • db_name (str): name of SQL Server database
  • user (str): user name used to authenticate
  • host (str): database host address
  • port (int, optional): port used to connect to SQL Server database, defaults to 1433 if not provided
  • driver (str, optional): driver used to communicate with SQL Server database
  • query (str, optional): query to execute against database
  • data (tuple, optional): values to use in query, must be specified using placeholder in query string
  • commit (bool, optional): set to True to commit transaction, defaults to false
  • **kwargs (dict, optional): additional keyword arguments to pass to the Task constructor

methods:                                                                                                                                                       

prefect.tasks.sql_server.sql_server.SqlServerExecute.run

(query=None, data=None, commit=False, password=None)[source]

Task run method. Executes a query against SQL Server database.

Args:

  • query (str, optional): query to execute against database
  • data (tuple, optional): values to use in query, must be specified using placeholder in query string
  • commit (bool, optional): set to True to commit transaction, defaults to false
  • password (str): password used to authenticate; should be provided from a Secret task
Returns:
  • None
Raises:
  • ValueError: if query parameter is None or a blank string
  • DatabaseError: if exception occurs when executing the query



# SqlServerExecuteMany

class

prefect.tasks.sql_server.sql_server.SqlServerExecuteMany

(db_name, user, host, port=1433, driver="ODBC Driver 17 for SQL Server", query=None, data=None, commit=False, **kwargs)[source]

Task for executing many queries against a SQL Server database.

Args:

  • db_name (str): name of SQL Server database
  • user (str): user name used to authenticate
  • host (str): database host address
  • port (int, optional): port used to connect to SQL Server database, defaults to 1433 if not provided
  • driver (str, optional): driver used to communicate with SQL Server database
  • query (str, optional): query to execute against database
  • data (tuple, optional): values to use in query, must be specified using placeholder in query string
  • commit (bool, optional): set to True to commit transaction, defaults to false
  • **kwargs (dict, optional): additional keyword arguments to pass to the Task constructor

methods:                                                                                                                                                       

prefect.tasks.sql_server.sql_server.SqlServerExecuteMany.run

(query=None, data=None, commit=False, password=None, fast_executemany=False)[source]

Task run method. Executes many queries against SQL Server database.

Args:

  • query (str, optional): query to execute against database
  • data (List[tuple], optional): list of values to use in query, must be specified using placeholder
  • commit (bool, optional): set to True to commit transaction, defaults to false
  • password (str): password used to authenticate; should be provided from a Secret task
  • fast_executemany (bool, optional): sends all params to the DB server in one bundle with the SQL statement. DB executes the SQL against all the params as one DB transaction
Returns:
  • None
Raises:
  • ValueError: if query parameter is None or a blank string
  • DatabaseError: if exception occurs when executing the query



# SqlServerFetch

class

prefect.tasks.sql_server.sql_server.SqlServerFetch

(db_name, user, host, port=1433, driver="ODBC Driver 17 for SQL Server", fetch="one", fetch_count=10, query=None, data=None, commit=False, **kwargs)[source]

Task for fetching results of query from SQL Server database.

Args:

  • db_name (str): name of SQL Server database
  • user (str): user name used to authenticate
  • host (str): database host address
  • port (int, optional): port used to connect to SQL Server database, defaults to 5432 if not provided
  • driver (str, optional): driver used to communicate with SQL Server database
  • fetch (str, optional): one of "one" "many" or "all", used to determine how many results to fetch from executed query
  • fetch_count (int, optional): if fetch = 'many', determines the number of results to fetch, defaults to 10
  • query (str, optional): query to execute against database
  • data (tuple, optional): values to use in query, must be specified using placeholder in query string
  • commit (bool, optional): set to True to commit transaction, defaults to false
  • **kwargs (dict, optional): additional keyword arguments to pass to the Task constructor

methods:                                                                                                                                                       

prefect.tasks.sql_server.sql_server.SqlServerFetch.run

(fetch="one", fetch_count=10, query=None, data=None, commit=False, password=None)[source]

Task run method. Executes a query against SQL Server database and fetches results.

Args:

  • fetch (str, optional): one of "one" "many" or "all", used to determine how many results to fetch from executed query
  • fetch_count (int, optional): if fetch = 'many', determines the number of results to fetch, defaults to 10
  • query (str, optional): query to execute against database
  • data (tuple, optional): values to use in query, must be specified using placeholder in query string
  • commit (bool, optional): set to True to commit transaction, defaults to false
  • password (str): password used to authenticate; should be provided from a Secret task
Returns:
  • records (tuple or list of tuples): records from provided query
Raises:
  • ValueError: if query parameter is None or a blank string
  • DatabaseError: if exception occurs when executing the query



This documentation was auto-generated from commit e699fce
on October 14, 2021 at 18:31 UTC