Coverage for /home/martinb/.local/share/virtualenvs/camcops/lib/python3.6/site-packages/sqlalchemy/dialects/mssql/pyodbc.py : 34%

Hot-keys on this page
r m x p toggle line displays
j k next/prev highlighted chunk
0 (zero) top of page
1 (one) first highlighted chunk
1# mssql/pyodbc.py
2# Copyright (C) 2005-2020 the SQLAlchemy authors and contributors
3# <see AUTHORS file>
4#
5# This module is part of SQLAlchemy and is released under
6# the MIT License: http://www.opensource.org/licenses/mit-license.php
8r"""
9.. dialect:: mssql+pyodbc
10 :name: PyODBC
11 :dbapi: pyodbc
12 :connectstring: mssql+pyodbc://<username>:<password>@<dsnname>
13 :url: http://pypi.python.org/pypi/pyodbc/
15Connecting to PyODBC
16--------------------
18The URL here is to be translated to PyODBC connection strings, as
19detailed in `ConnectionStrings <https://code.google.com/p/pyodbc/wiki/ConnectionStrings>`_.
21DSN Connections
22^^^^^^^^^^^^^^^
24A DSN connection in ODBC means that a pre-existing ODBC datasource is
25configured on the client machine. The application then specifies the name
26of this datasource, which encompasses details such as the specific ODBC driver
27in use as well as the network address of the database. Assuming a datasource
28is configured on the client, a basic DSN-based connection looks like::
30 engine = create_engine("mssql+pyodbc://scott:tiger@some_dsn")
32Which above, will pass the following connection string to PyODBC::
34 dsn=mydsn;UID=user;PWD=pass
36If the username and password are omitted, the DSN form will also add
37the ``Trusted_Connection=yes`` directive to the ODBC string.
39Hostname Connections
40^^^^^^^^^^^^^^^^^^^^
42Hostname-based connections are also supported by pyodbc. These are often
43easier to use than a DSN and have the additional advantage that the specific
44database name to connect towards may be specified locally in the URL, rather
45than it being fixed as part of a datasource configuration.
47When using a hostname connection, the driver name must also be specified in the
48query parameters of the URL. As these names usually have spaces in them, the
49name must be URL encoded which means using plus signs for spaces::
51 engine = create_engine("mssql+pyodbc://scott:tiger@myhost:port/databasename?driver=SQL+Server+Native+Client+10.0")
53Other keywords interpreted by the Pyodbc dialect to be passed to
54``pyodbc.connect()`` in both the DSN and hostname cases include:
55``odbc_autotranslate``, ``ansi``, ``unicode_results``, ``autocommit``.
56Note that in order for the dialect to recognize these keywords
57(including the ``driver`` keyword above) they must be all lowercase.
59Pass through exact Pyodbc string
60^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
62A PyODBC connection string can also be sent in pyodbc's format directly, as
63specified in `ConnectionStrings
64<https://code.google.com/p/pyodbc/wiki/ConnectionStrings>`_ into the driver
65using the parameter ``odbc_connect``. The delimeters must be URL encoded, as
66illustrated below using ``urllib.parse.quote_plus``::
68 import urllib
69 params = urllib.parse.quote_plus("DRIVER={SQL Server Native Client 10.0};SERVER=dagger;DATABASE=test;UID=user;PWD=password")
71 engine = create_engine("mssql+pyodbc:///?odbc_connect=%s" % params)
74Driver / Unicode Support
75-------------------------
77PyODBC works best with Microsoft ODBC drivers, particularly in the area
78of Unicode support on both Python 2 and Python 3.
80Using the FreeTDS ODBC drivers on Linux or OSX with PyODBC is **not**
81recommended; there have been historically many Unicode-related issues
82in this area, including before Microsoft offered ODBC drivers for Linux
83and OSX. Now that Microsoft offers drivers for all platforms, for
84PyODBC support these are recommended. FreeTDS remains relevant for
85non-ODBC drivers such as pymssql where it works very well.
88Rowcount Support
89----------------
91Pyodbc only has partial support for rowcount. See the notes at
92:ref:`mssql_rowcount_versioning` for important notes when using ORM
93versioning.
95.. _mssql_pyodbc_fastexecutemany:
97Fast Executemany Mode
98---------------------
100The Pyodbc driver has added support for a "fast executemany" mode of execution
101which greatly reduces round trips for a DBAPI ``executemany()`` call when using
102Microsoft ODBC drivers. The feature is enabled by setting the flag
103``.fast_executemany`` on the DBAPI cursor when an executemany call is to be
104used. The SQLAlchemy pyodbc SQL Server dialect supports setting this flag
105automatically when the ``.fast_executemany`` flag is passed to
106:func:`_sa.create_engine`
107; note that the ODBC driver must be the Microsoft driver
108in order to use this flag::
110 engine = create_engine(
111 "mssql+pyodbc://scott:tiger@mssql2017:1433/test?driver=ODBC+Driver+13+for+SQL+Server",
112 fast_executemany=True)
114.. versionadded:: 1.3
116.. seealso::
118 `fast executemany <https://github.com/mkleehammer/pyodbc/wiki/Features-beyond-the-DB-API#fast_executemany>`_
119 - on github
122""" # noqa
124import datetime
125import decimal
126import re
127import struct
129from .base import BINARY
130from .base import DATETIMEOFFSET
131from .base import MSDialect
132from .base import MSExecutionContext
133from .base import VARBINARY
134from ... import exc
135from ... import types as sqltypes
136from ... import util
137from ...connectors.pyodbc import PyODBCConnector
140class _ms_numeric_pyodbc(object):
142 """Turns Decimals with adjusted() < 0 or > 7 into strings.
144 The routines here are needed for older pyodbc versions
145 as well as current mxODBC versions.
147 """
149 def bind_processor(self, dialect):
151 super_process = super(_ms_numeric_pyodbc, self).bind_processor(dialect)
153 if not dialect._need_decimal_fix:
154 return super_process
156 def process(value):
157 if self.asdecimal and isinstance(value, decimal.Decimal):
158 adjusted = value.adjusted()
159 if adjusted < 0:
160 return self._small_dec_to_string(value)
161 elif adjusted > 7:
162 return self._large_dec_to_string(value)
164 if super_process:
165 return super_process(value)
166 else:
167 return value
169 return process
171 # these routines needed for older versions of pyodbc.
172 # as of 2.1.8 this logic is integrated.
174 def _small_dec_to_string(self, value):
175 return "%s0.%s%s" % (
176 (value < 0 and "-" or ""),
177 "0" * (abs(value.adjusted()) - 1),
178 "".join([str(nint) for nint in value.as_tuple()[1]]),
179 )
181 def _large_dec_to_string(self, value):
182 _int = value.as_tuple()[1]
183 if "E" in str(value):
184 result = "%s%s%s" % (
185 (value < 0 and "-" or ""),
186 "".join([str(s) for s in _int]),
187 "0" * (value.adjusted() - (len(_int) - 1)),
188 )
189 else:
190 if (len(_int) - 1) > value.adjusted():
191 result = "%s%s.%s" % (
192 (value < 0 and "-" or ""),
193 "".join([str(s) for s in _int][0 : value.adjusted() + 1]),
194 "".join([str(s) for s in _int][value.adjusted() + 1 :]),
195 )
196 else:
197 result = "%s%s" % (
198 (value < 0 and "-" or ""),
199 "".join([str(s) for s in _int][0 : value.adjusted() + 1]),
200 )
201 return result
204class _MSNumeric_pyodbc(_ms_numeric_pyodbc, sqltypes.Numeric):
205 pass
208class _MSFloat_pyodbc(_ms_numeric_pyodbc, sqltypes.Float):
209 pass
212class _ms_binary_pyodbc(object):
213 """Wraps binary values in dialect-specific Binary wrapper.
214 If the value is null, return a pyodbc-specific BinaryNull
215 object to prevent pyODBC [and FreeTDS] from defaulting binary
216 NULL types to SQLWCHAR and causing implicit conversion errors.
217 """
219 def bind_processor(self, dialect):
220 if dialect.dbapi is None:
221 return None
223 DBAPIBinary = dialect.dbapi.Binary
225 def process(value):
226 if value is not None:
227 return DBAPIBinary(value)
228 else:
229 # pyodbc-specific
230 return dialect.dbapi.BinaryNull
232 return process
235class _ODBCDateTimeOffset(DATETIMEOFFSET):
236 def bind_processor(self, dialect):
237 def process(value):
238 if value is None:
239 return None
240 elif isinstance(value, util.string_types):
241 # if a string was passed directly, allow it through
242 return value
243 else:
244 # Convert to string format required by T-SQL
245 dto_string = value.strftime("%Y-%m-%d %H:%M:%S.%f %z")
246 # offset needs a colon, e.g., -0700 -> -07:00
247 # "UTC offset in the form (+-)HHMM[SS[.ffffff]]"
248 # backend currently rejects seconds / fractional seconds
249 dto_string = re.sub(
250 r"([\+\-]\d{2})([\d\.]+)$", r"\1:\2", dto_string
251 )
252 return dto_string
254 return process
257class _VARBINARY_pyodbc(_ms_binary_pyodbc, VARBINARY):
258 pass
261class _BINARY_pyodbc(_ms_binary_pyodbc, BINARY):
262 pass
265class MSExecutionContext_pyodbc(MSExecutionContext):
266 _embedded_scope_identity = False
268 def pre_exec(self):
269 """where appropriate, issue "select scope_identity()" in the same
270 statement.
272 Background on why "scope_identity()" is preferable to "@@identity":
273 http://msdn.microsoft.com/en-us/library/ms190315.aspx
275 Background on why we attempt to embed "scope_identity()" into the same
276 statement as the INSERT:
277 http://code.google.com/p/pyodbc/wiki/FAQs#How_do_I_retrieve_autogenerated/identity_values?
279 """
281 super(MSExecutionContext_pyodbc, self).pre_exec()
283 # don't embed the scope_identity select into an
284 # "INSERT .. DEFAULT VALUES"
285 if (
286 self._select_lastrowid
287 and self.dialect.use_scope_identity
288 and len(self.parameters[0])
289 ):
290 self._embedded_scope_identity = True
292 self.statement += "; select scope_identity()"
294 def post_exec(self):
295 if self._embedded_scope_identity:
296 # Fetch the last inserted id from the manipulated statement
297 # We may have to skip over a number of result sets with
298 # no data (due to triggers, etc.)
299 while True:
300 try:
301 # fetchall() ensures the cursor is consumed
302 # without closing it (FreeTDS particularly)
303 row = self.cursor.fetchall()[0]
304 break
305 except self.dialect.dbapi.Error:
306 # no way around this - nextset() consumes the previous set
307 # so we need to just keep flipping
308 self.cursor.nextset()
310 self._lastrowid = int(row[0])
311 else:
312 super(MSExecutionContext_pyodbc, self).post_exec()
315class MSDialect_pyodbc(PyODBCConnector, MSDialect):
317 # mssql still has problems with this on Linux
318 supports_sane_rowcount_returning = False
320 execution_ctx_cls = MSExecutionContext_pyodbc
322 colspecs = util.update_copy(
323 MSDialect.colspecs,
324 {
325 sqltypes.Numeric: _MSNumeric_pyodbc,
326 sqltypes.Float: _MSFloat_pyodbc,
327 BINARY: _BINARY_pyodbc,
328 DATETIMEOFFSET: _ODBCDateTimeOffset,
329 # SQL Server dialect has a VARBINARY that is just to support
330 # "deprecate_large_types" w/ VARBINARY(max), but also we must
331 # handle the usual SQL standard VARBINARY
332 VARBINARY: _VARBINARY_pyodbc,
333 sqltypes.VARBINARY: _VARBINARY_pyodbc,
334 sqltypes.LargeBinary: _VARBINARY_pyodbc,
335 },
336 )
338 def __init__(
339 self, description_encoding=None, fast_executemany=False, **params
340 ):
341 if "description_encoding" in params:
342 self.description_encoding = params.pop("description_encoding")
343 super(MSDialect_pyodbc, self).__init__(**params)
344 self.use_scope_identity = (
345 self.use_scope_identity
346 and self.dbapi
347 and hasattr(self.dbapi.Cursor, "nextset")
348 )
349 self._need_decimal_fix = self.dbapi and self._dbapi_version() < (
350 2,
351 1,
352 8,
353 )
354 self.fast_executemany = fast_executemany
356 def _get_server_version_info(self, connection):
357 try:
358 # "Version of the instance of SQL Server, in the form
359 # of 'major.minor.build.revision'"
360 raw = connection.scalar(
361 "SELECT CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR)"
362 )
363 except exc.DBAPIError:
364 # SQL Server docs indicate this function isn't present prior to
365 # 2008. Before we had the VARCHAR cast above, pyodbc would also
366 # fail on this query.
367 return super(MSDialect_pyodbc, self)._get_server_version_info(
368 connection, allow_chars=False
369 )
370 else:
371 version = []
372 r = re.compile(r"[.\-]")
373 for n in r.split(raw):
374 try:
375 version.append(int(n))
376 except ValueError:
377 pass
378 return tuple(version)
380 def on_connect(self):
381 super_ = super(MSDialect_pyodbc, self).on_connect()
383 def on_connect(conn):
384 if super_ is not None:
385 super_(conn)
387 self._setup_timestampoffset_type(conn)
389 return on_connect
391 def _setup_timestampoffset_type(self, connection):
392 # output converter function for datetimeoffset
393 def _handle_datetimeoffset(dto_value):
394 tup = struct.unpack("<6hI2h", dto_value)
395 return datetime.datetime(
396 tup[0],
397 tup[1],
398 tup[2],
399 tup[3],
400 tup[4],
401 tup[5],
402 tup[6] // 1000,
403 util.timezone(
404 datetime.timedelta(hours=tup[7], minutes=tup[8])
405 ),
406 )
408 odbc_SQL_SS_TIMESTAMPOFFSET = -155 # as defined in SQLNCLI.h
409 connection.add_output_converter(
410 odbc_SQL_SS_TIMESTAMPOFFSET, _handle_datetimeoffset
411 )
413 def do_executemany(self, cursor, statement, parameters, context=None):
414 if self.fast_executemany:
415 cursor.fast_executemany = True
416 super(MSDialect_pyodbc, self).do_executemany(
417 cursor, statement, parameters, context=context
418 )
420 def is_disconnect(self, e, connection, cursor):
421 if isinstance(e, self.dbapi.Error):
422 code = e.args[0]
423 if code in (
424 "08S01",
425 "01002",
426 "08003",
427 "08007",
428 "08S02",
429 "08001",
430 "HYT00",
431 "HY010",
432 "10054",
433 ):
434 return True
435 return super(MSDialect_pyodbc, self).is_disconnect(
436 e, connection, cursor
437 )
440dialect = MSDialect_pyodbc