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):
tmp = re.sub(RE_SQL_ON_DUPLICATE, '',
re.sub(RE_SQL_COMMENT, '', operation))
m = re.search(RE_SQL_INSERT_VALUES, tmp)
if not m:
raise errors.InterfaceError(
"Failed rewriting statement for multi-row INSERT. "
"Check SQL syntax.")
fmt = m.group(1)
values = []
for params in seq_params:
values.append(fmt % self._process_params(params))
operation = operation.replace(m.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), err:
raise errors.InterfaceError("Failed executing the operation; %s" %
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
if self._have_unread_result():
raise errors.InternalError("Unread result found.")
self._reset_result()
stmt = ''
try:
if isinstance(operation, unicode):
operation = operation.encode(self._connection.charset)
except (UnicodeDecodeError, UnicodeEncodeError), e:
raise errors.ProgrammingError(str(e))
def _send_data(self, data_file, send_empty_packet=False):
"""Send data to the MySQL server
This method accepts a file-like object and sends its data
as is to the MySQL server. If the send_empty_packet is
True, it will send an extra empty package (for example
when using LOAD LOCAL DATA INFILE).
Returns a MySQL packet.
"""
if self.unread_result:
raise errors.InternalError("Unread result found.")
if not hasattr(data_file, 'read'):
raise ValueError("expecting a file-like object")
try:
buf = data_file.read(NET_BUFFER_LENGTH - 16)
while buf:
yield from self._socket.drain()
self._socket.send(buf)
buf = data_file.read(NET_BUFFER_LENGTH - 16)
except AttributeError:
raise errors.OperationalError("MySQL Connection not available.")
if send_empty_packet:
try:
yield from self._socket.drain()
self._socket.send(b'')
except AttributeError:
raise errors.OperationalError(
"MySQL Connection not available.")
return (yield from self._socket.recv())
def _send_cmd(self, command, argument=None, packet_number=0, packet=None,
expect_response=True):
"""Send a command to the MySQL server
This method sends a command with an optional argument.
If packet is not None, it will be sent and the argument will be
ignored.
The packet_number is optional and should usually not be used.
Some commands might not result in the MySQL server returning
a response. If a command does not return anything, you should
set expect_response to False. The _send_cmd method will then
return None instead of a MySQL packet.
Returns a MySQL packet or None.
"""
if self.unread_result:
raise errors.InternalError("Unread result found.")
try:
yield from self._socket.drain()
self._socket.send(
self._protocol.make_command(command, packet or argument),
packet_number)
except AttributeError:
raise errors.OperationalError("MySQL Connection not available.")
if not expect_response:
return None
return (yield from self._socket.recv())
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
if self._connection.unread_result is True:
raise errors.InternalError("Unread result found.")
self._reset_result()
stmt = ''
try:
if not isinstance(operation, bytes):
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 = []
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:
if self._connection._have_next_result: # pylint: disable=W0212
raise errors.InterfaceError(
"Use multi=True when executing multiple statements")
raise
return None
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
if not self._connection:
raise errors.ProgrammingError("Cursor is not connected.")
if self._have_unread_result():
raise errors.InternalError("Unread result found.")
self._reset_result()
stmt = ''
try:
if isinstance(operation, unicode):
operation = operation.encode(self._connection.python_charset)
except (UnicodeDecodeError, UnicodeEncodeError) as err:
raise errors.ProgrammingError(str(err))
if params is not None:
try:
stmt = operation % self._process_params(params)
except TypeError:
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:
if self._connection._have_next_result: # pylint: disable=W0212
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')
]
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._connection.unread_result is True:
raise errors.InternalError("Unread result found.")
if isinstance(operation, bytes):
return self.execute(operation)
if not isinstance(seq_params, (list, tuple)):
raise errors.ProgrammingError(
"Parameters for query must be list or tuple.")
# Optimize INSERTs by batching them
if re.match(RE_SQL_INSERT_STMT, operation):
if not seq_params:
self._rowcount = 0
return
stmt = self._batch_insert(operation, seq_params)
if stmt is not None:
return self.execute(stmt)
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; {}".format(err))
except:
# Raise whatever execute() raises
raise
self._rowcount = rowcnt
def _execute_query(self, query):
"""Execute a query
This method simply calls cmd_query() after checking for unread
result. If there are still unread result, an errors.InterfaceError
is raised. Otherwise whatever cmd_query() returns is returned.
Returns a dict()
"""
if self._unread_result is True:
raise errors.InternalError("Unread result found.")
yield from self.cmd_query(query)
def close(self):
"""Close the cursor
Returns True when successful, otherwise False.
"""
if self._connection is None:
return False
if self._have_unread_result():
raise errors.InternalError("Unread result found.")
self._reset_result()
self._connection = None
return True
def cmd_statistics(self):
"""Send the statistics command to the MySQL Server
This method sends the STATISTICS command to the MySQL server. The
result is a dictionary with various statistical information.
Returns a dict()
"""
if self.unread_result:
raise errors.InternalError("Unread result found.")
packet = self._protocol.make_command(ServerCmd.STATISTICS)
yield from self._socket.drain()
self._socket.send(packet, 0)
return self._protocol.parse_statistics((yield from self._socket.recv()))
def cmd_quit(self):
"""Close the current connection with the server
This method sends the QUIT command to the MySQL server, closing the
current connection. Since the no response can be returned to the
client, cmd_quit() will return the packet it send.
Returns a str()
"""
if self.unread_result:
raise errors.InternalError("Unread result found.")
packet = self._protocol.make_command(ServerCmd.QUIT)
yield from self._socket.drain()
self._socket.send(packet, 0)
return packet
def get_rows(self, count=None, binary=False, columns=None):
"""Get all rows returned by the MySQL server
This method gets all rows returned by the MySQL server after sending,
for example, the query command. The result is a tuple consisting of
a list of rows and the EOF packet.
Returns a tuple()
"""
if not self.unread_result:
raise errors.InternalError("No result set available.")
if binary:
rows = yield from self._protocol.read_binary_result(
self._socket, columns, count)
else:
rows = yield from self._protocol.read_text_result(self._socket, count)
if rows[-1] is not None:
self._handle_server_status(rows[-1]['status_flag'])
self.unread_result = False
return rows
def cmd_change_user(self, username='', password='', database='',
charset=33):
"""Change the current logged in user
This method allows to change the current logged in user information.
The result is a dictionary with OK packet information.
Returns a dict()
"""
if self.unread_result:
raise errors.InternalError("Unread result found.")
if self._compress:
raise errors.NotSupportedError("Change user is not supported with "
"compression.")
packet = yield from self._protocol.make_change_user(
handshake=self._handshake,
username=username, password=password, database=database,
charset=charset, client_flags=self._client_flags,
ssl_enabled=self._ssl_active,
auth_plugin=self._auth_plugin)
yield from self._socket.drain()
self._socket.send(packet, 0)
ok_packet = yield from self._auth_switch_request(username, password)
try:
if not (self._client_flags & ClientFlag.CONNECT_WITH_DB) \
and database:
yield from self.cmd_init_db(database)
except:
raise
self._charset_id = charset
self._post_connection()
return ok_packet
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.
"""
def remove_comments(match):
"""Remove comments from INSERT statements.
This function is used while removing comments from INSERT
statements. If the matched string is a comment not enclosed
by quotes, it returns an empty string, else the string itself.
"""
if match.group(1):
return ""
else:
return match.group(2)
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")
try:
if isinstance(operation, unicode):
operation = operation.encode(self._connection.python_charset)
except (UnicodeDecodeError, UnicodeEncodeError) as err:
raise errors.ProgrammingError(str(err))
# 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, remove_comments, 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))
if matches.group(1) in operation:
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 cursor(self, buffered=None, raw=None, prepared=None, cursor_class=None,
dictionary=None, named_tuple=None):
"""Instantiates and returns a cursor
By default, MySQLCursor is returned. Depending on the options
while connecting, a buffered and/or raw cursor is instantiated
instead. Also depending upon the cursor options, rows can be
returned as dictionary or named tuple.
Dictionary and namedtuple based cursors are available with buffered
output but not raw.
It is possible to also give a custom cursor through the
cursor_class parameter, but it needs to be a subclass of
mysql.connector.cursor.CursorBase.
Raises ProgrammingError when cursor_class is not a subclass of
CursorBase. Raises ValueError when cursor is not available.
Returns a cursor-object
"""
if self._unread_result is True:
raise errors.InternalError("Unread result found.")
connected = yield from self.is_connected()
if not connected:
raise errors.OperationalError("MySQL Connection not available.")
if cursor_class is not None:
if not issubclass(cursor_class, CursorBase):
raise errors.ProgrammingError(
"Cursor class needs be to subclass of cursor.CursorBase")
return (cursor_class)(self)
buffered = buffered or self._buffered
raw = raw or self._raw
cursor_type = 0
if buffered is True:
cursor_type |= 1
if raw is True:
cursor_type |= 2
if dictionary is True:
cursor_type |= 4
if named_tuple is True:
cursor_type |= 8
if prepared is True:
cursor_type |= 16
types = {
0: AioMySQLCursor, # 0
1: AioMySQLCursorBuffered,
2: AioMySQLCursorRaw,
3: AioMySQLCursorBufferedRaw,
4: AioMySQLCursorDict,
5: AioMySQLCursorBufferedDict,
8: AioMySQLCursorNamedTuple,
9: AioMySQLCursorBufferedNamedTuple,
16: AioMySQLCursorPrepared
}
try:
return (types[cursor_type])(self)
except KeyError:
args = ('buffered', 'raw', 'dictionary', 'named_tuple', 'prepared')
raise ValueError('Cursor not available with given criteria: ' +
', '.join([args[i] for i in range(5)
if cursor_type & (1 << i) != 0]))
def next_result(self):
if not self._have_next_result:
return None
if self.unread_result:
raise errors.InternalError("Unread result found.")
return (yield from self._handle_result((yield from self._socket.recv())))
