Coverage for /pythoncovmergedfiles/medio/medio/usr/local/lib/python3.11/site-packages/pymysql/connections.py: 26%
Shortcuts on this page
r m x toggle line displays
j k next/prev highlighted chunk
0 (zero) top of page
1 (one) first highlighted chunk
Shortcuts on this page
r m x toggle line displays
j k next/prev highlighted chunk
0 (zero) top of page
1 (one) first highlighted chunk
1# Python implementation of the MySQL client-server protocol
2# http://dev.mysql.com/doc/internals/en/client-server-protocol.html
3# Error codes:
4# https://dev.mysql.com/doc/refman/5.5/en/error-handling.html
5from __future__ import annotations
7import contextlib
8import errno
9import os
10import socket
11import struct
12import sys
13import traceback
14import warnings
16from . import VERSION_STRING, _auth, converters, err
17from .charset import charset_by_id, charset_by_name
18from .constants import CLIENT, COMMAND, CR, ER, FIELD_TYPE, SERVER_STATUS
19from .cursors import Cursor
20from .optionfile import Parser
21from .protocol import (
22 EOFPacketWrapper,
23 FieldDescriptorPacket,
24 LoadLocalPacketWrapper,
25 MysqlPacket,
26 OKPacketWrapper,
27 dump_packet,
28)
30try:
31 import ssl
33 SSL_ENABLED = True
34except ImportError:
35 ssl = None
36 SSL_ENABLED = False
38try:
39 import getpass
41 DEFAULT_USER = getpass.getuser()
42 del getpass
43except (ImportError, KeyError, OSError):
44 # When there's no entry in OS database for a current user:
45 # KeyError is raised in Python 3.12 and below.
46 # OSError is raised in Python 3.13+
47 DEFAULT_USER = None
49DEBUG = False
50_DEFAULT_AUTH_PLUGIN = None # if this is not None, use it instead of server's default.
52TEXT_TYPES = {
53 FIELD_TYPE.BIT,
54 FIELD_TYPE.BLOB,
55 FIELD_TYPE.LONG_BLOB,
56 FIELD_TYPE.MEDIUM_BLOB,
57 FIELD_TYPE.STRING,
58 FIELD_TYPE.TINY_BLOB,
59 FIELD_TYPE.VAR_STRING,
60 FIELD_TYPE.VARCHAR,
61 FIELD_TYPE.GEOMETRY,
62}
65DEFAULT_CHARSET = "utf8mb4"
67MAX_PACKET_LEN = 2**24 - 1
70def _pack_int24(n):
71 return struct.pack("<I", n)[:3]
74# https://dev.mysql.com/doc/internals/en/integer.html#packet-Protocol::LengthEncodedInteger
75def _lenenc_int(i):
76 if i < 0:
77 raise ValueError(
78 "Encoding %d is less than 0 - no representation in LengthEncodedInteger" % i
79 )
80 elif i < 0xFB:
81 return bytes([i])
82 elif i < (1 << 16):
83 return b"\xfc" + struct.pack("<H", i)
84 elif i < (1 << 24):
85 return b"\xfd" + struct.pack("<I", i)[:3]
86 elif i < (1 << 64):
87 return b"\xfe" + struct.pack("<Q", i)
88 else:
89 raise ValueError(
90 f"Encoding {i:x} is larger than {1 << 64:x} - no representation in LengthEncodedInteger"
91 )
94class Connection:
95 """
96 Representation of a socket with a mysql server.
98 The proper way to get an instance of this class is to call
99 connect().
101 Establish a connection to the MySQL database. Accepts several
102 arguments:
104 :param host: Host where the database server is located.
105 :param user: Username to log in as.
106 :param password: Password to use.
107 :param database: Database to use, None to not use a particular one.
108 :param port: MySQL port to use, default is usually OK. (default: 3306)
109 :param bind_address: When the client has multiple network interfaces, specify
110 the interface from which to connect to the host. Argument can be
111 a hostname or an IP address.
112 :param unix_socket: Use a unix socket rather than TCP/IP.
113 :param read_timeout: The timeout for reading from the connection in seconds.
114 (default: None - no timeout)
115 :param write_timeout: The timeout for writing to the connection in seconds.
116 (default: None - no timeout)
117 :param str charset: Charset to use. "utf8" (or "utf8mb4") is recommended.
118 legacy multibyte encodings may pose security risks.
119 Do not use such encodings for public-facing systems.
120 :param str collation: Collation name to use.
121 :param sql_mode: Default SQL_MODE to use.
122 :param read_default_file:
123 Specifies my.cnf file to read these parameters from under the [client] section.
124 :param conv:
125 Conversion dictionary to use instead of the default one.
126 This is used to provide custom marshalling and unmarshalling of types.
127 See converters.
128 :param use_unicode:
129 Whether or not to default to unicode strings.
130 This option defaults to true.
131 :param client_flag: Custom flags to send to MySQL. Find potential values in constants.CLIENT.
132 :param cursorclass: Custom cursor class to use.
133 :param init_command: Initial SQL statement to run when connection is established.
134 :param connect_timeout: The timeout for connecting to the database in seconds.
135 (default: 10, min: 1, max: 31536000)
136 :param ssl: An ssl.SSLContext, or a dict of arguments similar to mysql_ssl_set()'s parameters.
137 Passing a dict is deprecated; use the individual ``ssl_*`` parameters or an
138 ``ssl.SSLContext`` instead.
139 :param ssl_ca: Path to the file that contains a PEM-formatted CA certificate.
140 :param ssl_cert: Path to the file that contains a PEM-formatted client certificate.
141 :param ssl_disabled: A boolean value that disables usage of TLS. Unlike other SSL options,
142 setting this to True explicitly prohibits the use of TLS, even if the server supports it.
143 :param ssl_key: Path to the file that contains a PEM-formatted private key for
144 the client certificate.
145 :param ssl_key_password: The password for the client certificate private key.
146 :param ssl_verify_cert: Set to true to check the server certificate's validity.
147 :param ssl_verify_identity: Set to true to check the server's identity.
148 :param read_default_group: Group to read from in the configuration file.
149 :param autocommit: Autocommit mode. None means use server default. (default: False)
150 :param local_infile: Boolean to enable the use of LOAD DATA LOCAL command. (default: False)
151 :param max_allowed_packet: Max size of packet sent to server in bytes. (default: 16MB)
152 Only used to limit size of "LOAD LOCAL INFILE" data packet smaller than default (16KB).
153 :param defer_connect: Don't explicitly connect on construction - wait for connect call.
154 (default: False)
155 :param auth_plugin_map: A dict of plugin names to a class that processes that plugin.
156 The class will take the Connection object as the argument to the constructor.
157 The class needs an authenticate method taking an authentication packet as
158 an argument. For the dialog plugin, a prompt(echo, prompt) method can be used
159 (if no authenticate method) for returning a string from the user. (experimental)
160 :param server_public_key: SHA256 authentication plugin public key value. (default: None)
161 :param binary_prefix: **DEPRECATED**
162 :param compress: Not supported.
163 :param named_pipe: Not supported.
164 :param db: **DEPRECATED** Alias for database.
165 :param passwd: **DEPRECATED** Alias for password.
167 See `Connection <https://www.python.org/dev/peps/pep-0249/#connection-objects>`_ in the
168 specification.
169 """
171 _sock = None
172 _rfile = None
173 _auth_plugin_name = ""
174 _closed = False
175 _secure = False
177 def __init__(
178 self,
179 *,
180 user=None, # The first four arguments is based on DB-API 2.0 recommendation.
181 password="",
182 host=None,
183 database=None,
184 unix_socket=None,
185 port=0,
186 charset="",
187 collation=None,
188 sql_mode=None,
189 read_default_file=None,
190 conv=None,
191 use_unicode=True,
192 client_flag=0,
193 cursorclass=Cursor,
194 init_command=None,
195 connect_timeout=10,
196 read_default_group=None,
197 autocommit=False,
198 local_infile=False,
199 max_allowed_packet=16 * 1024 * 1024,
200 defer_connect=False,
201 auth_plugin_map=None,
202 read_timeout=None,
203 write_timeout=None,
204 bind_address=None,
205 binary_prefix=False,
206 program_name=None,
207 server_public_key=None,
208 ssl=None,
209 ssl_ca=None,
210 ssl_cert=None,
211 ssl_disabled=None,
212 ssl_key=None,
213 ssl_key_password=None,
214 ssl_verify_cert=None,
215 ssl_verify_identity=None,
216 compress=None, # not supported
217 named_pipe=None, # not supported
218 passwd=None, # deprecated
219 db=None, # deprecated
220 ):
221 if db is not None and database is None:
222 warnings.warn("'db' is deprecated, use 'database'", DeprecationWarning, 3)
223 database = db
224 if passwd is not None and not password:
225 warnings.warn(
226 "'passwd' is deprecated, use 'password'", DeprecationWarning, 3
227 )
228 password = passwd
230 if compress or named_pipe:
231 raise NotImplementedError(
232 "compress and named_pipe arguments are not supported"
233 )
235 self._local_infile = bool(local_infile)
236 if self._local_infile:
237 client_flag |= CLIENT.LOCAL_FILES
239 if read_default_group and not read_default_file:
240 if sys.platform.startswith("win"):
241 read_default_file = "c:\\my.ini"
242 else:
243 read_default_file = "/etc/my.cnf"
245 if read_default_file:
246 if not read_default_group:
247 read_default_group = "client"
249 cfg = Parser()
250 cfg.read(os.path.expanduser(read_default_file))
252 def _config(key, arg):
253 if arg:
254 return arg
255 try:
256 return cfg.get(read_default_group, key)
257 except Exception:
258 return arg
260 user = _config("user", user)
261 password = _config("password", password)
262 host = _config("host", host)
263 database = _config("database", database)
264 unix_socket = _config("socket", unix_socket)
265 port = int(_config("port", port))
266 bind_address = _config("bind-address", bind_address)
267 charset = _config("default-character-set", charset)
268 if not ssl:
269 ssl = {}
270 if isinstance(ssl, dict):
271 for key in ["ca", "capath", "cert", "key", "password", "cipher"]:
272 value = _config("ssl-" + key, ssl.get(key))
273 if value:
274 ssl[key] = value
276 self.ssl = False
277 self._ssl_required = False
278 if not ssl_disabled:
279 if ssl_ca or ssl_cert or ssl_key or ssl_verify_cert or ssl_verify_identity:
280 ssl_verify_identity = bool(ssl_verify_identity)
281 if ssl_verify_identity:
282 verify_mode = True
283 elif ssl_verify_cert is not None:
284 verify_mode = ssl_verify_cert
285 else:
286 verify_mode = False
287 ssl = {
288 "ca": ssl_ca,
289 "check_hostname": ssl_verify_identity,
290 "verify_mode": verify_mode,
291 }
292 if ssl_cert is not None:
293 ssl["cert"] = ssl_cert
294 if ssl_key is not None:
295 ssl["key"] = ssl_key
296 if ssl_key_password is not None:
297 ssl["password"] = ssl_key_password
298 if ssl:
299 if not SSL_ENABLED:
300 raise NotImplementedError("ssl module not found")
301 self.ssl = True
302 self._ssl_required = True
303 client_flag |= CLIENT.SSL
304 self.ctx = self._create_ssl_ctx(ssl)
305 elif SSL_ENABLED:
306 # No explicit SSL options specified: use PREFERRED mode.
307 # Attempt SSL but fall back gracefully if the server doesn't support it.
308 self.ssl = True
309 self._ssl_required = False
310 self.ctx = self._create_ssl_ctx({})
312 self.host = host or "localhost"
313 self.port = port or 3306
314 if type(self.port) is not int:
315 raise ValueError("port should be of type int")
316 self.user = user or DEFAULT_USER
317 self.password = password or b""
318 if isinstance(self.password, str):
319 self.password = self.password.encode("latin1")
320 self.db = database
321 self.unix_socket = unix_socket
322 self.bind_address = bind_address
323 if not (0 < connect_timeout <= 31536000):
324 raise ValueError("connect_timeout should be >0 and <=31536000")
325 self.connect_timeout = connect_timeout or None
326 if read_timeout is not None and read_timeout <= 0:
327 raise ValueError("read_timeout should be > 0")
328 self._read_timeout = read_timeout
329 if write_timeout is not None and write_timeout <= 0:
330 raise ValueError("write_timeout should be > 0")
331 self._write_timeout = write_timeout
333 self.charset = charset or DEFAULT_CHARSET
334 self.collation = collation
335 self.use_unicode = use_unicode
337 self.encoding = charset_by_name(self.charset).encoding
339 client_flag |= CLIENT.CAPABILITIES
340 if self.db:
341 client_flag |= CLIENT.CONNECT_WITH_DB
343 self.client_flag = client_flag
345 self.cursorclass = cursorclass
347 self._result = None
348 self._affected_rows = 0
349 self.host_info = "Not connected"
351 # specified autocommit mode. None means use server default.
352 self.autocommit_mode = autocommit
354 if conv is None:
355 conv = converters.conversions
357 # Need for MySQLdb compatibility.
358 self.encoders = {k: v for (k, v) in conv.items() if type(k) is not int}
359 self.decoders = {k: v for (k, v) in conv.items() if type(k) is int}
360 self.sql_mode = sql_mode
361 self.init_command = init_command
362 self.max_allowed_packet = max_allowed_packet
363 self._auth_plugin_map = auth_plugin_map or {}
364 self.server_public_key = server_public_key
366 self._connect_attrs = {
367 "_client_name": "pymysql",
368 "_client_version": VERSION_STRING,
369 "_pid": str(os.getpid()),
370 }
372 if program_name:
373 self._connect_attrs["program_name"] = program_name
375 if defer_connect:
376 self._sock = None
377 else:
378 self.connect()
380 def __enter__(self):
381 return self
383 def __exit__(self, *exc_info):
384 del exc_info
385 self.close()
387 def _create_ssl_ctx(self, sslp):
388 if isinstance(sslp, ssl.SSLContext):
389 return sslp
390 ca = sslp.get("ca")
391 capath = sslp.get("capath")
392 hasnoca = ca is None and capath is None
393 ctx = ssl.create_default_context(cafile=ca, capath=capath)
395 # Python 3.13 enables VERIFY_X509_STRICT by default.
396 # But self signed certificates that are generated by MySQL automatically
397 # doesn't pass the verification.
398 ctx.verify_flags &= ~ssl.VERIFY_X509_STRICT
400 ctx.check_hostname = not hasnoca and sslp.get("check_hostname", True)
401 verify_mode_value = sslp.get("verify_mode")
402 if verify_mode_value is None:
403 ctx.verify_mode = ssl.CERT_NONE if hasnoca else ssl.CERT_REQUIRED
404 elif isinstance(verify_mode_value, bool):
405 ctx.verify_mode = ssl.CERT_REQUIRED if verify_mode_value else ssl.CERT_NONE
406 else:
407 if isinstance(verify_mode_value, str):
408 verify_mode_value = verify_mode_value.lower()
409 if verify_mode_value in ("none", "0", "false", "no"):
410 ctx.verify_mode = ssl.CERT_NONE
411 elif verify_mode_value == "optional":
412 ctx.verify_mode = ssl.CERT_OPTIONAL
413 elif verify_mode_value in ("required", "1", "true", "yes"):
414 ctx.verify_mode = ssl.CERT_REQUIRED
415 else:
416 ctx.verify_mode = ssl.CERT_NONE if hasnoca else ssl.CERT_REQUIRED
417 if "cert" in sslp:
418 ctx.load_cert_chain(
419 sslp["cert"], keyfile=sslp.get("key"), password=sslp.get("password")
420 )
421 if "cipher" in sslp:
422 ctx.set_ciphers(sslp["cipher"])
423 ctx.options |= ssl.OP_NO_SSLv2
424 ctx.options |= ssl.OP_NO_SSLv3
425 return ctx
427 def close(self):
428 """
429 Send the quit message and close the socket.
431 See `Connection.close() <https://www.python.org/dev/peps/pep-0249/#Connection.close>`_
432 in the specification.
434 :raise Error: If the connection is already closed.
435 """
436 if self._closed:
437 raise err.Error("Already closed")
438 self._closed = True
439 if self._sock is None:
440 return
441 send_data = struct.pack("<iB", 1, COMMAND.COM_QUIT)
442 try:
443 with contextlib.suppress(Exception):
444 self._write_bytes(send_data)
445 finally:
446 self._force_close()
448 @property
449 def open(self):
450 """Return True if the connection is open."""
451 return self._sock is not None
453 def _force_close(self):
454 """Close connection without QUIT message."""
455 if self._rfile:
456 self._rfile.close()
457 if self._sock:
458 try:
459 self._sock.close()
460 except: # noqa
461 pass
462 self._sock = None
463 self._rfile = None
465 __del__ = _force_close
467 def autocommit(self, value):
468 self.autocommit_mode = bool(value)
469 current = self.get_autocommit()
470 if value != current:
471 self._send_autocommit_mode()
473 def get_autocommit(self):
474 return bool(self.server_status & SERVER_STATUS.SERVER_STATUS_AUTOCOMMIT)
476 def _read_ok_packet(self):
477 pkt = self._read_packet()
478 if not pkt.is_ok_packet():
479 raise err.OperationalError(
480 CR.CR_COMMANDS_OUT_OF_SYNC,
481 "Command Out of Sync",
482 )
483 ok = OKPacketWrapper(pkt)
484 self.server_status = ok.server_status
485 return ok
487 def _send_autocommit_mode(self):
488 """Set whether or not to commit after every execute()."""
489 self._execute_command(
490 COMMAND.COM_QUERY, "SET AUTOCOMMIT = %s" % self.escape(self.autocommit_mode)
491 )
492 self._read_ok_packet()
494 def begin(self):
495 """Begin transaction."""
496 self._execute_command(COMMAND.COM_QUERY, "BEGIN")
497 self._read_ok_packet()
499 def commit(self):
500 """
501 Commit changes to stable storage.
503 See `Connection.commit() <https://www.python.org/dev/peps/pep-0249/#commit>`_
504 in the specification.
505 """
506 self._execute_command(COMMAND.COM_QUERY, "COMMIT")
507 self._read_ok_packet()
509 def rollback(self):
510 """
511 Roll back the current transaction.
513 See `Connection.rollback() <https://www.python.org/dev/peps/pep-0249/#rollback>`_
514 in the specification.
515 """
516 self._execute_command(COMMAND.COM_QUERY, "ROLLBACK")
517 self._read_ok_packet()
519 def show_warnings(self):
520 """Send the "SHOW WARNINGS" SQL command."""
521 self._execute_command(COMMAND.COM_QUERY, "SHOW WARNINGS")
522 result = MySQLResult(self)
523 result.read()
524 return result.rows
526 def select_db(self, db):
527 """
528 Set current db.
530 :param db: The name of the db.
531 """
532 self._execute_command(COMMAND.COM_INIT_DB, db)
533 self._read_ok_packet()
535 def escape(self, obj, mapping=None) -> str:
536 """Escape whatever value is passed.
538 Non-standard, for internal use; do not use this in your applications.
539 """
540 if isinstance(obj, str):
541 return f"'{self._escape_string(obj)}'"
543 if isinstance(obj, (bytes, bytearray)):
544 return f"X'{obj.hex()}'"
546 # don't use isinstance here. it breaks subclasses like time.struct_time.
547 if type(obj) in (tuple, list, set, frozenset):
548 escape = self.escape
549 return "(" + ",".join([escape(item, mapping) for item in obj]) + ")"
551 if mapping is None:
552 mapping = self.encoders
553 return converters.escape_item(obj, self.encoding, mapping=mapping)
555 def literal(self, obj) -> str:
556 """Alias for escape().
558 Non-standard, for internal use; do not use this in your applications.
559 """
560 warnings.warn(
561 "literal() is deprecated and will be removed in the next version.",
562 DeprecationWarning,
563 stacklevel=2,
564 )
565 return self.escape(obj)
567 def _escape_string(self, s: str):
568 if self.server_status & SERVER_STATUS.SERVER_STATUS_NO_BACKSLASH_ESCAPES:
569 return s.replace("'", "''") # Escape only single quote. Use '' quote
570 return converters.escape_string(s)
572 def cursor(self, cursor=None):
573 """
574 Create a new cursor to execute queries with.
576 :param cursor: The type of cursor to create. None means use Cursor.
577 :type cursor: :py:class:`Cursor`, :py:class:`SSCursor`, :py:class:`DictCursor`,
578 or :py:class:`SSDictCursor`.
579 """
580 if cursor:
581 return cursor(self)
582 return self.cursorclass(self)
584 # The following methods are INTERNAL USE ONLY (called from Cursor)
585 def query(self, sql, unbuffered=False):
586 # if DEBUG:
587 # print("DEBUG: sending query:", sql)
588 if isinstance(sql, str):
589 sql = sql.encode(self.encoding)
590 self._execute_command(COMMAND.COM_QUERY, sql)
591 self._affected_rows = self._read_query_result(unbuffered=unbuffered)
592 return self._affected_rows
594 def next_result(self, unbuffered=False):
595 self._affected_rows = self._read_query_result(unbuffered=unbuffered)
596 return self._affected_rows
598 def affected_rows(self):
599 return self._affected_rows
601 def kill(self, thread_id):
602 if not isinstance(thread_id, int):
603 raise TypeError("thread_id must be an integer")
604 self.query(f"KILL {thread_id:d}")
606 def ping(self, reconnect=False):
607 """
608 Check if the server is alive.
610 `reconnect` is deprecated. Create a new connection if you want to reconnect.
612 :param reconnect: If the connection is closed, reconnect.
613 :type reconnect: boolean
615 :raise Error: If the connection is closed and reconnect=False.
616 """
617 # emit deprecation warning for reconnect.
618 if reconnect:
619 warnings.warn(
620 "The 'reconnect' argument is deprecated. Create a new connection if you want to reconnect.",
621 DeprecationWarning,
622 2,
623 )
624 if self._sock is None:
625 if reconnect:
626 self.connect()
627 reconnect = False
628 else:
629 raise err.Error("Already closed")
630 try:
631 self._execute_command(COMMAND.COM_PING, "")
632 self._read_ok_packet()
633 except Exception:
634 if reconnect:
635 self.connect()
636 self.ping(False)
637 else:
638 raise
640 def set_charset(self, charset):
641 """Deprecated. Use set_character_set() instead."""
642 warnings.warn(
643 "'set_charset' is deprecated, use 'set_character_set' instead",
644 DeprecationWarning,
645 2,
646 )
647 # This function has been implemented in old PyMySQL.
648 # But this name is different from MySQLdb.
649 # So we keep this function for compatibility and add
650 # new set_character_set() function.
651 self.set_character_set(charset)
653 def set_character_set(self, charset, collation=None):
654 """
655 Set charset (and collation)
657 Send "SET NAMES charset [COLLATE collation]" query.
658 Update Connection.encoding based on charset.
659 """
660 # Make sure charset is supported.
661 encoding = charset_by_name(charset).encoding
663 if collation:
664 query = f"SET NAMES {charset} COLLATE {collation}"
665 else:
666 query = f"SET NAMES {charset}"
667 self._execute_command(COMMAND.COM_QUERY, query)
668 self._read_packet()
669 self.charset = charset
670 self.encoding = encoding
671 self.collation = collation
673 def connect(self, sock=None):
674 self._closed = False
675 try:
676 if sock is None:
677 if self.unix_socket:
678 sock = socket.socket(socket.AF_UNIX, socket.SOCK_STREAM)
679 sock.settimeout(self.connect_timeout)
680 sock.connect(self.unix_socket)
681 self.host_info = "Localhost via UNIX socket"
682 self._secure = True
683 if DEBUG:
684 print("connected using unix_socket")
685 else:
686 kwargs = {}
687 if self.bind_address is not None:
688 kwargs["source_address"] = (self.bind_address, 0)
689 while True:
690 try:
691 sock = socket.create_connection(
692 (self.host, self.port), self.connect_timeout, **kwargs
693 )
694 break
695 except OSError as e:
696 if e.errno == errno.EINTR:
697 continue
698 raise
699 self.host_info = "socket %s:%d" % (self.host, self.port)
700 if DEBUG:
701 print("connected using socket")
702 sock.setsockopt(socket.IPPROTO_TCP, socket.TCP_NODELAY, 1)
703 sock.setsockopt(socket.SOL_SOCKET, socket.SO_KEEPALIVE, 1)
705 self._sock = sock
706 sock.settimeout(self._read_timeout)
707 self._current_timeout = self._read_timeout
708 self._rfile = sock.makefile("rb")
709 self._next_seq_id = 0
711 self._get_server_information()
712 self._request_authentication()
714 # Send "SET NAMES" query on init for:
715 # - Ensure charset (and collation) is set to the server.
716 # - collation_id in handshake packet may be ignored.
717 # - If collation is not specified, we don't know what is server's
718 # default collation for the charset. For example, default collation
719 # of utf8mb4 is:
720 # - MySQL 5.7, MariaDB 10.x: utf8mb4_general_ci
721 # - MySQL 8.0: utf8mb4_0900_ai_ci
722 #
723 # Reference:
724 # - https://github.com/PyMySQL/PyMySQL/issues/1092
725 # - https://github.com/wagtail/wagtail/issues/9477
726 # - https://zenn.dev/methane/articles/2023-mysql-collation (Japanese)
727 self.set_character_set(self.charset, self.collation)
729 if self.sql_mode is not None:
730 c = self.cursor()
731 c.execute("SET sql_mode=%s", (self.sql_mode,))
732 c.close()
734 if self.init_command is not None:
735 c = self.cursor()
736 c.execute(self.init_command)
737 c.close()
739 if self.autocommit_mode is not None:
740 self.autocommit(self.autocommit_mode)
741 except BaseException as e:
742 self._force_close()
744 if isinstance(e, OSError):
745 exc = err.OperationalError(
746 CR.CR_CONN_HOST_ERROR,
747 f"Can't connect to MySQL server on {self.host!r} ({e})",
748 )
749 # Keep original exception and traceback to investigate error.
750 exc.original_exception = e
751 exc.traceback = traceback.format_exc()
752 if DEBUG:
753 print(exc.traceback)
754 raise exc
756 # If e is neither DatabaseError or IOError, It's a bug.
757 # But raising AssertionError hides original error.
758 # So just reraise it.
759 raise
761 def write_packet(self, payload):
762 """Writes an entire "mysql packet" in its entirety to the network
763 adding its length and sequence number.
764 """
765 # Internal note: when you build packet manually and calls _write_bytes()
766 # directly, you should set self._next_seq_id properly.
767 data = _pack_int24(len(payload)) + bytes([self._next_seq_id]) + payload
768 if DEBUG:
769 dump_packet(data)
770 self._write_bytes(data)
771 self._next_seq_id = (self._next_seq_id + 1) % 256
773 def _read_packet(self, packet_type=MysqlPacket):
774 """Read an entire "mysql packet" in its entirety from the network
775 and return a MysqlPacket type that represents the results.
777 :raise OperationalError: If the connection to the MySQL server is lost.
778 :raise InternalError: If the packet sequence number is wrong.
779 """
780 # Although `socket.settimeout()` may appear fast, it temporarily releases
781 # the GIL, which can hurt performance in multithreaded applications.
782 # Avoid calling it repeatedly at high frequency.
783 if self._current_timeout != self._read_timeout:
784 self._sock.settimeout(self._read_timeout)
785 self._current_timeout = self._read_timeout
787 buff = []
788 while True:
789 packet_header = self._read_bytes(4)
790 # if DEBUG: dump_packet(packet_header)
792 btrl, btrh, packet_number = struct.unpack("<HBB", packet_header)
793 bytes_to_read = btrl + (btrh << 16)
794 if packet_number != self._next_seq_id:
795 self._force_close()
796 if packet_number == 0:
797 # MariaDB sends error packet with seqno==0 when shutdown
798 raise err.OperationalError(
799 CR.CR_SERVER_LOST,
800 "Lost connection to MySQL server during query",
801 )
802 raise err.InternalError(
803 "Packet sequence number wrong - got %d expected %d"
804 % (packet_number, self._next_seq_id)
805 )
806 self._next_seq_id = (self._next_seq_id + 1) % 256
808 recv_data = self._read_bytes(bytes_to_read)
809 if DEBUG:
810 dump_packet(recv_data)
811 buff.append(recv_data)
812 # https://dev.mysql.com/doc/internals/en/sending-more-than-16mbyte.html
813 if bytes_to_read < MAX_PACKET_LEN:
814 break
816 packet = packet_type(b"".join(buff), self.encoding)
817 if packet.is_error_packet():
818 if self._result is not None and self._result.unbuffered_active is True:
819 self._result.unbuffered_active = False
820 packet.raise_for_error()
821 return packet
823 def _read_bytes(self, num_bytes):
824 # NOTE: caller should call self._sock.settimeout(self._read_timeout)
825 # before first read.
826 while True:
827 try:
828 data = self._rfile.read(num_bytes)
829 break
830 except OSError as e:
831 if e.errno == errno.EINTR:
832 continue
833 self._force_close()
834 raise err.OperationalError(
835 CR.CR_SERVER_LOST,
836 f"Lost connection to MySQL server during query ({e})",
837 )
838 except BaseException:
839 # Don't convert unknown exception to MySQLError.
840 self._force_close()
841 raise
842 if len(data) < num_bytes:
843 self._force_close()
844 raise err.OperationalError(
845 CR.CR_SERVER_LOST, "Lost connection to MySQL server during query"
846 )
847 return data
849 def _write_bytes(self, data):
850 if self._current_timeout != self._write_timeout:
851 self._sock.settimeout(self._write_timeout)
852 self._current_timeout = self._write_timeout
853 try:
854 self._sock.sendall(data)
855 except OSError as e:
856 self._force_close()
857 raise err.OperationalError(
858 CR.CR_SERVER_GONE_ERROR, f"MySQL server has gone away ({e!r})"
859 )
861 def _read_query_result(self, unbuffered=False):
862 self._result = None
863 result = MySQLResult(self)
864 if unbuffered:
865 result.init_unbuffered_query()
866 else:
867 result.read()
868 self._result = result
869 if result.server_status is not None:
870 self.server_status = result.server_status
871 return result.affected_rows
873 def insert_id(self):
874 if self._result:
875 return self._result.insert_id
876 else:
877 return 0
879 def _execute_command(self, command, sql):
880 """
881 :raise InterfaceError: If the connection is closed.
882 :raise ValueError: If no username was specified.
883 """
884 if not self._sock:
885 raise err.InterfaceError(0, "")
887 # If the last query was unbuffered, make sure it finishes before
888 # sending new commands
889 if self._result is not None:
890 if self._result.unbuffered_active:
891 warnings.warn("Previous unbuffered result was left incomplete")
892 self._result._finish_unbuffered_query()
893 while self._result.has_next:
894 self.next_result()
895 self._result = None
897 if isinstance(sql, str):
898 sql = sql.encode(self.encoding)
900 packet_size = min(MAX_PACKET_LEN, len(sql) + 1) # +1 is for command
902 # tiny optimization: build first packet manually instead of
903 # calling self..write_packet()
904 prelude = struct.pack("<iB", packet_size, command)
905 packet = prelude + sql[: packet_size - 1]
906 self._write_bytes(packet)
907 if DEBUG:
908 dump_packet(packet)
909 self._next_seq_id = 1
911 if packet_size < MAX_PACKET_LEN:
912 return
914 sql = sql[packet_size - 1 :]
915 while True:
916 packet_size = min(MAX_PACKET_LEN, len(sql))
917 self.write_packet(sql[:packet_size])
918 sql = sql[packet_size:]
919 if not sql and packet_size < MAX_PACKET_LEN:
920 break
922 def _request_authentication(self):
923 # https://dev.mysql.com/doc/internals/en/connection-phase-packets.html#packet-Protocol::HandshakeResponse
924 if int(self.server_version.split(".", 1)[0]) >= 5:
925 self.client_flag |= CLIENT.MULTI_RESULTS
927 if self.user is None:
928 raise ValueError("Did not specify a username")
930 charset_id = charset_by_name(self.charset).id
931 if isinstance(self.user, str):
932 self.user = self.user.encode(self.encoding)
934 # Determine flags for the initial handshake packet.
935 # CLIENT.SSL is added conditionally: for REQUIRED mode it is already set in
936 # self.client_flag, but for PREFERRED mode it is only added when the server
937 # also advertises SSL support.
938 # _do_ssl is set here and checked below for sha256_password auth.
939 client_flags = self.client_flag
940 if self.ssl:
941 if self.server_capabilities & CLIENT.SSL:
942 # SSL upgrade: include CLIENT.SSL flag and wrap the socket.
943 _do_ssl = True
944 client_flags |= CLIENT.SSL
945 elif self._ssl_required:
946 raise err.OperationalError(
947 CR.CR_SSL_CONNECTION_ERROR,
948 "SSL is required but the server doesn't support it",
949 )
950 else:
951 # PREFERRED mode: server doesn't support SSL, fall back to non-SSL.
952 _do_ssl = False
953 else:
954 _do_ssl = False
956 data_init = struct.pack(
957 "<iIB23s", client_flags, MAX_PACKET_LEN, charset_id, b""
958 )
960 if _do_ssl:
961 self.write_packet(data_init)
962 self._sock = self.ctx.wrap_socket(self._sock, server_hostname=self.host)
963 self._rfile = self._sock.makefile("rb")
964 self._secure = True
966 data = data_init + self.user + b"\0"
968 authresp = b""
969 plugin_name = None
971 if self._auth_plugin_name == "":
972 plugin_name = b""
973 authresp = _auth.scramble_native_password(self.password, self.salt)
974 elif self._auth_plugin_name == "mysql_native_password":
975 plugin_name = b"mysql_native_password"
976 authresp = _auth.scramble_native_password(self.password, self.salt)
977 elif self._auth_plugin_name == "caching_sha2_password":
978 plugin_name = b"caching_sha2_password"
979 if self.password:
980 if DEBUG:
981 print("caching_sha2: trying fast path")
982 authresp = _auth.scramble_caching_sha2(self.password, self.salt)
983 else:
984 if DEBUG:
985 print("caching_sha2: empty password")
986 elif self._auth_plugin_name == "sha256_password":
987 plugin_name = b"sha256_password"
988 if _do_ssl:
989 authresp = self.password + b"\0"
990 elif self.password:
991 authresp = b"\1" # request public key
992 else:
993 authresp = b"\0" # empty password
995 if self.server_capabilities & CLIENT.PLUGIN_AUTH_LENENC_CLIENT_DATA:
996 data += _lenenc_int(len(authresp)) + authresp
997 elif self.server_capabilities & CLIENT.SECURE_CONNECTION:
998 data += struct.pack("B", len(authresp)) + authresp
999 else: # pragma: no cover - not testing against servers without secure auth (>=5.0)
1000 data += authresp + b"\0"
1002 if self.db and self.server_capabilities & CLIENT.CONNECT_WITH_DB:
1003 if isinstance(self.db, str):
1004 self.db = self.db.encode(self.encoding)
1005 data += self.db + b"\0"
1007 if self.server_capabilities & CLIENT.PLUGIN_AUTH:
1008 data += (plugin_name or b"") + b"\0"
1010 if self.server_capabilities & CLIENT.CONNECT_ATTRS:
1011 connect_attrs = b""
1012 for k, v in self._connect_attrs.items():
1013 k = k.encode("utf-8")
1014 connect_attrs += _lenenc_int(len(k)) + k
1015 v = v.encode("utf-8")
1016 connect_attrs += _lenenc_int(len(v)) + v
1017 data += _lenenc_int(len(connect_attrs)) + connect_attrs
1019 self.write_packet(data)
1020 auth_packet = self._read_packet()
1022 # Authentication is a state machine. An authentication plugin can return
1023 # another transition packet (for example, MySQL Router can request full
1024 # caching_sha2_password authentication and then switch to the backend's
1025 # mysql_native_password plugin), so keep dispatching until the server
1026 # sends a terminal packet.
1027 auth_plugin_name = self._auth_plugin_name
1028 if isinstance(auth_plugin_name, str):
1029 auth_plugin_name = auth_plugin_name.encode("ascii")
1030 auth_plugin_handler = self._get_auth_plugin_handler(auth_plugin_name)
1031 auth_switch_received = False
1033 while True:
1034 # Custom authentication handlers historically did not need to return
1035 # the final OK packet after consuming the complete exchange.
1036 if auth_packet is None and auth_plugin_handler:
1037 break
1039 # if authentication method isn't accepted the first byte
1040 # will have the octet 254
1041 if auth_packet.is_auth_switch_request():
1042 if auth_switch_received:
1043 raise err.OperationalError("received multiple auth switch requests")
1044 auth_switch_received = True
1045 if DEBUG:
1046 print("received auth switch")
1047 # https://dev.mysql.com/doc/internals/en/connection-phase-packets.html#packet-Protocol::AuthSwitchRequest
1048 auth_packet.read_uint8() # 0xfe packet identifier
1049 plugin_name = auth_packet.read_string()
1050 if (
1051 self.server_capabilities & CLIENT.PLUGIN_AUTH
1052 and plugin_name is not None
1053 ):
1054 auth_plugin_name = plugin_name
1055 auth_plugin_handler = self._get_auth_plugin_handler(plugin_name)
1056 auth_packet = self._process_auth(
1057 plugin_name, auth_packet, auth_plugin_handler
1058 )
1059 continue
1060 raise err.OperationalError("received unknown auth switch request")
1062 if auth_packet.is_extra_auth_data():
1063 if DEBUG:
1064 print("received extra data")
1065 # https://dev.mysql.com/doc/internals/en/successful-authentication.html
1066 if auth_plugin_handler:
1067 auth_packet = auth_plugin_handler.authenticate(auth_packet)
1068 continue
1069 elif auth_plugin_name in (
1070 b"caching_sha2_password",
1071 "caching_sha2_password",
1072 ):
1073 auth_packet = _auth.caching_sha2_password_auth(self, auth_packet)
1074 continue
1075 if auth_plugin_name in (b"sha256_password", "sha256_password"):
1076 auth_packet = _auth.sha256_password_auth(self, auth_packet)
1077 continue
1078 raise err.OperationalError(
1079 "Received extra packet for auth method %r", auth_plugin_name
1080 )
1082 if auth_packet.is_ok_packet():
1083 break
1084 raise err.OperationalError("unexpected packet during authentication")
1086 if DEBUG:
1087 print("Succeed to auth")
1089 def _process_auth(self, plugin_name, auth_packet, handler=None):
1090 if handler:
1091 try:
1092 return handler.authenticate(auth_packet)
1093 except AttributeError:
1094 if plugin_name != b"dialog":
1095 raise err.OperationalError(
1096 CR.CR_AUTH_PLUGIN_CANNOT_LOAD,
1097 f"Authentication plugin '{plugin_name}'"
1098 f" not loaded: - {type(handler)!r} missing authenticate method",
1099 )
1100 if plugin_name == b"caching_sha2_password":
1101 return _auth.caching_sha2_password_auth(self, auth_packet)
1102 elif plugin_name == b"sha256_password":
1103 return _auth.sha256_password_auth(self, auth_packet)
1104 elif plugin_name == b"mysql_native_password":
1105 data = _auth.scramble_native_password(self.password, auth_packet.read_all())
1106 elif plugin_name == b"client_ed25519":
1107 data = _auth.ed25519_password(self.password, auth_packet.read_all())
1108 elif plugin_name == b"mysql_old_password":
1109 data = (
1110 _auth.scramble_old_password(self.password, auth_packet.read_all())
1111 + b"\0"
1112 )
1113 elif plugin_name == b"mysql_clear_password":
1114 # https://dev.mysql.com/doc/internals/en/clear-text-authentication.html
1115 data = self.password + b"\0"
1116 elif plugin_name == b"dialog":
1117 pkt = auth_packet
1118 while True:
1119 flag = pkt.read_uint8()
1120 echo = (flag & 0x06) == 0x02
1121 last = (flag & 0x01) == 0x01
1122 prompt = pkt.read_all()
1124 if prompt == b"Password: ":
1125 self.write_packet(self.password + b"\0")
1126 elif handler:
1127 resp = "no response - TypeError within plugin.prompt method"
1128 try:
1129 resp = handler.prompt(echo, prompt)
1130 self.write_packet(resp + b"\0")
1131 except AttributeError:
1132 raise err.OperationalError(
1133 CR.CR_AUTH_PLUGIN_CANNOT_LOAD,
1134 f"Authentication plugin '{plugin_name}'"
1135 f" not loaded: - {handler!r} missing prompt method",
1136 )
1137 except TypeError:
1138 raise err.OperationalError(
1139 CR.CR_AUTH_PLUGIN_ERR,
1140 f"Authentication plugin '{plugin_name}'"
1141 f" {handler!r} didn't respond with string. Returned '{resp!r}' to prompt {prompt!r}",
1142 )
1143 else:
1144 raise err.OperationalError(
1145 CR.CR_AUTH_PLUGIN_CANNOT_LOAD,
1146 f"Authentication plugin '{plugin_name}' not configured",
1147 )
1148 pkt = self._read_packet()
1149 pkt.check_error()
1150 if pkt.is_ok_packet() or last:
1151 break
1152 return pkt
1153 else:
1154 raise err.OperationalError(
1155 CR.CR_AUTH_PLUGIN_CANNOT_LOAD,
1156 "Authentication plugin '%s' not configured" % plugin_name,
1157 )
1159 self.write_packet(data)
1160 pkt = self._read_packet()
1161 pkt.check_error()
1162 return pkt
1164 def _get_auth_plugin_handler(self, plugin_name):
1165 plugin_class = self._auth_plugin_map.get(plugin_name)
1166 if not plugin_class and isinstance(plugin_name, bytes):
1167 plugin_class = self._auth_plugin_map.get(plugin_name.decode("ascii"))
1168 if plugin_class:
1169 try:
1170 handler = plugin_class(self)
1171 except TypeError:
1172 raise err.OperationalError(
1173 CR.CR_AUTH_PLUGIN_CANNOT_LOAD,
1174 f"Authentication plugin '{plugin_name}'"
1175 f" not loaded: - {plugin_class!r} cannot be constructed with connection object",
1176 )
1177 else:
1178 handler = None
1179 return handler
1181 # _mysql support
1182 def thread_id(self):
1183 return self.server_thread_id[0]
1185 def character_set_name(self):
1186 return self.charset
1188 def get_host_info(self):
1189 return self.host_info
1191 def get_proto_info(self):
1192 return self.protocol_version
1194 def _get_server_information(self):
1195 i = 0
1196 packet = self._read_packet()
1197 data = packet.get_all_data()
1199 self.protocol_version = data[i]
1200 i += 1
1202 server_end = data.find(b"\0", i)
1203 self.server_version = data[i:server_end].decode("latin1")
1204 i = server_end + 1
1206 self.server_thread_id = struct.unpack("<I", data[i : i + 4])
1207 i += 4
1209 self.salt = data[i : i + 8]
1210 i += 9 # 8 + 1(filler)
1212 self.server_capabilities = struct.unpack("<H", data[i : i + 2])[0]
1213 i += 2
1215 if len(data) >= i + 6:
1216 lang, stat, cap_h, salt_len = struct.unpack("<BHHB", data[i : i + 6])
1217 i += 6
1218 # TODO: deprecate server_language and server_charset.
1219 # mysqlclient-python doesn't provide it.
1220 self.server_language = lang
1221 try:
1222 self.server_charset = charset_by_id(lang).name
1223 except KeyError:
1224 # unknown collation
1225 self.server_charset = None
1227 self.server_status = stat
1228 if DEBUG:
1229 print("server_status: %x" % stat)
1231 self.server_capabilities |= cap_h << 16
1232 if DEBUG:
1233 print("salt_len:", salt_len)
1234 salt_len = max(12, salt_len - 9)
1236 # reserved
1237 i += 10
1239 if len(data) >= i + salt_len:
1240 # salt_len includes auth_plugin_data_part_1 and filler
1241 self.salt += data[i : i + salt_len]
1242 i += salt_len
1244 i += 1
1245 # AUTH PLUGIN NAME may appear here.
1246 if self.server_capabilities & CLIENT.PLUGIN_AUTH and len(data) >= i:
1247 # Due to Bug#59453 the auth-plugin-name is missing the terminating
1248 # NUL-char in versions prior to 5.5.10 and 5.6.2.
1249 # ref: https://dev.mysql.com/doc/internals/en/connection-phase-packets.html#packet-Protocol::Handshake
1250 # didn't use version checks as mariadb is corrected and reports
1251 # earlier than those two.
1252 server_end = data.find(b"\0", i)
1253 if server_end < 0: # pragma: no cover - very specific upstream bug
1254 # not found \0 and last field so take it all
1255 self._auth_plugin_name = data[i:].decode("utf-8")
1256 else:
1257 self._auth_plugin_name = data[i:server_end].decode("utf-8")
1259 if _DEFAULT_AUTH_PLUGIN is not None: # for tests
1260 self._auth_plugin_name = _DEFAULT_AUTH_PLUGIN
1262 def get_server_info(self):
1263 return self.server_version
1265 Warning = err.Warning
1266 Error = err.Error
1267 InterfaceError = err.InterfaceError
1268 DatabaseError = err.DatabaseError
1269 DataError = err.DataError
1270 OperationalError = err.OperationalError
1271 IntegrityError = err.IntegrityError
1272 InternalError = err.InternalError
1273 ProgrammingError = err.ProgrammingError
1274 NotSupportedError = err.NotSupportedError
1277class MySQLResult:
1278 def __init__(self, connection):
1279 """
1280 :type connection: Connection
1281 """
1282 self.connection = connection
1283 self.affected_rows = None
1284 self.insert_id = None
1285 self.server_status = None
1286 self.warning_count = 0
1287 self.message = None
1288 self.field_count = 0
1289 self.description = None
1290 self.rows = None
1291 self.has_next = None
1292 self.unbuffered_active = False
1294 def __del__(self):
1295 if self.unbuffered_active:
1296 self._finish_unbuffered_query()
1298 def read(self):
1299 try:
1300 first_packet = self.connection._read_packet()
1302 if first_packet.is_ok_packet():
1303 self._read_ok_packet(first_packet)
1304 elif first_packet.is_load_local_packet():
1305 self._read_load_local_packet(first_packet)
1306 else:
1307 self._read_result_packet(first_packet)
1308 finally:
1309 self.connection = None
1311 def init_unbuffered_query(self):
1312 """
1313 :raise OperationalError: If the connection to the MySQL server is lost.
1314 :raise InternalError:
1315 """
1316 first_packet = self.connection._read_packet()
1318 if first_packet.is_ok_packet():
1319 self.connection = None
1320 self._read_ok_packet(first_packet)
1321 elif first_packet.is_load_local_packet():
1322 try:
1323 self._read_load_local_packet(first_packet)
1324 finally:
1325 self.connection = None
1326 else:
1327 self.field_count = first_packet.read_length_encoded_integer()
1328 self._get_descriptions()
1330 # Apparently, MySQLdb picks this number because it's the maximum
1331 # value of a 64bit unsigned integer. Since we're emulating MySQLdb,
1332 # we set it to this instead of None, which would be preferred.
1333 self.affected_rows = 18446744073709551615
1334 self.unbuffered_active = True
1336 def _read_ok_packet(self, packet):
1337 ok_packet = OKPacketWrapper(packet)
1338 self.affected_rows = ok_packet.affected_rows
1339 self.insert_id = ok_packet.insert_id
1340 self.server_status = ok_packet.server_status
1341 self.warning_count = ok_packet.warning_count
1342 self.message = ok_packet.message
1343 self.has_next = ok_packet.has_next
1345 def _read_load_local_packet(self, first_packet):
1346 conn: Connection = self.connection
1347 if not conn._local_infile:
1348 raise RuntimeError(
1349 "**WARN**: Received LOAD_LOCAL packet but local_infile option is false."
1350 )
1351 load_packet = LoadLocalPacketWrapper(first_packet)
1352 try:
1353 _send_local_file(load_packet.filename, conn)
1354 finally:
1355 # send the empty packet to signify we are done sending data
1356 conn.write_packet(b"")
1357 ok_packet = conn._read_packet()
1358 # If an error occurs while sending the file, exit here without handling
1359 # the OK packet.
1361 if not ok_packet.is_ok_packet():
1362 raise err.OperationalError(
1363 CR.CR_COMMANDS_OUT_OF_SYNC, "Commands Out of Sync"
1364 )
1365 self._read_ok_packet(ok_packet)
1367 def _check_packet_is_eof(self, packet):
1368 if not packet.is_eof_packet():
1369 return False
1370 # TODO: Support CLIENT.DEPRECATE_EOF
1371 # 1) Add DEPRECATE_EOF to CAPABILITIES
1372 # 2) Mask CAPABILITIES with server_capabilities
1373 # 3) if server_capabilities & CLIENT.DEPRECATE_EOF:
1374 # use OKPacketWrapper instead of EOFPacketWrapper
1375 wp = EOFPacketWrapper(packet)
1376 self.warning_count = wp.warning_count
1377 self.has_next = wp.has_next
1378 return True
1380 def _read_result_packet(self, first_packet):
1381 self.field_count = first_packet.read_length_encoded_integer()
1382 self._get_descriptions()
1383 self._read_rowdata_packet()
1385 def _read_rowdata_packet_unbuffered(self):
1386 # Check if in an active query
1387 if not self.unbuffered_active:
1388 return
1390 # EOF
1391 packet = self.connection._read_packet()
1392 if self._check_packet_is_eof(packet):
1393 self.unbuffered_active = False
1394 self.connection = None
1395 self.rows = None
1396 return
1398 row = self._read_row_from_packet(packet)
1399 self.affected_rows = 1
1400 self.rows = (row,) # rows should tuple of row for MySQL-python compatibility.
1401 return row
1403 def _finish_unbuffered_query(self):
1404 # After much reading on the MySQL protocol, it appears that there is,
1405 # in fact, no way to stop MySQL from sending all the data after
1406 # executing a query, so we just spin, and wait for an EOF packet.
1407 while self.unbuffered_active:
1408 try:
1409 packet = self.connection._read_packet()
1410 except err.OperationalError as e:
1411 if e.args[0] in (
1412 ER.QUERY_TIMEOUT,
1413 ER.STATEMENT_TIMEOUT,
1414 ):
1415 # if the query timed out we can simply ignore this error
1416 self.unbuffered_active = False
1417 self.connection = None
1418 return
1420 raise
1422 if self._check_packet_is_eof(packet):
1423 self.unbuffered_active = False
1424 self.connection = None # release reference to kill cyclic reference.
1426 def _read_rowdata_packet(self):
1427 """Read a rowdata packet for each data row in the result set."""
1428 rows = []
1429 while True:
1430 packet = self.connection._read_packet()
1431 if self._check_packet_is_eof(packet):
1432 self.connection = None # release reference to kill cyclic reference.
1433 break
1434 rows.append(self._read_row_from_packet(packet))
1436 self.affected_rows = len(rows)
1437 self.rows = tuple(rows)
1439 def _read_row_from_packet(self, packet):
1440 row = []
1441 for encoding, converter in self.converters:
1442 try:
1443 data = packet.read_length_coded_string()
1444 except IndexError:
1445 # No more columns in this row
1446 # See https://github.com/PyMySQL/PyMySQL/pull/434
1447 break
1448 if data is not None:
1449 if encoding is not None:
1450 data = data.decode(encoding)
1451 if DEBUG:
1452 print("DEBUG: DATA = ", data)
1453 if converter is not None:
1454 data = converter(data)
1455 row.append(data)
1456 return tuple(row)
1458 def _get_descriptions(self):
1459 """Read a column descriptor packet for each column in the result."""
1460 self.fields = []
1461 self.converters = []
1462 use_unicode = self.connection.use_unicode
1463 conn_encoding = self.connection.encoding
1464 description = []
1466 for i in range(self.field_count):
1467 field = self.connection._read_packet(FieldDescriptorPacket)
1468 self.fields.append(field)
1469 description.append(field.description())
1470 field_type = field.type_code
1471 if use_unicode:
1472 if field_type == FIELD_TYPE.JSON:
1473 # When SELECT from JSON column: charset = binary
1474 # When SELECT CAST(... AS JSON): charset = connection encoding
1475 # This behavior is different from TEXT / BLOB.
1476 # We should decode result by connection encoding regardless charsetnr.
1477 # See https://github.com/PyMySQL/PyMySQL/issues/488
1478 encoding = conn_encoding # SELECT CAST(... AS JSON)
1479 elif field_type in TEXT_TYPES:
1480 if field.charsetnr == 63: # binary
1481 # TEXTs with charset=binary means BINARY types.
1482 encoding = None
1483 else:
1484 encoding = conn_encoding
1485 else:
1486 # Integers, Dates and Times, and other basic data is encoded in ascii
1487 encoding = "ascii"
1488 else:
1489 encoding = None
1490 converter = self.connection.decoders.get(field_type)
1491 if converter is converters.through:
1492 converter = None
1493 if DEBUG:
1494 print(f"DEBUG: field={field}, converter={converter}")
1495 self.converters.append((encoding, converter))
1497 eof_packet = self.connection._read_packet()
1498 assert eof_packet.is_eof_packet(), "Protocol error, expecting EOF"
1499 self.description = tuple(description)
1502def _send_local_file(filename: str, conn: Connection):
1503 """Send data packets from the local file to the server"""
1504 packet_size = min(conn.max_allowed_packet, 16 * 1024)
1506 try:
1507 with open(filename, "rb") as file:
1508 # 16KB is efficient enough
1509 while True:
1510 chunk = file.read(packet_size)
1511 if not chunk:
1512 break
1513 conn.write_packet(chunk)
1514 except OSError as e:
1515 raise err.OperationalError(
1516 ER.FILE_NOT_FOUND,
1517 f"Can't open file '{filename}': {e}",
1518 )