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/pymssql.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 

8""" 

9.. dialect:: mssql+pymssql 

10 :name: pymssql 

11 :dbapi: pymssql 

12 :connectstring: mssql+pymssql://<username>:<password>@<freetds_name>/?charset=utf8 

13 

14pymssql is a Python module that provides a Python DBAPI interface around 

15`FreeTDS <http://www.freetds.org/>`_. 

16 

17.. note:: 

18 

19 pymssql is currently not included in SQLAlchemy's continuous integration 

20 (CI) testing. 

21 

22Modern versions of this driver worked very well with SQL Server and FreeTDS 

23from Linux and were highly recommended. However, pymssql is currently 

24unmaintained and has fallen behind the progress of the Microsoft ODBC driver in 

25its support for newer features of SQL Server. The latest official release of 

26pymssql at the time of this document is version 2.1.4 (August, 2018) and it 

27lacks support for: 

28 

291. table-valued parameters (TVPs), 

302. ``datetimeoffset`` columns using timezone-aware ``datetime`` objects 

31 (values are sent and retrieved as strings), and 

323. encrypted connections (e.g., to Azure SQL), when pymssql is installed from 

33 the pre-built wheels. Support for encrypted connections requires building 

34 pymssql from source, which can be a nuisance, especially under Windows. 

35 

36The above features are all supported by mssql+pyodbc when using Microsoft's 

37ODBC Driver for SQL Server (msodbcsql), which is now available for Windows, 

38(several flavors of) Linux, and macOS. 

39 

40 

41""" # noqa 

42import re 

43 

44from .base import MSDialect 

45from .base import MSIdentifierPreparer 

46from ... import processors 

47from ... import types as sqltypes 

48from ... import util 

49 

50 

51class _MSNumeric_pymssql(sqltypes.Numeric): 

52 def result_processor(self, dialect, type_): 

53 if not self.asdecimal: 

54 return processors.to_float 

55 else: 

56 return sqltypes.Numeric.result_processor(self, dialect, type_) 

57 

58 

59class MSIdentifierPreparer_pymssql(MSIdentifierPreparer): 

60 def __init__(self, dialect): 

61 super(MSIdentifierPreparer_pymssql, self).__init__(dialect) 

62 # pymssql has the very unusual behavior that it uses pyformat 

63 # yet does not require that percent signs be doubled 

64 self._double_percents = False 

65 

66 

67class MSDialect_pymssql(MSDialect): 

68 supports_native_decimal = True 

69 driver = "pymssql" 

70 

71 preparer = MSIdentifierPreparer_pymssql 

72 

73 colspecs = util.update_copy( 

74 MSDialect.colspecs, 

75 {sqltypes.Numeric: _MSNumeric_pymssql, sqltypes.Float: sqltypes.Float}, 

76 ) 

77 

78 @classmethod 

79 def dbapi(cls): 

80 module = __import__("pymssql") 

81 # pymmsql < 2.1.1 doesn't have a Binary method. we use string 

82 client_ver = tuple(int(x) for x in module.__version__.split(".")) 

83 if client_ver < (2, 1, 1): 

84 # TODO: monkeypatching here is less than ideal 

85 module.Binary = lambda x: x if hasattr(x, "decode") else str(x) 

86 

87 if client_ver < (1,): 

88 util.warn( 

89 "The pymssql dialect expects at least " 

90 "the 1.0 series of the pymssql DBAPI." 

91 ) 

92 return module 

93 

94 def _get_server_version_info(self, connection): 

95 vers = connection.scalar("select @@version") 

96 m = re.match(r"Microsoft .*? - (\d+).(\d+).(\d+).(\d+)", vers) 

97 if m: 

98 return tuple(int(x) for x in m.group(1, 2, 3, 4)) 

99 else: 

100 return None 

101 

102 def create_connect_args(self, url): 

103 opts = url.translate_connect_args(username="user") 

104 opts.update(url.query) 

105 port = opts.pop("port", None) 

106 if port and "host" in opts: 

107 opts["host"] = "%s:%s" % (opts["host"], port) 

108 return [[], opts] 

109 

110 def is_disconnect(self, e, connection, cursor): 

111 for msg in ( 

112 "Adaptive Server connection timed out", 

113 "Net-Lib error during Connection reset by peer", 

114 "message 20003", # connection timeout 

115 "Error 10054", 

116 "Not connected to any MS SQL server", 

117 "Connection is closed", 

118 "message 20006", # Write to the server failed 

119 "message 20017", # Unexpected EOF from the server 

120 "message 20047", # DBPROCESS is dead or not enabled 

121 ): 

122 if msg in str(e): 

123 return True 

124 else: 

125 return False 

126 

127 def set_isolation_level(self, connection, level): 

128 if level == "AUTOCOMMIT": 

129 connection.autocommit(True) 

130 else: 

131 connection.autocommit(False) 

132 super(MSDialect_pymssql, self).set_isolation_level( 

133 connection, level 

134 ) 

135 

136 

137dialect = MSDialect_pymssql