connections.py 12 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343
  1. """
  2. This module implements connections for MySQLdb. Presently there is
  3. only one class: Connection. Others are unlikely. However, you might
  4. want to make your own subclasses. In most cases, you will probably
  5. override Connection.default_cursor with a non-standard Cursor class.
  6. """
  7. import re
  8. from . import cursors, _mysql
  9. from ._exceptions import (
  10. Warning,
  11. Error,
  12. InterfaceError,
  13. DataError,
  14. DatabaseError,
  15. OperationalError,
  16. IntegrityError,
  17. InternalError,
  18. NotSupportedError,
  19. ProgrammingError,
  20. )
  21. # Mapping from MySQL charset name to Python codec name
  22. _charset_to_encoding = {
  23. "utf8mb4": "utf8",
  24. "utf8mb3": "utf8",
  25. "latin1": "cp1252",
  26. "koi8r": "koi8_r",
  27. "koi8u": "koi8_u",
  28. }
  29. re_numeric_part = re.compile(r"^(\d+)")
  30. def numeric_part(s):
  31. """Returns the leading numeric part of a string.
  32. >>> numeric_part("20-alpha")
  33. 20
  34. >>> numeric_part("foo")
  35. >>> numeric_part("16b")
  36. 16
  37. """
  38. m = re_numeric_part.match(s)
  39. if m:
  40. return int(m.group(1))
  41. return None
  42. class Connection(_mysql.connection):
  43. """MySQL Database Connection Object"""
  44. default_cursor = cursors.Cursor
  45. def __init__(self, *args, **kwargs):
  46. """
  47. Create a connection to the database. It is strongly recommended
  48. that you only use keyword parameters. Consult the MySQL C API
  49. documentation for more information.
  50. :param str host: host to connect
  51. :param str user: user to connect as
  52. :param str password: password to use
  53. :param str passwd: alias of password (deprecated)
  54. :param str database: database to use
  55. :param str db: alias of database (deprecated)
  56. :param int port: TCP/IP port to connect to
  57. :param str unix_socket: location of unix_socket to use
  58. :param dict conv: conversion dictionary, see MySQLdb.converters
  59. :param int connect_timeout:
  60. number of seconds to wait before the connection attempt fails.
  61. :param bool compress: if set, compression is enabled
  62. :param str named_pipe: if set, a named pipe is used to connect (Windows only)
  63. :param str init_command:
  64. command which is run once the connection is created
  65. :param str read_default_file:
  66. file from which default client values are read
  67. :param str read_default_group:
  68. configuration group to use from the default file
  69. :param type cursorclass:
  70. class object, used to create cursors (keyword only)
  71. :param bool use_unicode:
  72. If True, text-like columns are returned as unicode objects
  73. using the connection's character set. Otherwise, text-like
  74. columns are returned as bytes. Unicode objects will always
  75. be encoded to the connection's character set regardless of
  76. this setting.
  77. Default to True.
  78. :param str charset:
  79. If supplied, the connection character set will be changed
  80. to this character set.
  81. :param str collation:
  82. If ``charset`` and ``collation`` are both supplied, the
  83. character set and collation for the current connection
  84. will be set.
  85. If omitted, empty string, or None, the default collation
  86. for the ``charset`` is implied.
  87. :param str auth_plugin:
  88. If supplied, the connection default authentication plugin will be
  89. changed to this value. Example values:
  90. `mysql_native_password` or `caching_sha2_password`
  91. :param str sql_mode:
  92. If supplied, the session SQL mode will be changed to this
  93. setting.
  94. For more details and legal values, see the MySQL documentation.
  95. :param int client_flag:
  96. flags to use or 0 (see MySQL docs or constants/CLIENTS.py)
  97. :param bool multi_statements:
  98. If True, enable multi statements for clients >= 4.1.
  99. Defaults to True.
  100. :param str ssl_mode:
  101. specify the security settings for connection to the server;
  102. see the MySQL documentation for more details
  103. (mysql_option(), MYSQL_OPT_SSL_MODE).
  104. Only one of 'DISABLED', 'PREFERRED', 'REQUIRED',
  105. 'VERIFY_CA', 'VERIFY_IDENTITY' can be specified.
  106. :param dict ssl:
  107. dictionary or mapping contains SSL connection parameters;
  108. see the MySQL documentation for more details
  109. (mysql_ssl_set()). If this is set, and the client does not
  110. support SSL, NotSupportedError will be raised.
  111. Since mysqlclient 2.2.4, ssl=True is alias of ssl_mode=REQUIRED
  112. for better compatibility with PyMySQL and MariaDB.
  113. :param bool local_infile:
  114. enables LOAD LOCAL INFILE; zero disables
  115. :param bool autocommit:
  116. If False (default), autocommit is disabled.
  117. If True, autocommit is enabled.
  118. If None, autocommit isn't set and server default is used.
  119. :param bool binary_prefix:
  120. If set, the '_binary' prefix will be used for raw byte query
  121. arguments (e.g. Binary). This is disabled by default.
  122. There are a number of undocumented, non-standard methods. See the
  123. documentation for the MySQL C API for some hints on what they do.
  124. """
  125. from MySQLdb.constants import CLIENT, FIELD_TYPE
  126. from MySQLdb.converters import conversions, _bytes_or_str
  127. kwargs2 = kwargs.copy()
  128. if "db" in kwargs2:
  129. kwargs2["database"] = kwargs2.pop("db")
  130. if "passwd" in kwargs2:
  131. kwargs2["password"] = kwargs2.pop("passwd")
  132. if "conv" in kwargs:
  133. conv = kwargs["conv"]
  134. else:
  135. conv = conversions
  136. conv2 = {}
  137. for k, v in conv.items():
  138. if isinstance(k, int) and isinstance(v, list):
  139. conv2[k] = v[:]
  140. else:
  141. conv2[k] = v
  142. kwargs2["conv"] = conv2
  143. cursorclass = kwargs2.pop("cursorclass", self.default_cursor)
  144. charset = kwargs2.get("charset", "")
  145. collation = kwargs2.pop("collation", "")
  146. use_unicode = kwargs2.pop("use_unicode", True)
  147. sql_mode = kwargs2.pop("sql_mode", "")
  148. self._binary_prefix = kwargs2.pop("binary_prefix", False)
  149. client_flag = kwargs.get("client_flag", 0)
  150. client_flag |= CLIENT.MULTI_RESULTS
  151. multi_statements = kwargs2.pop("multi_statements", True)
  152. if multi_statements:
  153. client_flag |= CLIENT.MULTI_STATEMENTS
  154. kwargs2["client_flag"] = client_flag
  155. # PEP-249 requires autocommit to be initially off
  156. autocommit = kwargs2.pop("autocommit", False)
  157. super().__init__(*args, **kwargs2)
  158. self.cursorclass = cursorclass
  159. self.encoders = {
  160. k: v
  161. for k, v in conv.items()
  162. if type(k) is not int # noqa: E721
  163. }
  164. self._server_version = tuple(
  165. [numeric_part(n) for n in self.get_server_info().split(".")[:2]]
  166. )
  167. self.encoding = "ascii" # overridden in set_character_set()
  168. if not charset:
  169. charset = self.character_set_name()
  170. self.set_character_set(charset, collation)
  171. if sql_mode:
  172. self.set_sql_mode(sql_mode)
  173. if use_unicode:
  174. for t in (
  175. FIELD_TYPE.STRING,
  176. FIELD_TYPE.VAR_STRING,
  177. FIELD_TYPE.VARCHAR,
  178. FIELD_TYPE.TINY_BLOB,
  179. FIELD_TYPE.MEDIUM_BLOB,
  180. FIELD_TYPE.LONG_BLOB,
  181. FIELD_TYPE.BLOB,
  182. ):
  183. self.converter[t] = _bytes_or_str
  184. # Unlike other string/blob types, JSON is always text.
  185. # MySQL may return JSON with charset==binary.
  186. self.converter[FIELD_TYPE.JSON] = str
  187. self._transactional = self.server_capabilities & CLIENT.TRANSACTIONS
  188. if self._transactional:
  189. if autocommit is not None:
  190. self.autocommit(autocommit)
  191. self.messages = []
  192. def __enter__(self):
  193. return self
  194. def __exit__(self, exc_type, exc_value, traceback):
  195. self.close()
  196. def autocommit(self, on):
  197. on = bool(on)
  198. if self.get_autocommit() != on:
  199. _mysql.connection.autocommit(self, on)
  200. def cursor(self, cursorclass=None):
  201. """
  202. Create a cursor on which queries may be performed. The
  203. optional cursorclass parameter is used to create the
  204. Cursor. By default, self.cursorclass=cursors.Cursor is
  205. used.
  206. """
  207. return (cursorclass or self.cursorclass)(self)
  208. def query(self, query):
  209. # Since _mysql releases GIL while querying, we need immutable buffer.
  210. if isinstance(query, bytearray):
  211. query = bytes(query)
  212. _mysql.connection.query(self, query)
  213. def _bytes_literal(self, bs):
  214. assert isinstance(bs, (bytes, bytearray))
  215. x = self.string_literal(bs) # x is escaped and quoted bytes
  216. if self._binary_prefix:
  217. return b"_binary" + x
  218. return x
  219. def _tuple_literal(self, t):
  220. return b"(%s)" % (b",".join(map(self.literal, t)))
  221. def literal(self, o):
  222. """If o is a single object, returns an SQL literal as a string.
  223. If o is a non-string sequence, the items of the sequence are
  224. converted and returned as a sequence.
  225. Non-standard. For internal use; do not use this in your
  226. applications.
  227. """
  228. if isinstance(o, str):
  229. s = self.string_literal(o.encode(self.encoding))
  230. elif isinstance(o, bytearray):
  231. s = self._bytes_literal(o)
  232. elif isinstance(o, bytes):
  233. s = self._bytes_literal(o)
  234. elif isinstance(o, (tuple, list)):
  235. s = self._tuple_literal(o)
  236. else:
  237. s = self.escape(o, self.encoders)
  238. if isinstance(s, str):
  239. s = s.encode(self.encoding)
  240. assert isinstance(s, bytes)
  241. return s
  242. def begin(self):
  243. """Explicitly begin a connection.
  244. This method is not used when autocommit=False (default).
  245. """
  246. self.query(b"BEGIN")
  247. def set_character_set(self, charset, collation=None):
  248. """Set the connection character set to charset."""
  249. super().set_character_set(charset)
  250. self.encoding = _charset_to_encoding.get(charset, charset)
  251. if collation:
  252. self.query(f"SET NAMES {charset} COLLATE {collation}")
  253. self.store_result()
  254. def set_sql_mode(self, sql_mode):
  255. """Set the connection sql_mode. See MySQL documentation for
  256. legal values."""
  257. if self._server_version < (4, 1):
  258. raise NotSupportedError("server is too old to set sql_mode")
  259. self.query("SET SESSION sql_mode='%s'" % sql_mode)
  260. self.store_result()
  261. def show_warnings(self):
  262. """Return detailed information about warnings as a
  263. sequence of tuples of (Level, Code, Message). This
  264. is only supported in MySQL-4.1 and up. If your server
  265. is an earlier version, an empty sequence is returned."""
  266. if self._server_version < (4, 1):
  267. return ()
  268. self.query("SHOW WARNINGS")
  269. r = self.store_result()
  270. warnings = r.fetch_row(0)
  271. return warnings
  272. Warning = Warning
  273. Error = Error
  274. InterfaceError = InterfaceError
  275. DatabaseError = DatabaseError
  276. DataError = DataError
  277. OperationalError = OperationalError
  278. IntegrityError = IntegrityError
  279. InternalError = InternalError
  280. ProgrammingError = ProgrammingError
  281. NotSupportedError = NotSupportedError
  282. # vim: colorcolumn=100