def update_product(id, product):
try:
connection = get_connection()
cursor = connection.cursor(prepared=True)
stmt = 'UPDATE products SET updated_at = CURRENT_TIMESTAMP, name = %s, description = %s, price = %s, id_category = %s, id_brand = %s WHERE id = %s'
cursor.execute(
stmt, (product['name'], product['description'], product['price'],
product['id_category'], product['id_brand'], id))
connection.commit()
row_count = cursor.rowcount
cursor.close()
connection.close()
return {'rows_affected': row_count}
except mysql.connector.Error as err:
cursor.close()
connection.close()
print(f'Error: {err.msg}')
if err.errno == 1452 or err.errno == 1366:
abort(400)
else:
abort(500)
except:
cursor.close()
connection.close()
abort(500)
def insert_product(product):
try:
connection = get_connection()
cursor = connection.cursor(prepared=True)
stmt = 'INSERT INTO products (name, description, price, id_category, id_brand) VALUES (%s, %s, %s, %s, %s)'
cursor.execute(
stmt, (product['name'], product['description'], product['price'],
product['id_category'], product['id_brand']))
connection.commit()
last_row_id = cursor.lastrowid
product['id'] = last_row_id
response = {'message': 'INSERTED', 'record': product}, 201
cursor.close()
connection.close()
return response
except KeyError as err:
cursor.close()
connection.close()
abort(400)
except mysql.connector.Error as err:
cursor.close()
connection.close()
print(f'Error: {err.msg}')
if err.errno == 1452 or err.errno == 1366:
abort(400)
else:
abort(500)
except:
cursor.close()
connection.close()
abort(500)
def get_category_by_id(id):
connection = get_connection()
cursor = connection.cursor()
cursor.execute(
'SELECT id, name, description, created_at, updated_at FROM categories WHERE id = %s',
(id, ))
category = cursor.fetchone()
cursor.close()
connection.close()
return category
def remove_product(id):
try:
connection = get_connection()
cursor = connection.cursor(prepared=True)
stmt = 'DELETE FROM products WHERE id = %s'
cursor.execute(stmt, (id, ))
connection.commit()
cursor.close()
connection.close()
return {'removed': id}
except:
cursor.close()
connection.close()
abort(500)
def insert_category(name, description):
try:
connection = get_connection()
cursor = connection.cursor(prepared=True)
stmt = 'INSERT INTO categories (name, description) VALUES (%s, %s)'
cursor.execute(stmt, (name, description))
connection.commit()
cursor.close()
connection.close()
return True
except:
cursor.close()
connection.close()
return False
def update_category(id, name, description):
try:
connection = get_connection()
cursor = connection.cursor(prepared=True)
stmt = 'UPDATE categories SET updated_at = CURRENT_TIMESTAMP, name = %s, description = %s WHERE id = %s'
cursor.execute(stmt, (name, description, id))
connection.commit()
row_count = cursor.rowcount
cursor.close()
connection.close()
return row_count > 0
except:
cursor.close()
connection.close()
return False
def delete_category(id):
try:
connection = get_connection()
cursor = connection.cursor(prepared=True)
stmt = 'DELETE FROM categories WHERE id = %s'
cursor.execute(stmt, (id, ))
connection.commit()
row_count = cursor.rowcount
cursor.close()
connection.close()
return row_count > 0
except:
cursor.close()
connection.close()
return False
def customer_login(email, password):
connection = get_connection()
cursor = connection.cursor()
stmt = 'SELECT id, email, password, active FROM customers WHERE email = %s'
cursor.execute(stmt, (email, ))
row = cursor.fetchone()
if row is None:
abort(403)
is_valid = bcrypt.checkpw(password.encode('utf-8'), row[2].encode('utf-8'))
if is_valid:
token = generate_token({'sub': row[0], 'active': row[3]})
else:
abort(403)
cursor.close()
connection.close()
return {'accessToken': token.decode('utf-8')}
def get_all_categories():
connection = get_connection()
cursor = connection.cursor()
cursor.execute(
'SELECT id, name, description, created_at, updated_at FROM categories')
rows = cursor.fetchall()
cursor.close()
connection.close()
categories = []
for item in rows:
categories.append(
dict(id=item[0],
name=item[1],
description=item[2],
created_at=item[3],
updated_at=item[4]))
return categories_schema.dump(categories)
def customer_info(id):
connection = get_connection()
cursor = connection.cursor()
stmt = 'SELECT id, email, firstname, lastname, birth, active, created_at, updated_at FROM customers WHERE id = %s'
cursor.execute(stmt, (id, ))
row = cursor.fetchone()
if row is None:
abort(404)
else:
return dict(id=row[0],
email=row[1],
firstname=row[2],
lastname=row[3],
birth=row[4],
active=row[5],
created_at=row[6],
updated_at=row[7])
def get_product_by_id(id):
connection = get_connection()
cursor = connection.cursor()
cursor.execute(
'SELECT id, name, description, price, id_category, id_brand, created_at, updated_at FROM products WHERE id = %s',
(id, ))
item = cursor.fetchone()
if item is None:
abort(404)
cursor.close()
connection.close()
product = dict(id=item[0],
name=item[1],
description=item[2],
price=item[3],
id_category=item[4],
id_brand=item[5],
created_at=item[6],
updated_at=item[7])
return product_schema.dump(product)
def get_all_products():
connection = get_connection()
cursor = connection.cursor()
cursor.execute(
'SELECT id, name, description, price, id_category, id_brand, created_at, updated_at FROM products'
)
rows = cursor.fetchall()
cursor.close()
connection.close()
products = []
for item in rows:
products.append(
dict(id=item[0],
name=item[1],
description=item[2],
price=item[3],
id_category=item[4],
id_brand=item[5],
created_at=item[6],
updated_at=item[7]))
return products_schema.dump(products)
def customer_register(customer):
try:
customer_schema.load(customer)
hashed_password = bcrypt.hashpw(customer['password'].encode('utf-8'),
bcrypt.gensalt())
connection = get_connection()
cursor = connection.cursor(prepared=True)
stmt = 'INSERT INTO customers (email, password, firstname, lastname, birth) VALUES (%s, %s, %s, %s, %s)'
cursor.execute(
stmt,
(customer['email'], hashed_password.decode('utf-8'),
customer['firstname'], customer['lastname'], customer['birth']))
connection.commit()
last_row_id = cursor.lastrowid
customer['password'] = hashed_password.decode('utf-8')
customer['id'] = last_row_id
response = {'message': 'REGISTERED', 'record': customer}, 201
cursor.close()
connection.close()
return response
except KeyError:
cursor.close()
connection.close()
abort(400)
except mysql.connector.Error as err:
cursor.close()
connection.close()
if err.errno == 1062:
abort(409)
if err.errno == 1452 or err.errno == 1366:
abort(400)
else:
abort(500)
raise Exception(err)
except marshmallow.exceptions.ValidationError:
abort(400)
except:
cursor.close()
connection.close()
abort(500)
