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