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())))