def _fetch_all_and_make_objects(cls, sql): cursor = connection.cursor() cursor.execute(sql) keys = [x[0].lower() for x in cursor.description] rows = cursor.fetchall() return [cls(keys, x) for x in rows]
def delete_record(self, password): sql = "DELETE FROM employee_information WHERE password=%s" cursor = connection.cursor() cursor.execute(sql, [password]) connection.commit() return ""
def update_approval(case_id, level): sql = "UPDATE trainings SET " + level + "=TRUE WHERE case_id=%s RETURNING *" cursor = connection.cursor() cursor.execute(sql, [case_id]) connection.commit() return ""
def create_record(self, permission, fk1=None, fk2=None): sql = "INSERT INTO permissions VALUES (%s, %s, %s) RETURNING *" cursor = connection.cursor() cursor.execute(sql, (permission.position_name, permission.department_head_access, permission.ben_co_access)) connection.commit() record = cursor.fetchone() return Permissions(record[0], record[1], record[2])
def delete_record(self, position_name): sql = "DELETE FROM permissions WHERE position_name=%s" cursor = connection.cursor() cursor.execute(sql, [position_name]) connection.commit() return ""
def update_record(self, change): sql = "UPDATE departments SET department_head=%s WHERE department_name=%s RETURNING *" cursor = connection.cursor() cursor.execute(sql, (change.department_head, change.department_name)) connection.commit() return ""
def update_reimbursement(self, password, change): sql = "UPDATE reimbursement_funds_remaining=%s WHERE password=%s RETURNING *" cursor = connection.cursor() cursor.execute(sql, (change, password)) connection.commit() return ""
def delete_record(self, department_name): sql = "DELETE FROM departments WHERE department_name=%s" cursor = connection.cursor() cursor.execute(sql, [department_name]) connection.commit() return ""
def create_record(self, employee, fk1=None, fk2=None): sql = "INSERT INTO employees VALUES (%s) RETURNING *" cursor = connection.cursor() cursor.execute(sql, [employee.username]) connection.commit() record = cursor.fetchone() return Employee(record[0])
def delete_record(self, username): sql = "DELETE FROM employees WHERE username=%s" cursor = connection.cursor() cursor.execute(sql, [username]) connection.commit() return ""
def _get_quality(self): cursor = connection.cursor() cursor.execute("SELECT Quality FROM [AWR].[dbo].[FLOW_QUALITY] WHERE Station ='"+str(self.station)+"'") data = cursor.fetchall() if len(data) > 0 and type(data[0][0]) == float: return data[0][0] else: return 4.0
def update_reimbursement_amount(case_id, value): sql = "UPDATE trainings SET reimbursement_amount=%s WHERE case_id=%s RETURNING *" cursor = connection.cursor() cursor.execute(sql, (value, case_id)) connection.commit() return ""
def delete_record(self, tuition_name): sql = "DELETE FROM tuition_types WHERE tuition_name=%s" cursor = connection.cursor() cursor.execute(sql, [tuition_name]) connection.commit() return ""
def update_query(change): sql = "UPDATE trainings SET query=%s WHERE case_id=%s RETURNING *" cursor = connection.cursor() cursor.execute(sql, (change.query, change.case_id)) connection.commit() return ""
def update_record(self, change): sql = "UPDATE permissions SET department_head_access=%s, ben_co_access=%s WHERE position_name=%s RETURNING *" cursor = connection.cursor() cursor.execute(sql, (change.department_head_access, change.ben_co_access, change.position_name)) connection.commit() return ""
def delete_record(self, case_id): sql = "DELETE FROM trainings WHERE case_id=%s" cursor = connection.cursor() cursor.execute(sql, [case_id]) connection.commit() return ""
def create_record(self, department, fk1=None, fk2=None): sql = "INSERT INTO departments VALUES (%s, %s) RETURNING *" cursor = connection.cursor() cursor.execute( sql, (department.department_name, department.department_head)) connection.commit() record = cursor.fetchone() return Department(record[0], record[1])
def create_record(self, tuition, fk1=None, fk2=None): sql = "INSERT INTO tuition_types (%s,%s,%s,%s) RETURNING *" cursor = connection.cursor() cursor.execute(sql, (tuition.tuition_name, tuition.grading_format, tuition.grade_cutoff, tuition.reimbursement_percent)) connection.commit() record = cursor.fetchone() return TuitionType(record[0], record[1], record[2], float(record[3]))
def _get_quality(self): cursor = connection.cursor() cursor.execute( "SELECT Quality FROM [AWR].[dbo].[FLOW_QUALITY] WHERE Station ='" + str(self.station) + "'") data = cursor.fetchall() if len(data) > 0 and type(data[0][0]) == float: return data[0][0] else: return 4.0
def update_record(self, change): sql = "UPDATE tuition_types SET grading_format=%s, grade_cutoff=%s, reimbursement_percent=%s " \ "WHERE tuition_name=%s RETURNING *" cursor = connection.cursor() cursor.execute(sql, (change.grading_format, change.grade_cutoff, change.reimbursement_percent, change.tuition_name)) connection.commit() return ""
def get_record(self, username, fk1=None, fk2=None): sql = "SELECT * FROM employees WHERE username=%s" cursor = connection.cursor() cursor.execute(sql, [username]) record = cursor.fetchone() if record: return Employee(record[0]) else: raise ResourceNotFound(f"Employee with user {username} not found.")
def get_record(self, position_name, fk1=None, fk2=None): sql = "SELECT * FROM permissions WHERE position_name=%s" cursor = connection.cursor() cursor.execute(sql, [position_name]) record = cursor.fetchone() if record: return Permissions(record[0], record[1], record[2]) else: raise ResourceNotFound(f"Permissions for position {position_name} not found.")
def update_record(self, change): sql = "UPDATE employee_information SET last_name=%s,first_name=%s,dob=%s,department=%s," \ "position_name=%s,reimbursement_funds_remaining=%s WHERE password=%s RETURNING *" cursor = connection.cursor() cursor.execute(sql, (change.last_name, change.first_name, change.dob, change.department, change.position_name, change.reimbursement_funds_remaining, change.password)) connection.commit() return ""
def get_all_records(self): sql = "SELECT * FROM departments" cursor = connection.cursor() cursor.execute(sql) records = cursor.fetchall() department_list = [] for record in records: department = Department(record[0], record[1]) department_list.append(department.json()) return department_list
def get_record(self, department_name, fk1=None, fk2=None): sql = "SELECT * FROM departments WHERE department_name=%s" cursor = connection.cursor() cursor.execute(sql, [department_name]) record = cursor.fetchone() if record: return Department(record[0], record[1]) else: raise ResourceNotFound( f"Department with name {department_name} not found.")
def get_all_records(self): sql = "SELECT * FROM employees" cursor = connection.cursor() cursor.execute(sql) records = cursor.fetchall() employee_list = [] for record in records: employee = Employee(record[0]) employee_list.append(employee.json()) return employee_list
def get_all_records(self): sql = "SELECT * FROM permissions" cursor = connection.cursor() cursor.execute(sql) records = cursor.fetchall() permission_list = [] for record in records: permission = Permissions(record[0], record[1], record[2]) permission_list.append(permission.json()) return permission_list
def create_record(self, employee, fk1=None, fk2=None): sql = "INSERT INTO employee_information VALUES (%s,%s,%s,%s,%s,%s,%s,%s) RETURNING *" cursor = connection.cursor() cursor.execute( sql, (employee.last_name, employee.first_name, employee.username, employee.password, employee.dob, employee.department, employee.position_name, employee.reimbursement_funds_remaining)) connection.commit() record = cursor.fetchone() return EmployeeInformation(record[0], record[1], record[2], record[3], record[4], record[5], record[6], record[7])
def update_record(self, change): sql = "UPDATE trainings SET ds_approval=%s, dh_approval=%s, benco_approval=%s, final_grade=%s, " \ "reimbursement_amount=%s, query=%s, query_whom=%s, answer=%s WHERE case_id=%s RETURNING *" cursor = connection.cursor() cursor.execute( sql, (change.ds_approval, change.dh_approval, change.benco_approval, change.final_grade, change.reimbursement_amount, change.query, change.query_whom, change.answer, change.case_id)) connection.commit() return ""
def get_record(self, tuition_name, fk1=None, fk2=None): sql = "SELECT * FROM tuition_types WHERE tuition_name=%s" cursor = connection.cursor() cursor.execute(sql, [tuition_name]) record = cursor.fetchone() if record: return TuitionType(record[0], record[1], record[2], float(record[3])) else: raise ResourceNotFound( f"Tuition by name {tuition_name} not found.")
def get_all_records(self): sql = "SELECT * FROM tuition_types" cursor = connection.cursor() cursor.execute(sql) records = cursor.fetchall() tuitions_list = [] for record in records: tuition = TuitionType(record[0], record[1], record[2], float(record[3])) tuitions_list.append(tuition.json()) return tuitions_list
def __init__(self): self.stations = nx.Graph() cursor = connection.cursor() cursor.execute("SELECT code, next_station, name, municipality FROM [AWR].[dbo].[HSY_TARGETS]" # "WHERE target_type = 'JVP'" "WHERE next_station <> 'NULL'" "AND next_station <> ''") rows = cursor.fetchall() for i in rows: fr = PumpStation(i[0]) to = PumpStation("VKM") if "VKM" in i[1] else PumpStation(i[1]) self.stations.add_edge(fr, to)