def _scramble_password(self, passwd, seed): """Scramble a password ready to send to MySQL""" hash4 = None try: hash1 = sha1(passwd).digest() hash2 = sha1(hash1).digest() # Password as found in mysql.user() hash3 = sha1(seed + hash2).digest() xored = [ utils.intread(h1) ^ utils.intread(h3) for (h1, h3) in zip(hash1, hash3) ] hash4 = struct.pack('20B', *xored) except Exception as err: raise errors.InterfaceError('Failed scrambling password; %s' % err) return hash4
def _fetch_warnings(self): """ Fetch warnings doing a SHOW WARNINGS. Can be called after getting the result. Returns a result set or None when there were no warnings. """ res = [] try: c = self._connection.cursor() cnt = c.execute("SHOW WARNINGS") res = c.fetchall() c.close() except StandardError, e: raise errors.InterfaceError, errors.InterfaceError( "Failed getting warnings; %s" % e), sys.exc_info()[2]
def _row_to_python(self, rowdata, desc=None): """Convert the row from MySQL to Python types""" res = () try: if not desc: desc = self.description for idx, v in enumerate(rowdata): flddsc = desc[idx] res += (self._connection.converter.to_python(flddsc, v), ) except Exception as e: raise errors.InterfaceError( "Failed converting row to Python types; %s" % e) else: return res return None
def fetchall(self): """Returns all rows of a query result set """ if not self._have_unread_result(): raise errors.InterfaceError(_ERR_NO_RESULT_TO_FETCH) (rows, eof) = yield from self._connection.get_rows() if self._nextrow[0]: rows.insert(0, self._nextrow[0]) res = [self._row_to_python(row, self.description) for row in rows] yield from self._handle_eof(eof) rowcount = len(rows) if rowcount >= 0 and self._rowcount == -1: self._rowcount = 0 self._rowcount += rowcount return res
def _row_to_python(self, rowdata, desc=None): """Convert the row from MySQL to Python types""" res = [] to_python = self._connection.converter.to_python try: if not desc: desc = self.description for flddsc, val in zip(desc, rowdata): res.append(to_python(flddsc, val),) except Exception as err: raise errors.InterfaceError( "Failed converting row to Python types; %s" % err) else: return tuple(res) return None
def _handle_load_data_infile(self, filename): """Handle a LOAD DATA INFILE LOCAL request""" try: data_file = open(filename, 'rb') except IOError: # Send a empty packet to cancel the operation try: self._socket.send(b'') except AttributeError: raise errors.OperationalError( "MySQL Connection not available.") raise errors.InterfaceError( "File '{0}' could not be read".format(filename)) return self._handle_ok(self._send_data(data_file, send_empty_packet=True))
def cmd_shutdown(self, shutdown_type=None): """Shut down the MySQL Server This method sends the SHUTDOWN command to the MySQL server and is only possible if the current user has SUPER privileges. The result is a dictionary containing the OK packet information. Note: Most applications and scripts do not the SUPER privilege. Returns a dict() """ if shutdown_type: if not ShutdownType.get_info(shutdown_type): raise errors.InterfaceError("Invalid shutdown type") atype = shutdown_type else: atype = ShutdownType.SHUTDOWN_DEFAULT return self._handle_eof((yield from self._send_cmd(ServerCmd.SHUTDOWN, atype)))
def ping(self, reconnect=False, attempts=1, delay=0): """Check availability to the MySQL server When reconnect is set to True, one or more attempts are made to try to reconnect to the MySQL server using the reconnect()-method. delay is the number of seconds to wait between each retry. When the connection is not available, an InterfaceError is raised. Use the is_connected()-method if you just want to check the connection without raising an error. Raises InterfaceError on errors. """ try: yield from self.cmd_ping() except: if reconnect: yield from self.reconnect(attempts=attempts, delay=delay) else: raise errors.InterfaceError("Connection to MySQL is" " not available.")
def _fetch_warnings(self): """ Fetch warnings doing a SHOW WARNINGS. Can be called after getting the result. Returns a result set or None when there were no warnings. """ res = [] try: cur = self._connection.cursor() cur.execute("SHOW WARNINGS") res = cur.fetchall() cur.close() except Exception as err: raise errors.InterfaceError("Failed getting warnings; %s" % err) if self._connection.raise_on_warnings is True: raise errors.get_mysql_exception(res[0][1], res[0][2]) else: if len(res): return res return None
def parse_column(self, packet): """Parse a MySQL column-packet""" (packet, _) = utils.read_lc_string(packet[4:]) # catalog (packet, _) = utils.read_lc_string(packet) # db (packet, _) = utils.read_lc_string(packet) # table (packet, _) = utils.read_lc_string(packet) # org_table (packet, name) = utils.read_lc_string(packet) # name (packet, _) = utils.read_lc_string(packet) # org_name try: (_, _, field_type, flags, _) = struct.unpack('<xHIBHBxx', packet) except struct.error: raise errors.InterfaceError("Failed parsing column information") return ( name, field_type, None, # display_size None, # internal_size None, # precision None, # scale ~flags & FieldFlag.NOT_NULL, # null_ok flags, # MySQL specific )
def cmd_query(self, query): """Send a query to the MySQL server This method send the query to the MySQL server and returns the result. If there was a text result, a tuple will be returned consisting of the number of columns and a list containing information about these columns. When the query doesn't return a text result, the OK or EOF packet information as dictionary will be returned. In case the result was an error, exception errors.Error will be raised. Returns a tuple() """ if not isinstance(query, bytes): query = query.encode('utf-8') result = yield from self._handle_result((yield from self._send_cmd(ServerCmd.QUERY, query))) if self._have_next_result: raise errors.InterfaceError( 'Use cmd_query_iter for statements with multiple queries.') return result
def fetchall(self): if self._rows is None: raise errors.InterfaceError("No result set to fetch from.") return [r for r in self._rows]
def callproc(self, procname, args=()): """Calls a stored procedue with the given arguments The arguments will be set during this session, meaning they will be called like _<procname>__arg<nr> where <nr> is an enumeration (+1) of the arguments. Coding Example: 1) Definining the Stored Routine in MySQL: CREATE PROCEDURE multiply(IN pFac1 INT, IN pFac2 INT, OUT pProd INT) BEGIN SET pProd := pFac1 * pFac2; END 2) Executing in Python: args = (5,5,0) # 0 is to hold pprod cursor.callproc('multiply', args) print cursor.fetchone() Does not return a value, but a result set will be available when the CALL-statement execute successfully. Raises exceptions when something is wrong. """ if not procname or not isinstance(procname, str): raise ValueError("procname must be a string") if not isinstance(args, (tuple, list)): raise ValueError("args must be a sequence") argfmt = "@_%s_arg%d" self._stored_results = [] results = [] try: argnames = [] if args: for idx, arg in enumerate(args): argname = argfmt % (procname, idx + 1) argnames.append(argname) self.execute("SET %s=%%s" % (argname), (arg, )) call = "CALL %s(%s)" % (procname, ','.join(argnames)) for result in self._connection.cmd_query_iter(call): if 'columns' in result: tmp = MySQLCursorBuffered(self._connection._get_self()) tmp._handle_result(result) results.append(tmp) if argnames: select = "SELECT %s" % ','.join(argnames) self.execute(select) self._stored_results = results return self.fetchone() else: self._stored_results = results return () except errors.Error: raise except StandardError, e: raise errors.InterfaceError("Failed calling stored routine; %s" % e)
def _set_connection(self, connection): try: self._connection = weakref.proxy(connection) self._connection._protocol except (AttributeError, TypeError): raise errors.InterfaceError(errno=2048)
raise errors.ProgrammingError( "Wrong number of arguments during string formatting") else: stmt = operation if multi: self._executed = stmt self._executed_list = [] return self._execute_iter(self._connection.cmd_query_iter(stmt)) else: self._executed = stmt try: self._handle_result(self._connection.cmd_query(stmt)) except errors.InterfaceError, err: if self._connection._have_next_result: raise errors.InterfaceError( "Use multi=True when executing multiple statements") raise return None def executemany(self, operation, seq_params): """Execute the given operation multiple times The executemany() method will execute the operation iterating over the list of parameters in seq_params. Example: Inserting 3 new employees and their phone number data = [ ('Jane','555-001'), ('Joe', '555-001'), ('John', '555-003')
def executemany(self, operation, seq_params): """Execute the given operation multiple times The executemany() method will execute the operation iterating over the list of parameters in seq_params. Example: Inserting 3 new employees and their phone number data = [ ('Jane','555-001'), ('Joe', '555-001'), ('John', '555-003') ] stmt = "INSERT INTO employees (name, phone) VALUES ('%s','%s')" cursor.executemany(stmt, data) INSERT statements are optimized by batching the data, that is using the MySQL multiple rows syntax. Results are discarded. If they are needed, consider looping over data using the execute() method. """ if not operation: return if self._have_unread_result(): raise errors.InternalError("Unread result found.") elif len(RE_SQL_SPLIT_STMTS.split(operation)) > 1: raise errors.InternalError( "executemany() does not support multiple statements") # Optimize INSERTs by batching them if re.match(RE_SQL_INSERT_STMT, operation): if not seq_params: self._rowcount = 0 return tmp = re.sub(RE_SQL_ON_DUPLICATE, '', re.sub(RE_SQL_COMMENT, '', operation)) matches = re.search(RE_SQL_INSERT_VALUES, tmp) if not matches: raise errors.InterfaceError( "Failed rewriting statement for multi-row INSERT. " "Check SQL syntax.") fmt = matches.group(1) values = [] for params in seq_params: values.append(fmt % self._process_params(params)) operation = operation.replace(matches.group(1), ','.join(values), 1) return self.execute(operation) rowcnt = 0 try: for params in seq_params: self.execute(operation, params) if self.with_rows and self._have_unread_result(): self.fetchall() rowcnt += self._rowcount except (ValueError, TypeError) as err: raise errors.InterfaceError("Failed executing the operation; %s" % err) except: # Raise whatever execute() raises raise self._rowcount = rowcnt
def make_stmt_execute(self, statement_id, data=(), parameters=(), flags=0, long_data_used=None, charset='utf8'): """Make a MySQL packet with the Statement Execute command""" iteration_count = 1 null_bitmap = [0] * ((len(data) + 7) // 8) values = [] types = [] packed = '' if long_data_used is None: long_data_used = {} if parameters and data: if len(data) != len(parameters): raise errors.InterfaceError( "Failed executing prepared statement: data values does not" " match number of parameters") for pos, _ in enumerate(parameters): value = data[pos] flags = 0 if value is None: null_bitmap[(pos // 8)] |= 1 << (pos % 8) continue elif pos in long_data_used: if long_data_used[pos][0]: # We suppose binary data field_type = FieldType.BLOB else: # We suppose text data field_type = FieldType.STRING elif isinstance(value, (int, long)): (packed, field_type, flags) = self._prepare_binary_integer(value) values.append(packed) elif isinstance(value, str): values.append(utils.intstore(len(value)) + value) field_type = FieldType.VARCHAR elif isinstance(value, unicode): value = value.encode(charset) values.append(utils.intstore(len(value)) + value) field_type = FieldType.VARCHAR elif isinstance(value, Decimal): values.append(utils.intstore(len(str(value))) + str(value)) field_type = FieldType.DECIMAL elif isinstance(value, float): values.append(struct.pack('d', value)) field_type = FieldType.DOUBLE elif isinstance(value, (datetime.datetime, datetime.date)): (packed, field_type) = self._prepare_binary_timestamp( value) values.append(packed) elif isinstance(value, (datetime.timedelta, datetime.time)): (packed, field_type) = self._prepare_binary_time(value) values.append(packed) else: raise errors.ProgrammingError( "MySQL binary protocol can not handle " "'{classname}' objects".format( classname=value.__class__.__name__)) types.append(utils.int1store(field_type) + utils.int1store(flags)) packet = ( utils.int4store(statement_id), utils.int1store(flags), utils.int4store(iteration_count), ''.join([struct.pack('B', bit) for bit in null_bitmap]), utils.int1store(1), ''.join(types), ''.join(values) ) return ''.join(packet)
def parse_column_count(self, packet): """Parse a MySQL packet with the number of columns in result set""" try: return utils.read_lc_int(packet[4:])[1] except (struct.error, ValueError): raise errors.InterfaceError("Failed parsing column count")
if not chunk: raise errors.InterfaceError(errno=2013) packet += chunk rest = packet_totlen - len(packet) return packet except socket.timeout, err: raise errors.InterfaceError(errno=2013) except socket.error, err: try: msg = err.errno if msg is None: msg = str(err) except AttributeError: msg = str(err) raise errors.InterfaceError(errno=2055, values=(self.get_address(), msg)) recv = recv_plain def _split_zipped_payload(self, packet_bunch): """Split compressed payload""" while packet_bunch: payload_length = struct.unpack("<I", packet_bunch[0:3] + '\x00')[0] self._packet_queue.append(packet_bunch[0:payload_length + 4]) packet_bunch = packet_bunch[payload_length + 4:] def recv_compressed(self): """Receive compressed packets from the MySQL server""" try: return self._packet_queue.popleft() except IndexError:
def parse_auth_more_data(self, packet): """Parse a MySQL AuthMoreData-packet""" if not packet[4] == 1: raise errors.InterfaceError("Failed parsing AuthMoreData packet") return packet[5:]
header = self.sock.recv(4) if len(header) < 4: raise errors.InterfaceError(errno=2013) self._packet_number = ord(header[3]) payload_length = struct.unpack("<I", header[0:3] + '\x00')[0] payload = '' while len(payload) < payload_length: chunk = self.sock.recv(payload_length - len(payload)) if len(chunk) == 0: raise errors.InterfaceError(errno=2013) payload = payload + chunk return header + payload except socket.timeout, err: raise errors.InterfaceError(errno=2013) except socket.error, err: raise errors.InterfaceError(errno=2055, values=(self.get_address(), err.errno)) recv = recv_plain def _split_zipped_payload(self, packet_bunch): """Split compressed payload""" while packet_bunch: payload_length = struct.unpack("<I", packet_bunch[0:3] + '\x00')[0] self._packet_queue.append(packet_bunch[0:payload_length + 4]) packet_bunch = packet_bunch[payload_length + 4:] def recv_compressed(self): """Receive compressed packets from the MySQL server""" try: return self._packet_queue.popleft() except IndexError:
def callproc(self, procname, args=()): """Calls a stored procedure with the given arguments The arguments will be set during this session, meaning they will be called like _<procname>__arg<nr> where <nr> is an enumeration (+1) of the arguments. Coding Example: 1) Defining the Stored Routine in MySQL: CREATE PROCEDURE multiply(IN pFac1 INT, IN pFac2 INT, OUT pProd INT) BEGIN SET pProd := pFac1 * pFac2; END 2) Executing in Python: args = (5, 5, 0) # 0 is to hold pprod cursor.callproc('multiply', args) print(cursor.fetchone()) For OUT and INOUT parameters the user should provide the type of the parameter as well. The argument should be a tuple with first item as the value of the parameter to pass and second argument the type of the argument. In the above example, one can call callproc method like: args = (5, 5, (0, 'INT')) cursor.callproc('multiply', args) The type of the argument given in the tuple will be used by the MySQL CAST function to convert the values in the corresponding MySQL type (See CAST in MySQL Reference for more information) Does not return a value, but a result set will be available when the CALL-statement execute successfully. Raises exceptions when something is wrong. """ if not procname or not isinstance(procname, str): raise ValueError("procname must be a string") if not isinstance(args, (tuple, list)): raise ValueError("args must be a sequence") argfmt = "@_{name}_arg{index}" self._stored_results = [] results = [] try: argnames = [] argtypes = [] if args: for idx, arg in enumerate(args): argname = argfmt.format(name=procname, index=idx + 1) argnames.append(argname) if isinstance(arg, tuple): argtypes.append(" CAST({0} AS {1})".format( argname, arg[1])) self.execute("SET {0}=%s".format(argname), (arg[0],)) else: argtypes.append(argname) self.execute("SET {0}=%s".format(argname), (arg,)) call = "CALL {0}({1})".format(procname, ','.join(argnames)) for result in self._connection.cmd_query_iter(call): if 'columns' in result: # pylint: disable=W0212 tmp = MySQLCursorBuffered(self._connection._get_self()) tmp._handle_result(result) results.append(tmp) # pylint: enable=W0212 if argnames: select = "SELECT {0}".format(','.join(argtypes)) self.execute(select) self._stored_results = results return self.fetchone() else: self._stored_results = results return () except errors.Error: raise except Exception as err: raise errors.InterfaceError( "Failed calling stored routine; {0}".format(err))
def execute(self, operation, params=None, multi=False): """Executes the given operation Executes the given operation substituting any markers with the given parameters. For example, getting all rows where id is 5: cursor.execute("SELECT * FROM t1 WHERE id = %s", (5,)) The multi argument should be set to True when executing multiple statements in one operation. If not set and multiple results are found, an InterfaceError will be raised. If warnings where generated, and connection.get_warnings is True, then self._warnings will be a list containing these warnings. Returns an iterator when multi is True, otherwise None. """ if not operation: return None if not self._connection: raise errors.ProgrammingError("Cursor is not connected.") if self._connection.unread_result is True: raise errors.InternalError("Unread result found.") self._reset_result() self._executed_list = [] stmt = '' try: if not isinstance(operation, (bytes, bytearray)): stmt = operation.encode(self._connection.python_charset) else: stmt = operation except (UnicodeDecodeError, UnicodeEncodeError) as err: raise errors.ProgrammingError(str(err)) if params is not None: if isinstance(params, dict): for key, value in self._process_params_dict(params).items(): stmt = stmt.replace(key, value) elif isinstance(params, (list, tuple)): psub = _ParamSubstitutor(self._process_params(params)) stmt = RE_PY_PARAM.sub(psub, stmt) if psub.remaining != 0: raise errors.ProgrammingError( "Not all parameters were used in the SQL statement") if multi: self._executed = stmt self._executed_list = RE_SQL_SPLIT_STMTS.split(self._executed) #return self._execute_iter(self._connection.cmd_query_iter(stmt)) return (yield from self._execute_many((yield from self._connection.cmd_query_many(stmt)))) else: self._executed = stmt try: yield from self._handle_result((yield from self._connection.cmd_query(stmt))) except errors.InterfaceError: if self._connection._have_next_result: # pylint: disable=W0212 raise errors.InterfaceError( "Use multi=True when executing multiple statements") raise return None