def create_client(self, client):
sql = "INSERT INTO client VALUES (DEFAULT,%s ,%s) RETURNING *"
cursor = connection.cursor()
cursor.execute(sql, (client.username, client.accounts))
connection.commit()
record = cursor.fetchone()
return Client(record[0], record[1])
def create_account(self, account):
sql = "INSERT INTO account VALUES (DEFAULT,%s,%s) RETURNING *"
cursor = connection.cursor()
cursor.execute(sql, (account.balance, account.client_id))
connection.commit()
record = cursor.fetchone()
return Account(record[0], record[1])
def update_account(self, change):
sql = "UPDATE account SET balance = %s WHERE account_id = %s RETURNING *"
cursor = connection.cursor()
cursor.execute(sql, (change.balance, change.account_id))
connection.commit()
record = cursor.fetchone()
new_account = Account(record[0], float(record[1]), record[2])
return new_account
def get_client(self, client_id):
sql = "SELECT * FROM client where client_id = %s"
cursor = connection.cursor()
cursor.execute(sql, [client_id])
record = cursor.fetchone()
if record:
return Client(record[0], record[1])
else:
raise ResourceNotFound(f"Client with ID: {client_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 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 all_accounts(self, client_id):
amountLessThan = request.args.get('amountLessThan')
amountGreaterThan = request.args.get('amountGreaterThan')
if amountLessThan and amountGreaterThan:
sql = "SELECT * FROM account WHERE (balance BETWEEN %s and %s and client_id = %s) "
cursor = connection.cursor()
cursor.execute(sql, (int(amountGreaterThan),
int(amountLessThan), client_id))
records = cursor.fetchall()
account_list = []
for record in records:
account = Account(record[0], float(record[1]), record[2])
account_list.append(account.json())
return account_list
else:
sql = "SELECT * FROM account where client_id = %s"
cursor = connection.cursor()
cursor.execute(sql, (client_id))
records = cursor.fetchall()
account_list = []
for record in records:
account = Account(record[0], float(record[1]), record[2])
account_list.append(account.json())
return account_list
def all_client(self):
sql = "SELECT * FROM client"
cursor = connection.cursor()
cursor.execute(sql)
records = cursor.fetchall()
client_list = []
for record in records:
accounts = record[2]
client = Client(record[0], record[1])
for account in accounts:
client.accounts.append(AccountDAOImpl.get_account(account))
client_list.append(client.json())
return client_list
def delete_client(self, client_id):
sql = "DELETE FROM client WHERE client_id = %s"
cursor = connection.cursor()
cursor.execute(sql, [client_id])
connection.commit()
def delete_account(self, account_id):
sql = "DELETE FROM account WHERE account_id = %s"
cursor = connection.cursor()
cursor.execute(sql, [account_id])
connection.commit()