def insert(self, new_record):
"""
:param new_record: A dictionary representing a row to add to the set of records.
:return: None
"""
# Get the list of columns.
sql, args = dbutils.create_insert(self._full_table_name, new_record)
res, d = dbutils.run_q(sql, args=args, conn=self._cnx)
return res
def update_by_template(self, template, new_values):
"""
:param template: A template that defines which matching rows to update.
:param new_values: A dictionary containing fields and the values to set for the corresponding fields
in the records.
:return: The number of rows updates.
"""
sql, args = dbutils.create_update(self._full_table_name, template=template, changed_cols=new_values)
res, d = dbutils.run_q(sql, args=args, conn=self._cnx, commit=True)
return res
def get_row_count(self):
"""
:return: Returns the count of the number of rows in the table.
"""
# -- TO IMPLEMENT --
q = "select count(*) from " + self._table_name
res, d = dbutils.run_q(q, conn=self._cnx)
# print(d[0]['count(*)'],type(d[0]['count(*)']),"132",type(d),res)
return d[0]['count(*)']
def delete_by_template(self, template):
"""
Deletes all records that match the template.
:param template: A template.
:return: A count of the rows deleted.
"""
try:
sql, args = dbutils.create_select(self._full_table_name, template=template, is_select=False)
res, d = dbutils.run_q(sql, args=args, conn=self._cnx, commit=True)
return res
except Exception as e:
print("Got exception e = ", e)
raise e
def get_primary_key_columns(self):
"""
:return: A list of the primary key columns ordered by their position in the key.
"""
# -- TO IMPLEMENT --
q = "SELECT `COLUMN_NAME` FROM `information_schema`.`COLUMNS` WHERE (`TABLE_SCHEMA` = '" + self._db_name + "') AND (`TABLE_NAME` = '" + self._table_name + "') AND (`COLUMN_KEY` = 'PRI');"
res, d = dbutils.run_q(q, conn=self._cnx)
print(d)
col_name = []
i = len(d) - 1
for j in d:
if i >= 0:
col_name.append(d[i]['COLUMN_NAME'])
i -= 1
# print(col_name,type(col_name))
# print(d[0]['COLUMN_NAME'],type([d[0]['COLUMN_NAME']]),"146")
return col_name
def find_by_template(self, template, field_list=None, limit=None, offset=0, order_by=None, commit=True):
"""
: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.
"""
result = None
try:
sql, args = dbutils.create_select(self._full_table_name, template=template, fields=field_list, limit=limit,
offset=offset)
res, data = dbutils.run_q(sql=sql, args=args, conn=self._cnx, commit=True, fetch=True)
except Exception as e:
print("Exception e = ", e)
raise e
return list(data)