def list_of_customer(self, sort_params: list) -> list: """ Searches for all customers in the storage and returns the result :param sort_params: list of parameters for sorting :return: List """ if len(sort_params) == 0: query = """ SELECT * FROM customers; """ else: param = ",".join(sort_params) query = f""" SELECT * FROM customers ORDER BY {param}; """ customers = [] with create_connection(self.db_name, self.db_user, self.db_password, self.db_host, self.db_port) as connection: with connection.cursor() as cursor: cursor.execute(query) result = cursor.fetchall() for row in result: customer_id, full_name, position, name_of_the_organization, email, phone = row customer = Customer(customer_id, full_name, position, name_of_the_organization, email, phone) customers.append(customer) return customers
def delete_customer(self, customer: Customer) -> None: """ Remove the customer instance in the storage and writes the file :param customer: Customer :return: None """ query = f""" DELETE FROM customers WHERE customer_id = '{customer.customer_id}' """ with create_connection(self.db_name, self.db_user, self.db_password, self.db_host, self.db_port) as connection: with connection.cursor() as cursor: cursor.execute(query) connection.commit()
def find_customer(self, argument_name: str, argument_value: str) -> Customer: """ Searches for a customer in the storage by argument name and value and returns the result :param argument_name: the name of the argument to search for :param argument_value: the value of the argument to search for :return: Customer """ query = f""" SELECT * FROM customers WHERE customers.{argument_name} = '{argument_value}'; """ with create_connection(self.db_name, self.db_user, self.db_password, self.db_host, self.db_port) as connection: with connection.cursor() as cursor: cursor.execute(query) result = cursor.fetchone() if result is not None: customer_id, full_name, position, name_of_the_organization, email, phone = result customer = Customer(customer_id, full_name, position, name_of_the_organization, email, phone) return customer
def insert_customer(self, customer: Customer) -> None: """ Inserts a customer instance into the storage :param customer: Customer :return: None """ query = f""" INSERT INTO customers (customer_id, full_name, position, name_of_the_organization, email, phone) VALUES ( '{customer.customer_id}', '{customer.full_name}', '{customer.position}', '{customer.name_of_the_organization}', '{customer.email}', '{customer.phone}' ); """ with create_connection(self.db_name, self.db_user, self.db_password, self.db_host, self.db_port) as connection: with connection.cursor() as cursor: cursor.execute(query) connection.commit()
def update_customer(self, customer: Customer, updatable_arguments: dict) -> None: """ Updates the customer instance in the storage :param customer: Customer :param updatable_arguments: dict with updatable arguments :return: None """ query = f""" UPDATE customers SET full_name = '{updatable_arguments.get("full_name", customer.full_name)}', position = '{updatable_arguments.get("position", customer.position)}', name_of_the_organization = '{updatable_arguments.get("name_of_the_organization", customer.name_of_the_organization)}', email = '{updatable_arguments.get("email", customer.email)}', phone = '{updatable_arguments.get("phone", customer.phone)}' WHERE customer_id = '{customer.customer_id}'; """ with create_connection(self.db_name, self.db_user, self.db_password, self.db_host, self.db_port) as connection: with connection.cursor() as cursor: cursor.execute(query) connection.commit()