def update_user(self, change): sql = "UPDATE users SET firstname = %s, lastname=%s, phone=%s WHERE id =%s" cursor = connection.cursor() cursor.execute( sql, (change.firstname, change.lastname, change.phone, change.user_id)) connection.commit()
def withdraw_funds(self, client_id, bank_acct_id, amount): self.proj_zero_log("Attempting to withdraw funds...") 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: if float(amount) <= float(str(record[3])): sql = "update bank_accounts set funds=%s where holder_id = %s and id = %s returning *" cursor = connection.cursor() cursor.execute(sql, (float(str(record[3])) - float(amount), int(client_id), int(bank_acct_id))) connection.commit() record = cursor.fetchone() if record: return BankAcct(record[0], record[1], record[2], float(str(record[3])), record[4]) else: raise ResourceUnavailable( "Insufficient funds for that withdrawal.") else: raise ResourceNotFound("Bank account not found.")
def update_courses(self, change, courseid): # Update Courses Table sql = "UPDATE courses SET coursename=%s WHERE courseid=%s " cursor = connection.cursor() cursor.execute(sql, change.coursename) connection.commit() record = cursor.fetchone() return Courses(int(record[0]), record[1], int(record[2]))
def setup_process_for_tr(self, employee): if employee.direct_supervisor == 0: if employee.department_head == 0: step = 9 else: step = 5 else: step = 1 end_date = date.today() + timedelta(days=5) process_item = Process(0, "pending", step, str(end_date), str(False), employee.employee_id) sql = "INSERT INTO process (process_name, step, begin_date, completed, tr_instance_id) " \ "VALUES (%s,%s,%s,%s,%s) returning *" cursor = connection.cursor() cursor.execute(sql, (process_item.process_name, process_item.step, process_item.begin_date, process_item.completed, process_item.tr_instance_id)) connection.commit() record = cursor.fetchone() cursor.close() if record: return Process(record[0], record[1], record[2], record[3], record[4], record[5]) else: raise ResourceNotFound( f"The grading data was not formatted for the database.")
def delete_employee(self, employee_id): sql = "DELETE FROM employees WHERE employee_id = %s" log(f"Deleting an employee") cursor = connection.cursor() cursor.execute(sql, [employee_id]) connection.commit() return 'deleted'
def create_account(self, account): sql = 'INSERT INTO account VALUES (DEFAULT, %s, %s) RETURNING *' cursor = connection.cursor() cursor.execute(sql, (account.account_id, account.account_balance, account.cl_id)) connection.commit() record = cursor.fetchone() new_account = Account(record[0], record[1], record[2]) return new_account
def create_user(user): sql = "Insert into banking.users Values(default,%s,%s,%s) RETURNING *" cursor = connection.cursor() cursor.execute(sql, (user.username, user.first_name, user.last_name)) connection.commit() record = cursor.fetchone() new_user = Users(record[0], record[1], record[2], record[3]) return new_user
def create_acct(account): sql = "Insert into banking.accounts values (default,%s,%s,%s) returning *" cursor = connection.cursor() cursor.execute(sql, (account.acct_num, account.balance, account.userid)) connection.commit() record = cursor.fetchone() new_acct = Accounts(record[0], record[1], record[2], record[3]) return new_acct
def update_client(cls, client): sql = f"UPDATE clients SET client_name = '{client.name}'WHERE id = {int(client.client_id)} RETURNING client_name;" cursor = connection.cursor() cursor.execute(sql) connection.commit() record = cursor.fetchone() if record: return "Done!" return "no such client exist", 404
def delete_account_with_id(cls, client_id, account_id): account = cls.get_account_with_id(client_id, account_id) if not account: return "no account or client exists", 404 sql = f"DELETE FROM accounts WHERE id = {account_id} AND client_id={client_id};" cursor = connection.cursor() cursor.execute(sql) connection.commit() return "Deleted account"
def update_client(self, change): sql = "UPDATE clients SET client_name=%s WHERE client_id=%s RETURNING * " cursor = connection.cursor() cursor.execute(sql, (change.client_name, change.client_id)) connection.commit() record = cursor.fetchone() return Client(record[0], record[1])
def create_account(self, account): sql = "INSERT INTO account VALUES(DEFAULT, %s, %s, %s, %s, %s) RETURNING *" cursor = connection.cursor() cursor.execute(sql, (account.account_type, account.amt_dep, account.amt_withdraw, account.status, account.userid)) connection.commit() record = cursor.fetchone() return Account(Account(record[0], record[1], record[2], float(record[3]), record[4]))
def create_client(self, client): # 201 sql = "INSERT INTO clients (name) VALUES (%s) RETURNING *" cursor = connection.cursor() cursor.execute(sql, [client.client_name]) connection.commit() record = cursor.fetchone() if record: return Client(record[0], record[1]) else: raise ResourceNotFound(f"Client could not be created")
def delete_client(cls, client_id): sql = f"DELETE FROM clients WHERE id ={client_id} RETURNING client_name;" cursor = connection.cursor() cursor.execute(sql) connection.commit() record = cursor.fetchone() if record: return "Success!", 205 return "no such client exist", 404
def update_account(self, change): sql = "UPDATE accounts SET deposit_amount=%s, withdraw_amount=%s, account_balance=%s WHERE account_id=%s " \ "RETURNING * " cursor = connection.cursor() cursor.execute(sql, change.deposit_amount, change.withdraw_amount, change.account_balance) connection.commit() record = cursor.fetchone() return Account(record[0], record[1], record[2], record[3])
def create_client(self, client): sql = "INSERT INTO clients VALUES (DEFAULT,%s,%s,%s) RETURNING *" cursor = connection.cursor() cursor.execute(sql, (client.first_name, client.last_name, client.pin)) connection.commit() rec = cursor.fetchone() new_client = Client(rec[0], rec[1], rec[2], rec[3]).json() return new_client
def create_client(self, client): sql = "INSERT INTO clients VALUES (DEFAULT,%s,%s,%s) RETURNING *" cursor = connection.cursor() cursor.execute(sql, (client.name, client.email, client.password)) connection.commit() record = cursor.fetchone() new_client = Client(record[0], record[1], record[2], record[3]) return new_client
def update_employee(self, change): sql = "UPDATE employees SET name=%s WHERE employee_id=%s RETURNING *" cursor = connection.cursor() cursor.execute(sql, (change.name, change.employee_id)) connection.commit() log(f"Updating an employee") record = cursor.fetchone() new_employee = Employee(record[0], record[1], record[2], record[3], record[4], record[5], record[6]) return new_employee
def add_acc_to_client(self, client_id): sql = "INSERT INTO accounts Values(default, default, %s, accounttype = 1, accountbalance = 20) RETURNING *" cursor = connection.cursor() cursor.execute(sql, [client_id]) connection.commit() record = cursor.fetchone() new_account = Account(record[0], record[1], record[2], record[3], record[4]) return new_account
def deposit_into_account(cls, client_id, account_id, amount): account = cls.get_account_with_id(client_id, account_id) if not account: return "no account or client exists", 404 else: sql = f"UPDATE accounts SET amount ={amount + account[0].amount} WHERE id = {account_id} AND client_id={client_id};" cursor = connection.cursor() cursor.execute(sql) connection.commit() return "deposit complete", 200
def create_client(self, client): sql = "INSERT INTO clients VALUES(default, %s, %s) RETURNING *" cursor = connection.cursor() cursor.execute(sql, (client.first_name, client.last_name)) connection.commit() record = cursor.fetchone() new_client = Client(record[0], record[1], record[2]) return new_client
def create_account(self, account): sql = "INSERT INTO accounts VALUES(default, default, %s, %s, %s) RETURNING *" cursor = connection.cursor() cursor.execute(sql, (account.client_id, account.account_type, account.account_balance)) connection.commit() record = cursor.fetchone() new_account = Account(record[0], record[1], record[2], record[3], record[4]) return new_account
def update_employeeRole(self, change, empid): # Update Employee Table sql = "UPDATE employee SET emprole=%s WHERE empid=%s RETURNING *" cursor = connection.cursor() # print("test if change",change) # print("test if empid", empid) cursor.execute(sql, change.emprole.change.empid) connection.commit() record = cursor.fetchone() # print(record[0],record[0],record[0],record[0],record[0],record[0],record[0],record[0]) return Employee(record[0])
def update_client(self, client): # 404 if no such client exists sql = "UPDATE clients SET name=%s WHERE id= %s RETURNING *" cursor = connection.cursor() cursor.execute(sql, (client.client_name, int(client.client_id))) connection.commit() record = cursor.fetchone() if record: return Client(record[0], record[1]) else: raise ResourceNotFound( f"Client record {client.client_id} does not exist.")
def create_client(self, client): sql = "INSERT INTO clients VALUES(DEFAULT, %s) RETURNING *;" cursor = connection.cursor() # TypeError: Object of type Client is not JSON serializable cursor.execute(sql, (client.client_name, )) record = cursor.fetchone() # cursor.execute("ROLLBACK") # because this is insertion which is part of the DML, the data needs to be committed after it's added connection.commit() returned_val = Client(client_id=record[0], client_name=record[1]) return returned_val
def update_employee(self, change): # Update Employee Table sql = "UPDATE employee SET firstname = %s, midname=%s, lastname=%s, phone=%s , email=%s, address=%s,emprole=%s, username=%s ,password=%s WHERE empid=%s RETURNING *" cursor = connection.cursor() cursor.execute( sql, (change.firstname, change.midname, change.lastname, change.phone, change.email, change.address, change.emprole, change.username, change.password, change.empid)) connection.commit() record = cursor.fetchone() return Employee(record[0], record[1], record[2], record[3], record[4], record[5], record[6], record[7], record[8], record[9])
def create_client(self, bank): sql = "INSERT INTO clients VALUES string(DEFAULT,%s,%s,%s) RETURNING *" cursor = connection.cursor() cursor.execute(sql, (bank.firstname, bank.lastname, bank.address)) connection.commit() record = cursor.fetchone() print(record) new_client = Bank(record[0], record[1], record[2]) print(new_client) return new_client
def update_user(self, change): # Update user Table sql = "UPDATE users SET first_name = %s, last_name=%s, password=%s WHERE id=%s RETURNING *" cursor = connection.cursor() cursor.execute(sql, (change.first_name, change.last_name, change.password, change.users_id)) connection.commit() record = cursor.fetchone() user = Users(record[0], record[1], record[2], record[3], record[4], record[5]) return user
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 update_acct(acct): sql = "UPDATE banking.accounts SET acctnum = %s,balance= %s, userid = %s WHERE acctid = %s RETURNING *" cursor = connection.cursor() cursor.execute( sql, (acct.acct_num, acct.balance, acct.userid, acct.acct_id)) connection.commit() record = cursor.fetchone() acct1 = Accounts(int(record[0]), int(record[1]), int(record[2]), int(record[3])) return acct1