def find_by_primary_key(self, key_fields, field_list=None):
'''
:param key_fields: The list with the values for the key_columns, in order, to use to find a record.
:param field_list: A subset of the fields of the record to return.
:return: None, or a dictionary containing the requested fields for the record identified
by the key.
'''
if Helper.is_empty(self._data['key_columns']):
self._logger.error('Table has no primary keys')
raise Exception
if not Helper.are_key_fields_valid(key_fields,
self._data['key_columns']):
self._logger.error('Key fields are not valid')
raise Exception
template = Helper.convert_key_fields_to_template(
key_fields, self._data['key_columns'])
result = self.find_by_template(template, field_list, 1)
if Helper.is_empty(result):
return None
return result[0]
def _load(self):
dir_info = self._data['connect_info'].get('directory')
file_n = self._data['connect_info'].get('file_name')
full_name = os.path.join(dir_info, file_n)
i = 0
with open(full_name, 'r') as txt_file:
csv_d_rdr = csv.DictReader(txt_file)
for r in csv_d_rdr:
if i == 0:
if not Helper.is_empty(self._data['key_columns']):
if not Helper.is_column_list_valid(
self._data['key_columns'], r.keys()):
self._logger.error(
'Specified primary keys don\'t match table columns'
)
raise Exception
self._add_row(r)
i = i + 1
if not Helper.is_empty(self._data['key_columns']):
if not self._check_primary_key_constraint_for_first_load():
self._rows = []
self._logger.error(
'The specified primary keys don\'t comply with primary key constraint'
)
raise Exception
self._logger.debug('CSVDataTable._load: Loaded ' +
str(len(self._rows)) + ' rows')
def _violate_primary_key_constraint(self, new_keys_template):
if Helper.is_empty(self._data['key_columns']):
return False
key_fields = Helper.extract_key_fields_from_template(
new_keys_template, self._data['key_columns'])
records = self.find_by_primary_key(key_fields)
if Helper.is_empty(records):
return False
return True
def update_by_template(self, template, new_values, limit=None):
'''
:param template: Template for rows to match.
:param new_values: New values to set for matching fields.
:return: Number of rows updated.
'''
if Helper.is_empty(new_values):
return 0
if not Helper.is_empty(template):
if not Helper.is_template_valid(template, self.get_columns()):
self._logger.error(
'Some columns in the specified template don\'t match table columns'
)
raise Exception
# Extract key_fields from template if any
changed_keys = Helper.extract_key_columns_and_values_from_template(
new_values, self._data['key_columns'])
rows = self.get_rows()
r_indexes = []
for i in range(0, len(rows)):
if limit is not None:
if len(r_indexes) == limit:
break
if Helper.matches_template(rows[i], template):
# Apply changed_keys and check if modification would result in duplicate primary key
if not Helper.is_empty(changed_keys):
# Very important to make copy of rows[i] so that it will not be altered
new_keys_template = Helper.change_keys(
copy.copy(rows[i]), changed_keys)
if self._violate_primary_key_constraint(new_keys_template):
self._logger.error('Violates primary key constraint')
raise Exception
r_indexes.append(i)
if len(r_indexes) == 0:
return 0
elif len(r_indexes) == 1:
self._modify_row(r_indexes[0], new_values)
return 1
else:
self._modify_rows(r_indexes, new_values)
return len(r_indexes)
def delete_by_template(self, template):
'''
:param template: Template to determine rows to delete.
:return: Number of rows deleted.
'''
template_string = ''
if not Helper.is_empty(template):
if not Helper.is_template_valid(template, self.get_columns()):
self._logger.error(
'Some columns in the specified template don\'t match table columns'
)
raise Exception
template_string = 'WHERE ' + self._compose_template_string(
template)
query = 'DELETE FROM ' + '`' + self._data[
'table_name'] + '` ' + template_string
with self._connection.cursor() as cursor:
try:
cursor.execute(query)
if cursor.rowcount > 0:
if self._auto_commit:
self.commit()
return cursor.rowcount
except pymysql.Error as error:
self._logger.error(
'Failed to delete record(s) in the table {}'.format(error))
raise Exception
def delete_by_template(self, template, limit=None):
'''
:param template: Template to determine rows to delete.
:return: Number of rows deleted.
'''
if not Helper.is_empty(template):
if not Helper.is_template_valid(template, self.get_columns()):
self._logger.error(
'Some columns in the specified template don\'t match table columns'
)
raise Exception
rows = self.get_rows()
r_indexes = []
for i in range(0, len(rows)):
if limit is not None:
if len(r_indexes) == limit:
break
if Helper.matches_template(rows[i], template):
r_indexes.append(i)
if len(r_indexes) == 0:
return 0
elif len(r_indexes) == 1:
self._delete_row(r_indexes[0])
return 1
else:
self._delete_rows(r_indexes)
return len(r_indexes)
def find_by_template(self,
template,
field_list=None,
limit=None,
offset=None,
order_by=None):
'''
:param template: A dictionary of the form { 'field1' : value1, 'field2': value2, ...}
:param field_list: A list of request fields of the form, ['fielda', 'fieldb', ...]
:param limit: Do not worry about this for now.
:param offset: Do not worry about this for now.
:param order_by: Do not worry about this for now.
:return: A list containing dictionaries. A dictionary is in the list representing each record
that matches the template. The dictionary only contains the requested fields.
'''
template_string = ''
if not Helper.is_empty(template):
if not Helper.is_template_valid(template, self.get_columns()):
self._logger.error(
'Some columns in the specified template don\'t match table columns'
)
raise Exception
template_string = 'WHERE ' + self._compose_template_string(
template)
if Helper.is_empty(field_list):
field_list_string = '*'
else:
field_list_string = self._compose_field_list_string(field_list)
query = 'SELECT ' + field_list_string + ' FROM ' + '`' + self._data[
'table_name'] + '`' + template_string + ';'
with self._connection.cursor() as cursor:
try:
cursor.execute(query)
result = cursor.fetchall()
if len(result) == 0:
return []
return result
except pymysql.Error as error:
self._logger.error(
'Failed to find record(s) in the table {}'.format(error))
raise Exception
def find_by_template(self,
template,
field_list=None,
limit=None,
offset=None,
order_by=None):
'''
:param template: A dictionary of the form { 'field1' : value1, 'field2': value2, ...}
:param field_list: A list of request fields of the form, ['fielda', 'fieldb', ...]
:param limit: Do not worry about this for now.
:param offset: Do not worry about this for now.
:param order_by: Do not worry about this for now.
:return: A list containing dictionaries. A dictionary is in the list representing each record
that matches the template. The dictionary only contains the requested fields.
'''
if not Helper.is_empty(template):
if not Helper.is_template_valid(template, self.get_columns()):
self._logger.error(
'Some columns in the specified template don\'t match table columns'
)
raise Exception
if not Helper.is_empty(field_list):
if not Helper.is_column_list_valid(field_list, self.get_columns()):
self._logger.error(
'Some columns in the specified field_list don\'t match table columns'
)
raise Exception
matching_rows = []
for row in self.get_rows():
if limit is not None:
if len(matching_rows) == limit:
break
if Helper.matches_template(row, template):
matching_rows.append(
Helper.extract_needed_fields(field_list, row))
return matching_rows
def update_by_template(self, template, new_values):
'''
:param template: Template for rows to match.
:param new_values: New values to set for matching fields.
:return: Number of rows updated.
'''
if Helper.is_empty(new_values):
return 0
template_string = ''
if not Helper.is_empty(template):
if not Helper.is_template_valid(template, self.get_columns()):
self._logger.error(
'Some columns in the specified template don\'t match table columns'
)
raise Exception
template_string = 'WHERE ' + self._compose_template_string(
template)
update_string = ''
for key in new_values:
update_string = update_string + '`' + key + '`=\'' + new_values[
key] + '\', '
update_string = update_string[:-2]
query = 'UPDATE ' + '`' + self._data[
'table_name'] + '` SET ' + update_string + template_string + ';'
with self._connection.cursor() as cursor:
try:
cursor.execute(query)
if cursor.rowcount > 0:
if self._auto_commit:
self.commit()
return cursor.rowcount
except pymysql.Error as error:
self._logger.error(
'Failed to update record(s) in the table {}'.format(error))
raise Exception
def update_by_key(self, key_fields, new_values):
'''
:param key_fields: List of value for the key fields.
:param new_values: A dict of field:value to set for updated row.
:return: Number of rows updated.
'''
if Helper.is_empty(self._data['key_columns']):
self._logger.error('Table has no primary keys')
raise Exception
if not Helper.are_key_fields_valid(key_fields,
self._data['key_columns']):
self._logger.error('Key fields are not valid')
raise Exception
if Helper.is_empty(new_values):
return 0
template = Helper.convert_key_fields_to_template(
key_fields, self._data['key_columns'])
return self.update_by_template(template, new_values)
def delete_by_key(self, key_fields):
'''
Deletes the record that matches the key.
:param template: A template.
:return: A count of the rows deleted.
'''
if Helper.is_empty(self._data['key_columns']):
self._logger.error('Table has no primary keys')
raise Exception
if not Helper.are_key_fields_valid(key_fields,
self._data['key_columns']):
self._logger.error('Key fields are not valid')
raise Exception
template = Helper.convert_key_fields_to_template(
key_fields, self._data['key_columns'])
return self.delete_by_template(template)