def get_products(cls): try: sql = "SELECT * FROM products;" cursor = connection.cursor() cursor.execute(sql) records = cursor.fetchall() products = [] for product in records: products.append(Products(product[0], product[1], product[2], product[3], product[4])) return products except Exception as e: raise ResourceNotFound(f"Credentials do not match any existing records. Please try again.")
def get_account(self, account_id): sql = "SELECT * FROM account where account_id = %s" cursor = connection.cursor() cursor.execute(sql, [account_id]) record = cursor.fetchone() if record: return Account(record[0], float(record[1]), record[2]) else: raise ResourceNotFound( f"Account with ID: {account_id} - Not Found")
def delete_client( self, client_id): # 404 if no such client exists & 205 == success sql = "DELETE FROM clients WHERE id=%s RETURNING *" cursor = connection.cursor() cursor.execute(sql, [client_id]) connection.commit() record = cursor.fetchone() if record: return Client(record[0], record[1]) else: raise ResourceNotFound( f"Client record {client_id} does not exist.")
def get_account(self, account): sql = "SELECT * FROM accounts WHERE id = %s AND account_client_id = %s" cursor = connection.cursor() cursor.execute( sql, (int(account.account_id), int(account.account_client_id))) record = cursor.fetchone() if record: return Account(record[0], record[1], float(record[2]), record[3]).json() else: raise ResourceNotFound( f"Account id or client account id - NOT FOUND")
def get_tuition(self, empid): # Retrieve a single Tuition by passing a Employee ID sql = "SELECT * FROM tuition WHERE empid=%s" cursor = connection.cursor() cursor.execute(sql, [empid]) record = cursor.fetchone() if record: return Tuition(record[0], record[1], record[2], record[3], record[4], record[5], record[6], record[7], record[8], record[9]) else: raise ResourceNotFound(f" This Employee id: {empid} doesn't have a Tuition created yet")
def get_account(self, account_id): pass sql = "SELECT * FROM accounts WHERE account_id = %s" # %s is a placeholder for string cursor = connection.cursor() cursor.execute(sql, [account_id]) record = cursor.fetchone() if record: return Account(record[0], record[1], record[2], record[3]) else: raise ResourceNotFound(f"Account with id: {account_id} - Not Found")
def get_client(self, client_id): sql = "SELECT * FROM clients WHERE client_id = %s" cursor = connection.cursor() cursor.execute(sql, [client_id]) rec = cursor.fetchone() if rec: client = Client(rec[0], rec[1], rec[2], rec[3]) return client else: raise ResourceNotFound("Client ID not found")
def update_accounts_after_transfer(self, account): sql = "UPDATE accounts SET amount=%s WHERE id = %s RETURNING *" cursor = connection.cursor() cursor.execute(sql, [account.account_amount, account.account_id]) connection.commit() record = cursor.fetchone() if record: return Account(record[0], record[1], float(record[2]), record[3]).json() else: raise ResourceNotFound( f"No account has account and client id provided")
def get_movie(self, movie_id): sql = "SELECT * FROM movies WHERE id = %s" cursor = connection.cursor() cursor.execute(sql, [movie_id]) record = cursor.fetchone() if record: return Movie(record[0], record[1], record[2], float(record[3]), record[4], record[5]) else: raise ResourceNotFound(f"Movie with id: {movie_id} - Not Found")
def update_client(self, change): sql = "UPDATE client SET username = %s WHERE client_id = %s RETURNING *" cursor = connection.cursor() cursor.execute(sql, (change.username, change.client_id)) connection.commit() record = cursor.fetchone() if record: return Client(record[0], record[1]) else: raise ResourceNotFound( f"Client with ID: {change.client_id} - Not Found")
def employee_authentication(self, employee): sql = "SELECT * FROM employee where employee_name = %s AND title = %s" cursor = connection.cursor() cursor.execute(sql, (employee.name, employee.title)) record = cursor.fetchone() cursor.close() if record: return Employee(record[0], record[1], record[2], record[3], float(record[4]), record[5]) else: raise ResourceNotFound( f"Employee record could not be verified with input data.")
def get_employee_by_id(self, employee_id): sql = "SELECT * FROM employee where id = %s" cursor = connection.cursor() cursor.execute(sql, [employee_id]) record = cursor.fetchone() cursor.close() if record: return Employee(record[0], record[1], record[2], record[3], float(record[4]), record[5]) else: raise ResourceNotFound( f"Employee record could not be verified with input data.")
def update_client(self, change): sql = "UPDATE clients SET firstname = %s, lastname= %s WHERE clientid = %s RETURNING *" cursor = connection.cursor() cursor.execute(sql, (change.first_name, change.last_name, change.client_id)) connection.commit() record = cursor.fetchone() if record: updated_client = Client(record[0], record[1], record[2]) return updated_client else: raise ResourceNotFound(f"Client ID: {change.client_id}- NOT FOUND")
def create_grading_type(self, grading_type): sql = "INSERT INTO grading_type (graded, score, tr_instance_id) VALUES (%s,%s,%s) returning *" cursor = connection.cursor() cursor.execute(sql, (grading_type.graded, grading_type.score, grading_type.tr_instance_id)) connection.commit() record = cursor.fetchone() cursor.close() if record: return GradingType(record[0], record[1], record[2], record[3]) else: raise ResourceNotFound( f"The grading data was not formatted for the database.")
def create_grading(self, process): sql = "INSERT INTO grading (verification_person, tr_instance_id) VALUES RETURNING *" cursor = connection.cursor() cursor.execute(sql, (process.step, process.tr_instance_id)) connection.commit() record = cursor.fetchone() cursor.close() if record: return Grading(record[0], record[1], record[2], record[3], record[4]) else: raise ResourceNotFound( f"The database doesn't accept the input types entered")
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_user_credentials(cls, user_credentials): sql = "SELECT * FROM user_credentials WHERE username= %s AND password_ = %s" cursor = connection.cursor() cursor.execute(sql, [user_credentials.user_name, user_credentials.password]) record = cursor.fetchone() if record: return UserCredentials(record[0], record[1], record[2]) else: raise ResourceNotFound( f"Credentials do not match any existing records. Please try again." )
def get_record(self, password, fk1=None, fk2=None): sql = "SELECT * FROM employee_information WHERE password=%s" cursor = connection.cursor() cursor.execute(sql, [password]) record = cursor.fetchone() if record: return EmployeeInformation(record[0], record[1], record[2], record[3], record[4], record[5], record[6], record[7]) else: raise ResourceNotFound( f"Password does not match credentials in database.")
def delete_bank_acct(self, client_id, bank_acct_id): self.proj_zero_log("Attempting to delete bank account...") sql = "delete from bank_accounts where holder_id = %s and id = %s returning *" cursor = connection.cursor() cursor.execute(sql, (int(client_id), int(bank_acct_id))) connection.commit() record = cursor.fetchone() if not record: raise ResourceNotFound("Client or account not found.")
def delete_account(self, account): sql = "DELETE FROM accounts WHERE id=%s AND account_client_id = %s RETURNING *" cursor = connection.cursor() cursor.execute( sql, (int(account.account_id), int(account.account_client_id))) connection.commit() record = cursor.fetchone() if record: changed_account = Account(record[0], record[1], float(record[2]), record[3]) return changed_account else: raise ResourceNotFound( f"No account has account and client id provided")
def create_account(self, account): self.client_id_existence_test(int(account.account_client_id)) sql = "INSERT INTO accounts (name,amount, account_client_id) VALUES (%s, %s, %s) RETURNING *" cursor = connection.cursor() cursor.execute(sql, (account.account_name, account.account_amount, account.account_client_id)) connection.commit() record = cursor.fetchone() if record: return Account(record[0], record[1], float(record[2]), record[3]) else: raise ResourceNotFound( f"The Client id enter can't be verified. Unable to create an account without an activity client id" )
def get_all_products_from_cart_by_user_id(user_id): sql = "SELECT * FROM product_cart where user_id = %s" cursor = connection.cursor() cursor.execute(sql, [user_id]) connection.commit() records = cursor.fetchall() cart_list = [] if records: for record in records: product = ProductCart(record[0], record[1], record[2], record[3], record[4], record[5]) cart_list.append(product) return cart_list else: raise ResourceNotFound(f"User with ID {user_id} does not exist. Please try again.")
def update_account(self, change): sql = "UPDATE accounts Set clientId=%s, accounttype=%s, accountbalance=%s where accountid = %s" cursor = connection.cursor() cursor.execute(sql, (change.clientId, change.accounttype, change.accountbalance)) connection.commit() record = cursor.fetchone() if record: updated_account = Account(record[0], record[1], record[2], record[3], record[4]) return updated_account else: raise ResourceNotFound(f"there is no account with that ID.")
def get_client(self, client_id): self.proj_zero_log("Attempting to get client...") sql = "select * from clients where id = %s" cursor = connection.cursor() cursor.execute(sql, [client_id]) record = cursor.fetchone() if record: return Client(record[0], record[1]) else: raise ResourceNotFound("Client not found.")
def get_record(self, case_id, fk1=None, fk2=None): sql = "SELECT * FROM trainings WHERE case_id=%s" cursor = connection.cursor() cursor.execute(sql, [case_id]) record = cursor.fetchone() if record: return Training(record[0], record[1], record[2], record[3], record[4], record[5], record[6], record[7], record[8], record[9], record[10], record[11], float(record[12]), record[13], record[14], record[15]) else: raise ResourceNotFound("No submission found for that ID.")
def get_employee( self, empid): # Retrieve a single employee by passing an EmployeeID sql = "SELECT * FROM employee WHERE empid =%s" cursor = connection.cursor() cursor.execute(sql, [empid]) record = cursor.fetchone() if record: return Employee(record[0], record[1], record[2], record[3], record[4], record[5], record[6], record[7], record[8], record[9]) else: raise ResourceNotFound(f"Employee with id: {empid} - Not Found")
def login_employees( self, username, password ): # Retrieve a single employee by passing user name and password sql = "SELECT * FROM employee WHERE username =%s and password =%s" cursor = connection.cursor() cursor.execute(sql, [username, password]) record = cursor.fetchone() if record: return Employee(record[0], record[1], record[2], record[3], record[4], record[5], record[6], record[7], record[8], record[9]) else: raise ResourceNotFound( f"Employee with id: {username} or {username} - Not Found")
def get_all_accounts(self, client_id): sql = "SELECT * FROM accounts WHERE account_client_id = %s" cursor = connection.cursor() cursor.execute(sql, [client_id]) records = cursor.fetchall() account_list = [] if records: for record in records: account = Account(record[0], record[1], float(record[2]), record[3]) account_list.append(account.json()) return account_list else: raise ResourceNotFound( f"Client with id: {client_id} has no accounts.")
def all_accounts(self): sql = "SELECT * FROM accounts" cursor = connection.cursor() cursor.execute(sql) records = cursor.fetchall() account_list = [] if records: for record in records: account = Account(record[0], record[1], record[2], record[3], record[4]) account_list.append(account.json()) else: raise ResourceNotFound(f"Currently there are no accounts- Not Found")
def get_bank_acct(self, client_id, bank_acct_id): self.proj_zero_log("Attempting to get bank account...") sql = "select * from bank_accounts where holder_id = %s and id = %s" cursor = connection.cursor() cursor.execute(sql, (int(client_id), int(bank_acct_id))) record = cursor.fetchone() if record: return BankAcct(record[0], record[1], record[2], float(str(record[3])), record[4]) else: raise ResourceNotFound("Bank account not found.")