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 = SQLHelper.create_select(table_name=self.table_name,
template=template,
fields=field_list)
res, data = SQLHelper.run_q(sql=sql,
args=args,
conn=self.connect_info,
commit=self.commit)
if not data:
return None
else:
return data[0]
def insert(self, new_record):
"""
:param new_record: A dictionary representing a row to add to the set of records.
:return: None
"""
cox = pymysql.connect(**self.connect_info)
sql, args = SQLHelper.create_insert(self.table_name, new_record)
num, res = SQLHelper.run_q(sql, args, cox)
return None
def insert(self, new_record):
"""
:param new_record: A dictionary representing a row to add to the set of records.
:return: None
"""
sql, args = SQLHelper.create_insert(self._table_name, new_record)
res, data = SQLHelper.run_q(sql, args, conn=self._connect)
return res, data
def delete_by_template(self, template):
"""
:param template: Template to determine rows to delete.
:return: Number of rows deleted.
"""
cox = pymysql.connect(**self.connect_info)
sql, args = SQLHelper.create_delete(table_name=self.table_name,
template=template)
num, res = SQLHelper.run_q(sql, args, cox)
return num
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.
"""
cox = pymysql.connect(**self.connect_info)
sql, args = SQLHelper.create_update(self.table_name, new_values,
template)
num, res = SQLHelper.run_q(sql, args, conn=cox)
return num
def delete_by_template(self, template):
"""
:param template: Template to determine rows to delete.
:return: Number of rows deleted.
"""
sql, args = SQLHelper.create_delete_template(self._table_name,
template)
res, data = SQLHelper.run_q(sql, args, conn=self._connect)
return res, data
def insert(self, new_record):
"""
:param new_record: A dictionary representing a row to add to the set of records.
:return: None
"""
sql, args = SQLHelper.create_insert(table_name=self.table_name,
row=new_record)
res, data = SQLHelper.run_q(sql=sql,
args=args,
conn=self.connect_info,
commit=self.commit)
return None
def delete_by_key(self, key_fields):
"""
Deletes the record that matches the key.
:param template: A template.
:return: A count of the rows deleted.
"""
cox = pymysql.connect(**self.connect_info)
template = dict(zip(self.key_columns, key_fields))
sql, args = SQLHelper.create_delete(self.table_name, template)
num, res = SQLHelper.run_q(sql, args, cox)
return num
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 = SQLHelper.create_update_template(self._table_name,
template, new_values)
res, data = SQLHelper.run_q(sql, args, conn=self._connect)
return res, data
def update_by_key(self, key_fields, new_values):
"""
:param key_fields: List of value for the key fields.
:param new_values: A dict of field:value to set for updated row.
:return: Number of rows updated.
"""
cox = pymysql.connect(**self.connect_info)
template = dict(zip(self.key_columns, key_fields))
sql, args = SQLHelper.create_update(self.table_name, new_values,
template)
num, res = SQLHelper.run_q(sql, args, cox)
return num
def find_by_primary_key(self, key_fields, field_list=None):
"""
:param key_fields: The list with the values for the key_columns, in order, to use to find a record.
:param field_list: A subset of the fields of the record to return.
:return: None, or a dictionary containing the requested fields for the record identified
by the key.
"""
cox = pymysql.connect(**self.connect_info)
template = dict(zip(self.key_columns, key_fields))
sql, args = SQLHelper.create_select(self.table_name, template,
field_list)
num, res = SQLHelper.run_q(sql, args, cox)
def update_by_key(self, key_fields, new_values):
"""
:param key_fields: List of value for the key fields.
:param new_values: A dict of field:value to set for updated row.
:return: Number of rows updated.
"""
sql, args = SQLHelper.create_update_key_fields(self._table_name,
self._key_columns,
key_fields, new_values)
res, data = SQLHelper.run_q(sql, args, conn=self._connect)
return res, data
def delete_by_key(self, key_fields):
"""
Deletes the record that matches the key.
:param template: A template.
:return: A count of the rows deleted.
"""
template = {k: v for k, v in zip(self.key_columns, key_fields)}
sql, args = SQLHelper.create_delete(table_name=self.table_name,
template=template)
res, data = SQLHelper.run_q(sql=sql,
args=args,
conn=self.connect_info,
commit=self.commit)
return res
def delete_by_key(self, key_fields):
"""
Deletes the record that matches the key.
:param key_fields: The list with the values for the key_columns, in order, to use to find a record.
:return: A count of the rows deleted.
"""
clause = SQLHelper.key_columns_to_clause(self._key_columns)
sql = "Delete From " + self._table_name + clause
res, data = SQLHelper.run_q(sql, key_fields, conn=self._connect)
return res, data
def update_by_key(self, key_fields, new_values):
"""
:param key_fields: List of value for the key fields.
:param new_values: A dict of field:value to set for updated row.
:return: Number of rows updated.
"""
template = {k: v for k, v in zip(self.key_columns, key_fields)}
sql, args = SQLHelper.create_update(table_name=self.table_name,
template=template,
new_values=new_values)
res, data = SQLHelper.run_q(sql=sql,
args=args,
conn=self.connect_info,
commit=self.commit)
return res
def find_by_primary_key(self, key_fields, field_list=None):
"""
:param key_fields: The list with the values for the key_columns, in order, to use to find a record.
:param field_list: A subset of the fields of the record to return.
:return: None, or a dictionary containing the requested fields for the record identified
by the key.
"""
fields = SQLHelper.get_targeted_fields(field_list)
clause = SQLHelper.key_columns_to_clause(self._key_columns)
sql = "select" + fields + "from " + self._table_name + clause
result = SQLHelper.run_q(sql, key_fields, conn=self._connect)
return result
def find_by_primary_key(self, key_fields, field_list=None):
"""
:param key_fields: The list with the values for the key_columns, in order, to use to find a record.
:param field_list: A subset of the fields of the record to return.
:return: None, or a dictionary containing the requested fields for the record identified
by the key.
"""
template = {k: v for k, v in zip(self.key_columns, key_fields)}
sql, args = SQLHelper.create_select(table_name=self.table_name,
template=template,
fields=field_list)
res, data = SQLHelper.run_q(sql=sql,
args=args,
conn=self.connect_info,
commit=self.commit)
if not data:
return None
else:
return data[0]
def get_rows(self):
cox = pymysql.connect(**self.connect_info)
sql, args = SQLHelper.create_select(self.table_name, None, None)
num, res = SQLHelper.run_q(sql, args, cox)
return res