def update_customer_credit(customer_id, credit_limit):
"""
search an existing customer by customer_id and update their credit limit
or raise a ValueError exception if the customer does not exist
"""
try:
with database.transaction():
a_customer = Customer.get(Customer.customer_id == customer_id)
logger.info(f'Customer: {customer_id} credit limit is '
f'{a_customer.credit_limit}.')
except (IndexError, ValueError, peewee.DoesNotExist):
logger.info(f'Customer: {customer_id} not found.')
database.close()
return
with database.transaction():
query = (Customer.update({
Customer.credit_limit: credit_limit
}).where(Customer.customer_id == customer_id))
query.execute()
a_customer = Customer.get(Customer.customer_id == customer_id)
logger.info(f'Customer: {customer_id} credit limit is updated to '
f'{a_customer.credit_limit}.')
database.close()
def search_customer(customer_id):
"""
This function will return a dictionary object with name, lastname,
email address and phone number of a customer or an empty dictionary
object if no customer was found.
"""
ret_dict = {}
try:
database.connect()
database.execute_sql('PRAGMA foreign_keys = ON;') # needed for sqlite
with database.transaction():
cust = Customer.get(Customer.customer_id == customer_id)
if cust is not None:
ret_dict['name'] = cust.name
ret_dict['lastname'] = cust.lastname
ret_dict['email_address'] = cust.email_address
ret_dict['phone_number'] = cust.phone_info
logger.info('Database query successful')
else:
logger.error(f'Customer {customer_id} not found')
raise ValueError
except Exception as thrown_exception:
logger.info(f'Error querying {customer_id}')
logger.info(thrown_exception)
logger.info('See how the database protects our data')
finally:
database.close()
return ret_dict
def add_customer(**kwargs):
"""
This function will add a new customer to the sqlite3 database. keyword
args to keep pylint happy are the following:
customer_id, name, lastname, home_address, phone_number,
email_address, status, credit_limit
Ensure you application will create an empty database if one doesn’t exist
when the app is first run. Call it customers.db
"""
try:
database.connect()
database.execute_sql('PRAGMA foreign_keys = ON;') # needed for sqlite
with database.transaction():
new_cust = Customer.create(
customer_id=kwargs['customer_id'],
name=kwargs['name'],
lastname=kwargs['lastname'],
home_address=kwargs['home_address'],
phone_info=kwargs['phone_number'],
email_address=kwargs['email_address'],
status=True if kwargs['status'] == 'Active' else False,
credit_limit=kwargs['credit_limit'])
new_cust.save()
# logger.info('Database add successful')
except KeyError:
logger.error('kwargs not complete')
raise ValueError
except Exception as thrown_exception:
logger.info('Error creating customer')
logger.info(thrown_exception)
logger.info('See how the database protects our data')
finally:
database.close()
def delete_customer(customer_id):
""" delete a customer from the database """
try:
with database.transaction():
a_customer = Customer.get(Customer.customer_id == customer_id)
a_customer.delete_instance()
logger.info(f'Customer: {customer_id} deleted.')
except peewee.DoesNotExist:
logger.info(f'Customer: {customer_id} not found.')
finally:
database.close()
def list_active_customers():
"""
return an integer with the number of customers whose status is
currently active
"""
count = None
with database.transaction():
count = Customer.select().where(Customer.active).count()
logger.info(f'active customer count: {count}')
database.close()
return count
def util_drop_tables():
"""
Utility function that drops tables after testing
"""
tables = [Customer, PhoneInfo]
try:
database.connect()
database.execute_sql('PRAGMA foreign_keys = ON;') # needed for sqlite
database.drop_tables(tables)
logger.info('Database table drop successful')
except Exception as thrown_exception:
logger.info('Error dropping Customer table')
logger.info(thrown_exception)
logger.info('See how the database protects our data')
finally:
database.close()
def add_customer(customer_id, name, last_name, home_address, phone_number,
email_address, active, credit_limit):
""" add a new customer to the database """
try:
with database.transaction():
new_customer = Customer.create(customer_id=customer_id,
name=name,
last_name=last_name,
home_address=home_address,
phone_number=phone_number,
email_address=email_address,
active=active,
credit_limit=credit_limit)
new_customer.save()
logger.info(f'Customer {customer_id} add successful')
except (peewee.IntegrityError, peewee.OperationalError):
logger.info(f'Error adding customer: {customer_id}')
finally:
database.close()
def update_customer_credit(customer_id, credit_limit):
"""
This function will search an existing customer by customer_id
and update their credit limit or raise a ValueError exception
if the customer does not exist.
The credit limit stored in the database is returned as a float.
The value is 0.0 if the customer_id is not valid. The value is
the current value in the database if it _cannot_ be updated,
else it is the updated value stored in the databsae
"""
ret_credit = float(0.0)
if credit_limit < 0.0:
logger.info(f'Error setting credit limit for {customer_id}')
logger.info(f'Credit limit is below 0: {credit_limit}')
raise ValueError
try:
database.connect()
database.execute_sql('PRAGMA foreign_keys = ON;') # needed for sqlite
with database.transaction():
cust = Customer.get(Customer.customer_id == customer_id)
if cust is not None:
ret_credit = cust.credit_limit
logger.info(f'Updating credit limit from {cust.credit_limit} \
to {credit_limit}')
cust.credit_limit = credit_limit
ret_credit = cust.credit_limit
cust.save()
logger.info('Database update successful')
else:
logger.error(f'Customer {customer_id} not found')
raise ValueError
except Exception as thrown_exception:
logger.info(f'Error querying {customer_id}')
logger.info(thrown_exception)
logger.info('See how the database protects our data')
finally:
database.close()
return ret_credit
def delete_customer(customer_id):
"""
This function will delete the record of a customer matching
customer_id
"""
try:
database.connect()
database.execute_sql('PRAGMA foreign_keys = ON;') # needed for sqlite
with database.transaction():
cust = Customer.get(Customer.customer_id == customer_id)
if cust is not None:
cust.delete_instance()
logger.info('Database delete successful')
else:
logger.error(f'Customer {customer_id} not found')
raise ValueError
except Exception as thrown_exception:
logger.info(f'Error getting {customer_id}')
logger.info(thrown_exception)
logger.info('See how the database protects our data')
finally:
database.close()
def search_customer(customer_id):
"""
look up a customer and return a dictionary object with customer information
or an empty dictionary object if no customer was found
"""
customer_info = {}
try:
with database.transaction():
a_customer = Customer.get(Customer.customer_id == customer_id)
customer_info['name'] = a_customer.name
customer_info['last name'] = a_customer.last_name
customer_info['email address'] = a_customer.email_address
customer_info['phone number'] = a_customer.phone_number
logger.info(f'Customer: {customer_id} info returned.')
except peewee.DoesNotExist:
logger.info(f'Customer: {customer_id} not found.')
finally:
database.close()
return customer_info
def list_active_customers():
"""
This function will return an integer with the number of
customers whose status is currently active.
"""
active_cust = 0
try:
database.connect()
database.execute_sql('PRAGMA foreign_keys = ON;') # needed for sqlite
for cust in Customer.select().where(Customer.status != 0):
logger.info(f'Customer {cust.customer_id} is active')
active_cust += 1
logger.debug(f"active_cust: {active_cust}")
logger.info('Database query successful')
except Exception as thrown_exception:
logger.info(f'Error querying database')
logger.info(thrown_exception)
logger.info('See how the database protects our data')
finally:
database.close()
return active_cust
},
]
# Create Customer table in the database
database.drop_tables(MODELS)
database.create_tables(MODELS)
logging.info(f"database = {database.get_tables()}")
# All all customers in the CLIENTS dictionary into the database
for person in CLIENTS:
add_customer(**person)
# Search for an existing customer in the database
a_client = search_customer(1)
logging.info(f"a_client = {a_client}")
# Update an existing customer's credit limit in the database
update_customer_credit(2, 15000)
logging.info(f"new limit = {Customer.get_by_id(2).credit_limit}")
# Return the total number of customers in the database whose status is currently active
active_customers = list_active_customers()
logging.info(f"There are {active_customers} active customers.")
# Delete an existing customer in the database
delete_customer(1)
# Delete Customer table and close the database
database.drop_tables(MODELS)
database.close()
def tearDown(self):
Customer.delete().execute()
database.close()
def create_customer_table():
""" create Customer table if not exists """
with database:
Customer.create_table()
logger.info('Customer table create successful')
database.close()
