�
��4]c@sm
dZd
dl
Z
d
dlZddlmZ
ddlmZ
ddlmZ
ddlmZ
dd	lm	Z	
dd
lm
Z
ddlmZ
ddl
mZ
d
ef
d��YZdeejfd��YZdeejfd��YZdef
d��YZdeefd��YZdeefd��YZdef
d��YZdeefd��YZeZdS(s
.. dialect:: mssql+pyodbc
    :name: PyODBC
    :dbapi: pyodbc
    :connectstring: mssql+pyodbc://<username>:<password>@<dsnname>
    :url: http://pypi.python.org/pypi/pyodbc/

Connecting to PyODBC
--------------------

The URL here is to be translated to PyODBC connection strings, as
detailed in `ConnectionStrings <https://code.google.com/p/pyodbc/wiki/ConnectionStrings>`_.

DSN Connections
^^^^^^^^^^^^^^^

A DSN-based connection is **preferred** overall when using ODBC.  A
basic DSN-based connection looks like::

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

Which above, will pass the following connection string to PyODBC::

    dsn=mydsn;UID=user;PWD=pass

If the username and password are omitted, the DSN form will also add
the ``Trusted_Connection=yes`` directive to the ODBC string.

Hostname Connections
^^^^^^^^^^^^^^^^^^^^

Hostname-based connections are **not preferred**, however are supported.
The ODBC driver name must be explicitly specified::

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

.. versionchanged:: 1.0.0 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.

Other keywords interpreted by the Pyodbc dialect to be passed to
``pyodbc.connect()`` in both the DSN and hostname cases include:
``odbc_autotranslate``, ``ansi``, ``unicode_results``, ``autocommit``.

Pass through exact Pyodbc string
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

A PyODBC connection string can also be sent exactly as specified in
`ConnectionStrings <https://code.google.com/p/pyodbc/wiki/ConnectionStrings>`_
into the driver using the parameter ``odbc_connect``.  The delimeters must be
URL escaped, however, as illustrated below using ``urllib.parse.quote_plus``::

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

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


Driver / Unicode Support
-------------------------

PyODBC works best with Microsoft ODBC drivers, particularly in the area
of Unicode support on both Python 2 and Python 3.

Using the FreeTDS ODBC drivers on Linux or OSX with PyODBC is **not**
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.


Rowcount Support
----------------

Pyodbc only has partial support for rowcount.  See the notes at
:ref:`mssql_rowcount_versioning` for important notes when using ORM
versioning.

.. _mssql_pyodbc_fastexecutemany:

Fast Executemany Mode
---------------------

The Pyodbc driver has added support for a "fast executemany" mode of execution
which greatly reduces round trips for a DBAPI ``executemany()`` call when using
Microsoft ODBC drivers.  The feature is enabled by setting the flag
``.fast_executemany`` on the DBAPI cursor when an executemany call is to be
used.   The SQLAlchemy pyodbc SQL Server dialect supports setting this flag
automatically when the ``.fast_executemany`` flag is passed to
:func:`.create_engine`; note that the ODBC driver must be the Microsoft driver
in order to use this flag::

    engine = create_engine(
        "mssql+pyodbc://scott:tiger@mssql2017:1433/test?driver=ODBC+Driver+13+for+SQL+Server",
        fast_executemany=True)

.. versionadded:: 1.3

.. seealso::

    `fast executemany <https://github.com/mkleehammer/pyodbc/wiki/Features-beyond-the-DB-API#fast_executemany>`_
    - on github


i����Ni
(
tBINARY(
t	MSDialect(
tMSExecutionContext(
t	VARBINARYi(
texc(
ttypes(
tutil(
tPyODBCConnectort_ms_numeric_pyodbcc
Bs)eZ
dZd
�Zd�Zd�ZRS(s�Turns Decimals with adjusted() < 0 or > 7 into strings.

    The routines here are needed for older pyodbc versions
    as well as current mxODBC versions.

    cs;tt
��j|
�
�
|
js%�
S��
fd
�}|S(Nc
st�jr\t
|tj�r\|j�}
|
d
kr@�j|�
S|
dkr\�j|�
Sn�
rl�
|�
S|SdS(Nii(t	asdecimalt
isinstancetdecimaltDecimaltadjustedt_small_dec_to_stringt_large_dec_to_string(tvalueR
(tselft
super_process(sS/opt/alt/python27/lib64/python2.7/site-packages/sqlalchemy/dialects/mssql/pyodbc.pytprocess�s








(tsuperRtbind_processort_need_decimal_fix(RtdialectR((RRsS/opt/alt/python27/lib64/python2.7/site-packages/sqlalchemy/dialects/mssql/pyodbc.pyR�s
	

cCscd
|
dkrdpddt|
j
��
ddjg|
j�dD]}t|�
^qF�
fS(Ns%s0.%s%sit
-tt
0i
(tabsR
tjointas_tupletstr(RRtnint((sS/opt/alt/python27/lib64/python2.7/site-packages/sqlalchemy/dialects/mssql/pyodbc.pyR�s



cCs�
|
j�d
}dt
|
�
kr�d|
dkr7dp:ddjg|D]}t
|�
^qG�
d|
j�t|�
d
f}n�t|�
d
|
j�kr+
d|
dkr�dp�ddjg|D]}t
|�
^q�d|
j�d
!�
djg|D]}t
|�
^q�|
j�d
�
f}nUd	|
dkr@
dpC
ddjg|D]}t
|�
^qP
d|
j�d
!�
f}|S(
Ni
t
Es%s%s%siRRRs%s%s.%ss%s%s(RRRR
tlen(RRt_intt
stresult((sS/opt/alt/python27/lib64/python2.7/site-packages/sqlalchemy/dialects/mssql/pyodbc.pyR�s




%
%


6
=

=(t__name__t
__module__t__doc__RRR(((sS/opt/alt/python27/lib64/python2.7/site-packages/sqlalchemy/dialects/mssql/pyodbc.pyR�s		t_MSNumeric_pyodbcc
BseZ
RS((R%R&(((sS/opt/alt/python27/lib64/python2.7/site-packages/sqlalchemy/dialects/mssql/pyodbc.pyR(�s
t_MSFloat_pyodbcc
BseZ
RS((R%R&(((sS/opt/alt/python27/lib64/python2.7/site-packages/sqlalchemy/dialects/mssql/pyodbc.pyR)�s
t_ms_binary_pyodbcc
BseZ
dZd
�ZRS(s�Wraps binary values in dialect-specific Binary wrapper.
    If the value is null, return a pyodbc-specific BinaryNull
    object to prevent pyODBC [and FreeTDS] from defaulting binary
    NULL types to SQLWCHAR and causing implicit conversion errors.
    cs5�
jdkrdS�
jj���
fd
�}|S(Nc

s$|dk	r�|�
S�
j
jSdS(
N(tNonetdbapit
BinaryNull(
R(tDBAPIBinaryR(sS/opt/alt/python27/lib64/python2.7/site-packages/sqlalchemy/dialects/mssql/pyodbc.pyR�s


(R,R+tBinary(RRR((R.RsS/opt/alt/python27/lib64/python2.7/site-packages/sqlalchemy/dialects/mssql/pyodbc.pyR�s


(R%R&R'R(((sS/opt/alt/python27/lib64/python2.7/site-packages/sqlalchemy/dialects/mssql/pyodbc.pyR*�st_VARBINARY_pyodbcc
BseZ
RS((R%R&(((sS/opt/alt/python27/lib64/python2.7/site-packages/sqlalchemy/dialects/mssql/pyodbc.pyR0�s
t_BINARY_pyodbcc
BseZ
RS((R%R&(((sS/opt/alt/python27/lib64/python2.7/site-packages/sqlalchemy/dialects/mssql/pyodbc.pyR1�s
tMSExecutionContext_pyodbcc
Bs eZ
eZd�Zd
�ZRS(c

CsZtt
|�j�
|jrV|jjrVt|jd
�
rVt|_	|j
d7_
ndS(s�
where appropriate, issue "select scope_identity()" in the same
        statement.

        Background on why "scope_identity()" is preferable to "@@identity":
        http://msdn.microsoft.com/en-us/library/ms190315.aspx

        Background on why we attempt to embed "scope_identity()" into the same
        statement as the INSERT:
        http://code.google.com/p/pyodbc/wiki/FAQs#How_do_I_retrieve_autogenerated/identity_values?

        is; select scope_identity()N(RR2tpre_exect_select_lastrowidRtuse_scope_identityR!t
parameterstTruet_embedded_scope_identityt	statement(
R((sS/opt/alt/python27/lib64/python2.7/site-packages/sqlalchemy/dialects/mssql/pyodbc.pyR3�s
	

	c
Cs�|jrnxLt
rWy|jj�d
}
PWq|jjjk
rS


|jj�
qXqWt|
d
�
|_	nt
t|�j�
dS(Ni(
R8R7tcursortfetchallRR,tErrortnextsettintt
_lastrowidRR2t	post_exec(Rtrow((sS/opt/alt/python27/lib64/python2.7/site-packages/sqlalchemy/dialects/mssql/pyodbc.pyR@
s
		


(R%R&tFalseR8R3R@(((sS/opt/alt/python27/lib64/python2.7/site-packages/sqlalchemy/dialects/mssql/pyodbc.pyR2�s
	tMSDialect_pyodbccBs�eZ
eZejejiee	j
6ee	j6e
e6ee6ee	j6ee	j6�Zded�Zd
�Zdd�
Zd�ZRS(cKs�d
|kr!|jd
�
|_
ntt|�j|�
|jo[|jo[t|jjd�|_|joy|j	�dk|_
||_dS(Ntdescription_encodingR=ii
i(ii
i(tpopRDRRCt__init__R5R,thasattrtCursort_dbapi_versionRtfast_executemany(RRDRJtparams((sS/opt/alt/python27/lib64/python2.7/site-packages/sqlalchemy/dialects/mssql/pyodbc.pyRF-
s

	
	



cCs�y|
jd
�
}Wn-t
jk
rB


tt|�j|
dt�

SXg}tjd�
}xB|j	|�
D]1}y|j
t|�
�

Wqhtk
r�


qhXqhWt
|�
SdS(Ns8SELECT CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR)tallow_charss[.\-](tscalarRt
DBAPIErrorRRCt_get_server_version_infoRBtretcompiletsplittappendR>t
ValueErrorttuple(Rt
connectiontrawtversiont
rt
n((sS/opt/alt/python27/lib64/python2.7/site-packages/sqlalchemy/dialects/mssql/pyodbc.pyRO?
s











cCs;|jrt
|
_ntt|�j|
||d
|�

dS(Ntcontext(RJR7RRCtdo_executemany(RR:R9R6R[((sS/opt/alt/python27/lib64/python2.7/site-packages/sqlalchemy/dialects/mssql/pyodbc.pyR\W
s
	


c	Cs[t|
|j
j�r?x'd
D]}|t|
�
krtSqWntt|�j|
||�S(Nt08S01t01002t08003t08007t08S02t08001tHYT00tHY010t10054(	R]R^R_R`RaRbRcRdRe(R
R,R<RR7RRCt
is_disconnect(Rt
eRVR:tcode((sS/opt/alt/python27/lib64/python2.7/site-packages/sqlalchemy/dialects/mssql/pyodbc.pyRf^
s














N(R%R&R2texecution_ctx_clsRtupdate_copyR
tcolspecsR(tsqltypestNumericR)tFloatR1RR0RtLargeBinaryR+RBRFROR\Rf(((sS/opt/alt/python27/lib64/python2.7/site-packages/sqlalchemy/dialects/mssql/pyodbc.pyRC
s









	(R'RRPtbaseRR
RRRRRRlRtconnectors.pyodbcRtobjectRRmR(RnR)R*R0R1R2RCR(((sS/opt/alt/python27/lib64/python2.7/site-packages/sqlalchemy/dialects/mssql/pyodbc.pyt<module>rs&







@2X