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