def RemoveRow(self, table_name, row_dict):
"""Removes a row in the database using the table name and row dict
Inputs:
table_name: string of valid table name from constants
row_dict: dictionary that coresponds to table_name
Raises:
InvalidInputError: Table name not valid
TransactionError: Must run StartTansaction before deleting
Outputs:
int: number of rows affected
"""
if (not table_name in helpers_lib.GetValidTables()):
raise errors.InvalidInputError('Table name not valid: %s' %
table_name)
if (not self.transaction_init):
raise errors.TransactionError(
'Must run StartTansaction before deleting.')
if (self.data_validation_instance is None):
self.InitDataValidation()
self.data_validation_instance.ValidateRowDict(table_name, row_dict)
where_list = []
for k in row_dict.iterkeys():
where_list.append('%s=%s%s%s' % (k, '%(', k, ')s'))
query = 'DELETE FROM %s WHERE %s' % (table_name,
' AND '.join(where_list))
self.cursor_execute(query, row_dict)
return self.cursor.rowcount
def MakeRow(self, table_name, row_dict):
"""Creates a row in the database using the table name and row dict
Inputs:
table_name: string of valid table name from constants
row_dict: dictionary that coresponds to table_name
Raises:
InvalidInputError: Table name not valid
TransactionError: Must run StartTansaction before inserting
Outputs:
int: last insert id
"""
if (not table_name in helpers_lib.GetValidTables()):
raise errors.InvalidInputError('Table name not valid: %s' %
table_name)
if (not self.transaction_init):
raise errors.TransactionError('Must run StartTansaction before '
'inserting.')
if (self.data_validation_instance is None):
self.InitDataValidation()
self.data_validation_instance.ValidateRowDict(table_name, row_dict)
column_names = []
column_assignments = []
for k in row_dict.iterkeys():
column_names.append(k)
column_assignments.append('%s%s%s' % ('%(', k, ')s'))
query = 'INSERT INTO %s (%s) VALUES (%s)' % (
table_name, ','.join(column_names), ','.join(column_assignments))
self.cursor_execute(query, row_dict)
return self.cursor.lastrowid
def GetValidTables(self):
"""Export this function to the top level of the db_access stuff so
it can be used without importing un-needed classes.
Outputs:
list: valid table names
"""
helpers_lib.GetValidTables()
def UpdateRow(self, table_name, search_row_dict, update_row_dict):
"""Updates a row in the database using search and update dictionaries.
Inputs:
table_name: string of valid table name from constants
search_row_dict: dictionary that coresponds to table_name containing
search args
update_row_dict: dictionary that coresponds to table_name containing
update args
Raises:
InvalidInputError: Table name not valid
TransactionError: Must run StartTansaction before inserting
Outputs:
int: number of rows affected
"""
if (not table_name in helpers_lib.GetValidTables()):
raise errors.InvalidInputError('Table name not valid: %s' %
table_name)
if (not self.transaction_init):
raise errors.TransactionError(
'Must run StartTansaction before deleting.')
if (self.data_validation_instance is None):
self.InitDataValidation()
self.data_validation_instance.ValidateRowDict(table_name,
search_row_dict,
none_ok=True)
self.data_validation_instance.ValidateRowDict(table_name,
update_row_dict,
none_ok=True)
query_updates = []
query_searches = []
combined_dict = {}
for k, v in update_row_dict.iteritems():
if (v is not None):
query_updates.append('%s%s%s%s' % (k, '=%(update_', k, ')s'))
combined_dict['update_%s' % k] = v
for k, v in search_row_dict.iteritems():
if (v is not None):
query_searches.append('%s=%s%s%s' % (k, '%(search_', k, ')s'))
combined_dict['search_%s' % k] = v
query = 'UPDATE %s SET %s WHERE %s' % (
table_name, ','.join(query_updates), ' AND '.join(query_searches))
self.cursor_execute(query, combined_dict)
return self.cursor.rowcount
def TableRowCount(self, table_name):
"""Counts the amount of records in a table and returns it.
Inputs:
table_name: string of valid table name from constants
Raises:
InvalidInputError: Table name not valid
TransactionError: Must run StartTansaction before getting row count.
Outputs:
int: number of rows found
"""
if (not table_name in helpers_lib.GetValidTables()):
raise errors.InvalidInputError('Table name not valid: %s' %
table_name)
if (not self.transaction_init):
raise errors.TransactionError(
'Must run StartTansaction before getting '
'row count.')
self.cursor_execute('SELECT COUNT(*) FROM %s' % table_name)
row_count = self.cursor.fetchone()
return row_count['COUNT(*)']
def ListRow(self, *args, **kwargs):
"""Lists rows in the database using a dictionary of tables. Then returns
the rows found. Joins are auto generated on the fly based on foreign keys
in the database.
Inputs:
args: pairs of string of table name and dict of rows
kwargs: lock_rows: default False
column: column to search range on, if using multiple
tables, the column must be in the first table
in args.
range_values: range tuple of values to search within for on column
is_date: boolean of if range is of dates
example usage: ListRow('users', user_row_dict,
'user_group_assignments', user_assign_row_dict,
lock_rows=True)
Raises:
TransactionError: Must run StartTansaction before inserting
UnexpectedDataError: If is_date is specified you must specify column and range
UnexpectedDataError: If column or range is specified both are needed
InvalidInputError: Found unknown option(s)
UnexpectedDataError: No args given, must at least have a pair of table name and row dict
UnexpectedDataError: Number of unnamed args is not even.
Args should be entered in pairs of table name and row dict.
InvalidInputError: Table name not valid
InvalidInputError: Column not found in row
UnexpectedDataError: Column in table is not a DateTime type
UnexpectedDataError: Date from range is not a valid datetime object
InvalidInputError: Range must be int if is_date is not set
InvalidInputError: Multiple tables were passed in but no joins were found
Outputs:
tuple of row dicts consisting of all the tables that were in the input.
all column names in the db are unique so no colisions occour
example: ({'user_name': 'sharrell', 'access_level': 10,
'user_group_assignments_group_name: 'cs',
'user_group_assignments_user_name: 'sharrell'},
{'user_name': 'sharrell', 'access_level': 10,
'user_group_assignments_group_name: 'eas',
'user_group_assignments_user_name: 'sharrell'})
"""
if (not self.transaction_init):
raise errors.TransactionError(
'Must run StartTansaction before getting '
'data.')
if (self.data_validation_instance is None):
self.InitDataValidation()
valid_tables = helpers_lib.GetValidTables()
tables = {}
table_names = []
lock_rows = False
column = None
range_values = ()
is_date = None
if (kwargs):
if ('lock_rows' in kwargs):
lock_rows = kwargs['lock_rows']
del kwargs['lock_rows']
if ('column' in kwargs):
column = kwargs['column']
del kwargs['column']
if ('range_values' in kwargs):
range_values = kwargs['range_values']
del kwargs['range_values']
if ('is_date' in kwargs):
is_date = kwargs['is_date']
del kwargs['is_date']
if (column is None and is_date is not None):
raise errors.UnexpectedDataError(
'If is_date is specified you must '
'specify column and range')
if (bool(column) ^ bool(range_values)):
raise errors.UnexpectedDataError(
'If column or range is specified '
'both are needed')
if (kwargs):
raise errors.InvalidInputError('Found unknown option(s): '
'%s' % kwargs.keys())
if (not args):
raise errors.UnexpectedDataError(
'No args given, must at least have a '
'pair of table name and row dict')
if (len(args) % 2):
raise errors.UnexpectedDataError(
'Number of unnamed args is not even. Args '
'should be entered in pairs of table name '
'and row dict.')
count = 0
for arg in args:
count += 1
if (count % 2):
if (not arg in valid_tables):
raise errors.InvalidInputError('Table name not valid: %s' %
arg)
current_table_name = arg
else:
# do checking in validate row dict to check if it is a dict
self.data_validation_instance.ValidateRowDict(
current_table_name, arg, none_ok=True, all_none_ok=True)
tables[current_table_name] = arg
table_names.append(current_table_name)
if (range_values):
if (column not in args[1]):
raise errors.InvalidInputError('Column %s not found in row'
'dictionary: %s' %
(column, args[1]))
if (is_date):
if (constants.TABLES[args[0]][column] != 'DateTime'):
raise errors.UnexpectedDataError(
'column: %s in table %s is not a'
'DateTime type' % (column, args[0]))
for date in range_values:
if (not self.data_validation_instance.isDateTime(date)):
raise errors.UnexpectedDataError(
'Date: %s from range is not a valid '
'datetime object' % date)
else:
for value in range_values:
if (not self.data_validation_instance.isUnsignedInt(value)
):
raise errors.InvalidInputError(
'Range must be int if is_date '
'is not set')
query_where = []
if (len(tables) > 1):
if (not self.foreign_keys):
self.cursor_execute(
'SELECT table_name, column_name, '
'referenced_table_name, referenced_column_name '
'FROM information_schema.key_column_usage WHERE '
'referenced_table_name IS NOT NULL AND '
'referenced_table_schema="%s"' % self.db_name)
self.foreign_keys = self.cursor.fetchall()
for key in self.foreign_keys:
if (key['table_name'] in table_names
and key['referenced_table_name'] in table_names):
query_where.append('(%(table_name)s.%(column_name)s='
'%(referenced_table_name)s.'
'%(referenced_column_name)s)' % key)
if (not query_where):
raise errors.InvalidInputError(
'Multiple tables were passed in but no '
'joins were found')
column_names = []
search_dict = {}
for table_name, row_dict in tables.iteritems():
for key, value in row_dict.iteritems():
column_names.append('%s.%s' % (table_name, key))
if (value is not None):
search_dict[key] = value
query_where.append('%s%s%s%s' % (key, '=%(', key, ')s'))
if (range_values):
search_dict['start'] = range_values[0]
search_dict['end'] = range_values[1]
query_where.append(
'%s%s%s%s' % (column, '>=%(start)s AND ', column, '<=%(end)s'))
query_end = ''
if (query_where):
query_end = 'WHERE %s' % ' AND '.join(query_where)
if (lock_rows):
query_end = '%s FOR UPDATE' % query_end
query = 'SELECT %s FROM %s %s' % (','.join(column_names),
','.join(table_names), query_end)
self.cursor_execute(query, search_dict)
return self.cursor.fetchall()