PyMySQL的安装、使用

# Python implementation of the MySQL client-server protocol # http://forge.mysql.com/wiki/MySQL_Internals_ClientServer_Protocol try: import hashlib sha_new = lambda *args, **kwargs: hashlib.new("sha1", *args, **kwargs) except ImportError: import sha sha_new = sha.new import socket try: import ssl SSL_ENABLED = True except ImportError: SSL_ENABLED = False import struct import sys import os import configparser try: import io as StringIO except ImportError: import io try: import getpass DEFAULT_USER = getpass.getuser() except ImportError: DEFAULT_USER = None from .charset import MBLENGTH, charset_by_name, charset_by_id from .cursors import Cursor from .constants import FIELD_TYPE, FLAG from .constants import SERVER_STATUS from .constants.CLIENT import * from .constants.COMMAND import * from .util import join_bytes, byte2int, int2byte from .converters import escape_item, encoders, decoders from .err import raise_mysql_exception, Warning, Error, \ InterfaceError, DataError, DatabaseError, OperationalError, \ IntegrityError, InternalError, NotSupportedError, ProgrammingError DEBUG = False NULL_COLUMN = 251 UNSIGNED_CHAR_COLUMN = 251 UNSIGNED_SHORT_COLUMN = 252 UNSIGNED_INT24_COLUMN = 253 UNSIGNED_INT64_COLUMN = 254 UNSIGNED_CHAR_LENGTH = 1 UNSIGNED_SHORT_LENGTH = 2 UNSIGNED_INT24_LENGTH = 3 UNSIGNED_INT64_LENGTH = 8 DEFAULT_CHARSET = 'latin1' def dump_packet(data): def is_ascii(data): if byte2int(data) >= 65 and byte2int(data) <= 122: #data.isalnum(): return data return '.' print("packet length %d" % len(data)) print("method call[1]: %s" % sys._getframe(1).f_code.co_name) print("method call[2]: %s" % sys._getframe(2).f_code.co_name) print("method call[3]: %s" % sys._getframe(3).f_code.co_name) print("method call[4]: %s" % sys._getframe(4).f_code.co_name) print("method call[5]: %s" % sys._getframe(5).f_code.co_name) print("-" * 88) dump_data = [data[i:i+16] for i in range(len(data)) if i%16 == 0] for d in dump_data: print(' '.join(["%02X" % byte2int(x) for x in d]) + \ ' ' * (16 - len(d)) + ' ' * 2 + \ ' '.join(["%s" % is_ascii(x) for x in d])) print("-" * 88) print("") def _scramble(password, message): if password == None or len(password) == 0: return int2byte(0) if DEBUG: print('password=' + password) stage1 = sha_new(password).digest() stage2 = sha_new(stage1).digest() s = sha_new() s.update(message) s.update(stage2) result = s.digest() return _my_crypt(result, stage1) def _my_crypt(message1, message2): length = len(message1) result = struct.pack('B', length) for i in range(length): x = (struct.unpack('B', message1[i:i+1])[0] ^ \ struct.unpack('B', message2[i:i+1])[0]) result += struct.pack('B', x) return result # old_passwords support ported from libmysql/password.c SCRAMBLE_LENGTH_323 = 8 class RandStruct_323(object): def __init__(self, seed1, seed2): self.max_value = 0x3FFFFFFF self.seed1 = seed1 % self.max_value self.seed2 = seed2 % self.max_value def my_rnd(self): self.seed1 = (self.seed1 * 3 + self.seed2) % self.max_value self.seed2 = (self.seed1 + self.seed2 + 33) % self.max_value return float(self.seed1) / float(self.max_value) def _scramble_323(password, message): hash_pass = _hash_password_323(password) hash_message = _hash_password_323(message[:SCRAMBLE_LENGTH_323]) hash_pass_n = struct.unpack(">LL", hash_pass) hash_message_n = struct.unpack(">LL", hash_message) rand_st = RandStruct_323(hash_pass_n[0] ^ hash_message_n[0], hash_pass_n[1] ^ hash_message_n[1]) outbuf = io.StringIO() for _ in range(min(SCRAMBLE_LENGTH_323, len(message))): outbuf.write(int2byte(int(rand_st.my_rnd() * 31) + 64)) extra = int2byte(int(rand_st.my_rnd() * 31)) out = outbuf.getvalue() outbuf = io.StringIO() for c in out: outbuf.write(int2byte(byte2int(c) ^ byte2int(extra))) return outbuf.getvalue() def _hash_password_323(password): nr = 1345345333 add = 7 nr2 = 0x12345671 for c in [byte2int(x) for x in password if x not in (' ', '\t')]: nr^= (((nr & 63)+add)*c)+ (nr << 8) & 0xFFFFFFFF nr2= (nr2 + ((nr2 << 8) ^ nr)) & 0xFFFFFFFF add= (add + c) & 0xFFFFFFFF r1 = nr & ((1 << 31) - 1) # kill sign bits r2 = nr2 & ((1 << 31) - 1) # pack return struct.pack(">LL", r1, r2) def pack_int24(n): return struct.pack('BBB', n&0xFF, (n>>8)&0xFF, (n>>16)&0xFF) def unpack_uint16(n): return struct.unpack('<H', n[0:2])[0] # TODO: stop using bit-shifting in these functions... # TODO: rename to "uint" to make it clear they're unsigned... def unpack_int24(n): return struct.unpack('B',n[0])[0] + (struct.unpack('B', n[1])[0] << 8) +\ (struct.unpack('B',n[2])[0] << 16) def unpack_int32(n): return struct.unpack('B',n[0])[0] + (struct.unpack('B', n[1])[0] << 8) +\ (struct.unpack('B',n[2])[0] << 16) + (struct.unpack('B', n[3])[0] << 24) def unpack_int64(n): return struct.unpack('B',n[0])[0] + (struct.unpack('B', n[1])[0]<<8) +\ (struct.unpack('B',n[2])[0] << 16) + (struct.unpack('B',n[3])[0]<<24)+\ (struct.unpack('B',n[4])[0] << 32) + (struct.unpack('B',n[5])[0]<<40)+\ (struct.unpack('B',n[6])[0] << 48) + (struct.unpack('B',n[7])[0]<<56) def defaulterrorhandler(connection, cursor, errorclass, errorvalue): err = errorclass, errorvalue if DEBUG: raise if cursor: cursor.messages.append(err) else: connection.messages.append(err) del cursor del connection if not issubclass(errorclass, Error): raise Error(errorclass, errorvalue) else: raise errorclass(errorvalue) class MysqlPacket(object): """Representation of a MySQL response packet. Reads in the packet from the network socket, removes packet header and provides an interface for reading/parsing the packet results.""" def __init__(self, connection): self.connection = connection self.__position = 0 self.__recv_packet() def __recv_packet(self): """Parse the packet header and read entire packet payload into buffer.""" packet_header = self.connection.rfile.read(4) if len(packet_header) < 4: raise OperationalError(2013, "Lost connection to MySQL server during query") if DEBUG: dump_packet(packet_header) packet_length_bin = packet_header[:3] self.__packet_number = byte2int(packet_header[3]) # TODO: check packet_num is correct (+1 from last packet) bin_length = packet_length_bin + int2byte(0) # pad little-endian number bytes_to_read = struct.unpack('<I', bin_length)[0] recv_data = self.connection.rfile.read(bytes_to_read) if len(recv_data) < bytes_to_read: raise OperationalError(2013, "Lost connection to MySQL server during query") if DEBUG: dump_packet(recv_data) self.__data = recv_data def packet_number(self): return self.__packet_number def get_all_data(self): return self.__data def read(self, size): """Read the first 'size' bytes in packet and advance cursor past them.""" result = self.peek(size) self.advance(size) return result def read_all(self): """Read all remaining data in the packet. (Subsequent read() or peek() will return errors.) """ result = self.__data[self.__position:] self.__position = None # ensure no subsequent read() or peek() return result def advance(self, length): """Advance the cursor in data buffer 'length' bytes.""" new_position = self.__position + length if new_position < 0 or new_position > len(self.__data): raise Exception('Invalid advance amount (%s) for cursor. ' 'Position=%s' % (length, new_position)) self.__position = n