_mssql module reference

Complete documentation of _mssql module classes, methods and properties.

Module-level symbols

_mssql.__version__

See pymssql.__version__.

_mssql.VERSION

See pymssql.VERSION.

New in version 2.2.0.

_mssql.__full_version__

See pymssql.__full_version__.

Variables whose values you can change to alter behavior on a global basis:

_mssql.login_timeout

Timeout for connection and login in seconds, default 60.

_mssql.min_error_severity

Minimum severity of errors at which to begin raising exceptions. The default value of 6 should be appropriate in most cases.

Functions

_mssql.set_max_connections(number)

Sets maximum number of simultaneous connections allowed to be open at any given time. Default is 25.

_mssql.get_max_connections()

Gets current maximum number of simultaneous connections allowed to be open at any given time.

MSSQLConnection class

class _mssql.MSSQLConnection

This class represents an MS SQL database connection. You can make queries and obtain results through a database connection.

You can create an instance of this class by calling _mssql.connect(). It accepts the following arguments. Note that you can use keyword arguments, instead of positional arguments.

Parameters:
  • server (str) –

    Database server and instance you want to connect to. Valid examples are:

    • r'.\SQLEXPRESS' – SQLEXPRESS instance on local machine (Windows only)
    • r'(local)\SQLEXPRESS' – Same as above (Windows only)
    • 'SQLHOST' – Default instance at default port (Windows only)
    • 'SQLHOST' – Specific instance at specific port set up in freetds.conf (Linux/*nix only)
    • 'SQLHOST,1433' – Specified TCP port at specified host
    • 'SQLHOST:1433' – The same as above
    • 'SQLHOST,5000' – If you have set up an instance to listen on port 5000
    • 'SQLHOST:5000' – The same as above
  • user (str) – Database user to connect as
  • password (str) – User’s password
  • charset (str) – Character set name to set for the connection.
  • database (str) – The database you want to initially to connect to; by default, SQL Server selects the database which is set as the default for the specific user
  • appname (str) – Set the application name to use for the connection
  • port (str) – the TCP port to use to connect to the server
  • tds_version (str) – TDS protocol version to ask for. Default value: None
  • conn_properties – SQL queries to send to the server upon connection establishment. Can be a string or another kind of iterable of strings. Default value:
 
SET ARITHABORT ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET ANSI_NULLS ON;
SET ANSI_NULL_DFLT_ON ON;
SET ANSI_PADDING ON;
SET ANSI_WARNINGS ON;
SET ANSI_NULL_DFLT_ON ON;
SET CURSOR_CLOSE_ON_COMMIT ON;
SET QUOTED_IDENTIFIER ON;
SET TEXTSIZE 2147483647; -- http://msdn.microsoft.com/en-us/library/aa259190%28v=sql.80%29.aspx

New in version 2.1.1: The conn_properties parameter.

Changed in version 2.1.1: Before 2.1.1, the initialization queries now specified by conn_properties wasn’t customizable and its value was hard-coded to the literal shown above.

Note

If you need to connect to Azure read the relevant topic.

New in version 2.1.1: The ability to connect to Azure.

Changed in version 2.2.0: The default value of the tds_version parameter was changed to None. Between versions 2.0.0 and 2.1.2 its default value was '7.1'.

Warning

The tds_version parameter has a default value of None. This means two things:

  1. You can’t rely anymore in the old '7.1' default value and
  2. Now you’ll need to either
  • Specify its value explicitly by passing a value for this parameter or
  • Configure it using facilities provided by FreeTDS (see here and here)

This might look cumbersome but at the same time means you can now fully configure the characteristics of a connection to SQL Server when using pymssql/_mssql without using a stanza for the server in the freetds.conf file or even with no freetds.conf at all. Starting with pymssql version 2.0.0 and up to version 2.1.2 it was already possible to set the TDS protocol version to ask for when connecting to the server but version 7.1 was used if not specified.

Warning

FreeTDS added sopport for TDS protocol version 7.3 in version 0.95. You need to be careful of not asking for TDS 7.3 if you know the undelying FreeTDS used by pymssql is version 0.91 as it won’t raise any error nor keep you from passing such an invalid value.

MSSQLConnection object properties

MSSQLConnection.connected

True if the connection object has an open connection to a database, False otherwise.

MSSQLConnection.charset

Character set name that was passed to _mssql.connect().

MSSQLConnection.identity

Returns identity value of last inserted row. If previous operation did not involve inserting a row into a table with identity column, None is returned. Example usage – assume that persons table contains an identity column in addition to name column:

conn.execute_non_query("INSERT INTO persons (name) VALUES('John Doe')")
print "Last inserted row has id = " + conn.identity
MSSQLConnection.query_timeout

Query timeout in seconds, default is 0, which means to wait indefinitely for results. Due to the way DB-Library for C works, setting this property affects all connections opened from the current Python script (or, very technically, all connections made from this instance of dbinit()).

MSSQLConnection.rows_affected

Number of rows affected by last query. For SELECT statements this value is only meaningful after reading all rows.

MSSQLConnection.debug_queries

If set to true, all queries are printed to stderr after formatting and quoting, just before being sent to SQL Server. It may be helpful if you suspect problems with formatting or quoting.

MSSQLConnection.tds_version

The TDS version used by this connection. Can be one of 4.2, 5.0 7.0, 7.1, 7.2, 7.3 or None if no TDS version could be detected.

Changed in version 2.2.0: For correctness and consistency the value used to indicate TDS 7.1 changed from 8.0 to 7.1 on pymssql 2.2.0.

Changed in version 2.1.3: 7.3 was added as a possible value.

MSSQLConnection.tds_version_tuple

New in version 2.2.0.

The TDS version used by this connection in tuple form which is more easily handled (parse, compare) programmatically. Can be one of (4, 2), (5, 0), (7, 0), (7, 1), (7, 2), (7, 3) or None if no TDS version could be detected.

MSSQLConnection object methods

MSSQLConnection.cancel()

Cancel all pending results from the last SQL operation. It can be called more than one time in a row. No exception is raised in this case.

MSSQLConnection.close()

Close the connection and free all memory used. It can be called more than one time in a row. No exception is raised in this case.

MSSQLConnection.execute_query(query_string)
MSSQLConnection.execute_query(query_string, params)

This method sends a query to the MS SQL Server to which this object instance is connected. An exception is raised on failure. If there are pending results or rows prior to executing this command, they are silently discarded.

After calling this method you may iterate over the connection object to get rows returned by the query.

You can use Python formatting and all values get properly quoted. Please see examples for details.

This method is intented to be used on queries that return results, i.e. SELECT.

MSSQLConnection.execute_non_query(query_string)
MSSQLConnection.execute_non_query(query_string, params)

This method sends a query to the MS SQL Server to which this object instance is connected. After completion, its results (if any) are discarded. An exception is raised on failure. If there are pending results or rows prior to executing this command, they are silently discarded.

You can use Python formatting and all values get properly quoted. Please see examples for details.

This method is useful for INSERT, UPDATE, DELETE, and for Data Definition Language commands, i.e. when you need to alter your database schema.

MSSQLConnection.execute_scalar(query_string)
MSSQLConnection.execute_scalar(query_string, params)

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.

You can use Python formatting and all values get properly quoted. Please see examples for details.

This method is useful if you want just a single value from a query, as in the example below. This method works in the same way as iter(conn).next()[0]. Remaining rows, if any, can still be iterated after calling this method.

Example usage:

count = conn.execute_scalar("SELECT COUNT(*) FROM employees")
MSSQLConnection.execute_row(query_string)
MSSQLConnection.execute_row(query_string, params)

This method sends a query to the MS SQL Server to which this object instance is connected, then returns first row of data from result. An exception is raised on failure. If there are pending results or rows prior to executing this command, they are silently discarded.

You can use Python formatting and all values get properly quoted. Please see examples for details.

This method is useful if you want just a single row and don’t want or don’t need to iterate over the connection object. This method works in the same way as iter(conn).next() to obtain single row. Remaining rows, if any, can still be iterated after calling this method.

Example usage:

empinfo = conn.execute_row("SELECT * FROM employees WHERE empid=10")
MSSQLConnection.get_header()

This method is infrastructure and doesn’t need to be called by your code. It gets the Python DB-API compliant header information. Returns a list of 7-element tuples describing current result header. Only name and DB-API compliant type is filled, rest of the data is None, as permitted by the specs.

MSSQLConnection.init_procedure(name)

Create an MSSQLStoredProcedure object that will be used to invoke thestored procedure with the given name.

MSSQLConnection.nextresult()

Move to the next result, skipping all pending rows. This method fetches and discards any rows remaining from current operation, then it advances to next result (if any). Returns True value if next set is available, None otherwise. An exception is raised on failure.

MSSQLConnection.select_db(dbname)

This function makes the given database the current one. An exception is raised on failure.

MSSQLConnection.__iter__()
MSSQLConnection.next()

New in version 2.1.0.

These methods implement the Python iterator protocol. You most likely will not call them directly, but indirectly by using iterators.

MSSQLConnection.set_msghandler(handler)

New in version 2.1.1.

This method allows setting a message handler function for the connection to allow a client to gain access to the messages returned from the server.

The signature of the message handler function handler passed to this method must be:

def my_msg_handler(msgstate, severity, srvname, procname, line, msgtext):
    # The body of the message handler.

msgstate, severity and line will be integers, srvname, procname and msgtext will be strings.

MSSQLStoredProcedure class

class _mssql.MSSQLStoredProcedure

This class represents a stored procedure. You create an object of this class by calling the init_procedure() method on MSSQLConnection object.

MSSQLStoredProcedure object properties

MSSQLStoredProcedure.connection

An underlying MSSQLConnection object.

MSSQLStoredProcedure.name

The name of the procedure that this object represents.

MSSQLStoredProcedure.parameters

The parameters that have been bound to this procedure.

MSSQLStoredProcedure object methods

MSSQLStoredProcedure.bind(value, dbtype, name=None, output=False, null=False, max_length=-1)

This method binds a parameter to the stored procedure. value and dbtype are mandatory arguments, the rest is optional.

Parameters:
  • value – Is the value to store in the parameter.
  • dbtype – Is one of: SQLBINARY, SQLBIT, SQLBITN, SQLCHAR, SQLDATETIME, SQLDATETIM4, SQLDATETIMN, SQLDECIMAL, SQLFLT4, SQLFLT8, SQLFLTN, SQLIMAGE, SQLINT1, SQLINT2, SQLINT4, SQLINT8, SQLINTN, SQLMONEY, SQLMONEY4, SQLMONEYN, SQLNUMERIC, SQLREAL, SQLTEXT, SQLVARBINARY, SQLVARCHAR, SQLUUID.
  • name – Is the name of the parameter. Needs to be in "@name" form.
  • output – Is the direction of the parameter. True indicates that it is an output parameter i.e. it returns a value after procedure execution (in SQL DDL they are declared by using the "output" suffix, e.g. "@aname varchar(10) output").
  • null – Boolean. Signals than NULL must be the value to be bound to the argument of this input parameter.
  • max_length – Is the maximum data length for this parameter to be returned from the stored procedure.
MSSQLStoredProcedure.execute()

Execute the stored procedure.

Module-level exceptions

Exception hierarchy:

MSSQLException
|
+-- MSSQLDriverException
|
+-- MSSQLDatabaseException
exception _mssql.MSSQLDriverException

MSSQLDriverException is raised whenever there is a problem within _mssql – e.g. insufficient memory for data structures, and so on.

exception _mssql.MSSQLDatabaseException
MSSQLDatabaseException is raised whenever there is a problem with the database – e.g. query syntax error, invalid object name and so on. In this case you can use the following properties to access details of the error:
number

The error code, as returned by SQL Server.

severity

The so-called severity level, as returned by SQL Server. If value of this property is less than the value of _mssql.min_error_severity, such errors are ignored and exceptions are not raised.

state

The third error code, as returned by SQL Server.

message

The error message, as returned by SQL Server.

You can find an example of how to use this data at the bottom of _mssql examples page.