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()