def insert(self, new_record): """ :param new_record: A dictionary representing a row to add to the set of records. :return: None """ sql, args = dbutils.create_insert(self._data["table_name"], new_record) try: dbutils.run_q(sql, args, conn=self._cnx) except Exception as e: raise e print("Insert successfully.")
def insert(self, new_record): """ :param new_record: A dictionary representing a row to add to the set of records. :return: None """ insert = self.create_insert(self._data["table_name"], new_record=new_record) dbutils.run_q(insert[0], insert[1], fetch=True, conn=self._cnx, commit=True)
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. """ where_clause = self.template_to_where_clause(template) select = self.create_select(self._data["table_name"], template=template, fields=field_list) result = dbutils.run_q(select[0], select[1], fetch=True, conn=self._cnx, commit=True) return result
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. """ sql, args = dbutils.create_select(self._data["table_name"], template, field_list) try: _, data = dbutils.run_q(sql, args, conn=self._cnx) except Exception as e: raise e result = [] for values in data: row = dict(zip(self._data["table_columns"], values)) result.append(row) return result
def insert(self, new_record): """ :param new_record: A dictionary representing a row to add to the set of records. :return: None """ sql, args = dbutils.create_insert(self._data['table_name'], new_record) result, d = dbutils.run_q(sql, args=args, commit=True, fetch=False, conn=self._cnx) return result
def delete_by_template(self, template): """ :param template: Template to determine rows to delete. :return: Number of rows deleted. """ sql, args = dbutils.create_select(self._data['table_name'], template=template, fields=None, is_select=False) res = dbutils.run_q(sql, args=args, commit=True, conn=self._cnx, fetch=False) return res
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. """ sql, args = dbutils.create_update(self._data['table_name'], template=template, changed_cols=new_values) res = dbutils.run_q(sql, args=args, commit=True, conn=self._cnx, fetch=False) return res
def delete_by_template(self, template): """ :param template: Template to determine rows to delete. :return: Number of rows deleted. """ if template is None: return 0 sql, args = dbutils.create_delete(self._data["table_name"], template) try: res, _ = dbutils.run_q(sql, args, conn=self._cnx) except Exception as e: raise e return res
def __init__(self, table_name, connect_info, key_columns): """ :param table_name: Logical name of the table. :param connect_info: Dictionary of parameters necessary to connect to the data. :param key_columns: List, in order, of the columns (fields) that comprise the primary key. """ if table_name is None or connect_info is None: raise ValueError("Invalid input.") if key_columns is not None and len(key_columns) != len( set(key_columns)): raise ValueError("Duplicated key columns") self._data = { "table_name": table_name, "connect_info": connect_info, "key_columns": key_columns, "table_columns": None } cnx = dbutils.get_connection(connect_info) if cnx is not None: self._cnx = cnx else: raise Exception("Could not get a connection.") sql = f"SHOW KEYS FROM {self._data['table_name']} WHERE Key_name = 'PRIMARY'" _, data = dbutils.run_q(sql, conn=self._cnx) primary_key = [d[4] for d in data] if key_columns is None: self._data["key_columns"] = primary_key elif set(key_columns) != set(primary_key): raise ValueError( "Invalid primary key: not consistent with database") sql = f"SHOW COLUMNS FROM {self._data['table_name']}" _, data = dbutils.run_q(sql, conn=self._cnx) self._data["table_columns"] = [d[0] for d in data]
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 template is None: return 0 sql, args = dbutils.create_update(self._data["table_name"], new_values, template) try: res, _ = dbutils.run_q(sql, args, conn=self._cnx) except Exception as e: raise e return res
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. """ update = self.create_update(self._data["table_name"], template=template, new_values=new_values) result = dbutils.run_q(update[0], update[1], fetch=True, conn=self._cnx, commit=True) return result[0]
def delete_by_template(self, template): """ :param template: Template to determine rows to delete. :return: Number of rows deleted. """ n_rows = self.get_row_count() delete = self.create_delete(self._data["table_name"], template=template) result = dbutils.run_q(delete[0], delete[1], fetch=True, conn=self._cnx, commit=True) return n_rows - self.get_row_count()