def get_all_categories():
'''
Returns all categories in DB
'''
db = utils.get_db_instance()
cur = db.cursor(dictionary=True)
cur.execute('SELECT * FROM categories;')
categories = cur.fetchall()
cur.close()
db.close()
return categories
def get_all_products():
'''
Returns all products in DB
'''
db = utils.get_db_instance()
cur = db.cursor(dictionary=True)
cur.execute('SELECT * FROM products;')
products = cur.fetchall()
cur.close()
db.close()
return products
def update_product(self, product_id):
'''
Updates product
'''
db = utils.get_db_instance()
cursor = db.cursor()
query = f'UPDATE products SET name = "{self.name}", description = "{self.description}", details = "{self.details}", price = {self.price}, category_id = {self.category_id}, subcategory_id = {self.subcategory_id} WHERE id ={product_id};'
cursor.execute(query)
db.commit()
cursor.close()
db.close()
def all_by_category(category):
'''
Returns all products from db from category
'''
db = utils.get_db_instance()
cursor = db.cursor(dictionary=True)
# query = f'SELECT * FROM products LEFT JOIN categories ON products.id = categories.id WHERE products.category_id = {category};'
query = f'SELECT products.name as name, products.description, products.details, products.price, subcategories.name as subcategory FROM products LEFT JOIN subcategories ON products.subcategory_id = subcategories.id WHERE products.category_id = {category};'
cursor.execute(query)
products = cursor.fetchall()
cursor.close()
db.close()
return products
def save_to_db(query):
'''
Generic method to save to db by query
'''
db = utils.get_db_instance()
cursor = db.cursor()
cursor.execute(query)
last_id = cursor.lastrowid
db.commit()
cursor.close()
db.close()
if type(last_id) is int:
return last_id
else:
return False
def save(self):
'''
Saves instance of Product to DB and adds DB id to instance
'''
db = utils.get_db_instance()
cursor = db.cursor()
query = f'INSERT INTO products (name, description, details, price, category_id, subcategory_id) VALUES ("{self.name}", "{self.description}", "{self.details}", {self.price}, {self.category_id}, {self.subcategory_id});'
cursor.execute(query)
last_id = cursor.lastrowid
db.commit()
cursor.close()
db.close()
self.id = last_id
return self
def save_new_category(formData):
'''
Takes in form data and saves new category
'''
name = formData.get('name')
db = utils.get_db_instance()
cursor = db.cursor()
query = f'INSERT INTO categories (name) VALUES ("{name}");'
cursor.execute(query)
last_id = cursor.lastrowid
db.commit()
cursor.close()
db.close()
if type(last_id) is int:
return last_id
else:
return False
def find_by_id(self, id):
'''
Method to find Product by id from DB
'''
db = utils.get_db_instance()
cursor = db.cursor(dictionary=True)
query = f'SELECT * FROM products WHERE id = {id}'
cursor.execute(query)
product = cursor.fetchall()
cursor.close()
db.close()
product = product[0]
located_product = Product(product.name, product.description,
product.price, product.details,
product.category_id, product.subcategory_id)
located_product.id = product.id
return located_product