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