def load_data(self, resource_name, timestamp, data): """Load data into a snapshot table. Args: resource_name (str): String of the resource name. timestamp (str): String of timestamp, formatted as YYYYMMDDTHHMMSSZ. data (iterable): An iterable or a list of data to be uploaded. Raises: MySQLError: When an error has occured while executing the query. """ with csv_writer.write_csv(resource_name, data) as csv_file: try: snapshot_table_name = self._create_snapshot_table_name( resource_name, timestamp) load_data_sql = load_data_sql_provider.provide_load_data_sql( resource_name, csv_file.name, snapshot_table_name) LOGGER.debug('SQL: %s', load_data_sql) cursor = self.conn.cursor() cursor.execute(load_data_sql) self.conn.commit() # TODO: Return the snapshot table name so that it can be tracked # in the main snapshot table. except (DataError, IntegrityError, InternalError, NotSupportedError, OperationalError, ProgrammingError) as e: raise MySQLError(resource_name, e)
def get_latest_snapshot_timestamp(self, statuses): """Select the latest timestamp of the completed snapshot. Args: statuses (tuple): The tuple of snapshot statuses to filter on. Returns: str: The string timestamp of the latest complete snapshot. Raises: MySQLError: When no rows are found. """ # Build a dynamic parameterized query string for filtering the # snapshot statuses if not isinstance(statuses, tuple): statuses = ('SUCCESS', ) status_params = ','.join(['%s'] * len(statuses)) filter_clause = SNAPSHOT_STATUS_FILTER_CLAUSE.format(status_params) try: cursor = self.conn.cursor() cursor.execute( select_data.LATEST_SNAPSHOT_TIMESTAMP + filter_clause, statuses) row = cursor.fetchone() if row: return row[0] raise NoResultsError('No snapshot cycle found.') except (DataError, IntegrityError, InternalError, NotSupportedError, OperationalError, ProgrammingError, NoResultsError) as e: raise MySQLError('snapshot_cycles', e)
def load_data(self, resource_name, timestamp, data): """Load data into a snapshot table. Args: resource_name: String of the resource name. timestamp: String of timestamp, formatted as YYYYMMDDTHHMMSSZ. data: An iterable or a list of data to be uploaded. Returns: None Raises: MySQLError: An error with MySQL has occurred. """ try: snapshot_table_name = self._create_snapshot_table( resource_name, timestamp) csv_filename = csv_writer.write_csv(resource_name, data) load_data_sql = load_data_sql_provider.provide_load_data_sql( resource_name, csv_filename, snapshot_table_name) cursor = self.conn.cursor() cursor.execute(load_data_sql) self.conn.commit() # TODO: Return the snapshot table name so that it can be tracked # in the main snapshot table. except (DataError, IntegrityError, InternalError, NotSupportedError, OperationalError, ProgrammingError) as e: raise MySQLError(resource_name, e)
def __init__(self): """Initialize the db connector. Raises: MySQLError: An error with MySQL has occurred. """ configs = FLAGS.FlagValuesDict() try: # If specifying the passwd argument, MySQL expects a string, # which would not be correct if there is no password (i.e. # using cloud_sql_proxy to connect without a db password). if 'db_passwd' in configs and configs['db_passwd']: self.conn = MySQLdb.connect(host=configs['db_host'], user=configs['db_user'], passwd=configs['db_passwd'], db=configs['db_name'], local_infile=1) else: self.conn = MySQLdb.connect(host=configs['db_host'], user=configs['db_user'], db=configs['db_name'], local_infile=1) except OperationalError as e: LOGGER.error('Unable to create mysql connector:\n{0}'.format(e)) raise MySQLError('DB Connector', e)
def select_latest_complete_snapshot_timestamp(self, statuses): """Select the latest timestamp of the completed snapshot. Args: statuses: The tuple of snapshot statuses to filter on. Returns: The string timestamp of the latest complete snapshot. Raises: MySQLError (NoResultsError) if no rows are found. """ # Build a dynamic parameterized query string for filtering the # snapshot statuses if not statuses: statuses = ('SUCCESS') # TODO: Investigate improving to avoid the pylint disable. status_params = ','.join(['%s' for s in statuses]) # pylint: disable=unused-variable filter_clause = ' where status in ({})'.format(status_params) try: cursor = self.conn.cursor() cursor.execute( select_data.LATEST_SNAPSHOT_TIMESTAMP + filter_clause, statuses) rows = cursor.fetchall() if rows and rows[0]: return rows[0][0] raise NoResultsError('No snapshot cycle found.') except (DataError, IntegrityError, InternalError, NotSupportedError, OperationalError, ProgrammingError, NoResultsError) as e: raise MySQLError('snapshot_cycles', e)
def get_org_iam_policies(self, timestamp): """Get the organization policies. Args: timestamp: The timestamp of the snapshot. Returns: A dict keyed by the organizations (gcp_type.organization.Organization) and their iam policies (dict). """ org_iam_policies = {} try: cursor = self.conn.cursor() cursor.execute(select_data.ORG_IAM_POLICIES.format(timestamp)) rows = cursor.fetchall() for row in rows: try: org = Organization(organization_id=row[0]) iam_policy = json.loads(row[1]) org_iam_policies[org] = iam_policy except ValueError as json_error: self.logging.warn('Error parsing json {}'.format(row[2])) except (DataError, IntegrityError, InternalError, NotSupportedError, OperationalError, ProgrammingError) as e: LOGGER.error(MySQLError('organizations', e)) return org_iam_policies
def __init__(self): """Initialize the db connector. Raises: MySQLError: An error with MySQL has occurred. """ configs = FLAGS.FlagValuesDict() try: self.conn = MySQLdb.connect(host=configs['db_host'], user=configs['db_user'], db=configs['db_name'], local_infile=1) except OperationalError as e: LOGGER.error('Unable to create mysql connector:\n%s', e) raise MySQLError('DB Connector', e)
def __init__(self, global_configs=None): """Initialize the db connector. Args: global_configs (dict): Global configurations. Raises: MySQLError: An error with MySQL has occurred. """ try: self.conn = MySQLdb.connect(host=global_configs['db_host'], user=global_configs['db_user'], db=global_configs['db_name'], local_infile=1) except OperationalError as e: LOGGER.error('Unable to create mysql connector:\n%s', e) raise MySQLError('DB Connector', e)
def execute_sql_with_commit(self, resource_name, sql, values): """Executes a provided sql statement with commit. Args: resource_name (str): String of the resource name. sql (str): String of the sql statement. values (tuple): Tuple of string for sql placeholder values. Raises: MySQLError: When an error has occured while executing the query. """ try: cursor = self.conn.cursor() cursor.execute(sql, values) self.conn.commit() except (DataError, IntegrityError, InternalError, NotSupportedError, OperationalError, ProgrammingError) as e: raise MySQLError(resource_name, e)
def execute_sql_with_fetch(self, resource_name, sql, values): """Executes a provided sql statement with fetch. Args: resource_name (str): String of the resource name. sql (str): String of the sql statement. values (tuple): Tuple of string for sql placeholder values. Returns: list: A list of tuples representing rows of sql query result. Raises: MySQLError: When an error has occured while executing the query. """ try: cursor = self.conn.cursor(cursorclass=cursors.DictCursor) cursor.execute(sql, values) return cursor.fetchall() except (DataError, IntegrityError, InternalError, NotSupportedError, OperationalError, ProgrammingError) as e: raise MySQLError(resource_name, e)
def execute_sql_with_commit(self, resource_name, sql, values): """Executes a provided sql statement with commit. Args: resource_name: String of the resource name. sql: String of the sql statement. values: Tuple of string for sql placeholder values. Returns: None Raises: MySQLError: An error with MySQL has occurred. """ try: cursor = self.conn.cursor() cursor.execute(sql, values) self.conn.commit() except (DataError, IntegrityError, InternalError, NotSupportedError, OperationalError, ProgrammingError) as e: raise MySQLError(resource_name, e)
def select_group_ids(self, resource_name, timestamp): """Select the group ids from a snapshot table. Args: resource_name: String of the resource name. timestamp: String of timestamp, formatted as YYYYMMDDTHHMMSSZ. Returns: A list of group ids. Raises: MySQLError: An error with MySQL has occurred. """ try: group_ids_sql = select_data.GROUP_IDS.format(timestamp) cursor = self.conn.cursor(cursorclass=cursors.DictCursor) cursor.execute(group_ids_sql) rows = cursor.fetchall() return [row['group_id'] for row in rows] except (DataError, IntegrityError, InternalError, NotSupportedError, OperationalError, ProgrammingError) as e: raise MySQLError(resource_name, e)
def select_project_numbers(self, resource_name, timestamp): """Select the project numbers from a snapshot table. Args: resource_name: String of the resource name. timestamp: String of timestamp, formatted as YYYYMMDDTHHMMSSZ. Returns: list of project numbers Raises: MySQLError: An error with MySQL has occurred. """ try: project_numbers_sql = select_data.PROJECT_NUMBERS.format(timestamp) cursor = self.conn.cursor(cursorclass=cursors.DictCursor) cursor.execute(project_numbers_sql) rows = cursor.fetchall() return [row['project_number'] for row in rows] except (DataError, IntegrityError, InternalError, NotSupportedError, OperationalError, ProgrammingError) as e: raise MySQLError(resource_name, e)
def select_record_count(self, resource_name, timestamp): """Select the record count from a snapshot table. Args: resource_name: String of the resource name, which is embedded in the table name. timestamp: String of timestamp, formatted as YYYYMMDDTHHMMSSZ. Returns: Integer of the record count in a snapshot table. Raises: MySQLError: An error with MySQL has occurred. """ try: record_count_sql = select_data.RECORD_COUNT.format( resource_name, timestamp) cursor = self.conn.cursor() cursor.execute(record_count_sql) return cursor.fetchone()[0] except (DataError, IntegrityError, InternalError, NotSupportedError, OperationalError, ProgrammingError) as e: raise MySQLError(resource_name, e)
def get_project_policies(self, timestamp): """Get the project policies. Args: timestamp: The timestamp of the snapshot. Returns: A dict containing the projects (gcp_type.project.Project) and their iam policies (dict). """ project_policies = {} prev_proj_id = None prev_proj = None try: cursor = self.conn.cursor() cursor.execute( select_data.PROJECT_IAM_POLICIES.format(timestamp, timestamp)) rows = cursor.fetchall() for row in rows: proj_id = row[1] if prev_proj_id != proj_id: project = Project(project_id=row[1], project_name=row[2], project_number=row[0], lifecycle_state=row[3]) project.parent = ResourceUtil.create_resource( resource_id=row[5], resource_type=row[4]) else: project = prev_proj policy = project_policies.get(project) if not policy: policy = {'bindings': []} role = row[6] member_type = row[7] member_name = row[8] member_domain = row[9] member = '' if member_name: member = '{}:{}@{}'.format(member_type, member_name, member_domain) else: member = '{}:{}'.format(member_type, member_domain) member = member.strip() added_to_role = False for binding in policy.get('bindings'): if binding.get('role') == role: binding.get('members').append(member) added_to_role = True if not added_to_role: policy['bindings'].append({ 'role': role, 'members': [member] }) project_policies[project] = policy prev_proj = project except (DataError, IntegrityError, InternalError, NotSupportedError, OperationalError, ProgrammingError) as e: LOGGER.error(MySQLError('projects', e)) return project_policies