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
