python操作数据库语句大全_Python操作mysql

#Python implementation of the MySQL client-server protocol#http://dev.mysql.com/doc/internals/en/client-server-protocol.html#Error codes:#http://dev.mysql.com/doc/refman/5.5/en/error-messages-client.html

from __future__ importprint_functionfrom ._compat importPY2, range_type, text_type, str_type, JYTHON, IRONPYTHONimporterrnoimportioimportosimportsocketimportstructimportsysimporttracebackimportwarningsfrom . import_authfrom .charset importcharset_by_name, charset_by_idfrom .constants importCLIENT, COMMAND, CR, FIELD_TYPE, SERVER_STATUSfrom . importconvertersfrom .cursors importCursorfrom .optionfile importParserfrom .protocol import(

dump_packet, MysqlPacket, FieldDescriptorPacket, OKPacketWrapper,

EOFPacketWrapper, LoadLocalPacketWrapper

)from .util importbyte2int, int2bytefrom . importerr, VERSION_STRINGtry:importssl

SSL_ENABLED=TrueexceptImportError:

ssl=None

SSL_ENABLED=Falsetry:importgetpass

DEFAULT_USER=getpass.getuser()delgetpassexcept(ImportError, KeyError):#KeyError occurs when there's no entry in OS database for a current user.

DEFAULT_USER =None

DEBUG=False

_py_version= sys.version_info[:2]ifPY2:pass

elif _py_version < (3, 6):#See http://bugs.python.org/issue24870

_surrogateescape_table = [chr(i) if i < 0x80 else chr(i + 0xdc00) for i in range(256)]def_fast_surrogateescape(s):return s.decode('latin1').translate(_surrogateescape_table)else:def_fast_surrogateescape(s):return s.decode('ascii', 'surrogateescape')#socket.makefile() in Python 2 is not usable because very inefficient and#bad behavior about timeout.#XXX: ._socketio doesn't work under IronPython.

if PY2 and notIRONPYTHON:#read method of file-like returned by sock.makefile() is very slow.

#So we copy io-based one from Python 3.

from ._socketio importSocketIOdef_makefile(sock, mode):returnio.BufferedReader(SocketIO(sock, mode))else:#socket.makefile in Python 3 is nice.

def_makefile(sock, mode):returnsock.makefile(mode)

TEXT_TYPES={

FIELD_TYPE.BIT,

FIELD_TYPE.BLOB,

FIELD_TYPE.LONG_BLOB,

FIELD_TYPE.MEDIUM_BLOB,

FIELD_TYPE.STRING,

FIELD_TYPE.TINY_BLOB,

FIELD_TYPE.VAR_STRING,

FIELD_TYPE.VARCHAR,

FIELD_TYPE.GEOMETRY,

}

DEFAULT_CHARSET= 'utf8mb4' #TODO: change to utf8mb4

MAX_PACKET_LEN= 2**24-1

defpack_int24(n):return struct.pack('

deflenenc_int(i):if (i <0):raise ValueError("Encoding %d is less than 0 - no representation in LengthEncodedInteger" %i)elif (i < 0xfb):returnint2byte(i)elif (i < (1 << 16)):return b'\xfc' + struct.pack('

The proper way to get an instance of this class is to call

connect().

Establish a connection to the MySQL database. Accepts several

arguments:

:param host: Host where the database server is located

:param user: Username to log in as

:param password: Password to use.

:param database: Database to use, None to not use a particular one.

:param port: MySQL port to use, default is usually OK. (default: 3306)

:param bind_address: When the client has multiple network interfaces, specify

the interface from which to connect to the host. Argument can be

a hostname or an IP address.

:param unix_socket: Optionally, you can use a unix socket rather than TCP/IP.

:param read_timeout: The timeout for reading from the connection in seconds (default: None - no timeout)

:param write_timeout: The timeout for writing to the connection in seconds (default: None - no timeout)

:param charset: Charset you want to use.

:param sql_mode: Default SQL_MODE to use.

:param read_default_file:

Specifies my.cnf file to read these parameters from under the [client] section.

:param conv:

Conversion dictionary to use instead of the default one.

This is used to provide custom marshalling and unmarshaling of types.

See converters.

:param use_unicode:

Whether or not to default to unicode strings.

This option defaults to true for Py3k.

:param client_flag: Custom flags to send to MySQL. Find potential values in constants.CLIENT.

:param cursorclass: Custom cursor class to use.

:param init_command: Initial SQL statement to run when connection is established.

:param connect_timeout: Timeout before throwing an exception when connecting.

(default: 10, min: 1, max: 31536000)

:param ssl:

A dict of arguments similar to mysql_ssl_set()'s parameters.

For now the capath and cipher arguments are not supported.

:param read_default_group: Group to read from in the configuration file.

:param compress: Not supported

:param named_pipe: Not supported

:param autocommit: Autocommit mode. None means use server default. (default: False)

:param local_infile: Boolean to enable the use of LOAD DATA LOCAL command. (default: False)

:param max_allowed_packet: Max size of packet sent to server in bytes. (default: 16MB)

Only used to limit size of "LOAD LOCAL INFILE" data packet smaller than default (16KB).

:param defer_connect: Don't explicitly connect on contruction - wait for connect call.

(default: False)

:param auth_plugin_map: A dict of plugin names to a class that processes that plugin.

The class will take the Connection object as the argument to the constructor.

The class needs an authenticate method taking an authentication packet as

an argument. For the dialog plugin, a prompt(echo, prompt) method can be used

(if no authenticate method) for returning a string from the user. (experimental)

:param server_public_key: SHA256 authenticaiton plugin public key value. (default: None)

:param db: Alias for database. (for compatibility to MySQLdb)

:param passwd: Alias for password. (for compatibility to MySQLdb)

:param binary_prefix: Add _binary prefix on bytes and bytearray. (default: False)

See `Connection `_ in the

specification."""_sock=None

_auth_plugin_name= ''_closed=False

_secure=Falsedef __init__(self, host=None, user=None, password="",

database=None, port=0, unix_socket=None,

charset='', sql_mode=None,

read_default_file=None, conv=None, use_unicode=None,

client_flag=0, cursorclass=Cursor, init_command=None,

connect_timeout=10, ssl=None, read_default_group=None,

compress=None, named_pipe=None,

autocommit=False, db=None, passwd=None, local_infile=False,

max_allowed_packet=16*1024*1024, defer_connect=False,

auth_plugin_map=None, read_timeout=None, write_timeout=None,

bind_address=None, binary_prefix=False, program_name=None,

server_public_key=None):if use_unicode is None and sys.version_info[0] > 2:

use_unicode=Trueif db is not None and database isNone:

database=dbif passwd is not None and notpassword:

password=passwdif compress ornamed_pipe:raise NotImplementedError("compress and named_pipe arguments are not supported")

self._local_infile=bool(local_infile)ifself._local_infile:

client_flag|=CLIENT.LOCAL_FILESif read_default_group and notread_default_file:if sys.platform.startswith("win"):

read_default_file= "c:\\my.ini"

else:

read_default_file= "/etc/my.cnf"

ifread_default_file:if notread_default_group:

read_default_group= "client"cfg=Parser()

cfg.read(os.path.expanduser(read_default_file))def_config(key, arg):ifarg:returnargtry:returncfg.get(read_default_group, key)exceptException:returnarg

user= _config("user", user)

password= _config("password", password)

host= _config("host", host)

database= _config("database", database)

unix_socket= _config("socket", unix_socket)

port= int(_config("port", port))

bind_address= _config("bind-address", bind_address)

charset= _config("default-character-set", charset)if notssl:

ssl={}ifisinstance(ssl, dict):for key in ["ca", "capath", "cert", "key", "cipher"]:

value= _config("ssl-" +key, ssl.get(key))ifvalue:

ssl[key]=value

self.ssl=Falseifssl:if notSSL_ENABLED:raise NotImplementedError("ssl module not found")

self.ssl=True

client_flag|=CLIENT.SSL

self.ctx=self._create_ssl_ctx(ssl)

self.host= host or "localhost"self.port= port or 3306self.user= user orDEFAULT_USER

self.password= password or b""

ifisinstance(self.password, text_type):

self.password= self.password.encode('latin1')

self.db=database

self.unix_socket=unix_socket

self.bind_address=bind_addressif not (0 < connect_timeout <= 31536000):raise ValueError("connect_timeout should be >0 and <=31536000")

self.connect_timeout= connect_timeout orNoneif read_timeout is not None and read_timeout <=0:raise ValueError("read_timeout should be >= 0")

self._read_timeout=read_timeoutif write_timeout is not None and write_timeout <=0:raise ValueError("write_timeout should be >= 0")

self._write_timeout=write_timeoutifcharset:

self.charset=charset

self.use_unicode=Trueelse:

self.charset=DEFAULT_CHARSET

self.use_unicode=Falseif use_unicode is notNone:

self.use_unicode=use_unicode

self.encoding=charset_by_name(self.charset).encoding

client_flag|=CLIENT.CAPABILITIESifself.db:

client_flag|=CLIENT.CONNECT_WITH_DB

self.client_flag=client_flag

self.cursorclass=cursorclass

self._result=None

self._affected_rows=0

self.host_info= "Not connected"

#: specified autocommit mode. None means use server default.

self.autocommit_mode =autocommitif conv isNone:

conv=converters.conversions#Need for MySQLdb compatibility.

self.encoders = dict([(k, v) for (k, v) in conv.items() if type(k) is notint])

self.decoders= dict([(k, v) for (k, v) in conv.items() if type(k) isint])

self.sql_mode=sql_mode

self.init_command=init_command

self.max_allowed_packet=max_allowed_packet

self._auth_plugin_map= auth_plugin_map or{}

self._binary_prefix=binary_prefix

self.server_public_key=server_public_key

self._connect_attrs={'_client_name': 'pymysql','_pid': str(os.getpid()),'_client_version': VERSION_STRING,

}ifprogram_name:

self._connect_attrs["program_name"] =program_nameelifsys.argv:

self._connect_attrs["program_name"] =sys.argv[0]ifdefer_connect:

self._sock=Noneelse:

self.connect()def_create_ssl_ctx(self, sslp):ifisinstance(sslp, ssl.SSLContext):returnsslp

ca= sslp.get('ca')

capath= sslp.get('capath')

hasnoca= ca is None and capath isNone

ctx= ssl.create_default_context(cafile=ca, capath=capath)

ctx.check_hostname= not hasnoca and sslp.get('check_hostname', True)

ctx.verify_mode= ssl.CERT_NONE if hasnoca elsessl.CERT_REQUIREDif 'cert' insslp:

ctx.load_cert_chain(sslp['cert'], keyfile=sslp.get('key'))if 'cipher' insslp:

ctx.set_ciphers(sslp['cipher'])

ctx.options|=ssl.OP_NO_SSLv2

ctx.options|=ssl.OP_NO_SSLv3returnctxdefclose(self):"""Send the quit message and close the socket.

See `Connection.close() `_

in the specification.

:raise Error: If the connection is already closed."""

ifself._closed:raise err.Error("Already closed")

self._closed=Trueif self._sock isNone:returnsend_data= struct.pack('

self._write_bytes(send_data)exceptException:pass

finally:

self._force_close()

@propertydefopen(self):"""Return True if the connection is open"""

return self._sock is notNonedef_force_close(self):"""Close connection without QUIT message"""

ifself._sock:try:

self._sock.close()except: #noqa

passself._sock=None

self._rfile=None__del__ =_force_closedefautocommit(self, value):

self.autocommit_mode=bool(value)

current=self.get_autocommit()if value !=current:

self._send_autocommit_mode()defget_autocommit(self):return bool(self.server_status &SERVER_STATUS.SERVER_STATUS_AUTOCOMMIT)def_read_ok_packet(self):

pkt=self._read_packet()if notpkt.is_ok_packet():raise err.OperationalError(2014, "Command Out of Sync")

ok=OKPacketWrapper(pkt)

self.server_status=ok.server_statusreturnokdef_send_autocommit_mode(self):"""Set whether or not to commit after every execute()"""self._execute_command(COMMAND.COM_QUERY,"SET AUTOCOMMIT = %s" %self.escape(self.autocommit_mode))

self._read_ok_packet()defbegin(self):"""Begin transaction."""self._execute_command(COMMAND.COM_QUERY,"BEGIN")

self._read_ok_packet()defcommit(self):"""Commit changes to stable storage.

See `Connection.commit() `_

in the specification."""self._execute_command(COMMAND.COM_QUERY,"COMMIT")

self._read_ok_packet()defrollback(self):"""Roll back the current transaction.

See `Connection.rollback() `_

in the specification."""self._execute_command(COMMAND.COM_QUERY,"ROLLBACK")

self._read_ok_packet()defshow_warnings(self):"""Send the "SHOW WARNINGS" SQL command."""self._execute_command(COMMAND.COM_QUERY,"SHOW WARNINGS")

result=MySQLResult(self)

result.read()returnresult.rowsdefselect_db(self, db):"""Set current db.

:param db: The name of the db."""self._execute_command(COMMAND.COM_INIT_DB, db)

self._read_ok_packet()def escape(self, obj, mapping=None):"""Escape whatever value you pass to it.

Non-standard, for internal use; do not use this in your applications."""

ifisinstance(obj, str_type):return "'" + self.escape_string(obj) + "'"

ifisinstance(obj, (bytes, bytearray)):

ret=self._quote_bytes(obj)ifself._binary_prefix:

ret= "_binary" +retreturnretreturn converters.escape_item(obj, self.charset, mapping=mapping)defliteral(self, obj):"""Alias for escape()

Non-standard, for internal use; do not use this in your applications."""

returnself.escape(obj, self.encoders)defescape_string(self, s):if (self.server_status &SERVER_STATUS.SERVER_STATUS_NO_BACKSLASH_ESCAPES):return s.replace("'", "''")returnconverters.escape_string(s)def_quote_bytes(self, s):if (self.server_status &SERVER_STATUS.SERVER_STATUS_NO_BACKSLASH_ESCAPES):return "'%s'" % (_fast_surrogateescape(s.replace(b"'", b"''")),)returnconverters.escape_bytes(s)def cursor(self, cursor=None):"""Create a new cursor to execute queries with.

:param cursor: The type of cursor to create; one of :py:class:`Cursor`,

:py:class:`SSCursor`, :py:class:`DictCursor`, or :py:class:`SSDictCursor`.

None means use Cursor."""

ifcursor:returncursor(self)returnself.cursorclass(self)def __enter__(self):"""Context manager that returns a Cursor"""

returnself.cursor()def __exit__(self, exc, value, traceback):"""On successful exit, commit. On exception, rollback"""

ifexc:

self.rollback()else:

self.commit()#The following methods are INTERNAL USE ONLY (called from Cursor)

def query(self, sql, unbuffered=False):#if DEBUG:

#print("DEBUG: sending query:", sql)

if isinstance(sql, text_type) and not (JYTHON orIRONPYTHON):ifPY2:

sql=sql.encode(self.encoding)else:

sql= sql.encode(self.encoding, 'surrogateescape')

self._execute_command(COMMAND.COM_QUERY, sql)

self._affected_rows= self._read_query_result(unbuffered=unbuffered)returnself._affected_rowsdef next_result(self, unbuffered=False):

self._affected_rows= self._read_query_result(unbuffered=unbuffered)returnself._affected_rowsdefaffected_rows(self):returnself._affected_rowsdefkill(self, thread_id):

arg= struct.pack('

self._execute_command(COMMAND.COM_PROCESS_KILL, arg)returnself._read_ok_packet()def ping(self, reconnect=True):"""Check if the server is alive.

:param reconnect: If the connection is closed, reconnect.

:raise Error: If the connection is closed and reconnect=False."""

if self._sock isNone:ifreconnect:

self.connect()

reconnect=Falseelse:raise err.Error("Already closed")try:

self._execute_command(COMMAND.COM_PING,"")

self._read_ok_packet()exceptException:ifreconnect:

self.connect()

self.ping(False)else:raise

defset_charset(self, charset):#Make sure charset is supported.

encoding =charset_by_name(charset).encoding

self._execute_command(COMMAND.COM_QUERY,"SET NAMES %s" %self.escape(charset))

self._read_packet()

self.charset=charset

self.encoding=encodingdef connect(self, sock=None):

self._closed=Falsetry:if sock isNone:ifself.unix_socket:

sock=socket.socket(socket.AF_UNIX, socket.SOCK_STREAM)

sock.settimeout(self.connect_timeout)

sock.connect(self.unix_socket)

self.host_info= "Localhost via UNIX socket"self._secure=Trueif DEBUG: print('connected using unix_socket')else:

kwargs={}if self.bind_address is notNone:

kwargs['source_address'] =(self.bind_address, 0)whileTrue:try:

sock=socket.create_connection(

(self.host, self.port), self.connect_timeout,**kwargs)break

except(OSError, IOError) as e:if e.errno ==errno.EINTR:continue

raiseself.host_info= "socket %s:%d" %(self.host, self.port)if DEBUG: print('connected using socket')

sock.setsockopt(socket.IPPROTO_TCP, socket.TCP_NODELAY,1)

sock.settimeout(None)

sock.setsockopt(socket.SOL_SOCKET, socket.SO_KEEPALIVE,1)

self._sock=sock

self._rfile= _makefile(sock, 'rb')

self._next_seq_id=0

self._get_server_information()

self._request_authentication()if self.sql_mode is notNone:

c=self.cursor()

c.execute("SET sql_mode=%s", (self.sql_mode,))if self.init_command is notNone:

c=self.cursor()

c.execute(self.init_command)

c.close()

self.commit()if self.autocommit_mode is notNone:

self.autocommit(self.autocommit_mode)exceptBaseException as e:

self._rfile=Noneif sock is notNone:try:

sock.close()except: #noqa

pass

ifisinstance(e, (OSError, IOError, socket.error)):

exc=err.OperationalError(2003,"Can't connect to MySQL server on %r (%s)" %(

self.host, e))#Keep original exception and traceback to investigate error.

exc.original_exception =e

exc.traceback=traceback.format_exc()if DEBUG: print(exc.traceback)raiseexc#If e is neither DatabaseError or IOError, It's a bug.

#But raising AssertionError hides original error.

#So just reraise it.

raise

defwrite_packet(self, payload):"""Writes an entire "mysql packet" in its entirety to the network

addings its length and sequence number."""

#Internal note: when you build packet manualy and calls _write_bytes()

#directly, you should set self._next_seq_id properly.

data = pack_int24(len(payload)) + int2byte(self._next_seq_id) +payloadifDEBUG: dump_packet(data)

self._write_bytes(data)

self._next_seq_id= (self._next_seq_id + 1) % 256

def _read_packet(self, packet_type=MysqlPacket):"""Read an entire "mysql packet" in its entirety from the network

and return a MysqlPacket type that represents the results.

:raise OperationalError: If the connection to the MySQL server is lost.

:raise InternalError: If the packet sequence number is wrong."""buff= b''

whileTrue:

packet_header= self._read_bytes(4)#if DEBUG: dump_packet(packet_header)

btrl, btrh, packet_number= struct.unpack('

bytes_to_read= btrl + (btrh << 16)if packet_number !=self._next_seq_id:

self._force_close()if packet_number ==0:#MariaDB sends error packet with seqno==0 when shutdown

raiseerr.OperationalError(

CR.CR_SERVER_LOST,"Lost connection to MySQL server during query")raiseerr.InternalError("Packet sequence number wrong - got %d expected %d"

%(packet_number, self._next_seq_id))

self._next_seq_id= (self._next_seq_id + 1) % 256recv_data=self._read_bytes(bytes_to_read)ifDEBUG: dump_packet(recv_data)

buff+=recv_data#https://dev.mysql.com/doc/internals/en/sending-more-than-16mbyte.html

if bytes_to_read == 0xffffff:continue

if bytes_to_read

packet.check_error()returnpacketdef_read_bytes(self, num_bytes):

self._sock.settimeout(self._read_timeout)whileTrue:try:

data=self._rfile.read(num_bytes)break

except(IOError, OSError) as e:if e.errno ==errno.EINTR:continueself._force_close()raiseerr.OperationalError(

CR.CR_SERVER_LOST,"Lost connection to MySQL server during query (%s)" %(e,))if len(data)

self._force_close()raiseerr.OperationalError(

CR.CR_SERVER_LOST,"Lost connection to MySQL server during query")returndatadef_write_bytes(self, data):

self._sock.settimeout(self._write_timeout)try:

self._sock.sendall(data)exceptIOError as e:

self._force_close()raiseerr.OperationalError(

CR.CR_SERVER_GONE_ERROR,"MySQL server has gone away (%r)" %(e,))def _read_query_result(self, unbuffered=False):

self._result=Noneifunbuffered:try:

result=MySQLResult(self)

result.init_unbuffered_query()except:

result.unbuffered_active=False

result.connection=Noneraise

else:

result=MySQLResult(self)

result.read()

self._result=resultif result.server_status is notNone:

self.server_status=result.server_statusreturnresult.affected_rowsdefinsert_id(self):ifself._result:returnself._result.insert_idelse:return0def_execute_command(self, command, sql):""":raise InterfaceError: If the connection is closed.

:raise ValueError: If no username was specified."""

if notself._sock:raise err.InterfaceError("(0, '')")#If the last query was unbuffered, make sure it finishes before

#sending new commands

if self._result is notNone:ifself._result.unbuffered_active:

warnings.warn("Previous unbuffered result was left incomplete")

self._result._finish_unbuffered_query()whileself._result.has_next:

self.next_result()

self._result=Noneifisinstance(sql, text_type):

sql=sql.encode(self.encoding)

packet_size= min(MAX_PACKET_LEN, len(sql) + 1) #+1 is for command

#tiny optimization: build first packet manually instead of

#calling self..write_packet()

prelude = struct.pack('

packet= prelude + sql[:packet_size-1]

self._write_bytes(packet)ifDEBUG: dump_packet(packet)

self._next_seq_id= 1

if packet_size

packet_size=min(MAX_PACKET_LEN, len(sql))

self.write_packet(sql[:packet_size])

sql=sql[packet_size:]if not sql and packet_size

def_request_authentication(self):#https://dev.mysql.com/doc/internals/en/connection-phase-packets.html#packet-Protocol::HandshakeResponse

if int(self.server_version.split('.', 1)[0]) >= 5:

self.client_flag|=CLIENT.MULTI_RESULTSif self.user isNone:raise ValueError("Did not specify a username")

charset_id=charset_by_name(self.charset).idifisinstance(self.user, text_type):

self.user=self.user.encode(self.encoding)

data_init= struct.pack('

self.write_packet(data_init)

self._sock= self.ctx.wrap_socket(self._sock, server_hostname=self.host)

self._rfile= _makefile(self._sock, 'rb')

self._secure=True

data= data_init + self.user + b'\0'authresp= b''plugin_name=Noneif self._auth_plugin_name in ('', 'mysql_native_password'):

authresp=_auth.scramble_native_password(self.password, self.salt)elif self._auth_plugin_name == 'caching_sha2_password':

plugin_name= b'caching_sha2_password'

ifself.password:ifDEBUG:print("caching_sha2: trying fast path")

authresp=_auth.scramble_caching_sha2(self.password, self.salt)else:ifDEBUG:print("caching_sha2: empty password")elif self._auth_plugin_name == 'sha256_password':

plugin_name= b'sha256_password'

if self.ssl and self.server_capabilities &CLIENT.SSL:

authresp= self.password + b'\0'

elifself.password:

authresp= b'\1' #request public key

else:

authresp= b'\0' #empty password

if self.server_capabilities &CLIENT.PLUGIN_AUTH_LENENC_CLIENT_DATA:

data+= lenenc_int(len(authresp)) +authrespelif self.server_capabilities &CLIENT.SECURE_CONNECTION:

data+= struct.pack('B', len(authresp)) +authrespelse: #pragma: no cover - not testing against servers without secure auth (>=5.0)

data += authresp + b'\0'

if self.db and self.server_capabilities &CLIENT.CONNECT_WITH_DB:ifisinstance(self.db, text_type):

self.db=self.db.encode(self.encoding)

data+= self.db + b'\0'

if self.server_capabilities &CLIENT.PLUGIN_AUTH:

data+= (plugin_name or b'') + b'\0'

if self.server_capabilities &CLIENT.CONNECT_ATTRS:

connect_attrs= b''

for k, v inself._connect_attrs.items():

k= k.encode('utf8')

connect_attrs+= struct.pack('B', len(k)) +k

v= v.encode('utf8')

connect_attrs+= struct.pack('B', len(v)) +v

data+= struct.pack('B', len(connect_attrs)) +connect_attrs

self.write_packet(data)

auth_packet=self._read_packet()#if authentication method isn't accepted the first byte

#will have the octet 254

ifauth_packet.is_auth_switch_request():if DEBUG: print("received auth switch")#https://dev.mysql.com/doc/internals/en/connection-phase-packets.html#packet-Protocol::AuthSwitchRequest

auth_packet.read_uint8() #0xfe packet identifier

plugin_name =auth_packet.read_string()if self.server_capabilities & CLIENT.PLUGIN_AUTH and plugin_name is notNone:

auth_packet=self._process_auth(plugin_name, auth_packet)else:#send legacy handshake

data = _auth.scramble_old_password(self.password, self.salt) + b'\0'self.write_packet(data)

auth_packet=self._read_packet()elifauth_packet.is_extra_auth_data():ifDEBUG:print("received extra data")#https://dev.mysql.com/doc/internals/en/successful-authentication.html

if self._auth_plugin_name == "caching_sha2_password":

auth_packet=_auth.caching_sha2_password_auth(self, auth_packet)elif self._auth_plugin_name == "sha256_password":

auth_packet=_auth.sha256_password_auth(self, auth_packet)else:raise err.OperationalError("Received extra packet for auth method %r", self._auth_plugin_name)if DEBUG: print("Succeed to auth")def_process_auth(self, plugin_name, auth_packet):

handler=self._get_auth_plugin_handler(plugin_name)ifhandler:try:returnhandler.authenticate(auth_packet)exceptAttributeError:if plugin_name != b'dialog':raise err.OperationalError(2059, "Authentication plugin '%s'"

"not loaded: - %r missing authenticate method" %(plugin_name, type(handler)))if plugin_name == b"caching_sha2_password":return_auth.caching_sha2_password_auth(self, auth_packet)elif plugin_name == b"sha256_password":return_auth.sha256_password_auth(self, auth_packet)elif plugin_name == b"mysql_native_password":

data=_auth.scramble_native_password(self.password, auth_packet.read_all())elif plugin_name == b"mysql_old_password":

data= _auth.scramble_old_password(self.password, auth_packet.read_all()) + b'\0'

elif plugin_name == b"mysql_clear_password":#https://dev.mysql.com/doc/internals/en/clear-text-authentication.html

data = self.password + b'\0'

elif plugin_name == b"dialog":

pkt=auth_packetwhileTrue:

flag=pkt.read_uint8()

echo= (flag & 0x06) == 0x02last= (flag & 0x01) == 0x01prompt=pkt.read_all()if prompt == b"Password:":

self.write_packet(self.password+ b'\0')elifhandler:

resp= 'no response - TypeError within plugin.prompt method'

try:

resp=handler.prompt(echo, prompt)

self.write_packet(resp+ b'\0')exceptAttributeError:raise err.OperationalError(2059, "Authentication plugin '%s'"\"not loaded: - %r missing prompt method" %(plugin_name, handler))exceptTypeError:raise err.OperationalError(2061, "Authentication plugin '%s'"\"%r didn't respond with string. Returned '%r' to prompt %r" %(plugin_name, handler, resp, prompt))else:raise err.OperationalError(2059, "Authentication plugin '%s' (%r) not configured" %(plugin_name, handler))

pkt=self._read_packet()

pkt.check_error()if pkt.is_ok_packet() orlast:break

returnpktelse:raise err.OperationalError(2059, "Authentication plugin '%s' not configured" %plugin_name)

self.write_packet(data)

pkt=self._read_packet()

pkt.check_error()returnpktdef_get_auth_plugin_handler(self, plugin_name):

plugin_class=self._auth_plugin_map.get(plugin_name)if not plugin_class andisinstance(plugin_name, bytes):

plugin_class= self._auth_plugin_map.get(plugin_name.decode('ascii'))ifplugin_class:try:

handler=plugin_class(self)exceptTypeError:raise err.OperationalError(2059, "Authentication plugin '%s'"

"not loaded: - %r cannot be constructed with connection object" %(plugin_name, plugin_class))else:

handler=Nonereturnhandler#_mysql support

defthread_id(self):returnself.server_thread_id[0]defcharacter_set_name(self):returnself.charsetdefget_host_info(self):returnself.host_infodefget_proto_info(self):returnself.protocol_versiondef_get_server_information(self):

i=0

packet=self._read_packet()

data=packet.get_all_data()

self.protocol_version= byte2int(data[i:i+1])

i+= 1server_end= data.find(b'\0', i)

self.server_version= data[i:server_end].decode('latin1')

i= server_end + 1self.server_thread_id= struct.unpack('

i+= 4self.salt= data[i:i+8]

i+= 9 #8 + 1(filler)

self.server_capabilities= struct.unpack('

i+= 2

if len(data) >= i + 6:

lang, stat, cap_h, salt_len= struct.unpack('

i+= 6

#TODO: deprecate server_language and server_charset.

#mysqlclient-python doesn't provide it.

self.server_language =langtry:

self.server_charset=charset_by_id(lang).nameexceptKeyError:#unknown collation

self.server_charset =None

self.server_status=statif DEBUG: print("server_status: %x" %stat)

self.server_capabilities|= cap_h << 16

if DEBUG: print("salt_len:", salt_len)

salt_len= max(12, salt_len - 9)#reserved

i += 10

if len(data) >= i +salt_len:#salt_len includes auth_plugin_data_part_1 and filler

self.salt += data[i:i+salt_len]

i+=salt_len

i+=1

#AUTH PLUGIN NAME may appear here.

if self.server_capabilities & CLIENT.PLUGIN_AUTH and len(data) >=i:#Due to Bug#59453 the auth-plugin-name is missing the terminating

#NUL-char in versions prior to 5.5.10 and 5.6.2.

#ref: https://dev.mysql.com/doc/internals/en/connection-phase-packets.html#packet-Protocol::Handshake

#didn't use version checks as mariadb is corrected and reports

#earlier than those two.

server_end = data.find(b'\0', i)if server_end < 0: #pragma: no cover - very specific upstream bug

#not found \0 and last field so take it all

self._auth_plugin_name = data[i:].decode('utf-8')else:

self._auth_plugin_name= data[i:server_end].decode('utf-8')defget_server_info(self):returnself.server_version

Warning=err.Warning

Error=err.Error

InterfaceError=err.InterfaceError

DatabaseError=err.DatabaseError

DataError=err.DataError

OperationalError=err.OperationalError

IntegrityError=err.IntegrityError

InternalError=err.InternalError

ProgrammingError=err.ProgrammingError

NotSupportedError=err.NotSupportedErrorclassMySQLResult(object):def __init__(self, connection):""":type connection: Connection"""self.connection=connection

self.affected_rows=None

self.insert_id=None

self.server_status=None

self.warning_count=0

self.message=None

self.field_count=0

self.description=None

self.rows=None

self.has_next=None

self.unbuffered_active=Falsedef __del__(self):ifself.unbuffered_active:

self._finish_unbuffered_query()defread(self):try:

first_packet=self.connection._read_packet()iffirst_packet.is_ok_packet():

self._read_ok_packet(first_packet)eliffirst_packet.is_load_local_packet():

self._read_load_local_packet(first_packet)else:

self._read_result_packet(first_packet)finally:

self.connection=Nonedefinit_unbuffered_query(self):""":raise OperationalError: If the connection to the MySQL server is lost.

:raise InternalError:"""self.unbuffered_active=True

first_packet=self.connection._read_packet()iffirst_packet.is_ok_packet():

self._read_ok_packet(first_packet)

self.unbuffered_active=False

self.connection=Noneeliffirst_packet.is_load_local_packet():

self._read_load_local_packet(first_packet)

self.unbuffered_active=False

self.connection=Noneelse:

self.field_count=first_packet.read_length_encoded_integer()

self._get_descriptions()#Apparently, MySQLdb picks this number because it's the maximum

#value of a 64bit unsigned integer. Since we're emulating MySQLdb,

#we set it to this instead of None, which would be preferred.

self.affected_rows = 18446744073709551615

def_read_ok_packet(self, first_packet):

ok_packet=OKPacketWrapper(first_packet)

self.affected_rows=ok_packet.affected_rows

self.insert_id=ok_packet.insert_id

self.server_status=ok_packet.server_status

self.warning_count=ok_packet.warning_count

self.message=ok_packet.message

self.has_next=ok_packet.has_nextdef_read_load_local_packet(self, first_packet):if notself.connection._local_infile:raiseRuntimeError("**WARN**: Received LOAD_LOCAL packet but local_infile option is false.")

load_packet=LoadLocalPacketWrapper(first_packet)

sender=LoadLocalFile(load_packet.filename, self.connection)try:

sender.send_data()except:

self.connection._read_packet()#skip ok packet

raiseok_packet=self.connection._read_packet()if not ok_packet.is_ok_packet(): #pragma: no cover - upstream induced protocol error

raise err.OperationalError(2014, "Commands Out of Sync")

self._read_ok_packet(ok_packet)def_check_packet_is_eof(self, packet):if notpacket.is_eof_packet():returnFalse#TODO: Support CLIENT.DEPRECATE_EOF

#1) Add DEPRECATE_EOF to CAPABILITIES

#2) Mask CAPABILITIES with server_capabilities

#3) if server_capabilities & CLIENT.DEPRECATE_EOF: use OKPacketWrapper instead of EOFPacketWrapper

wp =EOFPacketWrapper(packet)

self.warning_count=wp.warning_count

self.has_next=wp.has_nextreturnTruedef_read_result_packet(self, first_packet):

self.field_count=first_packet.read_length_encoded_integer()

self._get_descriptions()

self._read_rowdata_packet()def_read_rowdata_packet_unbuffered(self):#Check if in an active query

if notself.unbuffered_active:return

#EOF

packet =self.connection._read_packet()ifself._check_packet_is_eof(packet):

self.unbuffered_active=False

self.connection=None

self.rows=Nonereturnrow=self._read_row_from_packet(packet)

self.affected_rows= 1self.rows= (row,) #rows should tuple of row for MySQL-python compatibility.

returnrowdef_finish_unbuffered_query(self):#After much reading on the MySQL protocol, it appears that there is,

#in fact, no way to stop MySQL from sending all the data after

#executing a query, so we just spin, and wait for an EOF packet.

whileself.unbuffered_active:

packet=self.connection._read_packet()ifself._check_packet_is_eof(packet):

self.unbuffered_active=False

self.connection= None #release reference to kill cyclic reference.

def_read_rowdata_packet(self):"""Read a rowdata packet for each data row in the result set."""rows=[]whileTrue:

packet=self.connection._read_packet()ifself._check_packet_is_eof(packet):

self.connection= None #release reference to kill cyclic reference.

breakrows.append(self._read_row_from_packet(packet))

self.affected_rows=len(rows)

self.rows=tuple(rows)def_read_row_from_packet(self, packet):

row=[]for encoding, converter inself.converters:try:

data=packet.read_length_coded_string()exceptIndexError:#No more columns in this row

#See https://github.com/PyMySQL/PyMySQL/pull/434

break

if data is notNone:if encoding is notNone:

data=data.decode(encoding)if DEBUG: print("DEBUG: DATA =", data)if converter is notNone:

data=converter(data)

row.append(data)returntuple(row)def_get_descriptions(self):"""Read a column descriptor packet for each column in the result."""self.fields=[]

self.converters=[]

use_unicode=self.connection.use_unicode

conn_encoding=self.connection.encoding

description=[]for i inrange_type(self.field_count):

field=self.connection._read_packet(FieldDescriptorPacket)

self.fields.append(field)

description.append(field.description())

field_type=field.type_codeifuse_unicode:if field_type ==FIELD_TYPE.JSON:#When SELECT from JSON column: charset = binary

#When SELECT CAST(... AS JSON): charset = connection encoding

#This behavior is different from TEXT / BLOB.

#We should decode result by connection encoding regardless charsetnr.

#See https://github.com/PyMySQL/PyMySQL/issues/488

encoding = conn_encoding #SELECT CAST(... AS JSON)

elif field_type inTEXT_TYPES:if field.charsetnr == 63: #binary

#TEXTs with charset=binary means BINARY types.

encoding =Noneelse:

encoding=conn_encodingelse:#Integers, Dates and Times, and other basic data is encoded in ascii

encoding = 'ascii'

else:

encoding=None

converter=self.connection.decoders.get(field_type)if converter isconverters.through:

converter=Noneif DEBUG: print("DEBUG: field={}, converter={}".format(field, converter))

self.converters.append((encoding, converter))

eof_packet=self.connection._read_packet()assert eof_packet.is_eof_packet(), 'Protocol error, expecting EOF'self.description=tuple(description)classLoadLocalFile(object):def __init__(self, filename, connection):

self.filename=filename

self.connection=connectiondefsend_data(self):"""Send data packets from the local file to the server"""

if notself.connection._sock:raise err.InterfaceError("(0, '')")

conn=self.connectiontry:

with open(self.filename,'rb') as open_file:

packet_size= min(conn.max_allowed_packet, 16*1024) #16KB is efficient enough

whileTrue:

chunk=open_file.read(packet_size)if notchunk:breakconn.write_packet(chunk)exceptIOError:raise err.OperationalError(1017, "Can't find file '{0}'".format(self.filename))finally:#send the empty packet to signify we are done sending data

conn.write_packet(b'')