Support for the Microsoft SQL Server database.

DBAPI Support¶

The following dialect/DBAPI options are available. Please refer to individual DBAPI sections for connect information.

• PyODBC
• mxODBC
• pymssql
• zxJDBC for Jython
• adodbapi

Auto Increment Behavior¶

SQL Server provides so-called “auto incrementing” behavior using the IDENTITY construct, which can be placed on an integer primary key. SQLAlchemy considers IDENTITY within its default “autoincrement” behavior, described at Column.autoincrement; this means that by default, the first integer primary key column in a Table will be considered to be the identity column and will generate DDL as such:

from sqlalchemy import Table, MetaData, Column, Integer

m = MetaData()
t = Table('t', m,
        Column('id', Integer, primary_key=True),
        Column('x', Integer))
m.create_all(engine)

The above example will generate DDL as:

CREATE TABLE t (
    id INTEGER NOT NULL IDENTITY(1,1),
    x INTEGER NULL,
    PRIMARY KEY (id)
)

For the case where this default generation of IDENTITY is not desired, specify autoincrement=False on all integer primary key columns:

m = MetaData()
t = Table('t', m,
        Column('id', Integer, primary_key=True, autoincrement=False),
        Column('x', Integer))
m.create_all(engine)

from sqlalchemy import Table, Integer, Sequence, Column

Table('test', metadata,
       Column('id', Integer,
              Sequence('blah', start=100, increment=10),
              primary_key=True),
       Column('name', String(20))
     ).create(some_engine)

CREATE TABLE test (
  id INTEGER NOT NULL IDENTITY(100,10) PRIMARY KEY,
  name VARCHAR(20) NULL,
  )

m = MetaData()
t = Table('t', m, Column('id', Integer, primary_key=True),
                Column('x', Integer))
m.create_all(engine)

engine.execute(t.insert(), {'id': 1, 'x':1}, {'id':2, 'x':2})

CREATE TABLE t (
    id INTEGER NOT NULL IDENTITY(1,1),
    x INTEGER NULL,
    PRIMARY KEY (id)
)

COMMIT
SET IDENTITY_INSERT t ON
INSERT INTO t (id, x) VALUES (?, ?)
((1, 1), (2, 2))
SET IDENTITY_INSERT t OFF
COMMIT

Rendering of SQL statements that include schema qualifiers¶

When using Table metadata that includes a “schema” qualifier, such as:

account_table = Table(
    'account', metadata,
    Column('id', Integer, primary_key=True),
    Column('info', String(100)),
    schema="customer_schema"
)

The SQL Server dialect has a long-standing behavior that it will attempt to turn a schema-qualified table name into an alias, such as:

>>> eng = create_engine("mssql+pymssql://mydsn")
>>> print(account_table.select().compile(eng))
SELECT account_1.id, account_1.info
FROM customer_schema.account AS account_1

This behavior is legacy, does not function correctly for many forms of SQL statements, and will be disabled by default in the 1.1 series of SQLAlchemy. As of 1.0.5, the above statement will produce the following warning:

SAWarning: legacy_schema_aliasing flag is defaulted to True;
  some schema-qualified queries may not function correctly.
  Consider setting this flag to False for modern SQL Server versions;
  this flag will default to False in version 1.1

This warning encourages the Engine to be created as follows:

>>> eng = create_engine("mssql+pymssql://mydsn", legacy_schema_aliasing=False)

Where the above SELECT statement will produce:

>>> print(account_table.select().compile(eng))
SELECT customer_schema.account.id, customer_schema.account.info
FROM customer_schema.account

The warning will not emit if the legacy_schema_aliasing flag is set to either True or False.

Collation Support¶

Character collations are supported by the base string types, specified by the string argument “collation”:

from sqlalchemy import VARCHAR
Column('login', VARCHAR(32, collation='Latin1_General_CI_AS'))

When such a column is associated with a Table, the CREATE TABLE statement for this column will yield:

login VARCHAR(32) COLLATE Latin1_General_CI_AS NULL

LIMIT/OFFSET Support¶

MSSQL has no support for the LIMIT or OFFSET keywords. LIMIT is supported directly through the TOP Transact SQL keyword:

select.limit

will yield:

SELECT TOP n

If using SQL Server 2005 or above, LIMIT with OFFSET support is available through the ROW_NUMBER OVER construct. For versions below 2005, LIMIT with OFFSET usage will fail.

Nullability¶

MSSQL has support for three levels of column nullability. The default nullability allows nulls and is explicit in the CREATE TABLE construct:

name VARCHAR(20) NULL

If nullable=None is specified then no specification is made. In other words the database’s configured default is used. This will render:

name VARCHAR(20)

If nullable is True or False then the column will be NULL` or ``NOT NULL respectively.

Date / Time Handling¶

DATE and TIME are supported. Bind parameters are converted to datetime.datetime() objects as required by most MSSQL drivers, and results are processed from strings if needed. The DATE and TIME types are not available for MSSQL 2005 and previous - if a server version below 2008 is detected, DDL for these types will be issued as DATETIME.

Large Text/Binary Type Deprecation¶

Per SQL Server 2012/2014 Documentation, the NTEXT, TEXT and IMAGE datatypes are to be removed from SQL Server in a future release. SQLAlchemy normally relates these types to the UnicodeText, Text and LargeBinary datatypes.

In order to accommodate this change, a new flag deprecate_large_types is added to the dialect, which will be automatically set based on detection of the server version in use, if not otherwise set by the user. The behavior of this flag is as follows:

• When this flag is True, the UnicodeText, Text and LargeBinary datatypes, when used to render DDL, will render the types NVARCHAR(max), VARCHAR(max), and VARBINARY(max), respectively. This is a new behavior as of the addition of this flag.

• When this flag is False, the UnicodeText, Text and LargeBinary datatypes, when used to render DDL, will render the types NTEXT, TEXT, and IMAGE, respectively. This is the long-standing behavior of these types.

• The flag begins with the value None, before a database connection is established. If the dialect is used to render DDL without the flag being set, it is interpreted the same as False.

• On first connection, the dialect detects if SQL Server version 2012 or greater is in use; if the flag is still at None, it sets it to True or False based on whether 2012 or greater is detected.

• The flag can be set to either True or False when the dialect is created, typically via create_engine():

  eng = create_engine("mssql+pymssql://user:pass@host/db",
                  deprecate_large_types=True)

• Complete control over whether the “old” or “new” types are rendered is available in all SQLAlchemy versions by using the UPPERCASE type objects instead: NVARCHAR, VARCHAR, types.VARBINARY, TEXT, mssql.NTEXT, mssql.IMAGE will always remain fixed and always output exactly that type.

Clustered Index Support¶

The MSSQL dialect supports clustered indexes (and primary keys) via the mssql_clustered option. This option is available to Index, UniqueConstraint. and PrimaryKeyConstraint.

To generate a clustered index:

Index("my_index", table.c.x, mssql_clustered=True)

which renders the index as CREATE CLUSTERED INDEX my_index ON table (x).

To generate a clustered primary key use:

Table('my_table', metadata,
      Column('x', ...),
      Column('y', ...),
      PrimaryKeyConstraint("x", "y", mssql_clustered=True))

which will render the table, for example, as:

CREATE TABLE my_table (x INTEGER NOT NULL, y INTEGER NOT NULL,
                       PRIMARY KEY CLUSTERED (x, y))

Similarly, we can generate a clustered unique constraint using:

  Table('my_table', metadata,
        Column('x', ...),
        Column('y', ...),
        PrimaryKeyConstraint("x"),
        UniqueConstraint("y", mssql_clustered=True),
        )

.. versionadded:: 0.9.2

MSSQL-Specific Index Options¶

In addition to clustering, the MSSQL dialect supports other special options for Index.

Index("my_index", table.c.x, mssql_include=['y'])

Index("my_index", table.c.x.desc())

Compatibility Levels¶

MSSQL supports the notion of setting compatibility levels at the database level. This allows, for instance, to run a database that is compatible with SQL2000 while running on a SQL2005 database server. server_version_info will always return the database server version information (in this case SQL2005) and not the compatibility level information. Because of this, if running under a backwards compatibility mode SQAlchemy may attempt to use T-SQL statements that are unable to be parsed by the database server.

Triggers¶

SQLAlchemy by default uses OUTPUT INSERTED to get at newly generated primary key values via IDENTITY columns or other server side defaults. MS-SQL does not allow the usage of OUTPUT INSERTED on tables that have triggers. To disable the usage of OUTPUT INSERTED on a per-table basis, specify implicit_returning=False for each Table which has triggers:

Table('mytable', metadata,
    Column('id', Integer, primary_key=True),
    # ...,
    implicit_returning=False
)

Declarative form:

class MyClass(Base):
    # ...
    __table_args__ = {'implicit_returning':False}

This option can also be specified engine-wide using the implicit_returning=False argument on create_engine().

Enabling Snapshot Isolation¶

Not necessarily specific to SQLAlchemy, SQL Server has a default transaction isolation mode that locks entire tables, and causes even mildly concurrent applications to have long held locks and frequent deadlocks. Enabling snapshot isolation for the database as a whole is recommended for modern levels of concurrency support. This is accomplished via the following ALTER DATABASE commands executed at the SQL prompt:

ALTER DATABASE MyDatabase SET ALLOW_SNAPSHOT_ISOLATION ON

ALTER DATABASE MyDatabase SET READ_COMMITTED_SNAPSHOT ON

Background on SQL Server snapshot isolation is available at http://msdn.microsoft.com/en-us/library/ms175095.aspx.

Known Issues¶

• No support for more than one IDENTITY column per table
• reflection of indexes does not work with versions older than SQL Server 2005

SQL Server Data Types¶

As with all SQLAlchemy dialects, all UPPERCASE types that are known to be valid with SQL server are importable from the top level dialect, whether they originate from sqlalchemy.types or from the local dialect:

from sqlalchemy.dialects.mssql import \
    BIGINT, BINARY, BIT, CHAR, DATE, DATETIME, DATETIME2, \
    DATETIMEOFFSET, DECIMAL, FLOAT, IMAGE, INTEGER, MONEY, \
    NCHAR, NTEXT, NUMERIC, NVARCHAR, REAL, SMALLDATETIME, \
    SMALLINT, SMALLMONEY, SQL_VARIANT, TEXT, TIME, \
    TIMESTAMP, TINYINT, UNIQUEIDENTIFIER, VARBINARY, VARCHAR

Types which are specific to SQL Server, or have SQL Server-specific construction arguments, are as follows:

class sqlalchemy.dialects.mssql.BIT¶

Bases: sqlalchemy.types.TypeEngine

__init__¶

inherited from the __init__ attribute of object

x.__init__(...) initializes x; see help(type(x)) for signature

class sqlalchemy.dialects.mssql.CHAR(length=None, collation=None, convert_unicode=False, unicode_error=None, _warn_on_bytestring=False)¶

Bases: sqlalchemy.types.String

The SQL CHAR type.

__init__(length=None, collation=None, convert_unicode=False, unicode_error=None, _warn_on_bytestring=False)¶

inherited from the __init__() method of String

Create a string-holding type.

Parameters:

length¶ – optional, a length for the column for use in DDL and CAST expressions. May be safely omitted if no CREATE TABLE will be issued. Certain databases may require a length for use in DDL, and will raise an exception when the CREATE TABLE DDL is issued if a VARCHAR with no length is included. Whether the value is interpreted as bytes or characters is database specific. collation¶ – Optional, a column-level collation for use in DDL and CAST expressions. Renders using the COLLATE keyword supported by SQLite, MySQL, and Postgresql. E.g.: >>> from sqlalchemy import cast, select, String >>> print select([cast('some string', String(collation='utf8'))]) SELECT CAST(:param_1 AS VARCHAR COLLATE utf8) AS anon_1 New in version 0.8: Added support for COLLATE to all string types. convert_unicode¶ – When set to True, the String type will assume that input is to be passed as Python unicode objects, and results returned as Python unicode objects. If the DBAPI in use does not support Python unicode (which is fewer and fewer these days), SQLAlchemy will encode/decode the value, using the value of the encoding parameter passed to create_engine() as the encoding. When using a DBAPI that natively supports Python unicode objects, this flag generally does not need to be set. For columns that are explicitly intended to store non-ASCII data, the Unicode or UnicodeText types should be used regardless, which feature the same behavior of convert_unicode but also indicate an underlying column type that directly supports unicode, such as NVARCHAR. For the extremely rare case that Python unicode is to be encoded/decoded by SQLAlchemy on a backend that does natively support Python unicode, the value force can be passed here which will cause SQLAlchemy’s encode/decode services to be used unconditionally. unicode_error¶ – Optional, a method to use to handle Unicode conversion errors. Behaves like the errors keyword argument to the standard library’s string.decode() functions. This flag requires that convert_unicode is set to force - otherwise, SQLAlchemy is not guaranteed to handle the task of unicode conversion. Note that this flag adds significant performance overhead to row-fetching operations for backends that already return unicode objects natively (which most DBAPIs do). This flag should only be used as a last resort for reading strings from a column with varied or corrupted encodings.

class sqlalchemy.dialects.mssql.DATETIME2(precision=None, **kw)¶

Bases: sqlalchemy.dialects.mssql.base._DateTimeBase, sqlalchemy.types.DateTime

class sqlalchemy.dialects.mssql.DATETIMEOFFSET(precision=None, **kwargs)¶

Bases: sqlalchemy.types.TypeEngine

class sqlalchemy.dialects.mssql.IMAGE(length=None)¶

Bases: sqlalchemy.types.LargeBinary

__init__(length=None)¶

inherited from the __init__() method of LargeBinary

Construct a LargeBinary type.

Parameters:	length¶ – optional, a length for the column for use in DDL statements, for those binary types that accept a length, such as the MySQL BLOB type.

class sqlalchemy.dialects.mssql.MONEY¶

Bases: sqlalchemy.types.TypeEngine

__init__¶

inherited from the __init__ attribute of object

x.__init__(...) initializes x; see help(type(x)) for signature

class sqlalchemy.dialects.mssql.NCHAR(length=None, **kwargs)¶

Bases: sqlalchemy.types.Unicode

The SQL NCHAR type.

__init__(length=None, **kwargs)¶

inherited from the __init__() method of Unicode

Create a Unicode object.

Parameters are the same as that of String, with the exception that convert_unicode defaults to True.

class sqlalchemy.dialects.mssql.NTEXT(length=None, **kwargs)¶

Bases: sqlalchemy.types.UnicodeText

MSSQL NTEXT type, for variable-length unicode text up to 2^30 characters.

__init__(length=None, **kwargs)¶

inherited from the __init__() method of UnicodeText

Create a Unicode-converting Text type.

Parameters are the same as that of Text, with the exception that convert_unicode defaults to True.

class sqlalchemy.dialects.mssql.NVARCHAR(length=None, **kwargs)¶

Bases: sqlalchemy.types.Unicode

The SQL NVARCHAR type.

__init__(length=None, **kwargs)¶

inherited from the __init__() method of Unicode

Create a Unicode object.

Parameters are the same as that of String, with the exception that convert_unicode defaults to True.

class sqlalchemy.dialects.mssql.REAL(**kw)¶

Bases: sqlalchemy.types.REAL

class sqlalchemy.dialects.mssql.SMALLDATETIME(timezone=False)¶

Bases: sqlalchemy.dialects.mssql.base._DateTimeBase, sqlalchemy.types.DateTime

__init__(timezone=False)¶

inherited from the __init__() method of DateTime

Construct a new DateTime.

Parameters:	timezone¶ – boolean. If True, and supported by the backend, will produce ‘TIMESTAMP WITH TIMEZONE’. For backends that don’t support timezone aware timestamps, has no effect.

class sqlalchemy.dialects.mssql.SMALLMONEY¶

Bases: sqlalchemy.types.TypeEngine

__init__¶

inherited from the __init__ attribute of object

x.__init__(...) initializes x; see help(type(x)) for signature

class sqlalchemy.dialects.mssql.SQL_VARIANT¶

Bases: sqlalchemy.types.TypeEngine

__init__¶

inherited from the __init__ attribute of object

x.__init__(...) initializes x; see help(type(x)) for signature

class sqlalchemy.dialects.mssql.TEXT(length=None, collation=None, convert_unicode=False, unicode_error=None, _warn_on_bytestring=False)¶

Bases: sqlalchemy.types.Text

The SQL TEXT type.

__init__(length=None, collation=None, convert_unicode=False, unicode_error=None, _warn_on_bytestring=False)¶

inherited from the __init__() method of String

Create a string-holding type.

Parameters:

length¶ – optional, a length for the column for use in DDL and CAST expressions. May be safely omitted if no CREATE TABLE will be issued. Certain databases may require a length for use in DDL, and will raise an exception when the CREATE TABLE DDL is issued if a VARCHAR with no length is included. Whether the value is interpreted as bytes or characters is database specific. collation¶ – Optional, a column-level collation for use in DDL and CAST expressions. Renders using the COLLATE keyword supported by SQLite, MySQL, and Postgresql. E.g.: >>> from sqlalchemy import cast, select, String >>> print select([cast('some string', String(collation='utf8'))]) SELECT CAST(:param_1 AS VARCHAR COLLATE utf8) AS anon_1 New in version 0.8: Added support for COLLATE to all string types. convert_unicode¶ – When set to True, the String type will assume that input is to be passed as Python unicode objects, and results returned as Python unicode objects. If the DBAPI in use does not support Python unicode (which is fewer and fewer these days), SQLAlchemy will encode/decode the value, using the value of the encoding parameter passed to create_engine() as the encoding. When using a DBAPI that natively supports Python unicode objects, this flag generally does not need to be set. For columns that are explicitly intended to store non-ASCII data, the Unicode or UnicodeText types should be used regardless, which feature the same behavior of convert_unicode but also indicate an underlying column type that directly supports unicode, such as NVARCHAR. For the extremely rare case that Python unicode is to be encoded/decoded by SQLAlchemy on a backend that does natively support Python unicode, the value force can be passed here which will cause SQLAlchemy’s encode/decode services to be used unconditionally. unicode_error¶ – Optional, a method to use to handle Unicode conversion errors. Behaves like the errors keyword argument to the standard library’s string.decode() functions. This flag requires that convert_unicode is set to force - otherwise, SQLAlchemy is not guaranteed to handle the task of unicode conversion. Note that this flag adds significant performance overhead to row-fetching operations for backends that already return unicode objects natively (which most DBAPIs do). This flag should only be used as a last resort for reading strings from a column with varied or corrupted encodings.

class sqlalchemy.dialects.mssql.TIME(precision=None, **kwargs)¶

Bases: sqlalchemy.types.TIME

class sqlalchemy.dialects.mssql.TINYINT¶

Bases: sqlalchemy.types.Integer

__init__¶

inherited from the __init__ attribute of object

x.__init__(...) initializes x; see help(type(x)) for signature

class sqlalchemy.dialects.mssql.UNIQUEIDENTIFIER¶

Bases: sqlalchemy.types.TypeEngine

__init__¶

inherited from the __init__ attribute of object

x.__init__(...) initializes x; see help(type(x)) for signature

class sqlalchemy.dialects.mssql.VARCHAR(length=None, collation=None, convert_unicode=False, unicode_error=None, _warn_on_bytestring=False)¶

Bases: sqlalchemy.types.String

The SQL VARCHAR type.

__init__(length=None, collation=None, convert_unicode=False, unicode_error=None, _warn_on_bytestring=False)¶

inherited from the __init__() method of String

Create a string-holding type.

Parameters:

length¶ – optional, a length for the column for use in DDL and CAST expressions. May be safely omitted if no CREATE TABLE will be issued. Certain databases may require a length for use in DDL, and will raise an exception when the CREATE TABLE DDL is issued if a VARCHAR with no length is included. Whether the value is interpreted as bytes or characters is database specific. collation¶ – Optional, a column-level collation for use in DDL and CAST expressions. Renders using the COLLATE keyword supported by SQLite, MySQL, and Postgresql. E.g.: >>> from sqlalchemy import cast, select, String >>> print select([cast('some string', String(collation='utf8'))]) SELECT CAST(:param_1 AS VARCHAR COLLATE utf8) AS anon_1 New in version 0.8: Added support for COLLATE to all string types. convert_unicode¶ – When set to True, the String type will assume that input is to be passed as Python unicode objects, and results returned as Python unicode objects. If the DBAPI in use does not support Python unicode (which is fewer and fewer these days), SQLAlchemy will encode/decode the value, using the value of the encoding parameter passed to create_engine() as the encoding. When using a DBAPI that natively supports Python unicode objects, this flag generally does not need to be set. For columns that are explicitly intended to store non-ASCII data, the Unicode or UnicodeText types should be used regardless, which feature the same behavior of convert_unicode but also indicate an underlying column type that directly supports unicode, such as NVARCHAR. For the extremely rare case that Python unicode is to be encoded/decoded by SQLAlchemy on a backend that does natively support Python unicode, the value force can be passed here which will cause SQLAlchemy’s encode/decode services to be used unconditionally. unicode_error¶ – Optional, a method to use to handle Unicode conversion errors. Behaves like the errors keyword argument to the standard library’s string.decode() functions. This flag requires that convert_unicode is set to force - otherwise, SQLAlchemy is not guaranteed to handle the task of unicode conversion. Note that this flag adds significant performance overhead to row-fetching operations for backends that already return unicode objects natively (which most DBAPIs do). This flag should only be used as a last resort for reading strings from a column with varied or corrupted encodings.

PyODBC¶

Support for the Microsoft SQL Server database via the PyODBC driver.

mssql+pyodbc://<username>:<password>@<dsnname>

engine = create_engine("mssql+pyodbc://scott:tiger@some_dsn")

dsn=mydsn;UID=user;PWD=pass

engine = create_engine("mssql+pyodbc://scott:tiger@myhost:port/databasename?driver=SQL+Server+Native+Client+10.0")

import urllib
params = urllib.quote_plus("DRIVER={SQL Server Native Client 10.0};SERVER=dagger;DATABASE=test;UID=user;PWD=password")

engine = create_engine("mssql+pyodbc:///?odbc_connect=%s" % params)

mxODBC¶

Support for the Microsoft SQL Server database via the mxODBC driver.

mssql+mxodbc://<username>:<password>@<dsnname>

pymssql¶

Support for the Microsoft SQL Server database via the pymssql driver.

mssql+pymssql://<username>:<password>@<freetds_name>?charset=utf8

pymssql is a Python module that provides a Python DBAPI interface around FreeTDS. Compatible builds are available for Linux, MacOSX and Windows platforms.

zxjdbc¶

Support for the Microsoft SQL Server database via the zxJDBC for Jython driver.

mssql+zxjdbc://user:pass@host:port/dbname[?key=value&key=value...]

AdoDBAPI¶

Support for the Microsoft SQL Server database via the adodbapi driver.

mssql+adodbapi://<username>:<password>@<dsnname>