Hide keyboard shortcuts

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 

7 

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/ 

14 

15Connecting to PyODBC 

16-------------------- 

17 

18The URL here is to be translated to PyODBC connection strings, as 

19detailed in `ConnectionStrings <https://code.google.com/p/pyodbc/wiki/ConnectionStrings>`_. 

20 

21DSN Connections 

22^^^^^^^^^^^^^^^ 

23 

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:: 

29 

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

31 

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

33 

34 dsn=mydsn;UID=user;PWD=pass 

35 

36If the username and password are omitted, the DSN form will also add 

37the ``Trusted_Connection=yes`` directive to the ODBC string. 

38 

39Hostname Connections 

40^^^^^^^^^^^^^^^^^^^^ 

41 

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. 

46 

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:: 

50 

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

52 

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. 

58 

59Pass through exact Pyodbc string 

60^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ 

61 

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``:: 

67 

68 import urllib 

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

70 

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

72 

73 

74Driver / Unicode Support 

75------------------------- 

76 

77PyODBC works best with Microsoft ODBC drivers, particularly in the area 

78of Unicode support on both Python 2 and Python 3. 

79 

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. 

86 

87 

88Rowcount Support 

89---------------- 

90 

91Pyodbc only has partial support for rowcount. See the notes at 

92:ref:`mssql_rowcount_versioning` for important notes when using ORM 

93versioning. 

94 

95.. _mssql_pyodbc_fastexecutemany: 

96 

97Fast Executemany Mode 

98--------------------- 

99 

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:: 

109 

110 engine = create_engine( 

111 "mssql+pyodbc://scott:tiger@mssql2017:1433/test?driver=ODBC+Driver+13+for+SQL+Server", 

112 fast_executemany=True) 

113 

114.. versionadded:: 1.3 

115 

116.. seealso:: 

117 

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

119 - on github 

120 

121 

122""" # noqa 

123 

124import datetime 

125import decimal 

126import re 

127import struct 

128 

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 

138 

139 

140class _ms_numeric_pyodbc(object): 

141 

142 """Turns Decimals with adjusted() < 0 or > 7 into strings. 

143 

144 The routines here are needed for older pyodbc versions 

145 as well as current mxODBC versions. 

146 

147 """ 

148 

149 def bind_processor(self, dialect): 

150 

151 super_process = super(_ms_numeric_pyodbc, self).bind_processor(dialect) 

152 

153 if not dialect._need_decimal_fix: 

154 return super_process 

155 

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) 

163 

164 if super_process: 

165 return super_process(value) 

166 else: 

167 return value 

168 

169 return process 

170 

171 # these routines needed for older versions of pyodbc. 

172 # as of 2.1.8 this logic is integrated. 

173 

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 ) 

180 

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 

202 

203 

204class _MSNumeric_pyodbc(_ms_numeric_pyodbc, sqltypes.Numeric): 

205 pass 

206 

207 

208class _MSFloat_pyodbc(_ms_numeric_pyodbc, sqltypes.Float): 

209 pass 

210 

211 

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 """ 

218 

219 def bind_processor(self, dialect): 

220 if dialect.dbapi is None: 

221 return None 

222 

223 DBAPIBinary = dialect.dbapi.Binary 

224 

225 def process(value): 

226 if value is not None: 

227 return DBAPIBinary(value) 

228 else: 

229 # pyodbc-specific 

230 return dialect.dbapi.BinaryNull 

231 

232 return process 

233 

234 

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 

253 

254 return process 

255 

256 

257class _VARBINARY_pyodbc(_ms_binary_pyodbc, VARBINARY): 

258 pass 

259 

260 

261class _BINARY_pyodbc(_ms_binary_pyodbc, BINARY): 

262 pass 

263 

264 

265class MSExecutionContext_pyodbc(MSExecutionContext): 

266 _embedded_scope_identity = False 

267 

268 def pre_exec(self): 

269 """where appropriate, issue "select scope_identity()" in the same 

270 statement. 

271 

272 Background on why "scope_identity()" is preferable to "@@identity": 

273 http://msdn.microsoft.com/en-us/library/ms190315.aspx 

274 

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? 

278 

279 """ 

280 

281 super(MSExecutionContext_pyodbc, self).pre_exec() 

282 

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 

291 

292 self.statement += "; select scope_identity()" 

293 

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() 

309 

310 self._lastrowid = int(row[0]) 

311 else: 

312 super(MSExecutionContext_pyodbc, self).post_exec() 

313 

314 

315class MSDialect_pyodbc(PyODBCConnector, MSDialect): 

316 

317 # mssql still has problems with this on Linux 

318 supports_sane_rowcount_returning = False 

319 

320 execution_ctx_cls = MSExecutionContext_pyodbc 

321 

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 ) 

337 

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 

355 

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) 

379 

380 def on_connect(self): 

381 super_ = super(MSDialect_pyodbc, self).on_connect() 

382 

383 def on_connect(conn): 

384 if super_ is not None: 

385 super_(conn) 

386 

387 self._setup_timestampoffset_type(conn) 

388 

389 return on_connect 

390 

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 ) 

407 

408 odbc_SQL_SS_TIMESTAMPOFFSET = -155 # as defined in SQLNCLI.h 

409 connection.add_output_converter( 

410 odbc_SQL_SS_TIMESTAMPOFFSET, _handle_datetimeoffset 

411 ) 

412 

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 ) 

419 

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 ) 

438 

439 

440dialect = MSDialect_pyodbc