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