<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>
Microsoft SQL Server
—
SQLAlchemy 1.2 Documentation
</title>
<!-- begin iterate through site-imported + sphinx environment css_files -->
<link rel="stylesheet" href="../_static/pygments.css" type="text/css" />
<link rel="stylesheet" href="../_static/docs.css" type="text/css" />
<link rel="stylesheet" href="../_static/changelog.css" type="text/css" />
<link rel="stylesheet" href="../_static/sphinx_paramlinks.css" type="text/css" />
<!-- end iterate through site-imported + sphinx environment css_files -->
<!-- begin layout.mako headers -->
<link rel="index" title="Index" href="../genindex.html" />
<link rel="search" title="Search" href="../search.html" />
<link rel="copyright" title="Copyright" href="../copyright.html" />
<link rel="top" title="SQLAlchemy 1.2 Documentation" href="../index.html" />
<link rel="up" title="Dialects" href="index.html" />
<link rel="next" title="Firebird" href="firebird.html" />
<link rel="prev" title="Oracle" href="oracle.html" />
<!-- end layout.mako headers -->
</head>
<body>
<div id="docs-container">
<div id="docs-top-navigation-container" class="body-background">
<div id="docs-header">
<div id="docs-version-header">
Release: <span class="version-num">1.2.19</span>
| Release Date: April 15, 2019
</div>
<h1>SQLAlchemy 1.2 Documentation</h1>
</div>
</div>
<div id="docs-body-container">
<div id="fixed-sidebar" class="withsidebar">
<div id="docs-sidebar-popout">
<h3><a href="../index.html">SQLAlchemy 1.2 Documentation</a></h3>
<p id="sidebar-topnav">
<a href="../contents.html">Contents</a> |
<a href="../genindex.html">Index</a>
</p>
<div id="sidebar-search">
<form class="search" action="../search.html" method="get">
<label>
Search terms:
<input type="text" placeholder="search..." name="q" size="12" />
</label>
<input type="hidden" name="check_keywords" value="yes" />
<input type="hidden" name="area" value="default" />
</form>
</div>
</div>
<div id="docs-sidebar">
<div id="sidebar-banner">
</div>
<div id="docs-sidebar-inner">
<h3>
<a href="index.html" title="Dialects">Dialects</a>
</h3>
<ul>
<li><span class="link-container"><a class="reference external" href="postgresql.html">PostgreSQL</a></span></li>
<li><span class="link-container"><a class="reference external" href="mysql.html">MySQL</a></span></li>
<li><span class="link-container"><a class="reference external" href="sqlite.html">SQLite</a></span></li>
<li><span class="link-container"><a class="reference external" href="oracle.html">Oracle</a></span></li>
<li class="selected"><span class="link-container"><strong>Microsoft SQL Server</strong><a class="paramlink headerlink reference internal" href="#">¶</a></span><ul>
<li><span class="link-container"><a class="reference external" href="#dialect-mssql">Support for the Microsoft SQL Server database.</a></span></li>
<li><span class="link-container"><a class="reference external" href="#auto-increment-behavior">Auto Increment Behavior</a></span><ul>
<li><span class="link-container"><a class="reference external" href="#controlling-start-and-increment">Controlling “Start” and “Increment”</a></span></li>
<li><span class="link-container"><a class="reference external" href="#insert-behavior">INSERT behavior</a></span></li>
</ul>
</li>
<li><span class="link-container"><a class="reference external" href="#max-on-varchar-nvarchar">MAX on VARCHAR / NVARCHAR</a></span></li>
<li><span class="link-container"><a class="reference external" href="#collation-support">Collation Support</a></span></li>
<li><span class="link-container"><a class="reference external" href="#limit-offset-support">LIMIT/OFFSET Support</a></span></li>
<li><span class="link-container"><a class="reference external" href="#transaction-isolation-level">Transaction Isolation Level</a></span></li>
<li><span class="link-container"><a class="reference external" href="#nullability">Nullability</a></span></li>
<li><span class="link-container"><a class="reference external" href="#date-time-handling">Date / Time Handling</a></span></li>
<li><span class="link-container"><a class="reference external" href="#large-text-binary-type-deprecation">Large Text/Binary Type Deprecation</a></span></li>
<li><span class="link-container"><a class="reference external" href="#multipart-schema-names">Multipart Schema Names</a></span></li>
<li><span class="link-container"><a class="reference external" href="#legacy-schema-mode">Legacy Schema Mode</a></span></li>
<li><span class="link-container"><a class="reference external" href="#clustered-index-support">Clustered Index Support</a></span></li>
<li><span class="link-container"><a class="reference external" href="#mssql-specific-index-options">MSSQL-Specific Index Options</a></span><ul>
<li><span class="link-container"><a class="reference external" href="#include">INCLUDE</a></span></li>
<li><span class="link-container"><a class="reference external" href="#index-ordering">Index ordering</a></span></li>
</ul>
</li>
<li><span class="link-container"><a class="reference external" href="#compatibility-levels">Compatibility Levels</a></span></li>
<li><span class="link-container"><a class="reference external" href="#triggers">Triggers</a></span></li>
<li><span class="link-container"><a class="reference external" href="#rowcount-support-orm-versioning">Rowcount Support / ORM Versioning</a></span></li>
<li><span class="link-container"><a class="reference external" href="#enabling-snapshot-isolation">Enabling Snapshot Isolation</a></span></li>
<li><span class="link-container"><a class="reference external" href="#sql-server-data-types">SQL Server Data Types</a></span></li>
<li><span class="link-container"><a class="reference external" href="#module-sqlalchemy.dialects.mssql.pyodbc">PyODBC</a></span><ul>
<li><span class="link-container"><a class="reference external" href="#dialect-mssql-pyodbc-url">DBAPI</a></span></li>
<li><span class="link-container"><a class="reference external" href="#dialect-mssql-pyodbc-connect">Connecting</a></span></li>
<li><span class="link-container"><a class="reference external" href="#connecting-to-pyodbc">Connecting to PyODBC</a></span><ul>
<li><span class="link-container"><a class="reference external" href="#dsn-connections">DSN Connections</a></span></li>
<li><span class="link-container"><a class="reference external" href="#hostname-connections">Hostname Connections</a></span></li>
<li><span class="link-container"><a class="reference external" href="#pass-through-exact-pyodbc-string">Pass through exact Pyodbc string</a></span></li>
</ul>
</li>
<li><span class="link-container"><a class="reference external" href="#driver-unicode-support">Driver / Unicode Support</a></span></li>
<li><span class="link-container"><a class="reference external" href="#rowcount-support">Rowcount Support</a></span></li>
</ul>
</li>
<li><span class="link-container"><a class="reference external" href="#module-sqlalchemy.dialects.mssql.mxodbc">mxODBC</a></span><ul>
<li><span class="link-container"><a class="reference external" href="#dialect-mssql-mxodbc-url">DBAPI</a></span></li>
<li><span class="link-container"><a class="reference external" href="#dialect-mssql-mxodbc-connect">Connecting</a></span></li>
<li><span class="link-container"><a class="reference external" href="#execution-modes">Execution Modes</a></span></li>
</ul>
</li>
<li><span class="link-container"><a class="reference external" href="#module-sqlalchemy.dialects.mssql.pymssql">pymssql</a></span><ul>
<li><span class="link-container"><a class="reference external" href="#dialect-mssql-pymssql-url">DBAPI</a></span></li>
<li><span class="link-container"><a class="reference external" href="#dialect-mssql-pymssql-connect">Connecting</a></span></li>
</ul>
</li>
<li><span class="link-container"><a class="reference external" href="#module-sqlalchemy.dialects.mssql.zxjdbc">zxjdbc</a></span><ul>
<li><span class="link-container"><a class="reference external" href="#dialect-mssql-zxjdbc-url">DBAPI</a></span></li>
<li><span class="link-container"><a class="reference external" href="#dialect-mssql-zxjdbc-connect">Connecting</a></span></li>
</ul>
</li>
<li><span class="link-container"><a class="reference external" href="#module-sqlalchemy.dialects.mssql.adodbapi">AdoDBAPI</a></span><ul>
<li><span class="link-container"><a class="reference external" href="#dialect-mssql-adodbapi-url">DBAPI</a></span></li>
<li><span class="link-container"><a class="reference external" href="#dialect-mssql-adodbapi-connect">Connecting</a></span></li>
</ul>
</li>
</ul>
</li>
<li><span class="link-container"><a class="reference external" href="firebird.html">Firebird</a></span></li>
<li><span class="link-container"><a class="reference external" href="sybase.html">Sybase</a></span></li>
</ul>
</div>
</div>
</div>
<div id="docs-body" class="withsidebar" >
<div class="section" id="module-sqlalchemy.dialects.mssql.base">
<span id="microsoft-sql-server"></span><span id="mssql-toplevel"></span><h1>Microsoft SQL Server<a class="headerlink" href="#module-sqlalchemy.dialects.mssql.base" title="Permalink to this headline">¶</a></h1>
<div class="section" id="dialect-mssql">
<p>Support for the Microsoft SQL Server database.</p>
<h2>DBAPI Support<a class="headerlink" href="#dialect-mssql" title="Permalink to this headline">¶</a></h2>
<p>The following dialect/DBAPI options are available. Please refer to individual DBAPI sections for connect information.<ul class="simple">
<li><p><a class="reference external" href="#module-sqlalchemy.dialects.mssql.pyodbc">PyODBC</a></p></li>
<li><p><a class="reference external" href="#module-sqlalchemy.dialects.mssql.mxodbc">mxODBC</a></p></li>
<li><p><a class="reference external" href="#module-sqlalchemy.dialects.mssql.pymssql">pymssql</a></p></li>
<li><p><a class="reference external" href="#module-sqlalchemy.dialects.mssql.zxjdbc">zxJDBC for Jython</a></p></li>
<li><p><a class="reference external" href="#module-sqlalchemy.dialects.mssql.adodbapi">adodbapi</a></p></li>
</ul>
</p>
</div>
<div class="section" id="auto-increment-behavior">
<h2>Auto Increment Behavior<a class="headerlink" href="#auto-increment-behavior" title="Permalink to this headline">¶</a></h2>
<p>SQL Server provides so-called “auto incrementing” behavior using the
<code class="docutils literal notranslate"><span class="pre">IDENTITY</span></code> construct, which can be placed on an integer primary key.
SQLAlchemy considers <code class="docutils literal notranslate"><span class="pre">IDENTITY</span></code> within its default “autoincrement” behavior,
described at <a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Column.params.autoincrement" title="sqlalchemy.schema.Column"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">Column.autoincrement</span></code></a>; this means
that by default, the first integer primary key column in a <a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><code class="xref py py-class docutils literal notranslate"><span class="pre">Table</span></code></a>
will be considered to be the identity column and will generate DDL as such:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="k">import</span> <span class="n">Table</span><span class="p">,</span> <span class="n">MetaData</span><span class="p">,</span> <span class="n">Column</span><span class="p">,</span> <span class="n">Integer</span>
<span class="n">m</span> <span class="o">=</span> <span class="n">MetaData</span><span class="p">()</span>
<span class="n">t</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s1">'t'</span><span class="p">,</span> <span class="n">m</span><span class="p">,</span>
<span class="n">Column</span><span class="p">(</span><span class="s1">'id'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">primary_key</span><span class="o">=</span><span class="kc">True</span><span class="p">),</span>
<span class="n">Column</span><span class="p">(</span><span class="s1">'x'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">))</span>
<span class="n">m</span><span class="o">.</span><span class="n">create_all</span><span class="p">(</span><span class="n">engine</span><span class="p">)</span></pre></div>
</div>
<p>The above example will generate DDL as:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">CREATE</span> <span class="k">TABLE</span> <span class="n">t</span> <span class="p">(</span>
<span class="n">id</span> <span class="nb">INTEGER</span> <span class="k">NOT</span> <span class="k">NULL</span> <span class="k">IDENTITY</span><span class="p">(</span><span class="mi">1</span><span class="p">,</span><span class="mi">1</span><span class="p">),</span>
<span class="n">x</span> <span class="nb">INTEGER</span> <span class="k">NULL</span><span class="p">,</span>
<span class="k">PRIMARY</span> <span class="k">KEY</span> <span class="p">(</span><span class="n">id</span><span class="p">)</span>
<span class="p">)</span></pre></div>
</div>
<p>For the case where this default generation of <code class="docutils literal notranslate"><span class="pre">IDENTITY</span></code> is not desired,
specify <code class="docutils literal notranslate"><span class="pre">autoincrement=False</span></code> on all integer primary key columns:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">m</span> <span class="o">=</span> <span class="n">MetaData</span><span class="p">()</span>
<span class="n">t</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s1">'t'</span><span class="p">,</span> <span class="n">m</span><span class="p">,</span>
<span class="n">Column</span><span class="p">(</span><span class="s1">'id'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">primary_key</span><span class="o">=</span><span class="kc">True</span><span class="p">,</span> <span class="n">autoincrement</span><span class="o">=</span><span class="kc">False</span><span class="p">),</span>
<span class="n">Column</span><span class="p">(</span><span class="s1">'x'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">))</span>
<span class="n">m</span><span class="o">.</span><span class="n">create_all</span><span class="p">(</span><span class="n">engine</span><span class="p">)</span></pre></div>
</div>
<div class="admonition note">
<p class="admonition-title">Note</p>
<p>An INSERT statement which refers to an explicit value for such
a column is prohibited by SQL Server, however SQLAlchemy will detect this
and modify the <code class="docutils literal notranslate"><span class="pre">IDENTITY_INSERT</span></code> flag accordingly at statement execution
time. As this is not a high performing process, care should be taken to
set the <code class="docutils literal notranslate"><span class="pre">autoincrement</span></code> flag appropriately for columns that will not
actually require IDENTITY behavior.</p>
</div>
<div class="section" id="controlling-start-and-increment">
<h3>Controlling “Start” and “Increment”<a class="headerlink" href="#controlling-start-and-increment" title="Permalink to this headline">¶</a></h3>
<p>Specific control over the parameters of the <code class="docutils literal notranslate"><span class="pre">IDENTITY</span></code> value is supported
using the <a class="reference internal" href="../core/defaults.html#sqlalchemy.schema.Sequence" title="sqlalchemy.schema.Sequence"><code class="xref py py-class docutils literal notranslate"><span class="pre">schema.Sequence</span></code></a> object. While this object normally
represents an explicit “sequence” for supporting backends, on SQL Server it is
re-purposed to specify behavior regarding the identity column, including
support of the “start” and “increment” values:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="k">import</span> <span class="n">Table</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">Sequence</span><span class="p">,</span> <span class="n">Column</span>
<span class="n">Table</span><span class="p">(</span><span class="s1">'test'</span><span class="p">,</span> <span class="n">metadata</span><span class="p">,</span>
<span class="n">Column</span><span class="p">(</span><span class="s1">'id'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span>
<span class="n">Sequence</span><span class="p">(</span><span class="s1">'blah'</span><span class="p">,</span> <span class="n">start</span><span class="o">=</span><span class="mi">100</span><span class="p">,</span> <span class="n">increment</span><span class="o">=</span><span class="mi">10</span><span class="p">),</span>
<span class="n">primary_key</span><span class="o">=</span><span class="kc">True</span><span class="p">),</span>
<span class="n">Column</span><span class="p">(</span><span class="s1">'name'</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="mi">20</span><span class="p">))</span>
<span class="p">)</span><span class="o">.</span><span class="n">create</span><span class="p">(</span><span class="n">some_engine</span><span class="p">)</span></pre></div>
</div>
<p>would yield:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">CREATE</span> <span class="k">TABLE</span> <span class="n">test</span> <span class="p">(</span>
<span class="n">id</span> <span class="nb">INTEGER</span> <span class="k">NOT</span> <span class="k">NULL</span> <span class="k">IDENTITY</span><span class="p">(</span><span class="mi">100</span><span class="p">,</span><span class="mi">10</span><span class="p">)</span> <span class="k">PRIMARY</span> <span class="k">KEY</span><span class="p">,</span>
<span class="n">name</span> <span class="nb">VARCHAR</span><span class="p">(</span><span class="mi">20</span><span class="p">)</span> <span class="k">NULL</span><span class="p">,</span>
<span class="p">)</span></pre></div>
</div>
<p>Note that the <code class="docutils literal notranslate"><span class="pre">start</span></code> and <code class="docutils literal notranslate"><span class="pre">increment</span></code> values for sequences are
optional and will default to 1,1.</p>
</div>
<div class="section" id="insert-behavior">
<h3>INSERT behavior<a class="headerlink" href="#insert-behavior" title="Permalink to this headline">¶</a></h3>
<p>Handling of the <code class="docutils literal notranslate"><span class="pre">IDENTITY</span></code> column at INSERT time involves two key
techniques. The most common is being able to fetch the “last inserted value”
for a given <code class="docutils literal notranslate"><span class="pre">IDENTITY</span></code> column, a process which SQLAlchemy performs
implicitly in many cases, most importantly within the ORM.</p>
<p>The process for fetching this value has several variants:</p>
<ul>
<li><p>In the vast majority of cases, RETURNING is used in conjunction with INSERT
statements on SQL Server in order to get newly generated primary key values:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">INSERT</span> <span class="k">INTO</span> <span class="n">t</span> <span class="p">(</span><span class="n">x</span><span class="p">)</span> <span class="k">OUTPUT</span> <span class="n">inserted</span><span class="p">.</span><span class="n">id</span> <span class="k">VALUES</span> <span class="p">(</span><span class="o">?</span><span class="p">)</span></pre></div>
</div>
</li>
<li><p>When RETURNING is not available or has been disabled via
<code class="docutils literal notranslate"><span class="pre">implicit_returning=False</span></code>, either the <code class="docutils literal notranslate"><span class="pre">scope_identity()</span></code> function or
the <code class="docutils literal notranslate"><span class="pre">@@identity</span></code> variable is used; behavior varies by backend:</p>
<ul>
<li><p>when using PyODBC, the phrase <code class="docutils literal notranslate"><span class="pre">;</span> <span class="pre">select</span> <span class="pre">scope_identity()</span></code> will be
appended to the end of the INSERT statement; a second result set will be
fetched in order to receive the value. Given a table as:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">t</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s1">'t'</span><span class="p">,</span> <span class="n">m</span><span class="p">,</span> <span class="n">Column</span><span class="p">(</span><span class="s1">'id'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">primary_key</span><span class="o">=</span><span class="kc">True</span><span class="p">),</span>
<span class="n">Column</span><span class="p">(</span><span class="s1">'x'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">),</span>
<span class="n">implicit_returning</span><span class="o">=</span><span class="kc">False</span><span class="p">)</span></pre></div>
</div>
<p>an INSERT will look like:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">INSERT</span> <span class="k">INTO</span> <span class="n">t</span> <span class="p">(</span><span class="n">x</span><span class="p">)</span> <span class="k">VALUES</span> <span class="p">(</span><span class="o">?</span><span class="p">);</span> <span class="k">select</span> <span class="n">scope_identity</span><span class="p">()</span></pre></div>
</div>
</li>
<li><p>Other dialects such as pymssql will call upon
<code class="docutils literal notranslate"><span class="pre">SELECT</span> <span class="pre">scope_identity()</span> <span class="pre">AS</span> <span class="pre">lastrowid</span></code> subsequent to an INSERT
statement. If the flag <code class="docutils literal notranslate"><span class="pre">use_scope_identity=False</span></code> is passed to
<a class="reference internal" href="../core/engines.html#sqlalchemy.create_engine" title="sqlalchemy.create_engine"><code class="xref py py-func docutils literal notranslate"><span class="pre">create_engine()</span></code></a>, the statement <code class="docutils literal notranslate"><span class="pre">SELECT</span> <span class="pre">@@identity</span> <span class="pre">AS</span> <span class="pre">lastrowid</span></code>
is used instead.</p></li>
</ul>
</li>
</ul>
<p>A table that contains an <code class="docutils literal notranslate"><span class="pre">IDENTITY</span></code> column will prohibit an INSERT statement
that refers to the identity column explicitly. The SQLAlchemy dialect will
detect when an INSERT construct, created using a core <a class="reference internal" href="../core/dml.html#sqlalchemy.sql.expression.insert" title="sqlalchemy.sql.expression.insert"><code class="xref py py-func docutils literal notranslate"><span class="pre">insert()</span></code></a>
construct (not a plain string SQL), refers to the identity column, and
in this case will emit <code class="docutils literal notranslate"><span class="pre">SET</span> <span class="pre">IDENTITY_INSERT</span> <span class="pre">ON</span></code> prior to the insert
statement proceeding, and <code class="docutils literal notranslate"><span class="pre">SET</span> <span class="pre">IDENTITY_INSERT</span> <span class="pre">OFF</span></code> subsequent to the
execution. Given this example:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">m</span> <span class="o">=</span> <span class="n">MetaData</span><span class="p">()</span>
<span class="n">t</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s1">'t'</span><span class="p">,</span> <span class="n">m</span><span class="p">,</span> <span class="n">Column</span><span class="p">(</span><span class="s1">'id'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">primary_key</span><span class="o">=</span><span class="kc">True</span><span class="p">),</span>
<span class="n">Column</span><span class="p">(</span><span class="s1">'x'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">))</span>
<span class="n">m</span><span class="o">.</span><span class="n">create_all</span><span class="p">(</span><span class="n">engine</span><span class="p">)</span>
<span class="n">engine</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">t</span><span class="o">.</span><span class="n">insert</span><span class="p">(),</span> <span class="p">{</span><span class="s1">'id'</span><span class="p">:</span> <span class="mi">1</span><span class="p">,</span> <span class="s1">'x'</span><span class="p">:</span><span class="mi">1</span><span class="p">},</span> <span class="p">{</span><span class="s1">'id'</span><span class="p">:</span><span class="mi">2</span><span class="p">,</span> <span class="s1">'x'</span><span class="p">:</span><span class="mi">2</span><span class="p">})</span></pre></div>
</div>
<p>The above column will be created with IDENTITY, however the INSERT statement
we emit is specifying explicit values. In the echo output we can see
how SQLAlchemy handles this:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">CREATE</span> <span class="k">TABLE</span> <span class="n">t</span> <span class="p">(</span>
<span class="n">id</span> <span class="nb">INTEGER</span> <span class="k">NOT</span> <span class="k">NULL</span> <span class="k">IDENTITY</span><span class="p">(</span><span class="mi">1</span><span class="p">,</span><span class="mi">1</span><span class="p">),</span>
<span class="n">x</span> <span class="nb">INTEGER</span> <span class="k">NULL</span><span class="p">,</span>
<span class="k">PRIMARY</span> <span class="k">KEY</span> <span class="p">(</span><span class="n">id</span><span class="p">)</span>
<span class="p">)</span>
<span class="k">COMMIT</span>
<span class="k">SET</span> <span class="n">IDENTITY_INSERT</span> <span class="n">t</span> <span class="k">ON</span>
<span class="k">INSERT</span> <span class="k">INTO</span> <span class="n">t</span> <span class="p">(</span><span class="n">id</span><span class="p">,</span> <span class="n">x</span><span class="p">)</span> <span class="k">VALUES</span> <span class="p">(</span><span class="o">?</span><span class="p">,</span> <span class="o">?</span><span class="p">)</span>
<span class="p">((</span><span class="mi">1</span><span class="p">,</span> <span class="mi">1</span><span class="p">),</span> <span class="p">(</span><span class="mi">2</span><span class="p">,</span> <span class="mi">2</span><span class="p">))</span>
<span class="k">SET</span> <span class="n">IDENTITY_INSERT</span> <span class="n">t</span> <span class="k">OFF</span>
<span class="k">COMMIT</span></pre></div>
</div>
<p>This
is an auxiliary use case suitable for testing and bulk insert scenarios.</p>
</div>
</div>
<div class="section" id="max-on-varchar-nvarchar">
<h2>MAX on VARCHAR / NVARCHAR<a class="headerlink" href="#max-on-varchar-nvarchar" title="Permalink to this headline">¶</a></h2>
<p>SQL Server supports the special string “MAX” within the
<code class="xref py py-class docutils literal notranslate"><span class="pre">sqltypes.VARCHAR</span></code> and <code class="xref py py-class docutils literal notranslate"><span class="pre">sqltypes.NVARCHAR</span></code> datatypes,
to indicate “maximum length possible”. The dialect currently handles this as
a length of “None” in the base type, rather than supplying a
dialect-specific version of these types, so that a base type
specified such as <code class="docutils literal notranslate"><span class="pre">VARCHAR(None)</span></code> can assume “unlengthed” behavior on
more than one backend without using dialect-specific types.</p>
<p>To build a SQL Server VARCHAR or NVARCHAR with MAX length, use None:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">my_table</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span>
<span class="s1">'my_table'</span><span class="p">,</span> <span class="n">metadata</span><span class="p">,</span>
<span class="n">Column</span><span class="p">(</span><span class="s1">'my_data'</span><span class="p">,</span> <span class="n">VARCHAR</span><span class="p">(</span><span class="kc">None</span><span class="p">)),</span>
<span class="n">Column</span><span class="p">(</span><span class="s1">'my_n_data'</span><span class="p">,</span> <span class="n">NVARCHAR</span><span class="p">(</span><span class="kc">None</span><span class="p">))</span>
<span class="p">)</span></pre></div>
</div>
</div>
<div class="section" id="collation-support">
<h2>Collation Support<a class="headerlink" href="#collation-support" title="Permalink to this headline">¶</a></h2>
<p>Character collations are supported by the base string types,
specified by the string argument “collation”:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="k">import</span> <span class="n">VARCHAR</span>
<span class="n">Column</span><span class="p">(</span><span class="s1">'login'</span><span class="p">,</span> <span class="n">VARCHAR</span><span class="p">(</span><span class="mi">32</span><span class="p">,</span> <span class="n">collation</span><span class="o">=</span><span class="s1">'Latin1_General_CI_AS'</span><span class="p">))</span></pre></div>
</div>
<p>When such a column is associated with a <a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><code class="xref py py-class docutils literal notranslate"><span class="pre">Table</span></code></a>, the
CREATE TABLE statement for this column will yield:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">login</span> <span class="n">VARCHAR</span><span class="p">(</span><span class="mi">32</span><span class="p">)</span> <span class="n">COLLATE</span> <span class="n">Latin1_General_CI_AS</span> <span class="n">NULL</span></pre></div>
</div>
</div>
<div class="section" id="limit-offset-support">
<h2>LIMIT/OFFSET Support<a class="headerlink" href="#limit-offset-support" title="Permalink to this headline">¶</a></h2>
<p>MSSQL has no support for the LIMIT or OFFSET keywords. LIMIT is
supported directly through the <code class="docutils literal notranslate"><span class="pre">TOP</span></code> Transact SQL keyword:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">select</span><span class="o">.</span><span class="n">limit</span></pre></div>
</div>
<p>will yield:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">SELECT</span> <span class="n">TOP</span> <span class="n">n</span></pre></div>
</div>
<p>If using SQL Server 2005 or above, LIMIT with OFFSET
support is available through the <code class="docutils literal notranslate"><span class="pre">ROW_NUMBER</span> <span class="pre">OVER</span></code> construct.
For versions below 2005, LIMIT with OFFSET usage will fail.</p>
</div>
<div class="section" id="transaction-isolation-level">
<span id="mssql-isolation-level"></span><h2>Transaction Isolation Level<a class="headerlink" href="#transaction-isolation-level" title="Permalink to this headline">¶</a></h2>
<p>All SQL Server dialects support setting of transaction isolation level
both via a dialect-specific parameter
<a class="reference internal" href="../core/engines.html#sqlalchemy.create_engine.params.isolation_level" title="sqlalchemy.create_engine"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">create_engine.isolation_level</span></code></a>
accepted by <a class="reference internal" href="../core/engines.html#sqlalchemy.create_engine" title="sqlalchemy.create_engine"><code class="xref py py-func docutils literal notranslate"><span class="pre">create_engine()</span></code></a>,
as well as the <a class="reference internal" href="../core/connections.html#sqlalchemy.engine.Connection.execution_options.params.isolation_level" title="sqlalchemy.engine.Connection.execution_options"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">Connection.execution_options.isolation_level</span></code></a>
argument as passed to
<a class="reference internal" href="../core/connections.html#sqlalchemy.engine.Connection.execution_options" title="sqlalchemy.engine.Connection.execution_options"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Connection.execution_options()</span></code></a>. This feature works by issuing the
command <code class="docutils literal notranslate"><span class="pre">SET</span> <span class="pre">TRANSACTION</span> <span class="pre">ISOLATION</span> <span class="pre">LEVEL</span> <span class="pre"><level></span></code> for
each new connection.</p>
<p>To set isolation level using <a class="reference internal" href="../core/engines.html#sqlalchemy.create_engine" title="sqlalchemy.create_engine"><code class="xref py py-func docutils literal notranslate"><span class="pre">create_engine()</span></code></a>:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">engine</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span>
<span class="s2">"mssql+pyodbc://scott:tiger@ms_2008"</span><span class="p">,</span>
<span class="n">isolation_level</span><span class="o">=</span><span class="s2">"REPEATABLE READ"</span>
<span class="p">)</span></pre></div>
</div>
<p>To set using per-connection execution options:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">connection</span> <span class="o">=</span> <span class="n">engine</span><span class="o">.</span><span class="n">connect</span><span class="p">()</span>
<span class="n">connection</span> <span class="o">=</span> <span class="n">connection</span><span class="o">.</span><span class="n">execution_options</span><span class="p">(</span>
<span class="n">isolation_level</span><span class="o">=</span><span class="s2">"READ COMMITTED"</span>
<span class="p">)</span></pre></div>
</div>
<p>Valid values for <code class="docutils literal notranslate"><span class="pre">isolation_level</span></code> include:</p>
<ul class="simple">
<li><p><code class="docutils literal notranslate"><span class="pre">AUTOCOMMIT</span></code> - pyodbc / pymssql-specific</p></li>
<li><p><code class="docutils literal notranslate"><span class="pre">READ</span> <span class="pre">COMMITTED</span></code></p></li>
<li><p><code class="docutils literal notranslate"><span class="pre">READ</span> <span class="pre">UNCOMMITTED</span></code></p></li>
<li><p><code class="docutils literal notranslate"><span class="pre">REPEATABLE</span> <span class="pre">READ</span></code></p></li>
<li><p><code class="docutils literal notranslate"><span class="pre">SERIALIZABLE</span></code></p></li>
<li><p><code class="docutils literal notranslate"><span class="pre">SNAPSHOT</span></code> - specific to SQL Server</p></li>
</ul>
<div class="versionadded">
<p><span class="versionmodified added">New in version 1.1: </span>support for isolation level setting on Microsoft
SQL Server.</p>
</div>
<div class="versionadded">
<p><span class="versionmodified added">New in version 1.2: </span>added AUTOCOMMIT isolation level setting</p>
</div>
</div>
<div class="section" id="nullability">
<h2>Nullability<a class="headerlink" href="#nullability" title="Permalink to this headline">¶</a></h2>
<p>MSSQL has support for three levels of column nullability. The default
nullability allows nulls and is explicit in the CREATE TABLE
construct:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">name</span> <span class="n">VARCHAR</span><span class="p">(</span><span class="mi">20</span><span class="p">)</span> <span class="n">NULL</span></pre></div>
</div>
<p>If <code class="docutils literal notranslate"><span class="pre">nullable=None</span></code> is specified then no specification is made. In
other words the database’s configured default is used. This will
render:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">name</span> <span class="n">VARCHAR</span><span class="p">(</span><span class="mi">20</span><span class="p">)</span></pre></div>
</div>
<p>If <code class="docutils literal notranslate"><span class="pre">nullable</span></code> is <code class="docutils literal notranslate"><span class="pre">True</span></code> or <code class="docutils literal notranslate"><span class="pre">False</span></code> then the column will be
<code class="docutils literal notranslate"><span class="pre">NULL</span></code> or <code class="docutils literal notranslate"><span class="pre">NOT</span> <span class="pre">NULL</span></code> respectively.</p>
</div>
<div class="section" id="date-time-handling">
<h2>Date / Time Handling<a class="headerlink" href="#date-time-handling" title="Permalink to this headline">¶</a></h2>
<p>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.</p>
</div>
<div class="section" id="large-text-binary-type-deprecation">
<span id="mssql-large-type-deprecation"></span><h2>Large Text/Binary Type Deprecation<a class="headerlink" href="#large-text-binary-type-deprecation" title="Permalink to this headline">¶</a></h2>
<p>Per
<a class="reference external" href="http://technet.microsoft.com/en-us/library/ms187993.aspx">SQL Server 2012/2014 Documentation</a>,
the <code class="docutils literal notranslate"><span class="pre">NTEXT</span></code>, <code class="docutils literal notranslate"><span class="pre">TEXT</span></code> and <code class="docutils literal notranslate"><span class="pre">IMAGE</span></code> datatypes are to be removed from SQL
Server in a future release. SQLAlchemy normally relates these types to the
<a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.UnicodeText" title="sqlalchemy.types.UnicodeText"><code class="xref py py-class docutils literal notranslate"><span class="pre">UnicodeText</span></code></a>, <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.Text" title="sqlalchemy.types.Text"><code class="xref py py-class docutils literal notranslate"><span class="pre">Text</span></code></a> and <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.LargeBinary" title="sqlalchemy.types.LargeBinary"><code class="xref py py-class docutils literal notranslate"><span class="pre">LargeBinary</span></code></a> datatypes.</p>
<p>In order to accommodate this change, a new flag <code class="docutils literal notranslate"><span class="pre">deprecate_large_types</span></code>
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:</p>
<ul>
<li><p>When this flag is <code class="docutils literal notranslate"><span class="pre">True</span></code>, the <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.UnicodeText" title="sqlalchemy.types.UnicodeText"><code class="xref py py-class docutils literal notranslate"><span class="pre">UnicodeText</span></code></a>, <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.Text" title="sqlalchemy.types.Text"><code class="xref py py-class docutils literal notranslate"><span class="pre">Text</span></code></a> and
<a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.LargeBinary" title="sqlalchemy.types.LargeBinary"><code class="xref py py-class docutils literal notranslate"><span class="pre">LargeBinary</span></code></a> datatypes, when used to render DDL, will render the
types <code class="docutils literal notranslate"><span class="pre">NVARCHAR(max)</span></code>, <code class="docutils literal notranslate"><span class="pre">VARCHAR(max)</span></code>, and <code class="docutils literal notranslate"><span class="pre">VARBINARY(max)</span></code>,
respectively. This is a new behavior as of the addition of this flag.</p></li>
<li><p>When this flag is <code class="docutils literal notranslate"><span class="pre">False</span></code>, the <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.UnicodeText" title="sqlalchemy.types.UnicodeText"><code class="xref py py-class docutils literal notranslate"><span class="pre">UnicodeText</span></code></a>, <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.Text" title="sqlalchemy.types.Text"><code class="xref py py-class docutils literal notranslate"><span class="pre">Text</span></code></a> and
<a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.LargeBinary" title="sqlalchemy.types.LargeBinary"><code class="xref py py-class docutils literal notranslate"><span class="pre">LargeBinary</span></code></a> datatypes, when used to render DDL, will render the
types <code class="docutils literal notranslate"><span class="pre">NTEXT</span></code>, <code class="docutils literal notranslate"><span class="pre">TEXT</span></code>, and <code class="docutils literal notranslate"><span class="pre">IMAGE</span></code>,
respectively. This is the long-standing behavior of these types.</p></li>
<li><p>The flag begins with the value <code class="docutils literal notranslate"><span class="pre">None</span></code>, 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 <code class="docutils literal notranslate"><span class="pre">False</span></code>.</p></li>
<li><p>On first connection, the dialect detects if SQL Server version 2012 or
greater is in use; if the flag is still at <code class="docutils literal notranslate"><span class="pre">None</span></code>, it sets it to <code class="docutils literal notranslate"><span class="pre">True</span></code>
or <code class="docutils literal notranslate"><span class="pre">False</span></code> based on whether 2012 or greater is detected.</p></li>
<li><p>The flag can be set to either <code class="docutils literal notranslate"><span class="pre">True</span></code> or <code class="docutils literal notranslate"><span class="pre">False</span></code> when the dialect
is created, typically via <a class="reference internal" href="../core/engines.html#sqlalchemy.create_engine" title="sqlalchemy.create_engine"><code class="xref py py-func docutils literal notranslate"><span class="pre">create_engine()</span></code></a>:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">eng</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s2">"mssql+pymssql://user:pass@host/db"</span><span class="p">,</span>
<span class="n">deprecate_large_types</span><span class="o">=</span><span class="kc">True</span><span class="p">)</span></pre></div>
</div>
</li>
<li><p>Complete control over whether the “old” or “new” types are rendered is
available in all SQLAlchemy versions by using the UPPERCASE type objects
instead: <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.NVARCHAR" title="sqlalchemy.types.NVARCHAR"><code class="xref py py-class docutils literal notranslate"><span class="pre">NVARCHAR</span></code></a>, <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.VARCHAR" title="sqlalchemy.types.VARCHAR"><code class="xref py py-class docutils literal notranslate"><span class="pre">VARCHAR</span></code></a>, <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.VARBINARY" title="sqlalchemy.types.VARBINARY"><code class="xref py py-class docutils literal notranslate"><span class="pre">types.VARBINARY</span></code></a>,
<a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.TEXT" title="sqlalchemy.types.TEXT"><code class="xref py py-class docutils literal notranslate"><span class="pre">TEXT</span></code></a>, <a class="reference internal" href="#sqlalchemy.dialects.mssql.NTEXT" title="sqlalchemy.dialects.mssql.NTEXT"><code class="xref py py-class docutils literal notranslate"><span class="pre">mssql.NTEXT</span></code></a>, <a class="reference internal" href="#sqlalchemy.dialects.mssql.IMAGE" title="sqlalchemy.dialects.mssql.IMAGE"><code class="xref py py-class docutils literal notranslate"><span class="pre">mssql.IMAGE</span></code></a> will always
remain fixed and always output exactly that type.</p></li>
</ul>
<div class="versionadded">
<p><span class="versionmodified added">New in version 1.0.0.</span></p>
</div>
</div>
<div class="section" id="multipart-schema-names">
<span id="id1"></span><h2>Multipart Schema Names<a class="headerlink" href="#multipart-schema-names" title="Permalink to this headline">¶</a></h2>
<p>SQL Server schemas sometimes require multiple parts to their “schema”
qualifier, that is, including the database name and owner name as separate
tokens, such as <code class="docutils literal notranslate"><span class="pre">mydatabase.dbo.some_table</span></code>. These multipart names can be set
at once using the <a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Table.params.schema" title="sqlalchemy.schema.Table"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">Table.schema</span></code></a> argument of <a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><code class="xref py py-class docutils literal notranslate"><span class="pre">Table</span></code></a>:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">Table</span><span class="p">(</span>
<span class="s2">"some_table"</span><span class="p">,</span> <span class="n">metadata</span><span class="p">,</span>
<span class="n">Column</span><span class="p">(</span><span class="s2">"q"</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="mi">50</span><span class="p">)),</span>
<span class="n">schema</span><span class="o">=</span><span class="s2">"mydatabase.dbo"</span>
<span class="p">)</span></pre></div>
</div>
<p>When performing operations such as table or component reflection, a schema
argument that contains a dot will be split into separate
“database” and “owner” components in order to correctly query the SQL
Server information schema tables, as these two values are stored separately.
Additionally, when rendering the schema name for DDL or SQL, the two
components will be quoted separately for case sensitive names and other
special characters. Given an argument as below:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">Table</span><span class="p">(</span>
<span class="s2">"some_table"</span><span class="p">,</span> <span class="n">metadata</span><span class="p">,</span>
<span class="n">Column</span><span class="p">(</span><span class="s2">"q"</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="mi">50</span><span class="p">)),</span>
<span class="n">schema</span><span class="o">=</span><span class="s2">"MyDataBase.dbo"</span>
<span class="p">)</span></pre></div>
</div>
<p>The above schema would be rendered as <code class="docutils literal notranslate"><span class="pre">[MyDataBase].dbo</span></code>, and also in
reflection, would be reflected using “dbo” as the owner and “MyDataBase”
as the database name.</p>
<p>To control how the schema name is broken into database / owner,
specify brackets (which in SQL Server are quoting characters) in the name.
Below, the “owner” will be considered as <code class="docutils literal notranslate"><span class="pre">MyDataBase.dbo</span></code> and the
“database” will be None:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">Table</span><span class="p">(</span>
<span class="s2">"some_table"</span><span class="p">,</span> <span class="n">metadata</span><span class="p">,</span>
<span class="n">Column</span><span class="p">(</span><span class="s2">"q"</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="mi">50</span><span class="p">)),</span>
<span class="n">schema</span><span class="o">=</span><span class="s2">"[MyDataBase.dbo]"</span>
<span class="p">)</span></pre></div>
</div>
<p>To individually specify both database and owner name with special characters
or embedded dots, use two sets of brackets:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">Table</span><span class="p">(</span>
<span class="s2">"some_table"</span><span class="p">,</span> <span class="n">metadata</span><span class="p">,</span>
<span class="n">Column</span><span class="p">(</span><span class="s2">"q"</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="mi">50</span><span class="p">)),</span>
<span class="n">schema</span><span class="o">=</span><span class="s2">"[MyDataBase.Period].[MyOwner.Dot]"</span>
<span class="p">)</span></pre></div>
</div>
<div class="versionchanged">
<p><span class="versionmodified changed">Changed in version 1.2: </span>the SQL Server dialect now treats brackets as
identifier delimeters splitting the schema into separate database
and owner tokens, to allow dots within either name itself.</p>
</div>
</div>
<div class="section" id="legacy-schema-mode">
<span id="legacy-schema-rendering"></span><h2>Legacy Schema Mode<a class="headerlink" href="#legacy-schema-mode" title="Permalink to this headline">¶</a></h2>
<p>Very old versions of the MSSQL dialect introduced the behavior such that a
schema-qualified table would be auto-aliased when used in a
SELECT statement; given a table:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">account_table</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span>
<span class="s1">'account'</span><span class="p">,</span> <span class="n">metadata</span><span class="p">,</span>
<span class="n">Column</span><span class="p">(</span><span class="s1">'id'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">primary_key</span><span class="o">=</span><span class="kc">True</span><span class="p">),</span>
<span class="n">Column</span><span class="p">(</span><span class="s1">'info'</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="mi">100</span><span class="p">)),</span>
<span class="n">schema</span><span class="o">=</span><span class="s2">"customer_schema"</span>
<span class="p">)</span></pre></div>
</div>
<p>this legacy mode of rendering would assume that “customer_schema.account”
would not be accepted by all parts of the SQL statement, as illustrated
below:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="n">eng</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s2">"mssql+pymssql://mydsn"</span><span class="p">,</span> <span class="n">legacy_schema_aliasing</span><span class="o">=</span><span class="kc">True</span><span class="p">)</span>
<span class="gp">>>> </span><span class="nb">print</span><span class="p">(</span><span class="n">account_table</span><span class="o">.</span><span class="n">select</span><span class="p">()</span><span class="o">.</span><span class="n">compile</span><span class="p">(</span><span class="n">eng</span><span class="p">))</span>
<span class="go">SELECT account_1.id, account_1.info</span>
<span class="go">FROM customer_schema.account AS account_1</span></pre></div>
</div>
<p>This mode of behavior is now off by default, as it appears to have served
no purpose; however in the case that legacy applications rely upon it,
it is available using the <code class="docutils literal notranslate"><span class="pre">legacy_schema_aliasing</span></code> argument to
<a class="reference internal" href="../core/engines.html#sqlalchemy.create_engine" title="sqlalchemy.create_engine"><code class="xref py py-func docutils literal notranslate"><span class="pre">create_engine()</span></code></a> as illustrated above.</p>
<div class="versionchanged">
<p><span class="versionmodified changed">Changed in version 1.1: </span>the <code class="docutils literal notranslate"><span class="pre">legacy_schema_aliasing</span></code> flag introduced
in version 1.0.5 to allow disabling of legacy mode for schemas now
defaults to False.</p>
</div>
</div>
<div class="section" id="clustered-index-support">
<span id="mssql-indexes"></span><h2>Clustered Index Support<a class="headerlink" href="#clustered-index-support" title="Permalink to this headline">¶</a></h2>
<p>The MSSQL dialect supports clustered indexes (and primary keys) via the
<code class="docutils literal notranslate"><span class="pre">mssql_clustered</span></code> option. This option is available to <a class="reference internal" href="../core/constraints.html#sqlalchemy.schema.Index" title="sqlalchemy.schema.Index"><code class="xref py py-class docutils literal notranslate"><span class="pre">Index</span></code></a>,
<a class="reference internal" href="../core/constraints.html#sqlalchemy.schema.UniqueConstraint" title="sqlalchemy.schema.UniqueConstraint"><code class="xref py py-class docutils literal notranslate"><span class="pre">UniqueConstraint</span></code></a>. and <a class="reference internal" href="../core/constraints.html#sqlalchemy.schema.PrimaryKeyConstraint" title="sqlalchemy.schema.PrimaryKeyConstraint"><code class="xref py py-class docutils literal notranslate"><span class="pre">PrimaryKeyConstraint</span></code></a>.</p>
<p>To generate a clustered index:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">Index</span><span class="p">(</span><span class="s2">"my_index"</span><span class="p">,</span> <span class="n">table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">x</span><span class="p">,</span> <span class="n">mssql_clustered</span><span class="o">=</span><span class="kc">True</span><span class="p">)</span></pre></div>
</div>
<p>which renders the index as <code class="docutils literal notranslate"><span class="pre">CREATE</span> <span class="pre">CLUSTERED</span> <span class="pre">INDEX</span> <span class="pre">my_index</span> <span class="pre">ON</span> <span class="pre">table</span> <span class="pre">(x)</span></code>.</p>
<p>To generate a clustered primary key use:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">Table</span><span class="p">(</span><span class="s1">'my_table'</span><span class="p">,</span> <span class="n">metadata</span><span class="p">,</span>
<span class="n">Column</span><span class="p">(</span><span class="s1">'x'</span><span class="p">,</span> <span class="o">...</span><span class="p">),</span>
<span class="n">Column</span><span class="p">(</span><span class="s1">'y'</span><span class="p">,</span> <span class="o">...</span><span class="p">),</span>
<span class="n">PrimaryKeyConstraint</span><span class="p">(</span><span class="s2">"x"</span><span class="p">,</span> <span class="s2">"y"</span><span class="p">,</span> <span class="n">mssql_clustered</span><span class="o">=</span><span class="kc">True</span><span class="p">))</span></pre></div>
</div>
<p>which will render the table, for example, as:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">CREATE</span> <span class="n">TABLE</span> <span class="n">my_table</span> <span class="p">(</span><span class="n">x</span> <span class="n">INTEGER</span> <span class="n">NOT</span> <span class="n">NULL</span><span class="p">,</span> <span class="n">y</span> <span class="n">INTEGER</span> <span class="n">NOT</span> <span class="n">NULL</span><span class="p">,</span>
<span class="n">PRIMARY</span> <span class="n">KEY</span> <span class="n">CLUSTERED</span> <span class="p">(</span><span class="n">x</span><span class="p">,</span> <span class="n">y</span><span class="p">))</span></pre></div>
</div>
<p>Similarly, we can generate a clustered unique constraint using:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">Table</span><span class="p">(</span><span class="s1">'my_table'</span><span class="p">,</span> <span class="n">metadata</span><span class="p">,</span>
<span class="n">Column</span><span class="p">(</span><span class="s1">'x'</span><span class="p">,</span> <span class="o">...</span><span class="p">),</span>
<span class="n">Column</span><span class="p">(</span><span class="s1">'y'</span><span class="p">,</span> <span class="o">...</span><span class="p">),</span>
<span class="n">PrimaryKeyConstraint</span><span class="p">(</span><span class="s2">"x"</span><span class="p">),</span>
<span class="n">UniqueConstraint</span><span class="p">(</span><span class="s2">"y"</span><span class="p">,</span> <span class="n">mssql_clustered</span><span class="o">=</span><span class="kc">True</span><span class="p">),</span>
<span class="p">)</span></pre></div>
</div>
<p>To explicitly request a non-clustered primary key (for example, when
a separate clustered index is desired), use:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">Table</span><span class="p">(</span><span class="s1">'my_table'</span><span class="p">,</span> <span class="n">metadata</span><span class="p">,</span>
<span class="n">Column</span><span class="p">(</span><span class="s1">'x'</span><span class="p">,</span> <span class="o">...</span><span class="p">),</span>
<span class="n">Column</span><span class="p">(</span><span class="s1">'y'</span><span class="p">,</span> <span class="o">...</span><span class="p">),</span>
<span class="n">PrimaryKeyConstraint</span><span class="p">(</span><span class="s2">"x"</span><span class="p">,</span> <span class="s2">"y"</span><span class="p">,</span> <span class="n">mssql_clustered</span><span class="o">=</span><span class="kc">False</span><span class="p">))</span></pre></div>
</div>
<p>which will render the table, for example, as:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">CREATE</span> <span class="n">TABLE</span> <span class="n">my_table</span> <span class="p">(</span><span class="n">x</span> <span class="n">INTEGER</span> <span class="n">NOT</span> <span class="n">NULL</span><span class="p">,</span> <span class="n">y</span> <span class="n">INTEGER</span> <span class="n">NOT</span> <span class="n">NULL</span><span class="p">,</span>
<span class="n">PRIMARY</span> <span class="n">KEY</span> <span class="n">NONCLUSTERED</span> <span class="p">(</span><span class="n">x</span><span class="p">,</span> <span class="n">y</span><span class="p">))</span></pre></div>
</div>
<div class="versionchanged">
<p><span class="versionmodified changed">Changed in version 1.1: </span>the <code class="docutils literal notranslate"><span class="pre">mssql_clustered</span></code> option now defaults
to None, rather than False. <code class="docutils literal notranslate"><span class="pre">mssql_clustered=False</span></code> now explicitly
renders the NONCLUSTERED clause, whereas None omits the CLUSTERED
clause entirely, allowing SQL Server defaults to take effect.</p>
</div>
</div>
<div class="section" id="mssql-specific-index-options">
<h2>MSSQL-Specific Index Options<a class="headerlink" href="#mssql-specific-index-options" title="Permalink to this headline">¶</a></h2>
<p>In addition to clustering, the MSSQL dialect supports other special options
for <a class="reference internal" href="../core/constraints.html#sqlalchemy.schema.Index" title="sqlalchemy.schema.Index"><code class="xref py py-class docutils literal notranslate"><span class="pre">Index</span></code></a>.</p>
<div class="section" id="include">
<h3>INCLUDE<a class="headerlink" href="#include" title="Permalink to this headline">¶</a></h3>
<p>The <code class="docutils literal notranslate"><span class="pre">mssql_include</span></code> option renders INCLUDE(colname) for the given string
names:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">Index</span><span class="p">(</span><span class="s2">"my_index"</span><span class="p">,</span> <span class="n">table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">x</span><span class="p">,</span> <span class="n">mssql_include</span><span class="o">=</span><span class="p">[</span><span class="s1">'y'</span><span class="p">])</span></pre></div>
</div>
<p>would render the index as <code class="docutils literal notranslate"><span class="pre">CREATE</span> <span class="pre">INDEX</span> <span class="pre">my_index</span> <span class="pre">ON</span> <span class="pre">table</span> <span class="pre">(x)</span> <span class="pre">INCLUDE</span> <span class="pre">(y)</span></code></p>
</div>
<div class="section" id="index-ordering">
<h3>Index ordering<a class="headerlink" href="#index-ordering" title="Permalink to this headline">¶</a></h3>
<p>Index ordering is available via functional expressions, such as:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">Index</span><span class="p">(</span><span class="s2">"my_index"</span><span class="p">,</span> <span class="n">table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">x</span><span class="o">.</span><span class="n">desc</span><span class="p">())</span></pre></div>
</div>
<p>would render the index as <code class="docutils literal notranslate"><span class="pre">CREATE</span> <span class="pre">INDEX</span> <span class="pre">my_index</span> <span class="pre">ON</span> <span class="pre">table</span> <span class="pre">(x</span> <span class="pre">DESC)</span></code></p>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="../core/constraints.html#schema-indexes-functional"><span class="std std-ref">Functional Indexes</span></a></p>
</div>
</div>
</div>
<div class="section" id="compatibility-levels">
<h2>Compatibility Levels<a class="headerlink" href="#compatibility-levels" title="Permalink to this headline">¶</a></h2>
<p>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. <code class="docutils literal notranslate"><span class="pre">server_version_info</span></code> 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 SQLAlchemy may attempt to use T-SQL
statements that are unable to be parsed by the database server.</p>
</div>
<div class="section" id="triggers">
<h2>Triggers<a class="headerlink" href="#triggers" title="Permalink to this headline">¶</a></h2>
<p>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 <code class="docutils literal notranslate"><span class="pre">implicit_returning=False</span></code> for each <a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><code class="xref py py-class docutils literal notranslate"><span class="pre">Table</span></code></a>
which has triggers:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">Table</span><span class="p">(</span><span class="s1">'mytable'</span><span class="p">,</span> <span class="n">metadata</span><span class="p">,</span>
<span class="n">Column</span><span class="p">(</span><span class="s1">'id'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">primary_key</span><span class="o">=</span><span class="kc">True</span><span class="p">),</span>
<span class="c1"># ...,</span>
<span class="n">implicit_returning</span><span class="o">=</span><span class="kc">False</span>
<span class="p">)</span></pre></div>
</div>
<p>Declarative form:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="k">class</span> <span class="nc">MyClass</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
<span class="c1"># ...</span>
<span class="n">__table_args__</span> <span class="o">=</span> <span class="p">{</span><span class="s1">'implicit_returning'</span><span class="p">:</span><span class="kc">False</span><span class="p">}</span></pre></div>
</div>
<p>This option can also be specified engine-wide using the
<code class="docutils literal notranslate"><span class="pre">implicit_returning=False</span></code> argument on <a class="reference internal" href="../core/engines.html#sqlalchemy.create_engine" title="sqlalchemy.create_engine"><code class="xref py py-func docutils literal notranslate"><span class="pre">create_engine()</span></code></a>.</p>
</div>
<div class="section" id="rowcount-support-orm-versioning">
<span id="mssql-rowcount-versioning"></span><h2>Rowcount Support / ORM Versioning<a class="headerlink" href="#rowcount-support-orm-versioning" title="Permalink to this headline">¶</a></h2>
<p>The SQL Server drivers may have limited ability to return the number
of rows updated from an UPDATE or DELETE statement.</p>
<p>As of this writing, the PyODBC driver is not able to return a rowcount when
OUTPUT INSERTED is used. This impacts the SQLAlchemy ORM’s versioning feature
in many cases where server-side value generators are in use in that while the
versioning operations can succeed, the ORM cannot always check that an UPDATE
or DELETE statement matched the number of rows expected, which is how it
verifies that the version identifier matched. When this condition occurs, a
warning will be emitted but the operation will proceed.</p>
<p>The use of OUTPUT INSERTED can be disabled by setting the
<a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Table.params.implicit_returning" title="sqlalchemy.schema.Table"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">Table.implicit_returning</span></code></a> flag to <code class="docutils literal notranslate"><span class="pre">False</span></code> on a particular
<a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><code class="xref py py-class docutils literal notranslate"><span class="pre">Table</span></code></a>, which in declarative looks like:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="k">class</span> <span class="nc">MyTable</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
<span class="n">__tablename__</span> <span class="o">=</span> <span class="s1">'mytable'</span>
<span class="nb">id</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">Integer</span><span class="p">,</span> <span class="n">primary_key</span><span class="o">=</span><span class="kc">True</span><span class="p">)</span>
<span class="n">stuff</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">String</span><span class="p">(</span><span class="mi">10</span><span class="p">))</span>
<span class="n">timestamp</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">TIMESTAMP</span><span class="p">(),</span> <span class="n">default</span><span class="o">=</span><span class="n">text</span><span class="p">(</span><span class="s1">'DEFAULT'</span><span class="p">))</span>
<span class="n">__mapper_args__</span> <span class="o">=</span> <span class="p">{</span>
<span class="s1">'version_id_col'</span><span class="p">:</span> <span class="n">timestamp</span><span class="p">,</span>
<span class="s1">'version_id_generator'</span><span class="p">:</span> <span class="kc">False</span><span class="p">,</span>
<span class="p">}</span>
<span class="n">__table_args__</span> <span class="o">=</span> <span class="p">{</span>
<span class="s1">'implicit_returning'</span><span class="p">:</span> <span class="kc">False</span>
<span class="p">}</span></pre></div>
</div>
</div>
<div class="section" id="enabling-snapshot-isolation">
<h2>Enabling Snapshot Isolation<a class="headerlink" href="#enabling-snapshot-isolation" title="Permalink to this headline">¶</a></h2>
<p>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:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">ALTER</span> <span class="n">DATABASE</span> <span class="n">MyDatabase</span> <span class="n">SET</span> <span class="n">ALLOW_SNAPSHOT_ISOLATION</span> <span class="n">ON</span>
<span class="n">ALTER</span> <span class="n">DATABASE</span> <span class="n">MyDatabase</span> <span class="n">SET</span> <span class="n">READ_COMMITTED_SNAPSHOT</span> <span class="n">ON</span></pre></div>
</div>
<p>Background on SQL Server snapshot isolation is available at
<a class="reference external" href="http://msdn.microsoft.com/en-us/library/ms175095.aspx">http://msdn.microsoft.com/en-us/library/ms175095.aspx</a>.</p>
</div>
<div class="section" id="sql-server-data-types">
<h2>SQL Server Data Types<a class="headerlink" href="#sql-server-data-types" title="Permalink to this headline">¶</a></h2>
<p>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 <a class="reference internal" href="../core/type_basics.html#module-sqlalchemy.types" title="sqlalchemy.types"><code class="xref py py-mod docutils literal notranslate"><span class="pre">sqlalchemy.types</span></code></a> or from the local dialect:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="kn">from</span> <span class="nn">sqlalchemy.dialects.mssql</span> <span class="k">import</span> \
<span class="n">BIGINT</span><span class="p">,</span> <span class="n">BINARY</span><span class="p">,</span> <span class="n">BIT</span><span class="p">,</span> <span class="n">CHAR</span><span class="p">,</span> <span class="n">DATE</span><span class="p">,</span> <span class="n">DATETIME</span><span class="p">,</span> <span class="n">DATETIME2</span><span class="p">,</span> \
<span class="n">DATETIMEOFFSET</span><span class="p">,</span> <span class="n">DECIMAL</span><span class="p">,</span> <span class="n">FLOAT</span><span class="p">,</span> <span class="n">IMAGE</span><span class="p">,</span> <span class="n">INTEGER</span><span class="p">,</span> <span class="n">MONEY</span><span class="p">,</span> \
<span class="n">NCHAR</span><span class="p">,</span> <span class="n">NTEXT</span><span class="p">,</span> <span class="n">NUMERIC</span><span class="p">,</span> <span class="n">NVARCHAR</span><span class="p">,</span> <span class="n">REAL</span><span class="p">,</span> <span class="n">SMALLDATETIME</span><span class="p">,</span> \
<span class="n">SMALLINT</span><span class="p">,</span> <span class="n">SMALLMONEY</span><span class="p">,</span> <span class="n">SQL_VARIANT</span><span class="p">,</span> <span class="n">TEXT</span><span class="p">,</span> <span class="n">TIME</span><span class="p">,</span> \
<span class="n">TIMESTAMP</span><span class="p">,</span> <span class="n">TINYINT</span><span class="p">,</span> <span class="n">UNIQUEIDENTIFIER</span><span class="p">,</span> <span class="n">VARBINARY</span><span class="p">,</span> <span class="n">VARCHAR</span></pre></div>
</div>
<p>Types which are specific to SQL Server, or have SQL Server-specific
construction arguments, are as follows:</p>
<dl class="class">
<dt id="sqlalchemy.dialects.mssql.BIT">
<em class="property">class </em><code class="descclassname">sqlalchemy.dialects.mssql.</code><code class="descname">BIT</code><a class="headerlink" href="#sqlalchemy.dialects.mssql.BIT" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/type_api.html#sqlalchemy.types.TypeEngine" title="sqlalchemy.types.TypeEngine"><code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.types.TypeEngine</span></code></a></p>
<dl class="attribute">
<dt id="sqlalchemy.dialects.mssql.BIT.__init__">
<code class="descname">__init__</code><a class="headerlink" href="#sqlalchemy.dialects.mssql.BIT.__init__" title="Permalink to this definition">¶</a></dt>
<dd><div class="inherited-member docutils container">
<p><em>inherited from the</em> <code class="xref py py-attr docutils literal notranslate"><span class="pre">__init__</span></code> <em>attribute of</em> <code class="xref py py-class docutils literal notranslate"><span class="pre">object</span></code></p>
</div>
<p>Initialize self. See help(type(self)) for accurate signature.</p>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.mssql.CHAR">
<em class="property">class </em><code class="descclassname">sqlalchemy.dialects.mssql.</code><code class="descname">CHAR</code><span class="sig-paren">(</span><em>length=None</em>, <em>collation=None</em>, <em>convert_unicode=False</em>, <em>unicode_error=None</em>, <em>_warn_on_bytestring=False</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mssql.CHAR" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.String" title="sqlalchemy.types.String"><code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.types.String</span></code></a></p>
<p>The SQL CHAR type.</p>
<dl class="method">
<dt id="sqlalchemy.dialects.mssql.CHAR.__init__">
<code class="descname">__init__</code><span class="sig-paren">(</span><em>length=None</em>, <em>collation=None</em>, <em>convert_unicode=False</em>, <em>unicode_error=None</em>, <em>_warn_on_bytestring=False</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mssql.CHAR.__init__" title="Permalink to this definition">¶</a></dt>
<dd><div class="inherited-member docutils container">
<p><em>inherited from the</em> <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.String.__init__" title="sqlalchemy.types.String.__init__"><code class="xref py py-meth docutils literal notranslate"><span class="pre">__init__()</span></code></a> <em>method of</em> <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.String" title="sqlalchemy.types.String"><code class="xref py py-class docutils literal notranslate"><span class="pre">String</span></code></a></p>
</div>
<p>Create a string-holding type.</p>
<dl class="field-list simple">
<dt class="field-odd">Parameters</dt>
<dd class="field-odd"><ul class="simple">
<li><p><span class="target" id="sqlalchemy.dialects.mssql.CHAR.params.length"></span><strong>length</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mssql.CHAR.params.length">¶</a> – optional, a length for the column for use in
DDL and CAST expressions. May be safely omitted if no <code class="docutils literal notranslate"><span class="pre">CREATE</span>
<span class="pre">TABLE</span></code> will be issued. Certain databases may require a
<code class="docutils literal notranslate"><span class="pre">length</span></code> for use in DDL, and will raise an exception when
the <code class="docutils literal notranslate"><span class="pre">CREATE</span> <span class="pre">TABLE</span></code> DDL is issued if a <code class="docutils literal notranslate"><span class="pre">VARCHAR</span></code>
with no length is included. Whether the value is
interpreted as bytes or characters is database specific.</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mssql.CHAR.params.collation"></span><strong>collation</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mssql.CHAR.params.collation">¶</a> – <p>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.:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="k">import</span> <span class="n">cast</span><span class="p">,</span> <span class="n">select</span><span class="p">,</span> <span class="n">String</span>
<span class="gp">>>> </span><span class="nb">print</span> <span class="n">select</span><span class="p">([</span><span class="n">cast</span><span class="p">(</span><span class="s1">'some string'</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="n">collation</span><span class="o">=</span><span class="s1">'utf8'</span><span class="p">))])</span>
<span class="go">SELECT CAST(:param_1 AS VARCHAR COLLATE utf8) AS anon_1</span></pre></div>
</div>
</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mssql.CHAR.params.convert_unicode"></span><strong>convert_unicode</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mssql.CHAR.params.convert_unicode">¶</a> – <p>When set to <code class="docutils literal notranslate"><span class="pre">True</span></code>, the
<a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.String" title="sqlalchemy.types.String"><code class="xref py py-class docutils literal notranslate"><span class="pre">String</span></code></a> type will assume that
input is to be passed as Python Unicode objects under Python 2,
and results returned as Python Unicode objects.
In the rare circumstance that the DBAPI does not support
Python unicode under Python 2, SQLAlchemy will use its own
encoder/decoder functionality on strings, referring to the
value of the <a class="reference internal" href="../core/engines.html#sqlalchemy.create_engine.params.encoding" title="sqlalchemy.create_engine"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">create_engine.encoding</span></code></a> parameter
parameter passed to <a class="reference internal" href="../core/engines.html#sqlalchemy.create_engine" title="sqlalchemy.create_engine"><code class="xref py py-func docutils literal notranslate"><span class="pre">create_engine()</span></code></a> as the encoding.</p>
<p>For the extremely rare case that Python Unicode
is to be encoded/decoded by SQLAlchemy on a backend
that <em>does</em> natively support Python Unicode,
the string value <code class="docutils literal notranslate"><span class="pre">"force"</span></code> can be passed here which will
cause SQLAlchemy’s encode/decode services to be
used unconditionally.</p>
<div class="admonition note">
<p class="admonition-title">Note</p>
<p>SQLAlchemy’s unicode-conversion flags and features only apply
to Python 2; in Python 3, all string objects are Unicode objects.
For this reason, as well as the fact that virtually all modern
DBAPIs now support Unicode natively even under Python 2,
the <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.String.params.convert_unicode" title="sqlalchemy.types.String"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">String.convert_unicode</span></code></a> flag is inherently a
legacy feature.</p>
</div>
<div class="admonition note">
<p class="admonition-title">Note</p>
<p>In the vast majority of cases, the <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.Unicode" title="sqlalchemy.types.Unicode"><code class="xref py py-class docutils literal notranslate"><span class="pre">Unicode</span></code></a> or
<a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.UnicodeText" title="sqlalchemy.types.UnicodeText"><code class="xref py py-class docutils literal notranslate"><span class="pre">UnicodeText</span></code></a> datatypes should be used for a
<a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><code class="xref py py-class docutils literal notranslate"><span class="pre">Column</span></code></a> that expects to store non-ascii data. These
datatypes will ensure that the correct types are used on the
database side as well as set up the correct Unicode behaviors
under Python 2.</p>
</div>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="../core/engines.html#sqlalchemy.create_engine.params.convert_unicode" title="sqlalchemy.create_engine"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">create_engine.convert_unicode</span></code></a> -
<a class="reference internal" href="../core/connections.html#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a>-wide parameter</p>
</div>
</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mssql.CHAR.params.unicode_error"></span><strong>unicode_error</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mssql.CHAR.params.unicode_error">¶</a> – Optional, a method to use to handle Unicode
conversion errors. Behaves like the <code class="docutils literal notranslate"><span class="pre">errors</span></code> keyword argument to
the standard library’s <code class="docutils literal notranslate"><span class="pre">string.decode()</span></code> functions. This flag
requires that <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.String.params.convert_unicode" title="sqlalchemy.types.String"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">String.convert_unicode</span></code></a> is set to
<code class="docutils literal notranslate"><span class="pre">"force"</span></code> - 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.</p></li>
</ul>
</dd>
</dl>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.mssql.DATETIME2">
<em class="property">class </em><code class="descclassname">sqlalchemy.dialects.mssql.</code><code class="descname">DATETIME2</code><span class="sig-paren">(</span><em>precision=None</em>, <em>**kw</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mssql.DATETIME2" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.dialects.mssql.base._DateTimeBase</span></code>, <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.DateTime" title="sqlalchemy.types.DateTime"><code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.types.DateTime</span></code></a></p>
<dl class="method">
<dt id="sqlalchemy.dialects.mssql.DATETIME2.__init__">
<code class="descname">__init__</code><span class="sig-paren">(</span><em>precision=None</em>, <em>**kw</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mssql.DATETIME2.__init__" title="Permalink to this definition">¶</a></dt>
<dd><p>Initialize self. See help(type(self)) for accurate signature.</p>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.mssql.DATETIMEOFFSET">
<em class="property">class </em><code class="descclassname">sqlalchemy.dialects.mssql.</code><code class="descname">DATETIMEOFFSET</code><span class="sig-paren">(</span><em>precision=None</em>, <em>**kwargs</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mssql.DATETIMEOFFSET" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/type_api.html#sqlalchemy.types.TypeEngine" title="sqlalchemy.types.TypeEngine"><code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.types.TypeEngine</span></code></a></p>
<dl class="method">
<dt id="sqlalchemy.dialects.mssql.DATETIMEOFFSET.__init__">
<code class="descname">__init__</code><span class="sig-paren">(</span><em>precision=None</em>, <em>**kwargs</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mssql.DATETIMEOFFSET.__init__" title="Permalink to this definition">¶</a></dt>
<dd><p>Initialize self. See help(type(self)) for accurate signature.</p>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.mssql.IMAGE">
<em class="property">class </em><code class="descclassname">sqlalchemy.dialects.mssql.</code><code class="descname">IMAGE</code><span class="sig-paren">(</span><em>length=None</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mssql.IMAGE" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.LargeBinary" title="sqlalchemy.types.LargeBinary"><code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.types.LargeBinary</span></code></a></p>
<dl class="method">
<dt id="sqlalchemy.dialects.mssql.IMAGE.__init__">
<code class="descname">__init__</code><span class="sig-paren">(</span><em>length=None</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mssql.IMAGE.__init__" title="Permalink to this definition">¶</a></dt>
<dd><div class="inherited-member docutils container">
<p><em>inherited from the</em> <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.LargeBinary.__init__" title="sqlalchemy.types.LargeBinary.__init__"><code class="xref py py-meth docutils literal notranslate"><span class="pre">__init__()</span></code></a> <em>method of</em> <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.LargeBinary" title="sqlalchemy.types.LargeBinary"><code class="xref py py-class docutils literal notranslate"><span class="pre">LargeBinary</span></code></a></p>
</div>
<p>Construct a LargeBinary type.</p>
<dl class="field-list simple">
<dt class="field-odd">Parameters</dt>
<dd class="field-odd"><p><span class="target" id="sqlalchemy.dialects.mssql.IMAGE.params.length"></span><strong>length</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mssql.IMAGE.params.length">¶</a> – 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.</p>
</dd>
</dl>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.mssql.MONEY">
<em class="property">class </em><code class="descclassname">sqlalchemy.dialects.mssql.</code><code class="descname">MONEY</code><a class="headerlink" href="#sqlalchemy.dialects.mssql.MONEY" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/type_api.html#sqlalchemy.types.TypeEngine" title="sqlalchemy.types.TypeEngine"><code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.types.TypeEngine</span></code></a></p>
<dl class="attribute">
<dt id="sqlalchemy.dialects.mssql.MONEY.__init__">
<code class="descname">__init__</code><a class="headerlink" href="#sqlalchemy.dialects.mssql.MONEY.__init__" title="Permalink to this definition">¶</a></dt>
<dd><div class="inherited-member docutils container">
<p><em>inherited from the</em> <code class="xref py py-attr docutils literal notranslate"><span class="pre">__init__</span></code> <em>attribute of</em> <code class="xref py py-class docutils literal notranslate"><span class="pre">object</span></code></p>
</div>
<p>Initialize self. See help(type(self)) for accurate signature.</p>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.mssql.NCHAR">
<em class="property">class </em><code class="descclassname">sqlalchemy.dialects.mssql.</code><code class="descname">NCHAR</code><span class="sig-paren">(</span><em>length=None</em>, <em>**kwargs</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mssql.NCHAR" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.Unicode" title="sqlalchemy.types.Unicode"><code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.types.Unicode</span></code></a></p>
<p>The SQL NCHAR type.</p>
<dl class="method">
<dt id="sqlalchemy.dialects.mssql.NCHAR.__init__">
<code class="descname">__init__</code><span class="sig-paren">(</span><em>length=None</em>, <em>**kwargs</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mssql.NCHAR.__init__" title="Permalink to this definition">¶</a></dt>
<dd><div class="inherited-member docutils container">
<p><em>inherited from the</em> <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.Unicode.__init__" title="sqlalchemy.types.Unicode.__init__"><code class="xref py py-meth docutils literal notranslate"><span class="pre">__init__()</span></code></a> <em>method of</em> <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.Unicode" title="sqlalchemy.types.Unicode"><code class="xref py py-class docutils literal notranslate"><span class="pre">Unicode</span></code></a></p>
</div>
<p>Create a <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.Unicode" title="sqlalchemy.types.Unicode"><code class="xref py py-class docutils literal notranslate"><span class="pre">Unicode</span></code></a> object.</p>
<p>Parameters are the same as that of <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.String" title="sqlalchemy.types.String"><code class="xref py py-class docutils literal notranslate"><span class="pre">String</span></code></a>,
with the exception that <code class="docutils literal notranslate"><span class="pre">convert_unicode</span></code>
defaults to <code class="docutils literal notranslate"><span class="pre">True</span></code>.</p>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.mssql.NTEXT">
<em class="property">class </em><code class="descclassname">sqlalchemy.dialects.mssql.</code><code class="descname">NTEXT</code><span class="sig-paren">(</span><em>length=None</em>, <em>**kwargs</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mssql.NTEXT" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.UnicodeText" title="sqlalchemy.types.UnicodeText"><code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.types.UnicodeText</span></code></a></p>
<p>MSSQL NTEXT type, for variable-length unicode text up to 2^30
characters.</p>
<dl class="method">
<dt id="sqlalchemy.dialects.mssql.NTEXT.__init__">
<code class="descname">__init__</code><span class="sig-paren">(</span><em>length=None</em>, <em>**kwargs</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mssql.NTEXT.__init__" title="Permalink to this definition">¶</a></dt>
<dd><div class="inherited-member docutils container">
<p><em>inherited from the</em> <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.UnicodeText.__init__" title="sqlalchemy.types.UnicodeText.__init__"><code class="xref py py-meth docutils literal notranslate"><span class="pre">__init__()</span></code></a> <em>method of</em> <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.UnicodeText" title="sqlalchemy.types.UnicodeText"><code class="xref py py-class docutils literal notranslate"><span class="pre">UnicodeText</span></code></a></p>
</div>
<p>Create a Unicode-converting Text type.</p>
<p>Parameters are the same as that of <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.Text" title="sqlalchemy.types.Text"><code class="xref py py-class docutils literal notranslate"><span class="pre">Text</span></code></a>,
with the exception that <code class="docutils literal notranslate"><span class="pre">convert_unicode</span></code>
defaults to <code class="docutils literal notranslate"><span class="pre">True</span></code>.</p>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.mssql.NVARCHAR">
<em class="property">class </em><code class="descclassname">sqlalchemy.dialects.mssql.</code><code class="descname">NVARCHAR</code><span class="sig-paren">(</span><em>length=None</em>, <em>**kwargs</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mssql.NVARCHAR" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.Unicode" title="sqlalchemy.types.Unicode"><code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.types.Unicode</span></code></a></p>
<p>The SQL NVARCHAR type.</p>
<dl class="method">
<dt id="sqlalchemy.dialects.mssql.NVARCHAR.__init__">
<code class="descname">__init__</code><span class="sig-paren">(</span><em>length=None</em>, <em>**kwargs</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mssql.NVARCHAR.__init__" title="Permalink to this definition">¶</a></dt>
<dd><div class="inherited-member docutils container">
<p><em>inherited from the</em> <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.Unicode.__init__" title="sqlalchemy.types.Unicode.__init__"><code class="xref py py-meth docutils literal notranslate"><span class="pre">__init__()</span></code></a> <em>method of</em> <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.Unicode" title="sqlalchemy.types.Unicode"><code class="xref py py-class docutils literal notranslate"><span class="pre">Unicode</span></code></a></p>
</div>
<p>Create a <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.Unicode" title="sqlalchemy.types.Unicode"><code class="xref py py-class docutils literal notranslate"><span class="pre">Unicode</span></code></a> object.</p>
<p>Parameters are the same as that of <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.String" title="sqlalchemy.types.String"><code class="xref py py-class docutils literal notranslate"><span class="pre">String</span></code></a>,
with the exception that <code class="docutils literal notranslate"><span class="pre">convert_unicode</span></code>
defaults to <code class="docutils literal notranslate"><span class="pre">True</span></code>.</p>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.mssql.REAL">
<em class="property">class </em><code class="descclassname">sqlalchemy.dialects.mssql.</code><code class="descname">REAL</code><span class="sig-paren">(</span><em>**kw</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mssql.REAL" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.REAL" title="sqlalchemy.types.REAL"><code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.types.REAL</span></code></a></p>
<dl class="method">
<dt id="sqlalchemy.dialects.mssql.REAL.__init__">
<code class="descname">__init__</code><span class="sig-paren">(</span><em>**kw</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mssql.REAL.__init__" title="Permalink to this definition">¶</a></dt>
<dd><p>Construct a Float.</p>
<dl class="field-list simple">
<dt class="field-odd">Parameters</dt>
<dd class="field-odd"><ul class="simple">
<li><p><span class="target" id="sqlalchemy.dialects.mssql.REAL.params.precision"></span><strong>precision</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mssql.REAL.params.precision">¶</a> – the numeric precision for use in DDL <code class="docutils literal notranslate"><span class="pre">CREATE</span>
<span class="pre">TABLE</span></code>.</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mssql.REAL.params.asdecimal"></span><strong>asdecimal</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mssql.REAL.params.asdecimal">¶</a> – the same flag as that of <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.Numeric" title="sqlalchemy.types.Numeric"><code class="xref py py-class docutils literal notranslate"><span class="pre">Numeric</span></code></a>, but
defaults to <code class="docutils literal notranslate"><span class="pre">False</span></code>. Note that setting this flag to <code class="docutils literal notranslate"><span class="pre">True</span></code>
results in floating point conversion.</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mssql.REAL.params.decimal_return_scale"></span><strong>decimal_return_scale</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mssql.REAL.params.decimal_return_scale">¶</a> – <p>Default scale to use when converting
from floats to Python decimals. Floating point values will typically
be much longer due to decimal inaccuracy, and most floating point
database types don’t have a notion of “scale”, so by default the
float type looks for the first ten decimal places when converting.
Specifying this value will override that length. Note that the
MySQL float types, which do include “scale”, will use “scale”
as the default for decimal_return_scale, if not otherwise specified.</p>
<div class="versionadded">
<p><span class="versionmodified added">New in version 0.9.0.</span></p>
</div>
</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mssql.REAL.params.**kwargs"></span><strong>**kwargs</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mssql.REAL.params.**kwargs">¶</a> – <div class="deprecated">
<p><span class="versionmodified deprecated">Deprecated since version 0.9: </span>Additional keyword arguments are ignored by the base
<a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.Float" title="sqlalchemy.types.Float"><code class="xref py py-class docutils literal notranslate"><span class="pre">Float</span></code></a> type, and keyword arguments will no longer
be accepted in a future release. For database specific floats
that support additional arguments, see that dialect’s
documentation for details, such as
<a class="reference internal" href="mysql.html#sqlalchemy.dialects.mysql.FLOAT" title="sqlalchemy.dialects.mysql.FLOAT"><code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.dialects.mysql.FLOAT</span></code></a>.</p>
</div>
</p></li>
</ul>
</dd>
</dl>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.mssql.ROWVERSION">
<em class="property">class </em><code class="descclassname">sqlalchemy.dialects.mssql.</code><code class="descname">ROWVERSION</code><span class="sig-paren">(</span><em>convert_int=False</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mssql.ROWVERSION" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.dialects.mssql.base.TIMESTAMP</span></code></p>
<p>Implement the SQL Server ROWVERSION type.</p>
<p>The ROWVERSION datatype is a SQL Server synonym for the TIMESTAMP
datatype, however current SQL Server documentation suggests using
ROWVERSION for new datatypes going forward.</p>
<p>The ROWVERSION datatype does <strong>not</strong> reflect (e.g. introspect) from the
database as itself; the returned datatype will be
<a class="reference internal" href="#sqlalchemy.dialects.mssql.TIMESTAMP" title="sqlalchemy.dialects.mssql.TIMESTAMP"><code class="xref py py-class docutils literal notranslate"><span class="pre">mssql.TIMESTAMP</span></code></a>.</p>
<p>This is a read-only datatype that does not support INSERT of values.</p>
<div class="versionadded">
<p><span class="versionmodified added">New in version 1.2.</span></p>
</div>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="#sqlalchemy.dialects.mssql.TIMESTAMP" title="sqlalchemy.dialects.mssql.TIMESTAMP"><code class="xref py py-class docutils literal notranslate"><span class="pre">mssql.TIMESTAMP</span></code></a></p>
</div>
<dl class="method">
<dt id="sqlalchemy.dialects.mssql.ROWVERSION.__init__">
<code class="descname">__init__</code><span class="sig-paren">(</span><em>convert_int=False</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mssql.ROWVERSION.__init__" title="Permalink to this definition">¶</a></dt>
<dd><div class="inherited-member docutils container">
<p><em>inherited from the</em> <code class="xref py py-meth docutils literal notranslate"><span class="pre">__init__()</span></code> <em>method of</em> <code class="xref py py-class docutils literal notranslate"><span class="pre">TIMESTAMP</span></code></p>
</div>
<p>Construct a TIMESTAMP or ROWVERSION type.</p>
<dl class="field-list simple">
<dt class="field-odd">Parameters</dt>
<dd class="field-odd"><p><span class="target" id="sqlalchemy.dialects.mssql.ROWVERSION.params.convert_int"></span><strong>convert_int</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mssql.ROWVERSION.params.convert_int">¶</a> – if True, binary integer values will
be converted to integers on read.</p>
</dd>
</dl>
<div class="versionadded">
<p><span class="versionmodified added">New in version 1.2.</span></p>
</div>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.mssql.SMALLDATETIME">
<em class="property">class </em><code class="descclassname">sqlalchemy.dialects.mssql.</code><code class="descname">SMALLDATETIME</code><span class="sig-paren">(</span><em>timezone=False</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mssql.SMALLDATETIME" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.dialects.mssql.base._DateTimeBase</span></code>, <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.DateTime" title="sqlalchemy.types.DateTime"><code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.types.DateTime</span></code></a></p>
<dl class="method">
<dt id="sqlalchemy.dialects.mssql.SMALLDATETIME.__init__">
<code class="descname">__init__</code><span class="sig-paren">(</span><em>timezone=False</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mssql.SMALLDATETIME.__init__" title="Permalink to this definition">¶</a></dt>
<dd><div class="inherited-member docutils container">
<p><em>inherited from the</em> <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.DateTime.__init__" title="sqlalchemy.types.DateTime.__init__"><code class="xref py py-meth docutils literal notranslate"><span class="pre">__init__()</span></code></a> <em>method of</em> <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.DateTime" title="sqlalchemy.types.DateTime"><code class="xref py py-class docutils literal notranslate"><span class="pre">DateTime</span></code></a></p>
</div>
<p>Construct a new <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.DateTime" title="sqlalchemy.types.DateTime"><code class="xref py py-class docutils literal notranslate"><span class="pre">DateTime</span></code></a>.</p>
<dl class="field-list simple">
<dt class="field-odd">Parameters</dt>
<dd class="field-odd"><p><span class="target" id="sqlalchemy.dialects.mssql.SMALLDATETIME.params.timezone"></span><strong>timezone</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mssql.SMALLDATETIME.params.timezone">¶</a> – boolean. Indicates that the datetime type should
enable timezone support, if available on the
<strong>base date/time-holding type only</strong>. It is recommended
to make use of the <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.TIMESTAMP" title="sqlalchemy.types.TIMESTAMP"><code class="xref py py-class docutils literal notranslate"><span class="pre">TIMESTAMP</span></code></a> datatype directly when
using this flag, as some databases include separate generic
date/time-holding types distinct from the timezone-capable
TIMESTAMP datatype, such as Oracle.</p>
</dd>
</dl>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.mssql.SMALLMONEY">
<em class="property">class </em><code class="descclassname">sqlalchemy.dialects.mssql.</code><code class="descname">SMALLMONEY</code><a class="headerlink" href="#sqlalchemy.dialects.mssql.SMALLMONEY" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/type_api.html#sqlalchemy.types.TypeEngine" title="sqlalchemy.types.TypeEngine"><code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.types.TypeEngine</span></code></a></p>
<dl class="attribute">
<dt id="sqlalchemy.dialects.mssql.SMALLMONEY.__init__">
<code class="descname">__init__</code><a class="headerlink" href="#sqlalchemy.dialects.mssql.SMALLMONEY.__init__" title="Permalink to this definition">¶</a></dt>
<dd><div class="inherited-member docutils container">
<p><em>inherited from the</em> <code class="xref py py-attr docutils literal notranslate"><span class="pre">__init__</span></code> <em>attribute of</em> <code class="xref py py-class docutils literal notranslate"><span class="pre">object</span></code></p>
</div>
<p>Initialize self. See help(type(self)) for accurate signature.</p>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.mssql.SQL_VARIANT">
<em class="property">class </em><code class="descclassname">sqlalchemy.dialects.mssql.</code><code class="descname">SQL_VARIANT</code><a class="headerlink" href="#sqlalchemy.dialects.mssql.SQL_VARIANT" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/type_api.html#sqlalchemy.types.TypeEngine" title="sqlalchemy.types.TypeEngine"><code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.types.TypeEngine</span></code></a></p>
<dl class="attribute">
<dt id="sqlalchemy.dialects.mssql.SQL_VARIANT.__init__">
<code class="descname">__init__</code><a class="headerlink" href="#sqlalchemy.dialects.mssql.SQL_VARIANT.__init__" title="Permalink to this definition">¶</a></dt>
<dd><div class="inherited-member docutils container">
<p><em>inherited from the</em> <code class="xref py py-attr docutils literal notranslate"><span class="pre">__init__</span></code> <em>attribute of</em> <code class="xref py py-class docutils literal notranslate"><span class="pre">object</span></code></p>
</div>
<p>Initialize self. See help(type(self)) for accurate signature.</p>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.mssql.TEXT">
<em class="property">class </em><code class="descclassname">sqlalchemy.dialects.mssql.</code><code class="descname">TEXT</code><span class="sig-paren">(</span><em>length=None</em>, <em>collation=None</em>, <em>convert_unicode=False</em>, <em>unicode_error=None</em>, <em>_warn_on_bytestring=False</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mssql.TEXT" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.Text" title="sqlalchemy.types.Text"><code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.types.Text</span></code></a></p>
<p>The SQL TEXT type.</p>
<dl class="method">
<dt id="sqlalchemy.dialects.mssql.TEXT.__init__">
<code class="descname">__init__</code><span class="sig-paren">(</span><em>length=None</em>, <em>collation=None</em>, <em>convert_unicode=False</em>, <em>unicode_error=None</em>, <em>_warn_on_bytestring=False</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mssql.TEXT.__init__" title="Permalink to this definition">¶</a></dt>
<dd><div class="inherited-member docutils container">
<p><em>inherited from the</em> <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.String.__init__" title="sqlalchemy.types.String.__init__"><code class="xref py py-meth docutils literal notranslate"><span class="pre">__init__()</span></code></a> <em>method of</em> <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.String" title="sqlalchemy.types.String"><code class="xref py py-class docutils literal notranslate"><span class="pre">String</span></code></a></p>
</div>
<p>Create a string-holding type.</p>
<dl class="field-list simple">
<dt class="field-odd">Parameters</dt>
<dd class="field-odd"><ul class="simple">
<li><p><span class="target" id="sqlalchemy.dialects.mssql.TEXT.params.length"></span><strong>length</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mssql.TEXT.params.length">¶</a> – optional, a length for the column for use in
DDL and CAST expressions. May be safely omitted if no <code class="docutils literal notranslate"><span class="pre">CREATE</span>
<span class="pre">TABLE</span></code> will be issued. Certain databases may require a
<code class="docutils literal notranslate"><span class="pre">length</span></code> for use in DDL, and will raise an exception when
the <code class="docutils literal notranslate"><span class="pre">CREATE</span> <span class="pre">TABLE</span></code> DDL is issued if a <code class="docutils literal notranslate"><span class="pre">VARCHAR</span></code>
with no length is included. Whether the value is
interpreted as bytes or characters is database specific.</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mssql.TEXT.params.collation"></span><strong>collation</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mssql.TEXT.params.collation">¶</a> – <p>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.:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="k">import</span> <span class="n">cast</span><span class="p">,</span> <span class="n">select</span><span class="p">,</span> <span class="n">String</span>
<span class="gp">>>> </span><span class="nb">print</span> <span class="n">select</span><span class="p">([</span><span class="n">cast</span><span class="p">(</span><span class="s1">'some string'</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="n">collation</span><span class="o">=</span><span class="s1">'utf8'</span><span class="p">))])</span>
<span class="go">SELECT CAST(:param_1 AS VARCHAR COLLATE utf8) AS anon_1</span></pre></div>
</div>
</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mssql.TEXT.params.convert_unicode"></span><strong>convert_unicode</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mssql.TEXT.params.convert_unicode">¶</a> – <p>When set to <code class="docutils literal notranslate"><span class="pre">True</span></code>, the
<a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.String" title="sqlalchemy.types.String"><code class="xref py py-class docutils literal notranslate"><span class="pre">String</span></code></a> type will assume that
input is to be passed as Python Unicode objects under Python 2,
and results returned as Python Unicode objects.
In the rare circumstance that the DBAPI does not support
Python unicode under Python 2, SQLAlchemy will use its own
encoder/decoder functionality on strings, referring to the
value of the <a class="reference internal" href="../core/engines.html#sqlalchemy.create_engine.params.encoding" title="sqlalchemy.create_engine"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">create_engine.encoding</span></code></a> parameter
parameter passed to <a class="reference internal" href="../core/engines.html#sqlalchemy.create_engine" title="sqlalchemy.create_engine"><code class="xref py py-func docutils literal notranslate"><span class="pre">create_engine()</span></code></a> as the encoding.</p>
<p>For the extremely rare case that Python Unicode
is to be encoded/decoded by SQLAlchemy on a backend
that <em>does</em> natively support Python Unicode,
the string value <code class="docutils literal notranslate"><span class="pre">"force"</span></code> can be passed here which will
cause SQLAlchemy’s encode/decode services to be
used unconditionally.</p>
<div class="admonition note">
<p class="admonition-title">Note</p>
<p>SQLAlchemy’s unicode-conversion flags and features only apply
to Python 2; in Python 3, all string objects are Unicode objects.
For this reason, as well as the fact that virtually all modern
DBAPIs now support Unicode natively even under Python 2,
the <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.String.params.convert_unicode" title="sqlalchemy.types.String"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">String.convert_unicode</span></code></a> flag is inherently a
legacy feature.</p>
</div>
<div class="admonition note">
<p class="admonition-title">Note</p>
<p>In the vast majority of cases, the <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.Unicode" title="sqlalchemy.types.Unicode"><code class="xref py py-class docutils literal notranslate"><span class="pre">Unicode</span></code></a> or
<a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.UnicodeText" title="sqlalchemy.types.UnicodeText"><code class="xref py py-class docutils literal notranslate"><span class="pre">UnicodeText</span></code></a> datatypes should be used for a
<a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><code class="xref py py-class docutils literal notranslate"><span class="pre">Column</span></code></a> that expects to store non-ascii data. These
datatypes will ensure that the correct types are used on the
database side as well as set up the correct Unicode behaviors
under Python 2.</p>
</div>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="../core/engines.html#sqlalchemy.create_engine.params.convert_unicode" title="sqlalchemy.create_engine"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">create_engine.convert_unicode</span></code></a> -
<a class="reference internal" href="../core/connections.html#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a>-wide parameter</p>
</div>
</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mssql.TEXT.params.unicode_error"></span><strong>unicode_error</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mssql.TEXT.params.unicode_error">¶</a> – Optional, a method to use to handle Unicode
conversion errors. Behaves like the <code class="docutils literal notranslate"><span class="pre">errors</span></code> keyword argument to
the standard library’s <code class="docutils literal notranslate"><span class="pre">string.decode()</span></code> functions. This flag
requires that <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.String.params.convert_unicode" title="sqlalchemy.types.String"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">String.convert_unicode</span></code></a> is set to
<code class="docutils literal notranslate"><span class="pre">"force"</span></code> - 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.</p></li>
</ul>
</dd>
</dl>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.mssql.TIME">
<em class="property">class </em><code class="descclassname">sqlalchemy.dialects.mssql.</code><code class="descname">TIME</code><span class="sig-paren">(</span><em>precision=None</em>, <em>**kwargs</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mssql.TIME" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.TIME" title="sqlalchemy.types.TIME"><code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.types.TIME</span></code></a></p>
<dl class="method">
<dt id="sqlalchemy.dialects.mssql.TIME.__init__">
<code class="descname">__init__</code><span class="sig-paren">(</span><em>precision=None</em>, <em>**kwargs</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mssql.TIME.__init__" title="Permalink to this definition">¶</a></dt>
<dd><p>Initialize self. See help(type(self)) for accurate signature.</p>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.mssql.TIMESTAMP">
<em class="property">class </em><code class="descclassname">sqlalchemy.dialects.mssql.</code><code class="descname">TIMESTAMP</code><span class="sig-paren">(</span><em>convert_int=False</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mssql.TIMESTAMP" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.types._Binary</span></code></p>
<p>Implement the SQL Server TIMESTAMP type.</p>
<p>Note this is <strong>completely different</strong> than the SQL Standard
TIMESTAMP type, which is not supported by SQL Server. It
is a read-only datatype that does not support INSERT of values.</p>
<div class="versionadded">
<p><span class="versionmodified added">New in version 1.2.</span></p>
</div>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="#sqlalchemy.dialects.mssql.ROWVERSION" title="sqlalchemy.dialects.mssql.ROWVERSION"><code class="xref py py-class docutils literal notranslate"><span class="pre">mssql.ROWVERSION</span></code></a></p>
</div>
<dl class="method">
<dt id="sqlalchemy.dialects.mssql.TIMESTAMP.__init__">
<code class="descname">__init__</code><span class="sig-paren">(</span><em>convert_int=False</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mssql.TIMESTAMP.__init__" title="Permalink to this definition">¶</a></dt>
<dd><p>Construct a TIMESTAMP or ROWVERSION type.</p>
<dl class="field-list simple">
<dt class="field-odd">Parameters</dt>
<dd class="field-odd"><p><span class="target" id="sqlalchemy.dialects.mssql.TIMESTAMP.params.convert_int"></span><strong>convert_int</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mssql.TIMESTAMP.params.convert_int">¶</a> – if True, binary integer values will
be converted to integers on read.</p>
</dd>
</dl>
<div class="versionadded">
<p><span class="versionmodified added">New in version 1.2.</span></p>
</div>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.mssql.TINYINT">
<em class="property">class </em><code class="descclassname">sqlalchemy.dialects.mssql.</code><code class="descname">TINYINT</code><a class="headerlink" href="#sqlalchemy.dialects.mssql.TINYINT" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.Integer" title="sqlalchemy.types.Integer"><code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.types.Integer</span></code></a></p>
<dl class="attribute">
<dt id="sqlalchemy.dialects.mssql.TINYINT.__init__">
<code class="descname">__init__</code><a class="headerlink" href="#sqlalchemy.dialects.mssql.TINYINT.__init__" title="Permalink to this definition">¶</a></dt>
<dd><div class="inherited-member docutils container">
<p><em>inherited from the</em> <code class="xref py py-attr docutils literal notranslate"><span class="pre">__init__</span></code> <em>attribute of</em> <code class="xref py py-class docutils literal notranslate"><span class="pre">object</span></code></p>
</div>
<p>Initialize self. See help(type(self)) for accurate signature.</p>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.mssql.UNIQUEIDENTIFIER">
<em class="property">class </em><code class="descclassname">sqlalchemy.dialects.mssql.</code><code class="descname">UNIQUEIDENTIFIER</code><a class="headerlink" href="#sqlalchemy.dialects.mssql.UNIQUEIDENTIFIER" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/type_api.html#sqlalchemy.types.TypeEngine" title="sqlalchemy.types.TypeEngine"><code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.types.TypeEngine</span></code></a></p>
<dl class="attribute">
<dt id="sqlalchemy.dialects.mssql.UNIQUEIDENTIFIER.__init__">
<code class="descname">__init__</code><a class="headerlink" href="#sqlalchemy.dialects.mssql.UNIQUEIDENTIFIER.__init__" title="Permalink to this definition">¶</a></dt>
<dd><div class="inherited-member docutils container">
<p><em>inherited from the</em> <code class="xref py py-attr docutils literal notranslate"><span class="pre">__init__</span></code> <em>attribute of</em> <code class="xref py py-class docutils literal notranslate"><span class="pre">object</span></code></p>
</div>
<p>Initialize self. See help(type(self)) for accurate signature.</p>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.mssql.VARCHAR">
<em class="property">class </em><code class="descclassname">sqlalchemy.dialects.mssql.</code><code class="descname">VARCHAR</code><span class="sig-paren">(</span><em>length=None</em>, <em>collation=None</em>, <em>convert_unicode=False</em>, <em>unicode_error=None</em>, <em>_warn_on_bytestring=False</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mssql.VARCHAR" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.String" title="sqlalchemy.types.String"><code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.types.String</span></code></a></p>
<p>The SQL VARCHAR type.</p>
<dl class="method">
<dt id="sqlalchemy.dialects.mssql.VARCHAR.__init__">
<code class="descname">__init__</code><span class="sig-paren">(</span><em>length=None</em>, <em>collation=None</em>, <em>convert_unicode=False</em>, <em>unicode_error=None</em>, <em>_warn_on_bytestring=False</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mssql.VARCHAR.__init__" title="Permalink to this definition">¶</a></dt>
<dd><div class="inherited-member docutils container">
<p><em>inherited from the</em> <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.String.__init__" title="sqlalchemy.types.String.__init__"><code class="xref py py-meth docutils literal notranslate"><span class="pre">__init__()</span></code></a> <em>method of</em> <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.String" title="sqlalchemy.types.String"><code class="xref py py-class docutils literal notranslate"><span class="pre">String</span></code></a></p>
</div>
<p>Create a string-holding type.</p>
<dl class="field-list simple">
<dt class="field-odd">Parameters</dt>
<dd class="field-odd"><ul class="simple">
<li><p><span class="target" id="sqlalchemy.dialects.mssql.VARCHAR.params.length"></span><strong>length</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mssql.VARCHAR.params.length">¶</a> – optional, a length for the column for use in
DDL and CAST expressions. May be safely omitted if no <code class="docutils literal notranslate"><span class="pre">CREATE</span>
<span class="pre">TABLE</span></code> will be issued. Certain databases may require a
<code class="docutils literal notranslate"><span class="pre">length</span></code> for use in DDL, and will raise an exception when
the <code class="docutils literal notranslate"><span class="pre">CREATE</span> <span class="pre">TABLE</span></code> DDL is issued if a <code class="docutils literal notranslate"><span class="pre">VARCHAR</span></code>
with no length is included. Whether the value is
interpreted as bytes or characters is database specific.</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mssql.VARCHAR.params.collation"></span><strong>collation</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mssql.VARCHAR.params.collation">¶</a> – <p>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.:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="k">import</span> <span class="n">cast</span><span class="p">,</span> <span class="n">select</span><span class="p">,</span> <span class="n">String</span>
<span class="gp">>>> </span><span class="nb">print</span> <span class="n">select</span><span class="p">([</span><span class="n">cast</span><span class="p">(</span><span class="s1">'some string'</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="n">collation</span><span class="o">=</span><span class="s1">'utf8'</span><span class="p">))])</span>
<span class="go">SELECT CAST(:param_1 AS VARCHAR COLLATE utf8) AS anon_1</span></pre></div>
</div>
</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mssql.VARCHAR.params.convert_unicode"></span><strong>convert_unicode</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mssql.VARCHAR.params.convert_unicode">¶</a> – <p>When set to <code class="docutils literal notranslate"><span class="pre">True</span></code>, the
<a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.String" title="sqlalchemy.types.String"><code class="xref py py-class docutils literal notranslate"><span class="pre">String</span></code></a> type will assume that
input is to be passed as Python Unicode objects under Python 2,
and results returned as Python Unicode objects.
In the rare circumstance that the DBAPI does not support
Python unicode under Python 2, SQLAlchemy will use its own
encoder/decoder functionality on strings, referring to the
value of the <a class="reference internal" href="../core/engines.html#sqlalchemy.create_engine.params.encoding" title="sqlalchemy.create_engine"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">create_engine.encoding</span></code></a> parameter
parameter passed to <a class="reference internal" href="../core/engines.html#sqlalchemy.create_engine" title="sqlalchemy.create_engine"><code class="xref py py-func docutils literal notranslate"><span class="pre">create_engine()</span></code></a> as the encoding.</p>
<p>For the extremely rare case that Python Unicode
is to be encoded/decoded by SQLAlchemy on a backend
that <em>does</em> natively support Python Unicode,
the string value <code class="docutils literal notranslate"><span class="pre">"force"</span></code> can be passed here which will
cause SQLAlchemy’s encode/decode services to be
used unconditionally.</p>
<div class="admonition note">
<p class="admonition-title">Note</p>
<p>SQLAlchemy’s unicode-conversion flags and features only apply
to Python 2; in Python 3, all string objects are Unicode objects.
For this reason, as well as the fact that virtually all modern
DBAPIs now support Unicode natively even under Python 2,
the <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.String.params.convert_unicode" title="sqlalchemy.types.String"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">String.convert_unicode</span></code></a> flag is inherently a
legacy feature.</p>
</div>
<div class="admonition note">
<p class="admonition-title">Note</p>
<p>In the vast majority of cases, the <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.Unicode" title="sqlalchemy.types.Unicode"><code class="xref py py-class docutils literal notranslate"><span class="pre">Unicode</span></code></a> or
<a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.UnicodeText" title="sqlalchemy.types.UnicodeText"><code class="xref py py-class docutils literal notranslate"><span class="pre">UnicodeText</span></code></a> datatypes should be used for a
<a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><code class="xref py py-class docutils literal notranslate"><span class="pre">Column</span></code></a> that expects to store non-ascii data. These
datatypes will ensure that the correct types are used on the
database side as well as set up the correct Unicode behaviors
under Python 2.</p>
</div>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="../core/engines.html#sqlalchemy.create_engine.params.convert_unicode" title="sqlalchemy.create_engine"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">create_engine.convert_unicode</span></code></a> -
<a class="reference internal" href="../core/connections.html#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a>-wide parameter</p>
</div>
</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mssql.VARCHAR.params.unicode_error"></span><strong>unicode_error</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mssql.VARCHAR.params.unicode_error">¶</a> – Optional, a method to use to handle Unicode
conversion errors. Behaves like the <code class="docutils literal notranslate"><span class="pre">errors</span></code> keyword argument to
the standard library’s <code class="docutils literal notranslate"><span class="pre">string.decode()</span></code> functions. This flag
requires that <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.String.params.convert_unicode" title="sqlalchemy.types.String"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">String.convert_unicode</span></code></a> is set to
<code class="docutils literal notranslate"><span class="pre">"force"</span></code> - 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.</p></li>
</ul>
</dd>
</dl>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.mssql.XML">
<em class="property">class </em><code class="descclassname">sqlalchemy.dialects.mssql.</code><code class="descname">XML</code><span class="sig-paren">(</span><em>length=None</em>, <em>collation=None</em>, <em>convert_unicode=False</em>, <em>unicode_error=None</em>, <em>_warn_on_bytestring=False</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mssql.XML" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.Text" title="sqlalchemy.types.Text"><code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.types.Text</span></code></a></p>
<p>MSSQL XML type.</p>
<p>This is a placeholder type for reflection purposes that does not include
any Python-side datatype support. It also does not currently support
additional arguments, such as “CONTENT”, “DOCUMENT”,
“xml_schema_collection”.</p>
<div class="versionadded">
<p><span class="versionmodified added">New in version 1.1.11.</span></p>
</div>
<dl class="method">
<dt id="sqlalchemy.dialects.mssql.XML.__init__">
<code class="descname">__init__</code><span class="sig-paren">(</span><em>length=None</em>, <em>collation=None</em>, <em>convert_unicode=False</em>, <em>unicode_error=None</em>, <em>_warn_on_bytestring=False</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mssql.XML.__init__" title="Permalink to this definition">¶</a></dt>
<dd><div class="inherited-member docutils container">
<p><em>inherited from the</em> <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.String.__init__" title="sqlalchemy.types.String.__init__"><code class="xref py py-meth docutils literal notranslate"><span class="pre">__init__()</span></code></a> <em>method of</em> <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.String" title="sqlalchemy.types.String"><code class="xref py py-class docutils literal notranslate"><span class="pre">String</span></code></a></p>
</div>
<p>Create a string-holding type.</p>
<dl class="field-list simple">
<dt class="field-odd">Parameters</dt>
<dd class="field-odd"><ul class="simple">
<li><p><span class="target" id="sqlalchemy.dialects.mssql.XML.params.length"></span><strong>length</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mssql.XML.params.length">¶</a> – optional, a length for the column for use in
DDL and CAST expressions. May be safely omitted if no <code class="docutils literal notranslate"><span class="pre">CREATE</span>
<span class="pre">TABLE</span></code> will be issued. Certain databases may require a
<code class="docutils literal notranslate"><span class="pre">length</span></code> for use in DDL, and will raise an exception when
the <code class="docutils literal notranslate"><span class="pre">CREATE</span> <span class="pre">TABLE</span></code> DDL is issued if a <code class="docutils literal notranslate"><span class="pre">VARCHAR</span></code>
with no length is included. Whether the value is
interpreted as bytes or characters is database specific.</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mssql.XML.params.collation"></span><strong>collation</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mssql.XML.params.collation">¶</a> – <p>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.:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="k">import</span> <span class="n">cast</span><span class="p">,</span> <span class="n">select</span><span class="p">,</span> <span class="n">String</span>
<span class="gp">>>> </span><span class="nb">print</span> <span class="n">select</span><span class="p">([</span><span class="n">cast</span><span class="p">(</span><span class="s1">'some string'</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="n">collation</span><span class="o">=</span><span class="s1">'utf8'</span><span class="p">))])</span>
<span class="go">SELECT CAST(:param_1 AS VARCHAR COLLATE utf8) AS anon_1</span></pre></div>
</div>
</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mssql.XML.params.convert_unicode"></span><strong>convert_unicode</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mssql.XML.params.convert_unicode">¶</a> – <p>When set to <code class="docutils literal notranslate"><span class="pre">True</span></code>, the
<a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.String" title="sqlalchemy.types.String"><code class="xref py py-class docutils literal notranslate"><span class="pre">String</span></code></a> type will assume that
input is to be passed as Python Unicode objects under Python 2,
and results returned as Python Unicode objects.
In the rare circumstance that the DBAPI does not support
Python unicode under Python 2, SQLAlchemy will use its own
encoder/decoder functionality on strings, referring to the
value of the <a class="reference internal" href="../core/engines.html#sqlalchemy.create_engine.params.encoding" title="sqlalchemy.create_engine"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">create_engine.encoding</span></code></a> parameter
parameter passed to <a class="reference internal" href="../core/engines.html#sqlalchemy.create_engine" title="sqlalchemy.create_engine"><code class="xref py py-func docutils literal notranslate"><span class="pre">create_engine()</span></code></a> as the encoding.</p>
<p>For the extremely rare case that Python Unicode
is to be encoded/decoded by SQLAlchemy on a backend
that <em>does</em> natively support Python Unicode,
the string value <code class="docutils literal notranslate"><span class="pre">"force"</span></code> can be passed here which will
cause SQLAlchemy’s encode/decode services to be
used unconditionally.</p>
<div class="admonition note">
<p class="admonition-title">Note</p>
<p>SQLAlchemy’s unicode-conversion flags and features only apply
to Python 2; in Python 3, all string objects are Unicode objects.
For this reason, as well as the fact that virtually all modern
DBAPIs now support Unicode natively even under Python 2,
the <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.String.params.convert_unicode" title="sqlalchemy.types.String"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">String.convert_unicode</span></code></a> flag is inherently a
legacy feature.</p>
</div>
<div class="admonition note">
<p class="admonition-title">Note</p>
<p>In the vast majority of cases, the <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.Unicode" title="sqlalchemy.types.Unicode"><code class="xref py py-class docutils literal notranslate"><span class="pre">Unicode</span></code></a> or
<a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.UnicodeText" title="sqlalchemy.types.UnicodeText"><code class="xref py py-class docutils literal notranslate"><span class="pre">UnicodeText</span></code></a> datatypes should be used for a
<a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><code class="xref py py-class docutils literal notranslate"><span class="pre">Column</span></code></a> that expects to store non-ascii data. These
datatypes will ensure that the correct types are used on the
database side as well as set up the correct Unicode behaviors
under Python 2.</p>
</div>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="../core/engines.html#sqlalchemy.create_engine.params.convert_unicode" title="sqlalchemy.create_engine"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">create_engine.convert_unicode</span></code></a> -
<a class="reference internal" href="../core/connections.html#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a>-wide parameter</p>
</div>
</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mssql.XML.params.unicode_error"></span><strong>unicode_error</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mssql.XML.params.unicode_error">¶</a> – Optional, a method to use to handle Unicode
conversion errors. Behaves like the <code class="docutils literal notranslate"><span class="pre">errors</span></code> keyword argument to
the standard library’s <code class="docutils literal notranslate"><span class="pre">string.decode()</span></code> functions. This flag
requires that <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.String.params.convert_unicode" title="sqlalchemy.types.String"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">String.convert_unicode</span></code></a> is set to
<code class="docutils literal notranslate"><span class="pre">"force"</span></code> - 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.</p></li>
</ul>
</dd>
</dl>
</dd></dl>
</dd></dl>
</div>
<div class="section" id="module-sqlalchemy.dialects.mssql.pyodbc">
<span id="pyodbc"></span><h2>PyODBC<a class="headerlink" href="#module-sqlalchemy.dialects.mssql.pyodbc" title="Permalink to this headline">¶</a></h2>
<p>Support for the Microsoft SQL Server database via the PyODBC driver.</p>
<div class="section" id="dialect-mssql-pyodbc-url">
<h3>DBAPI<a class="headerlink" href="#dialect-mssql-pyodbc-url" title="Permalink to this headline">¶</a></h3>
<p>Documentation and download information (if applicable) for PyODBC is available at:
<a class="reference external" href="http://pypi.python.org/pypi/pyodbc/">http://pypi.python.org/pypi/pyodbc/</a></p>
</div>
<div class="section" id="dialect-mssql-pyodbc-connect">
<h3>Connecting<a class="headerlink" href="#dialect-mssql-pyodbc-connect" title="Permalink to this headline">¶</a></h3>
<p>Connect String:<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">mssql</span><span class="o">+</span><span class="n">pyodbc</span><span class="p">:</span><span class="o">//<</span><span class="n">username</span><span class="o">></span><span class="p">:</span><span class="o"><</span><span class="n">password</span><span class="o">>@<</span><span class="n">dsnname</span><span class="o">></span></pre></div>
</div>
</p>
</div>
<div class="section" id="connecting-to-pyodbc">
<h3>Connecting to PyODBC<a class="headerlink" href="#connecting-to-pyodbc" title="Permalink to this headline">¶</a></h3>
<p>The URL here is to be translated to PyODBC connection strings, as
detailed in <a class="reference external" href="https://code.google.com/p/pyodbc/wiki/ConnectionStrings">ConnectionStrings</a>.</p>
<div class="section" id="dsn-connections">
<h4>DSN Connections<a class="headerlink" href="#dsn-connections" title="Permalink to this headline">¶</a></h4>
<p>A DSN-based connection is <strong>preferred</strong> overall when using ODBC. A
basic DSN-based connection looks like:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">engine</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s2">"mssql+pyodbc://scott:tiger@some_dsn"</span><span class="p">)</span></pre></div>
</div>
<p>Which above, will pass the following connection string to PyODBC:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">dsn</span><span class="o">=</span><span class="n">mydsn</span><span class="p">;</span><span class="n">UID</span><span class="o">=</span><span class="n">user</span><span class="p">;</span><span class="n">PWD</span><span class="o">=</span><span class="k">pass</span></pre></div>
</div>
<p>If the username and password are omitted, the DSN form will also add
the <code class="docutils literal notranslate"><span class="pre">Trusted_Connection=yes</span></code> directive to the ODBC string.</p>
</div>
<div class="section" id="hostname-connections">
<h4>Hostname Connections<a class="headerlink" href="#hostname-connections" title="Permalink to this headline">¶</a></h4>
<p>Hostname-based connections are <strong>not preferred</strong>, however are supported.
The ODBC driver name must be explicitly specified:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">engine</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s2">"mssql+pyodbc://scott:tiger@myhost:port/databasename?driver=SQL+Server+Native+Client+10.0"</span><span class="p">)</span></pre></div>
</div>
<div class="versionchanged">
<p><span class="versionmodified changed">Changed in version 1.0.0: </span>Hostname-based PyODBC connections now require the
SQL Server driver name specified explicitly. SQLAlchemy cannot
choose an optimal default here as it varies based on platform
and installed drivers.</p>
</div>
<p>Other keywords interpreted by the Pyodbc dialect to be passed to
<code class="docutils literal notranslate"><span class="pre">pyodbc.connect()</span></code> in both the DSN and hostname cases include:
<code class="docutils literal notranslate"><span class="pre">odbc_autotranslate</span></code>, <code class="docutils literal notranslate"><span class="pre">ansi</span></code>, <code class="docutils literal notranslate"><span class="pre">unicode_results</span></code>, <code class="docutils literal notranslate"><span class="pre">autocommit</span></code>.</p>
</div>
<div class="section" id="pass-through-exact-pyodbc-string">
<h4>Pass through exact Pyodbc string<a class="headerlink" href="#pass-through-exact-pyodbc-string" title="Permalink to this headline">¶</a></h4>
<p>A PyODBC connection string can also be sent exactly as specified in
<a class="reference external" href="https://code.google.com/p/pyodbc/wiki/ConnectionStrings">ConnectionStrings</a>
into the driver using the parameter <code class="docutils literal notranslate"><span class="pre">odbc_connect</span></code>. The delimeters must be
URL escaped, however, as illustrated below using <code class="docutils literal notranslate"><span class="pre">urllib.quote_plus</span></code>:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="kn">import</span> <span class="nn">urllib</span>
<span class="n">params</span> <span class="o">=</span> <span class="n">urllib</span><span class="o">.</span><span class="n">quote_plus</span><span class="p">(</span><span class="s2">"DRIVER={SQL Server Native Client 10.0};SERVER=dagger;DATABASE=test;UID=user;PWD=password"</span><span class="p">)</span>
<span class="n">engine</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s2">"mssql+pyodbc:///?odbc_connect=</span><span class="si">%s</span><span class="s2">"</span> <span class="o">%</span> <span class="n">params</span><span class="p">)</span></pre></div>
</div>
</div>
</div>
<div class="section" id="driver-unicode-support">
<h3>Driver / Unicode Support<a class="headerlink" href="#driver-unicode-support" title="Permalink to this headline">¶</a></h3>
<p>PyODBC works best with Microsoft ODBC drivers, particularly in the area
of Unicode support on both Python 2 and Python 3.</p>
<p>Using the FreeTDS ODBC drivers on Linux or OSX with PyODBC is <strong>not</strong>
recommended; there have been historically many Unicode-related issues
in this area, including before Microsoft offered ODBC drivers for Linux
and OSX. Now that Microsoft offers drivers for all platforms, for
PyODBC support these are recommended. FreeTDS remains relevant for
non-ODBC drivers such as pymssql where it works very well.</p>
</div>
<div class="section" id="rowcount-support">
<h3>Rowcount Support<a class="headerlink" href="#rowcount-support" title="Permalink to this headline">¶</a></h3>
<p>Pyodbc only has partial support for rowcount. See the notes at
<a class="reference internal" href="#mssql-rowcount-versioning"><span class="std std-ref">Rowcount Support / ORM Versioning</span></a> for important notes when using ORM
versioning.</p>
</div>
</div>
<div class="section" id="module-sqlalchemy.dialects.mssql.mxodbc">
<span id="mxodbc"></span><h2>mxODBC<a class="headerlink" href="#module-sqlalchemy.dialects.mssql.mxodbc" title="Permalink to this headline">¶</a></h2>
<p>Support for the Microsoft SQL Server database via the mxODBC driver.</p>
<div class="section" id="dialect-mssql-mxodbc-url">
<h3>DBAPI<a class="headerlink" href="#dialect-mssql-mxodbc-url" title="Permalink to this headline">¶</a></h3>
<p>Documentation and download information (if applicable) for mxODBC is available at:
<a class="reference external" href="http://www.egenix.com/">http://www.egenix.com/</a></p>
</div>
<div class="section" id="dialect-mssql-mxodbc-connect">
<h3>Connecting<a class="headerlink" href="#dialect-mssql-mxodbc-connect" title="Permalink to this headline">¶</a></h3>
<p>Connect String:<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">mssql</span><span class="o">+</span><span class="n">mxodbc</span><span class="p">:</span><span class="o">//<</span><span class="n">username</span><span class="o">></span><span class="p">:</span><span class="o"><</span><span class="n">password</span><span class="o">>@<</span><span class="n">dsnname</span><span class="o">></span></pre></div>
</div>
</p>
</div>
<div class="section" id="execution-modes">
<h3>Execution Modes<a class="headerlink" href="#execution-modes" title="Permalink to this headline">¶</a></h3>
<p>mxODBC features two styles of statement execution, using the
<code class="docutils literal notranslate"><span class="pre">cursor.execute()</span></code> and <code class="docutils literal notranslate"><span class="pre">cursor.executedirect()</span></code> methods (the second being
an extension to the DBAPI specification). The former makes use of a particular
API call specific to the SQL Server Native Client ODBC driver known
SQLDescribeParam, while the latter does not.</p>
<p>mxODBC apparently only makes repeated use of a single prepared statement
when SQLDescribeParam is used. The advantage to prepared statement reuse is
one of performance. The disadvantage is that SQLDescribeParam has a limited
set of scenarios in which bind parameters are understood, including that they
cannot be placed within the argument lists of function calls, anywhere outside
the FROM, or even within subqueries within the FROM clause - making the usage
of bind parameters within SELECT statements impossible for all but the most
simplistic statements.</p>
<p>For this reason, the mxODBC dialect uses the “native” mode by default only for
INSERT, UPDATE, and DELETE statements, and uses the escaped string mode for
all other statements.</p>
<p>This behavior can be controlled via
<a class="reference internal" href="../core/selectable.html#sqlalchemy.sql.expression.Executable.execution_options" title="sqlalchemy.sql.expression.Executable.execution_options"><code class="xref py py-meth docutils literal notranslate"><span class="pre">execution_options()</span></code></a> using the
<code class="docutils literal notranslate"><span class="pre">native_odbc_execute</span></code> flag with a value of <code class="docutils literal notranslate"><span class="pre">True</span></code> or <code class="docutils literal notranslate"><span class="pre">False</span></code>, where a
value of <code class="docutils literal notranslate"><span class="pre">True</span></code> will unconditionally use native bind parameters and a value
of <code class="docutils literal notranslate"><span class="pre">False</span></code> will unconditionally use string-escaped parameters.</p>
</div>
</div>
<div class="section" id="module-sqlalchemy.dialects.mssql.pymssql">
<span id="pymssql"></span><h2>pymssql<a class="headerlink" href="#module-sqlalchemy.dialects.mssql.pymssql" title="Permalink to this headline">¶</a></h2>
<p>Support for the Microsoft SQL Server database via the pymssql driver.</p>
<div class="section" id="dialect-mssql-pymssql-url">
<h3>DBAPI<a class="headerlink" href="#dialect-mssql-pymssql-url" title="Permalink to this headline">¶</a></h3>
<p>Documentation and download information (if applicable) for pymssql is available at:
<a class="reference external" href="http://pymssql.org/">http://pymssql.org/</a></p>
</div>
<div class="section" id="dialect-mssql-pymssql-connect">
<h3>Connecting<a class="headerlink" href="#dialect-mssql-pymssql-connect" title="Permalink to this headline">¶</a></h3>
<p>Connect String:<div class="highlight-default notranslate"><div class="highlight"><pre><span></span>mssql+pymssql://<username>:<password>@<freetds_name>/?charset=utf8</pre></div>
</div>
</p>
</div>
<p>pymssql is a Python module that provides a Python DBAPI interface around
<a class="reference external" href="http://www.freetds.org/">FreeTDS</a>. Compatible builds are available for
Linux, MacOSX and Windows platforms.</p>
<p>Modern versions of this driver work very well with SQL Server and
FreeTDS from Linux and is highly recommended.</p>
</div>
<div class="section" id="module-sqlalchemy.dialects.mssql.zxjdbc">
<span id="zxjdbc"></span><h2>zxjdbc<a class="headerlink" href="#module-sqlalchemy.dialects.mssql.zxjdbc" title="Permalink to this headline">¶</a></h2>
<p>Support for the Microsoft SQL Server database via the zxJDBC for Jython driver.<div class="admonition note">
<p class="admonition-title">Note</p>
<p>Jython is not supported by current versions of SQLAlchemy. The
zxjdbc dialect should be considered as experimental.</p>
</div>
</p>
<div class="section" id="dialect-mssql-zxjdbc-url">
<h3>DBAPI<a class="headerlink" href="#dialect-mssql-zxjdbc-url" title="Permalink to this headline">¶</a></h3>
<p>Drivers for this database are available at:
<a class="reference external" href="http://jtds.sourceforge.net/">http://jtds.sourceforge.net/</a></p>
</div>
<div class="section" id="dialect-mssql-zxjdbc-connect">
<h3>Connecting<a class="headerlink" href="#dialect-mssql-zxjdbc-connect" title="Permalink to this headline">¶</a></h3>
<p>Connect String:<div class="highlight-default notranslate"><div class="highlight"><pre><span></span>mssql+zxjdbc://user:pass@host:port/dbname[?key=value&key=value...]</pre></div>
</div>
</p>
</div>
</div>
<div class="section" id="module-sqlalchemy.dialects.mssql.adodbapi">
<span id="adodbapi"></span><h2>AdoDBAPI<a class="headerlink" href="#module-sqlalchemy.dialects.mssql.adodbapi" title="Permalink to this headline">¶</a></h2>
<p>Support for the Microsoft SQL Server database via the adodbapi driver.</p>
<div class="section" id="dialect-mssql-adodbapi-url">
<h3>DBAPI<a class="headerlink" href="#dialect-mssql-adodbapi-url" title="Permalink to this headline">¶</a></h3>
<p>Documentation and download information (if applicable) for adodbapi is available at:
<a class="reference external" href="http://adodbapi.sourceforge.net/">http://adodbapi.sourceforge.net/</a></p>
</div>
<div class="section" id="dialect-mssql-adodbapi-connect">
<h3>Connecting<a class="headerlink" href="#dialect-mssql-adodbapi-connect" title="Permalink to this headline">¶</a></h3>
<p>Connect String:<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">mssql</span><span class="o">+</span><span class="n">adodbapi</span><span class="p">:</span><span class="o">//<</span><span class="n">username</span><span class="o">></span><span class="p">:</span><span class="o"><</span><span class="n">password</span><span class="o">>@<</span><span class="n">dsnname</span><span class="o">></span></pre></div>
</div>
</p>
</div>
<div class="admonition note">
<p class="admonition-title">Note</p>
<p>The adodbapi dialect is not implemented SQLAlchemy versions 0.6 and
above at this time.</p>
</div>
</div>
</div>
</div>
</div>
<div id="docs-bottom-navigation" class="docs-navigation-links, withsidebar">
Previous:
<a href="oracle.html" title="previous chapter">Oracle</a>
Next:
<a href="firebird.html" title="next chapter">Firebird</a>
<div id="docs-copyright">
© <a href="../copyright.html">Copyright</a> 2007-2019, the SQLAlchemy authors and contributors.
Created using <a href="http://sphinx.pocoo.org/">Sphinx</a> 2.0.1.
</div>
</div>
</div>
<script type="text/javascript">
var DOCUMENTATION_OPTIONS = {
URL_ROOT: '../',
VERSION: '1.2.19',
COLLAPSE_MODINDEX: false,
FILE_SUFFIX: '.html'
};
</script>
<script type="text/javascript" id="documentation_options" data-url_root="../" src="../_static/documentation_options.js"></script>
<!-- begin iterate through sphinx environment script_files -->
<script type="text/javascript" src="../_static/jquery.js"></script>
<script type="text/javascript" src="../_static/underscore.js"></script>
<script type="text/javascript" src="../_static/doctools.js"></script>
<script type="text/javascript" src="../_static/language_data.js"></script>
<!-- end iterate through sphinx environment script_files -->
<script type="text/javascript" src="../_static/detectmobile.js"></script>
<script type="text/javascript" src="../_static/init.js"></script>
</body>
</html>
distrib
>
Mageia
>
7
>
armv7hl
>
media
>
core-updates
>
by-pkgid
>
b0b6ffab06cbeede296e36ce94734bf8
>
files
>
788
