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_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 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_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 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 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 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 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 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 t_rdb_delete_by_key():
connect_info = SQLHelper._get_default_connection()
rdb_tbl = RDBDataTable("people", connect_info, ["playerID"], commit=False)
print("Lines removed:", rdb_tbl.delete_by_key(["abbotgl01"]), "should equal 1")
print("Lines removed:", rdb_tbl.delete_by_key(["abbotgl01"]), "should equal 0")
print("Lines removed:", rdb_tbl.delete_by_key(["abbeych01"]), "should equal 1")
def t_rdb_insert():
connect_info = SQLHelper._get_default_connection()
rdb_tbl = RDBDataTable("people", connect_info, ["playerID"], commit=False)
rdb_tbl.insert({"playerID": "abbotgl99"})
if not rdb_tbl.find_by_primary_key(["abbotgl99"]):
print("insert test, failed")
else:
print("insert test, passed")
def t_rdb_find_by_primary_key():
connect_info = SQLHelper._get_default_connection()
rdb_tbl = RDBDataTable("people", connect_info, ["playerID"], commit=False)
r = {
"playerID": "abbotgl01"
}
print(rdb_tbl.find_by_primary_key(r.values()))
print(rdb_tbl.find_by_primary_key(r.values(), ["birthCity", "birthState"]))
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 t_rdb_update_by_key():
connect_info = SQLHelper._get_default_connection()
rdb_tbl = RDBDataTable("people", connect_info, ["playerID"], commit=False)
keys = ["abbotgl01"]
new_val = {"deathYear": "123", "deathMonth": "12", "deathDay": "1"}
print("Lines updated:", rdb_tbl.update_by_key(keys, new_val), "should equal 1")
print("Lines updated:", rdb_tbl.update_by_key(keys, new_val), "should equal 0")
print("Lines updated:", rdb_tbl.update_by_key(keys, {"playerID": "abbotgl01"}), "should equal 0")
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 t_rdb_delete_by_template():
connect_info = SQLHelper._get_default_connection()
rdb_tbl = RDBDataTable("people", connect_info, ["playerID"], commit=False)
r = {
"playerID": "abbotgl01",
"birthYear": "1951",
"birthMonth": "2"
}
print("Lines removed:", rdb_tbl.delete_by_template(r), "should equal 1")
print("Lines removed:", rdb_tbl.delete_by_template(r), "should equal 0")
print("Lines removed:", rdb_tbl.delete_by_template({"birthYear": "1951"}), "should > 1")
print("Lines removed:", rdb_tbl.delete_by_template({"birthYear": "1951"}), "should equal 0")
def t_rdb_find_by_template():
connect_info = SQLHelper._get_default_connection()
rdb_tbl = RDBDataTable("people", connect_info, ["playerID"], commit=False)
r = {
"playerID": "aasedo01",
"birthYear": "1954",
"birthCity": "Orange"
}
# print(rdb_tbl.find_by_template({}))
print(rdb_tbl.find_by_template(r))
print(rdb_tbl.find_by_template(r, ["weight", "height"]))
print(rdb_tbl.find_by_template(r, ["deathYear"]))
def delete_by_key_test():
connection = SQLHelper.get_default_connection()
table_name = "lahman2019raw.people"
rdb_table = RDBDataTable(table_name, ['playerID'], connection)
result = rdb_table.delete_by_key(['abbotgl01'])
if result[0] is not None:
print("%d rows deleted" % result[0])
else:
print("None.")
def find_by_primary_key_test():
connection = SQLHelper.get_default_connection()
table_name = "lahman2019raw.people"
rdb_table = RDBDataTable(table_name, ['playerID'], connection)
result = rdb_table.find_by_primary_key(
['aardsda01'], ['playerID', 'birthYear', 'birthMonth', 'birthDay'])
if result[1] is not None:
print(json.dumps(result[1], indent=2))
else:
print("None.")
def __init__(self, table_name, connect_info, key_columns, commit=False):
"""
: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 not table_name:
table_name = "lahman2019raw"
if not key_columns:
raise Exception("Key columns can't be empty")
self.table_name = table_name
self.connect_info = connect_info if connect_info else SQLHelper._get_default_connection(
)
self.key_columns = key_columns
self.commit = commit # if testing, don't commit
def delete_by_template_test():
connection = SQLHelper.get_default_connection()
table_name = "lahman2019raw.people"
rdb_table = RDBDataTable(table_name, ['playerID'], connection)
template = {"birthCity": "Zanesville"}
result = rdb_table.delete_by_template(template)
if result[0] is not None:
print("%d rows deleted" % result[0])
else:
print("None.")
def find_by_template_test():
connection = SQLHelper.get_default_connection()
table_name = "lahman2019raw.people"
rdb_table = RDBDataTable(table_name, ['playerID'], connection)
template = {"birthCity": "La Guaira"}
result = rdb_table.find_by_template(
template, ['playerID', 'birthYear', 'birthMonth', 'birthDay'])
if result[1] is not None:
print(json.dumps(result, indent=2))
else:
print("None.")
def update_by_key_test():
connection = SQLHelper.get_default_connection()
table_name = "lahman2019raw.batting"
rdb_table = RDBDataTable(table_name,
['playerID', 'yearID', 'stint', 'teamID'],
connection)
result = rdb_table.update_by_key(["aardsda01", "2010", "1", "SEA"], {
"teamID": "ATL",
"AB": "11"
})
if result[1] is not None:
print("%d rows updated" % result[0])
print(json.dumps(result[0], indent=2))
else:
print("None.")
def insert():
connection = SQLHelper.get_default_connection()
table_name = "lahman2019raw.batting"
rdb_table = RDBDataTable(table_name,
['playerID', 'yearID', 'stint', 'teamID'],
connection)
result = rdb_table.insert({
"playerID": "test1",
"yearID": "test",
"stint": 1,
"teamID": "test"
})
if result[0] is not None:
print("%d rows inserted" % result[0])
else:
print("None.")