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 _batch_insert(self, operation, seq_params):
"""Implements multi row insert"""
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)
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).encode(self._connection.charset)
values = []
try:
stmt = operation.encode(self._connection.charset)
for params in seq_params:
tmp = fmt
if isinstance(params, dict):
for key, value in self._process_params_dict(params).items():
tmp = tmp.replace(key, value)
else:
psub = _ParamSubstitutor(self._process_params(params))
tmp = RE_PY_PARAM.sub(psub, tmp)
if psub.remaining != 0:
raise errors.ProgrammingError("Not all parameters "
"were used in the SQL statement")
#for p in self._process_params(params):
# tmp = tmp.replace(b'%s',p,1)
values.append(tmp)
if fmt in stmt:
stmt = stmt.replace(fmt, b','.join(values), 1)
return stmt
else:
return None
except (UnicodeDecodeError, UnicodeEncodeError) as err:
raise errors.ProgrammingError(str(err))
except errors.Error:
raise
except Exception as err:
raise errors.InterfaceError(
"Failed executing the operation; %s" % err)
else:
self._executed = stmt
return self._rowcount
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 make_auth(self, handshake, username=None, password=None, database=None,
charset=33, client_flags=0,
max_allowed_packet=1073741824, ssl_enabled=False,
auth_plugin=None):
"""Make a MySQL Authentication packet"""
try:
auth_data = handshake['auth_data']
auth_plugin = auth_plugin or handshake['auth_plugin']
except (TypeError, KeyError) as exc:
raise errors.ProgrammingError(
"Handshake misses authentication info ({0})".format(exc))
if not username:
username = ''
elif isinstance(username, unicode):
username = username.encode('utf8')
packet = struct.pack('<IIB{filler}{usrlen}sx'.format(
filler='x' * 23, usrlen=len(username)),
client_flags, max_allowed_packet, charset, username)
packet += self._auth_response(client_flags, username, password,
database,
auth_plugin,
auth_data, ssl_enabled)
packet += self._connect_with_db(client_flags, database)
if client_flags & ClientFlag.PLUGIN_AUTH:
packet += auth_plugin.encode('utf8') + '\x00'
return packet
def make_change_user(self, handshake, username=None, password=None,
database=None, charset=33, client_flags=0,
ssl_enabled=False, auth_plugin=None):
"""Make a MySQL packet with the Change User command"""
try:
auth_data = handshake['auth_data']
auth_plugin = auth_plugin or handshake['auth_plugin']
except (TypeError, KeyError) as exc:
raise errors.ProgrammingError(
"Handshake misses authentication info ({0})".format(exc))
if not username:
username = ''
elif isinstance(username, unicode):
username = username.encode('utf8')
packet = struct.pack('<B{usrlen}sx'.format(usrlen=len(username)),
ServerCmd.CHANGE_USER, username)
packet += self._auth_response(client_flags, username, password,
database,
auth_plugin,
auth_data, ssl_enabled)
packet += self._connect_with_db(client_flags, database)
packet += struct.pack('<H', charset)
if client_flags & ClientFlag.PLUGIN_AUTH:
packet += auth_plugin.encode('utf8') + '\x00'
return packet
def _process_params(self, params):
"""
Process the parameters which were given when self.execute() was
called. It does following using the MySQLConnection converter:
* Convert Python types to MySQL types
* Escapes characters required for MySQL.
* Quote values when needed.
Returns a list.
"""
try:
res = params
to_mysql = self._connection.converter.to_mysql
escape = self._connection.converter.escape
quote = self._connection.converter.quote
res = list(map(to_mysql, res))
res = list(map(escape, res))
res = list(map(quote, res))
except Exception as e:
raise errors.ProgrammingError(
"Failed processing format-parameters; %s" % e)
else:
return tuple(res)
return None
def __call__(self, matchobj):
index = self.index
self.index += 1
try:
return self.params[index]
except IndexError:
raise errors.ProgrammingError(
"Not enough parameters for the SQL statement")
def execute(self, operation, params=(), multi=False): # multi is unused
"""Prepare and execute a MySQL Prepared Statement
This method will preare the given operation and execute it using
the optionally given parameters.
If the cursor instance already had a prepared statement, it is
first closed.
"""
if operation is not self._executed:
if self._prepared:
self._connection.cmd_stmt_close(self._prepared['statement_id'])
self._executed = operation
try:
if not isinstance(operation, bytes):
operation = operation.encode(self._connection.charset)
except (UnicodeDecodeError, UnicodeEncodeError) as err:
raise errors.ProgrammingError(str(err))
# need to convert %s to ? before sending it to MySQL
if b'%s' in operation:
operation = re.sub(RE_SQL_FIND_PARAM, b'?', operation)
try:
self._prepared = self._connection.cmd_stmt_prepare(operation)
except errors.Error:
self._executed = None
raise
self._connection.cmd_stmt_reset(self._prepared['statement_id'])
if self._prepared['parameters'] and not params:
return
elif len(self._prepared['parameters']) != len(params):
raise errors.ProgrammingError(
errno=1210,
msg="Incorrect number of arguments "
"executing prepared statement")
res = self._connection.cmd_stmt_execute(
self._prepared['statement_id'],
data=params,
parameters=self._prepared['parameters'])
self._handle_result(res)
def _handle_noresultset(self, res):
"""Handles result of execute() when there is no result set
"""
try:
self._rowcount = res['affected_rows']
self._last_insert_id = res['insert_id']
self._warning_count = res['warning_count']
except (KeyError, TypeError), err:
raise errors.ProgrammingError("Failed handling non-resultset; %s" %
err)
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 _batch_insert(self, operation, seq_params):
"""Implemets multi row insert"""
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).encode(self._connection.charset)
values = []
try:
stmt = operation.encode(self._connection.charset)
for params in seq_params:
tmp = fmt
if isinstance(params, dict):
for key, value in self._process_params_dict(
params).items():
tmp = tmp.replace(key, value, 1)
else:
psub = _ParamSubstitutor(self._process_params(params))
tmp = RE_PY_PARAM.sub(psub, tmp)
if psub.remaining != 0:
raise errors.ProgrammingError(
"Not all parameters "
"were used in the SQL statement")
#for p in self._process_params(params):
# tmp = tmp.replace(b'%s',p,1)
values.append(tmp)
stmt = stmt.replace(fmt, b','.join(values), 1)
return self.execute(stmt)
except (UnicodeDecodeError, UnicodeEncodeError) as err:
raise errors.ProgrammingError(str(err))
except errors.Error:
raise
except Exception as err:
raise errors.InterfaceError("Failed executing the operation; %s" %
err)
else:
self._executed = stmt
return self._rowcount
def make_change_user(self, seed, username=None, password=None,
database=None, charset=33, client_flags=0):
"""Make a MySQL packet with the Change User command"""
if not seed:
raise errors.ProgrammingError('Seed missing')
auth = self._prepare_auth(username, password, database,
client_flags, seed)
data = utils.int1store(ServerCmd.CHANGE_USER) +\
auth[0] + auth[1] + auth[2] + utils.int2store(charset)
return data
def _handle_noresultset(self, res):
"""Handles result of execute() when there is no result set
"""
try:
self._rowcount = res['affected_rows']
self._last_insert_id = res['insert_id']
self._warning_count = res['warning_count']
except (KeyError, TypeError) as err:
raise errors.ProgrammingError(
"Failed handling non-resultset; {}".format(err))
if self._connection.get_warnings is True and self._warning_count:
self._warnings = self._fetch_warnings()
def make_auth(self, seed, username=None, password=None, database=None,
charset=33, client_flags=0,
max_allowed_packet=1073741824):
"""Make a MySQL Authentication packet"""
if not seed:
raise errors.ProgrammingError('Seed missing')
auth = self._prepare_auth(username, password, database,
client_flags, seed)
return utils.int4store(client_flags) +\
utils.int4store(max_allowed_packet) +\
utils.int1store(charset) +\
'\x00' * 23 + auth[0] + auth[1] + auth[2]
def start_transaction(self, consistent_snapshot=False,
isolation_level=None, readonly=None):
"""Start a transaction
This method explicitly starts a transaction sending the
START TRANSACTION statement to the MySQL server. You can optionally
set whether there should be a consistent snapshot, which
isolation level you need or which access mode i.e. READ ONLY or
READ WRITE.
For example, to start a transaction with isolation level SERIALIZABLE,
you would do the following:
>>> cnx = mysql.connector.connect(..)
>>> cnx.start_transaction(isolation_level='SERIALIZABLE')
Raises ProgrammingError when a transaction is already in progress
and when ValueError when isolation_level specifies an Unknown
level.
"""
if self.in_transaction:
raise errors.ProgrammingError("Transaction already in progress")
if isolation_level:
level = isolation_level.strip().replace('-', ' ').upper()
levels = ['READ UNCOMMITTED', 'READ COMMITTED', 'REPEATABLE READ',
'SERIALIZABLE']
if level not in levels:
raise ValueError(
'Unknown isolation level "{0}"'.format(isolation_level))
yield from self._execute_query(
"SET TRANSACTION ISOLATION LEVEL {0}".format(level))
if readonly is not None:
if self._server_version < (5, 6, 5):
raise ValueError(
"MySQL server version {0} does not support "
"this feature".format(self._server_version))
if readonly:
access_mode = 'READ ONLY'
else:
access_mode = 'READ WRITE'
yield from self._execute_query(
"SET TRANSACTION {0}".format(access_mode))
query = "START TRANSACTION"
if consistent_snapshot:
query += " WITH CONSISTENT SNAPSHOT"
yield from self._execute_query(query)
def _process_params_dict(self, params):
try:
to_mysql = self._connection.converter.to_mysql
escape = self._connection.converter.escape
quote = self._connection.converter.quote
res = {}
for k, v in params.items():
c = v
c = to_mysql(c)
c = escape(c)
c = quote(c)
res[k] = c
except StandardError, e:
raise errors.ProgrammingError(
"Failed processing pyformat-parameters; %s" % e)
def _process_params(self, params):
"""Process query parameters."""
if isinstance(params, dict):
return self._process_params_dict(params)
try:
res = params
# pylint: disable=W0141
res = map(self._connection.converter.to_mysql, res)
res = map(self._connection.converter.escape, res)
res = map(self._connection.converter.quote, res)
# pylint: enable=W0141
except StandardError as err:
raise errors.ProgrammingError(
"Failed processing format-parameters; %s" % err)
else:
return tuple(res)
return None
def _process_params(self, params):
"""Process query parameters."""
try:
res = params
to_mysql = self._connection.converter.to_mysql
escape = self._connection.converter.escape
quote = self._connection.converter.quote
res = [to_mysql(i) for i in res]
res = [escape(i) for i in res]
res = [quote(i) for i in res]
except Exception as err:
raise errors.ProgrammingError(
"Failed processing format-parameters; %s" % err)
else:
return tuple(res)
return None
def _process_params_dict(self, params):
try:
to_mysql = self._connection.converter.to_mysql
escape = self._connection.converter.escape
quote = self._connection.converter.quote
res = {}
for k, v in list(params.items()):
c = v
c = to_mysql(c)
c = escape(c)
c = quote(c)
res["%({})s".format(k).encode()] = c
except Exception as e:
raise errors.ProgrammingError(
"Failed processing pyformat-parameters; %s" % e)
else:
return res
return None
def _process_params_dict(self, params):
"""Process query parameters given as dictionary"""
try:
to_mysql = self._connection.converter.to_mysql
escape = self._connection.converter.escape
quote = self._connection.converter.quote
res = {}
for key, val in params.items():
conv = val
conv = to_mysql(conv)
conv = escape(conv)
conv = quote(conv)
res[key] = conv
except StandardError as err:
raise errors.ProgrammingError(
"Failed processing pyformat-parameters; %s" % err)
else:
return res
return None
def _process_params_dict(self, params):
"""Process query parameters given as dictionary"""
try:
to_mysql = self._connection.converter.to_mysql
escape = self._connection.converter.escape
quote = self._connection.converter.quote
res = {}
for key, value in list(params.items()):
conv = value
conv = to_mysql(conv)
conv = escape(conv)
conv = quote(conv)
res["%({})s".format(key).encode()] = conv
except Exception as err:
raise errors.ProgrammingError(
"Failed processing pyformat-parameters; %s" % err)
else:
return res
return None
def _process_params(self, params):
"""
Process the parameters which were given when self.execute() was
called. It does following using the MySQLConnection converter:
* Convert Python types to MySQL types
* Escapes characters required for MySQL.
* Quote values when needed.
Returns a list.
"""
if isinstance(params, dict):
return self._process_params_dict(params)
try:
res = params
res = map(self._connection.converter.to_mysql, res)
res = map(self._connection.converter.escape, res)
res = map(self._connection.converter.quote, res)
except StandardError, e:
raise errors.ProgrammingError(
"Failed processing format-parameters; %s" % e)
def _auth_response(self, client_flags, username, password, database,
auth_plugin, auth_data, ssl_enabled):
"""Prepare the authentication response"""
if not password:
return '\x00'
try:
auth = get_auth_plugin(auth_plugin)(auth_data,
username=username, password=password, database=database,
ssl_enabled=ssl_enabled)
plugin_auth_response = auth.auth_response()
except (TypeError, errors.InterfaceError) as exc:
raise errors.ProgrammingError(
"Failed authentication: {0}".format(str(exc)))
if client_flags & ClientFlag.SECURE_CONNECTION:
resplen = len(plugin_auth_response)
auth_response = struct.pack('<B', resplen) + plugin_auth_response
else:
auth_response = plugin_auth_response + '\x00'
return auth_response
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]))
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))
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, err:
if self._connection._have_next_result:
raise errors.InterfaceError(
"Use multi=True when executing multiple statements")
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 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