def is_login(sk):
"""This method is used to verify if at least one administrator has been setup."""
try:
conn = Connect(host=HOST, user=USER, password=PASSWORD, database=DATABASE)
except ConnectionError as e:
print(e)
return False
cur = conn.cursor()
query = "SELECT * FROM `administrator`"
try:
cur.execute(query)
except errors.ProgrammingError as e:
print(e)
return False
conn.close()
response = cur.fetchall()
if response:
b = Bcrypt()
try:
return b.check_password_hash(request.cookies['admin_session'], sk)
except KeyError as e:
return False
else:
return True
def get_all_employees_from_db():
emps = []
try:
#connect to db
db_connection = Connect(user='******',
password='******',
database='training',
port=3306)
#get the db cursor
db_cursor = db_connection.cursor()
#identify the query and execute using cursor
db_cursor.execute("select * from emp_data")
#process data received from cursor
for (empno, name, salary) in db_cursor:
emps.append(
Employee({
"empno": empno,
"name": name,
"daily_salary": salary
}))
#close the cursor and connection
db_connection.close()
db_cursor.close()
except:
print("exception occurred", sys.exc_info())
return emps
def auth_admin(self):
"""Authenticate administrator trying to login."""
try:
conn = Connect(host=HOST, user=USER, password=PASSWORD, database=DATABASE)
except ConnectionError as e:
print(e)
return False
cur = conn.cursor()
query = "SELECT `password` FROM `administrator` WHERE `user_name` = '{}'".format(self.user_name)
try:
cur.execute(query)
except errors.ProgrammingError as e:
print(e)
return False
response = cur.fetchall()
conn.close()
bcrypt = Bcrypt()
try:
return bcrypt.check_password_hash(response[0][0], self.password)
except IndexError:
return False
def create_employee(self):
try:
conn = Connect(host=HOST, user=USER, password=PASSWORD, database=DATABASE)
except ConnectionError as e:
print(e)
return False
cur = conn.cursor()
query = ("INSERT INTO `employees`(name, street, city, state, zipcode, phone, email, join_date)"
"VALUES ('{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}')"
"".format(self.name, self.address['street'], self.address['city'], self.address['state'],
self.address['zipcode'], self.phone, self.email, self.join_date))
if self.name == "" or self.phone == "" or self.email == "" or self.address == "":
raise Exception
try:
cur.execute(query)
except errors.ProgrammingError as e:
print(e)
return False
conn.commit()
conn.close()
return True
def create_admin(self):
if not self.check_duplicate():
try:
conn = Connect(host=HOST, user=USER, password=PASSWORD, database=DATABASE)
except ConnectionError as e:
print(e)
return False
cur = conn.cursor()
bcrypt = Bcrypt()
query = ("INSERT INTO "
"`administrator`(name, user_name, password, street, city, state, zipcode, phone, email, join_date)"
"VALUES ('{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}')"
"".format(self.name, self.user_name, bcrypt.generate_password_hash(self.password),
self.address['street'], self.address['city'], self.address['state'],
self.address['zipcode'], self.phone, self.email, self.join_date))
try:
cur.execute(query)
except errors.ProgrammingError as e:
print(e)
return False
conn.commit()
conn.close()
return True
return False
def create_database():
try:
conn = Connect(host=HOST, user=USER, password=PASSWORD)
except:
raise ConnectionError
cur = conn.cursor()
query = 'CREATE DATABASE IF NOT EXISTS `pytry`'
try:
cur.execute(query)
except errors.ProgrammingError as e:
print(e)
conn.close()
def delete_employee_from_db(empno):
try:
#connect to db
db_connection = Connect(user='******',
password='******',
database='training',
port=3306)
#get the db cursor
db_cursor = db_connection.cursor()
#identify the query and execute using cursor
db_cursor.execute("delete from emp_data where empno = %s",
(empno, ))
db_connection.commit()
db_connection.close()
db_cursor.close()
except:
print("Excepion Occurred", sys.exc_info())
return
def create_item_table():
try:
conn = Connect(host=HOST, user=USER, password=PASSWORD, database=DATABASE)
except:
raise ConnectionError
cur = conn.cursor()
query = ("CREATE TABLE IF NOT EXISTS `items` (`id` INT(11) NOT NULL AUTO_INCREMENT, "
"`description` VARCHAR(255), `market_price` DECIMAL(6,2) NOT NULL, "
"`costumer_price` DECIMAL(6,2) NOT NULL, `distributor_price` DECIMAL(6,2) NOT NULL, "
"`quantity` INT(11) NOT NULL, PRIMARY KEY(`id`));")
try:
cur.execute(query)
except errors.ProgrammingError as e:
print(e)
conn.close()
def create_costumer_table():
try:
conn = Connect(host=HOST, user=USER, password=PASSWORD, database=DATABASE)
except:
raise ConnectionError
cur = conn.cursor()
query = ("CREATE TABLE IF NOT EXISTS `costumers` (`id` INT(11) NOT NULL AUTO_INCREMENT, "
"`name` VARCHAR(255) NOT NULL, `street` VARCHAR(255) NOT NULL, `city` VARCHAR(255) NOT NULL,"
"`state` VARCHAR(255) NOT NULL, `zipcode` VARCHAR(255) NOT NULL, `phone` VARCHAR(255) NOT NULL,"
"`email` VARCHAR(255) NOT NULL, PRIMARY KEY(`id`));")
try:
cur.execute(query)
except errors.ProgrammingError as e:
print(e)
conn.close()
def create_invoice_table():
try:
conn = Connect(host=HOST, user=USER, password=PASSWORD, database=DATABASE)
except:
raise ConnectionError
cur = conn.cursor()
query = ("CREATE TABLE IF NOT EXISTS `invoices`(`id` INT(11) NOT NULL AUTO_INCREMENT,"
"`invoice_id` VARCHAR(255),`costumer` VARCHAR(255) NOT NULL, `date` DATE NOT NULL,"
"seller VARCHAR(255) NOT NULL, `items` TEXT NOT NULL, `total` DECIMAL(6,2),"
"is_pending BOOLEAN NOT NULL, PRIMARY KEY(`id`));")
try:
cur.execute(query)
except errors.ProgrammingError as e:
print(e)
conn.close()
def add_employee_to_db(new_emp):
try:
#connect to db
db_connection = Connect(user='******',
password='******',
database='training',
port=3306)
#get the db cursor
db_cursor = db_connection.cursor()
#identify the query and execute using cursor
db_cursor.execute(
"insert into emp_data values(%s,%s,%s)",
(new_emp.empno, new_emp.name, new_emp.salary_per_day))
db_connection.commit()
db_connection.close()
db_cursor.close()
except:
new_emp = None
print("Exception Occured", sys.exc_info())
return new_emp
def get_pending_invoices():
try:
conn = Connect(host=HOST, user=USER, password=PASSWORD, database=DATABASE)
except ConnectionError as e:
print(e)
return False
cur = conn.cursor()
query = "SELECT * FROM invoices WHERE `is_pending` = 1"
try:
cur.execute(query)
except errors.ProgrammingError as e:
print(e)
return None
response = cur.fetchall()
conn.close()
return response
def get_item_description_by_id(id):
try:
conn = Connect(host=HOST, user=USER, password=PASSWORD, database=DATABASE)
except ConnectionError as e:
print(e)
return False
cur = conn.cursor()
query = ("SELECT `description` FROM items WHERE id = '{}'".format(id))
try:
cur.execute(query)
except errors.ProgrammingError as e:
print(e)
return None
response = cur.fetchall()
conn.close()
return response[0][0]
def delete_invoice(self):
try:
conn = Connect(host=HOST, user=USER, password=PASSWORD, database=DATABASE)
except ConnectionError as e:
print(e)
return False
cur = conn.cursor()
query = "DELETE FROM `invoices` WHERE `invoice_id` = '{}'".format(self.invoice_id)
try:
cur.execute(query)
except errors.ProgrammingError as e:
print(e)
return False
conn.commit()
conn.close()
return True
def erase_employee(id):
try:
conn = Connect(host=HOST, user=USER, password=PASSWORD, database=DATABASE)
except ConnectionError as e:
print(e)
return False
cur = conn.cursor()
query = ("DELETE FROM employees WHERE '{}' = id".format(id))
try:
cur.execute(query)
except errors.ProgrammingError as e:
print(e)
return False
conn.commit()
conn.close()
return True
def get_invoice_by_id(self):
try:
conn = Connect(host=HOST, user=USER, password=PASSWORD, database=DATABASE)
except ConnectionError as e:
print(e)
return False
cur = conn.cursor()
query = ("SELECT * FROM invoices WHERE `invoice_id` = {}".format(self.invoice_id))
try:
cur.execute(query)
except errors.ProgrammingError as e:
print(e)
return None
response = cur.fetchall()
conn.close()
return response
def check_if_admin_exist():
"""This method is used to verify if at least one administrator has been setup."""
try:
conn = Connect(host=HOST, user=USER, password=PASSWORD, database=DATABASE)
except ConnectionError as e:
print(e)
return False
cur = conn.cursor()
query = "SELECT * FROM `administrator`"
try:
cur.execute(query)
except errors.ProgrammingError as e:
print(e)
return False
conn.close()
return cur.fetchall()
def change_pending_status(invoce_num):
try:
conn = Connect(host=HOST, user=USER, password=PASSWORD, database=DATABASE)
except ConnectionError as e:
print(e)
return False
cur = conn.cursor()
query = ("SELECT `is_pending` FROM `invoices` "
"WHERE `invoice_id` = '{}'".format(invoce_num))
try:
cur.execute(query)
except errors.ProgrammingError as e:
print(e)
return None
response = cur.fetchall()
is_pending = response[0][0]
new_status = None
if is_pending == 1:
new_status = 0
elif is_pending == 0:
new_status = 1
query = ("UPDATE `invoices` SET `is_pending` = {} "
"WHERE invoice_id = '{}';".format(new_status, invoce_num))
try:
cur.execute(query)
except errors.ProgrammingError as e:
print(e)
return None
conn.commit()
conn.close()
return response
def create_invoice(self):
try:
conn = Connect(host=HOST, user=USER, password=PASSWORD, database=DATABASE)
except ConnectionError as e:
print(e)
return False
cur = conn.cursor()
query = ("INSERT INTO `invoices`(invoice_id, costumer, date, seller, items, total, is_pending)"
"VALUES ('{}', '{}', '{}', '{}', '{}', '{}', '{}')"
"".format(self.invoice_id, self.info, self.date, self.seller, self.items, self.total, self.pending))
try:
cur.execute(query)
except errors.ProgrammingError as e:
print(e)
return False
conn.commit()
conn.close()
return True
def create_business(self):
try:
conn = Connect(host=HOST, user=USER, password=PASSWORD, database=DATABASE)
except ConnectionError as e:
print(e)
return False
cur = conn.cursor()
query = ("INSERT INTO `business`(name, street, city, state, zipcode, phone, email, manager)"
"VALUES ('{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}')"
"".format(self.name, self.address['street'], self.address['city'], self.address['state'],
self.address['zipcode'], self.phone, self.email, self.manager))
try:
cur.execute(query)
except errors.ProgrammingError as e:
print(e)
return False
conn.commit()
conn.close()
return True
def add_item(self):
try:
conn = Connect(host=HOST, user=USER, password=PASSWORD, database=DATABASE)
except ConnectionError as e:
print(e)
return False
cur = conn.cursor()
query = ("INSERT INTO `items`(description, market_price, costumer_price, distributor_price, quantity)"
"VALUES('{}', '{}', '{}', '{}', '{}')"
"".format(self.description, self.market_price, self.costumer_price, self.distributor_price,
self.quantity))
try:
cur.execute(query)
except errors.ProgrammingError as e:
print(e)
return False
conn.commit()
conn.close()
return True
def check_duplicate(self):
try:
conn = Connect(host=HOST, user=USER, password=PASSWORD, database=DATABASE)
except ConnectionError as e:
print(e)
return False
cur = conn.cursor()
# Check if username exist
query = "SELECT * FROM `administrator` WHERE `user_name` = '{}'".format(self.user_name)
try:
cur.execute(query)
except errors.ProgrammingError as e:
print(e)
return False
response = cur.fetchall()
print(response)
# If so, return True
if response:
conn.close()
return True
# If not, check if email exists
query = "SELECT * FROM `administrator` WHERE `email` = '{}'".format(self.email)
try:
cur.execute(query)
except errors.ProgrammingError as e:
print(e)
return False
response = cur.fetchall()
print(response)
if response:
conn.close()
return True
conn.close()
# If not return False
return False
class MyDBConnection:
"""
Database connection class
"""
def __init__(self, conf=None, db_type=None):
"""
Function name : __init__()
If conf file path is passed , read the file and write its content to conf_vars dictionary.
Update conf_vars dictionary for specified db_type
Args : self (reference to the object of class MyDBConnection), conf (path to config file of DB),db_type (DB type name defined in config file)
Returns : None
Exception : None
"""
self.conf_vars = {}
self.db = None
if conf:
execfile(conf, self.conf_vars)
self.conf_vars = self.conf_vars[db_type]
#del self.conf_vars['__builtins__']
def get_connection(self):
"""
Function name : get_connection()
Return DB object of specified DB reading parameters from conf_vars dictionary
Args : self (reference to the object of class MyDBConnection)
Returns : DB object
Exception : SQL_ERR (write to log)
Exception (write to log)
"""
try:
self.db = Connect(host=self.conf_vars['host'],
port=self.conf_vars['port'],
user=self.conf_vars['user'],
password=self.conf_vars['password'],
database=self.conf_vars['database'])
except SQL_ERR as e:
logger.exception('Exception: %s', str(e))
print e
except Exception as exp:
logger.exception('Exception: %s', str(exp))
print exp
return self.db
def close_connection(self):
"""
Function name : close_connection()
Closes the MySQL DB connection
Args : self (reference to the object of class MyDBConnection)
Returns : None
Exception : None
"""
if isinstance(self.db, mysql.connector.connection.MySQLConnection):
self.db.close()
def close(self, conn: Connect):
"""
Closes the connection to the database
"""
conn.close()
