# pytds – main module

DB-SIG compliant module for communicating with MS SQL servers

class `pytds.``Connection`

  Connection object, this object should be created by calling `connect()`

  `as_dict`

    Instructs all cursors this connection creates to return results as a dictionary rather than a tuple.

  `autocommit`

    The current state of autocommit on the connection.

  `autocommit_state`

    An alias for autocommit, provided for compatibility with pymssql

  `chunk_handler`

    Returns current chunk handler Default is MemoryChunkedHandler()

  `close`()

    Close connection to an MS SQL Server.

    This function tries to close the connection and free all memory used. It can be called more than once in a row. No exception is raised in this case.

  `commit`()

    Commit transaction which is currently in progress.

  `cursor`()

    Return cursor object that can be used to make queries and fetch results from the database.

  `isolation_level`

    Isolation level for transactions, for possible values see Isolation level constants

    See also

    SET TRANSACTION ISOLATION LEVEL in MSSQL documentation

  `mars_enabled`

    Whether MARS is enabled or not on connection

  `product_version`

    Version of the MSSQL server

  `rollback`()

    Roll back transaction which is currently in progress.

  `set_autocommit`(value)

    An alias for autocommit, provided for compatibility with ADO dbapi

  `tds_version`

    Version of tds protocol that is being used by this connection

class `pytds.``Cursor`(conn, session, tzinfo_factory)

  This class represents a database cursor, which is used to issue queries and fetch results from a database connection.

  `callproc`(procname, parameters=())

    Call a stored procedure with the given name.

    Parameters:

    - procname (str) – The name of the procedure to call
    - parameters (sequence) – The optional parameters for the procedure

  `cancel`()

    Cancel current statement

  `close`()

    Closes the cursor. The cursor is unusable from this point.

  `connection`

    Provides link back to `Connection` of this cursor

  `copy_to`(file, table_or_view, sep='\t', columns=None, check_constraints=False, fire_triggers=False, keep_nulls=False, kb_per_batch=None, rows_per_batch=None, order=None, tablock=False)

    Experimental. Efficiently load data to database from file using `BULK INSERT` operation

    Parameters:

    - file – Source file-like object, should be in csv format
    - table_or_view (str) – Destination table or view in the database

    Optional parameters:

    Parameters:

    - sep (str) – Separator used in csv file
    - columns (list) – List of column names in target table to insert to, if not provided will insert into all columns
    - check_constraints (bool) – Check table constraints for incoming data
    - fire_triggers (bool) – Enable or disable triggers for table
    - keep_nulls (bool) – If enabled null values inserted as-is, instead of inserting default value for column
    - kb_per_batch (int) – Kilobytes per batch can be used to optimize performance, see MSSQL server documentation for details
    - rows_per_batch (int) – Rows per batch can be used to optimize performance, see MSSQL server documentation for details
    - order (list) – The ordering of the data in source table. List of columns with ASC or DESC suffix. E.g. `['order_id ASC', 'name DESC']` Can be used to optimize performance, see MSSQL server documentation for details
    - tablock – Enable or disable table lock for the duration of bulk load

  `description`

    Cursor description, see http://legacy.python.org/dev/peps/pep-0249/#description

  `execute`(operation, params=())

    Execute the query

    Parameters: | operation (str) – SQL statement

  `execute_scalar`(query_string, params=None)

    This method sends a query to the MS SQL Server to which this object instance is connected, then returns first column of first row from result. An exception is raised on failure. If there are pending

    results or rows prior to executing this command, they are silently discarded.

    This method accepts Python formatting. Please see execute_query() for details.

    This method is useful if you want just a single value, as in:

    > `conn.execute_scalar('SELECT COUNT(*) FROM employees')`

    This method works in the same way as `iter(conn).next()[0]`. Remaining rows, if any, can still be iterated after calling this method.

  `fetchall`()

    Fetches all remaining rows

  `fetchmany`(size=None)

    Fetches next multiple rows

    Parameters: | size – Maximum number of rows to return, default value is cursor.arraysize
    Returns: | List of rows

  `fetchone`()

    Fetches next row, or `None` if there are no more rows

  `get_proc_return_status`()

    Last stored proc result

  `messages`

    Messages generated by server, see http://legacy.python.org/dev/peps/pep-0249/#cursor-messages

  `native_description`

    todo document

  `nextset`()

    Move to next recordset in batch statement, all rows of current recordset are discarded if present.

    Returns: | true if successful or `None` when there are no more recordsets

  `return_value`

    Alias to `get_proc_return_status()`

  `rowcount`

    Number of rows affected by previous statement

    Returns: | -1 if this information was not supplied by MSSQL server

  `setinputsizes`(sizes=None)

    This method does nothing, as permitted by DB-API specification.

  `setoutputsize`(size=None, column=0)

    This method does nothing, as permitted by DB-API specification.

  `spid`

    MSSQL Server’s SPID (session id)

`pytds.``apilevel` = '2.0'

  Compliant with DB SIG 2.0

`pytds.``connect`(dsn=None, database=None, user=None, password=None, timeout=None, login_timeout=15, as_dict=None, appname=None, port=None, tds_version=1946157060, encryption_level=0, autocommit=False, blocksize=4096, use_mars=False, auth=None, readonly=False, load_balancer=None, use_tz=None, bytes_to_unicode=True, row_strategy=None, failover_partner=None, server=None)

  Opens connection to the database

  Parameters:

  - dsn (string) – SQL server host and instance: <host>[<instance>]
  - failover_partner (string) – secondary database host, used if primary is not accessible
  - database (string) – the database to initially connect to
  - user (string) – database user to connect as
  - password (string) – user’s password
  - timeout (int) – query timeout in seconds, default 0 (no timeout)
  - login_timeout (int) – timeout for connection and login in seconds, default 15
  - as_dict (boolean) – whether rows should be returned as dictionaries instead of tuples.
  - appname (string) – Set the application name to use for the connection
  - port (int) – the TCP port to use to connect to the server
  - tds_version (int) – Maximum TDS version to use, should only be used for testing
  - encryption_level – Encryption level to use, not supported
  - autocommit (bool) – Enable or disable database level autocommit
  - blocksize (int) – Size of block for the TDS protocol, usually should not be used
  - use_mars (bool) – Enable or disable MARS
  - auth – An instance of authentication method class, e.g. Ntlm or Sspi
  - readonly (bool) – Allows to enable read-only mode for connection, only supported by MSSQL 2012, earlier versions will ignore this parameter
  - load_balancer – An instance of load balancer class to use, if not provided will not use load balancer
  - use_tz – Provides timezone for naive database times, if not provided date and time will be returned in naive format
  - bytes_to_unicode (bool) – If true single byte database strings will be converted to unicode Python strings, otherwise will return strings as `bytes` without conversion.
  - row_strategy (function of list of column names returning row factory) – strategy used to create rows, determines type of returned rows, can be custom or one of: `tuple_row_strategy()`, `list_row_strategy()`, `dict_row_strategy()`, `namedtuple_row_strategy()`, `recordtype_row_strategy()`

  Returns: | An instance of `Connection`

`pytds.``dict_row_strategy`(column_names)

  Dict row strategy, rows returned as dictionaries

`pytds.``list_row_strategy`(column_names)

  List row strategy, rows returned as lists

`pytds.``namedtuple_row_strategy`(column_names)

  Namedtuple row strategy, rows returned as named tuples

  Column names that are not valid Python identifiers will be replaced with col<number>_

`pytds.``paramstyle` = 'pyformat'

  This module uses extended python format codes

`pytds.``recordtype_row_strategy`(column_names)

  Recordtype row strategy, rows returned as recordtypes

  Column names that are not valid Python identifiers will be replaced with col<number>_

`pytds.``threadsafety` = 1

  Module may be shared, but not connections

`pytds.``tuple_row_strategy`(column_names)

  Tuple row strategy, rows returned as tuples, default

# pytds.login – login with NTLM and SSPI

class `pytds.login.``NtlmAuth`(user_name, password)

  NTLM authentication, uses Python implementation

  Parameters:

  - user_name (str) – User name
  - password (str) – User password

class `pytds.login.``SspiAuth`(user_name='', password='', server_name='', port=None, spn=None)

  SSPI authentication

  Platform: | Windows

  Required parameters are server_name and port or spn

  Parameters:

  - user_name (str) – User name, if not provided current security context will be used
  - password (str) – User password, if not provided current security context will be used
  - server_name (str) – MSSQL server host name
  - port (int) – MSSQL server port
  - spn (str) – Service name

# pytds.tz – timezones

class `pytds.tz.``FixedOffsetTimezone`(offset, name=None)

  Fixed offset in minutes east from UTC.

class `pytds.tz.``UTC`