ó ¿b›]c@smdZddlZddlZddlmZddlmZddlmZddlmZdd lm Z dd lm Z dd lm Z dd l mZd efd„ƒYZdee jfd„ƒYZdee jfd„ƒYZdefd„ƒYZdeefd„ƒYZdeefd„ƒYZdefd„ƒYZdeefd„ƒYZeZdS(s .. dialect:: mssql+pyodbc :name: PyODBC :dbapi: pyodbc :connectstring: mssql+pyodbc://:@ :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 `_. 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 `_ 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 `_ - on github iÿÿÿÿNi(tBINARY(t MSDialect(tMSExecutionContext(t VARBINARYi(texc(ttypes(tutil(tPyODBCConnectort_ms_numeric_pyodbccBs)eZdZd„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(Ncstˆjr\t|tjƒr\|jƒ}|dkr@ˆj|ƒS|dkr\ˆj|ƒSnˆrlˆ|ƒS|SdS(Nii(t asdecimalt isinstancetdecimaltDecimaltadjustedt_small_dec_to_stringt_large_dec_to_string(tvalueR (tselft super_process(s?lib/python2.7/site-packages/sqlalchemy/dialects/mssql/pyodbc.pytprocess‘s     (tsuperRtbind_processort_need_decimal_fix(RtdialectR((RRs?lib/python2.7/site-packages/sqlalchemy/dialects/mssql/pyodbc.pyRŠs   cCscd|dkrdpddt|jƒƒddjg|jƒdD]}t|ƒ^qFƒfS(Ns%s0.%s%sit-tt0i(tabsR tjointas_tupletstr(RRtnint((s?lib/python2.7/site-packages/sqlalchemy/dialects/mssql/pyodbc.pyR£scCs„|jƒd}dt|ƒkr„d|dkr7dp:ddjg|D]}t|ƒ^qGƒd|jƒt|ƒdf}nüt|ƒd|jƒkr+d|dkrµdp¸ddjg|D]}t|ƒ^qÅd|jƒd!ƒdjg|D]}t|ƒ^qû|jƒdƒf}nUd |dkr@dpCddjg|D]}t|ƒ^qPd|jƒd!ƒf}|S( NitEs%s%s%siRRRs%s%s.%ss%s%s(RRRR tlen(RRt_inttstresult((s?lib/python2.7/site-packages/sqlalchemy/dialects/mssql/pyodbc.pyRªs%%6==(t__name__t __module__t__doc__RRR(((s?lib/python2.7/site-packages/sqlalchemy/dialects/mssql/pyodbc.pyRs  t_MSNumeric_pyodbccBseZRS((R%R&(((s?lib/python2.7/site-packages/sqlalchemy/dialects/mssql/pyodbc.pyR(Ást_MSFloat_pyodbccBseZRS((R%R&(((s?lib/python2.7/site-packages/sqlalchemy/dialects/mssql/pyodbc.pyR)Åst_ms_binary_pyodbccBseZdZd„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(Ncs$|dk rˆ|ƒSˆjjSdS(N(tNonetdbapit BinaryNull(R(t DBAPIBinaryR(s?lib/python2.7/site-packages/sqlalchemy/dialects/mssql/pyodbc.pyRÖs  (R,R+tBinary(RRR((R.Rs?lib/python2.7/site-packages/sqlalchemy/dialects/mssql/pyodbc.pyRÐs  (R%R&R'R(((s?lib/python2.7/site-packages/sqlalchemy/dialects/mssql/pyodbc.pyR*Ést_VARBINARY_pyodbccBseZRS((R%R&(((s?lib/python2.7/site-packages/sqlalchemy/dialects/mssql/pyodbc.pyR0àst_BINARY_pyodbccBseZRS((R%R&(((s?lib/python2.7/site-packages/sqlalchemy/dialects/mssql/pyodbc.pyR1ästMSExecutionContext_pyodbccBs eZeZd„Zd„ZRS(cCsZtt|ƒ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((s?lib/python2.7/site-packages/sqlalchemy/dialects/mssql/pyodbc.pyR3ës    cCs…|jrnxLtrWy|jjƒd}PWq |jjjk rS|jjƒq Xq Wt|dƒ|_ nt t |ƒj ƒdS(Ni( R8R7tcursortfetchallRR,tErrortnextsettintt _lastrowidRR2t post_exec(Rtrow((s?lib/python2.7/site-packages/sqlalchemy/dialects/mssql/pyodbc.pyR@s  (R%R&tFalseR8R3R@(((s?lib/python2.7/site-packages/sqlalchemy/dialects/mssql/pyodbc.pyR2ès tMSDialect_pyodbccBs†eZeZejejiee j 6e e 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=iii(iii( tpopRDRRCt__init__R5R,thasattrtCursort_dbapi_versionRtfast_executemany(RRDRJtparams((s?lib/python2.7/site-packages/sqlalchemy/dialects/mssql/pyodbc.pyRF-s    cCs«y|jdƒ}Wn-tjk rBtt|ƒj|dtƒSXg}tjdƒ}xB|j |ƒD]1}y|j t |ƒƒWqht k r˜qhXqhWt |ƒSdS(Ns8SELECT CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR)t allow_charss[.\-](tscalarRt DBAPIErrorRRCt_get_server_version_infoRBtretcompiletsplittappendR>t ValueErrorttuple(Rt connectiontrawtversiontrtn((s?lib/python2.7/site-packages/sqlalchemy/dialects/mssql/pyodbc.pyRO?s  cCs;|jrt|_ntt|ƒj|||d|ƒdS(Ntcontext(RJR7RRCtdo_executemany(RR:R9R6R[((s?lib/python2.7/site-packages/sqlalchemy/dialects/mssql/pyodbc.pyR\Ws  c Cs[t||jjƒr?x'd D]}|t|ƒkrtSqWntt|ƒj|||ƒS( Nt08S01t01002t08003t08007t08S02t08001tHYT00tHY010t10054( R]R^R_R`RaRbRcRdRe(R R,R<RR7RRCt is_disconnect(RteRVR:tcode((s?lib/python2.7/site-packages/sqlalchemy/dialects/mssql/pyodbc.pyRf^s  N(R%R&R2texecution_ctx_clsRt update_copyRtcolspecsR(tsqltypestNumericR)tFloatR1RR0Rt LargeBinaryR+RBRFROR\Rf(((s?lib/python2.7/site-packages/sqlalchemy/dialects/mssql/pyodbc.pyRCs     (R'R RPtbaseRRRRRRRRlRtconnectors.pyodbcRtobjectRRmR(RnR)R*R0R1R2RCR(((s?lib/python2.7/site-packages/sqlalchemy/dialects/mssql/pyodbc.pytrs&  @2X