def update_or_create(category_entity: CategoryEntity):
sql_string = """INSERT INTO itpartner_category(id, name, parent_id, is_deleted)
VALUES (%s, %s, %s, FALSE)
ON DUPLICATE KEY UPDATE
name = %s,
parent_id = %s,
is_deleted=FALSE;
"""
prepared_statements = (
category_entity.id,
category_entity.name,
category_entity.parent_id,
category_entity.name,
category_entity.parent_id,
)
connection = None
try:
connection = get_connection()
with connection.cursor() as cursor:
cursor.execute(sql_string, prepared_statements)
connection.commit()
return cursor.lastrowid
finally:
if connection:
connection.close()
return False
def remove(pk: int):
sql_string = "DELETE FROM comment WHERE id=?"
prepared_statements = (pk,)
with get_connection() as connection:
cursor = connection.cursor()
cursor.execute(sql_string, prepared_statements)
connection.commit()
return cursor.lastrowid
def init_db(cameras_data=None):
db = get_connection()
db[config.CAMERAS_COLLECTION].drop()
if cameras_data:
for data in cameras_data:
db[config.CAMERAS_COLLECTION].insert_one(data)
else:
generate_cameras()
db.cameras.create_index('camera_id')
def get_all():
sql_string = "SELECT id, name FROM region"
with get_connection() as connection:
cursor = connection.cursor()
cursor.execute(sql_string)
return [
RegionEntity(_id=get_item(row, index=0),
_name=get_item(row, index=1))
for row in cursor.fetchall()
]
def generate_cameras():
db = get_connection()
timeout_error = config.CAMERAS_REQUEST_TIMEOUT_ERROR
for camera_id in range(config.CAMERAS_COUNT):
camera_data = {
'camera_id': camera_id,
'timeout_error': timeout_error > 0,
'timeout': randint(30, 40),
'images': _init_images()
}
timeout_error -= 1
db[config.CAMERAS_COLLECTION].insert_one(camera_data)
def get_by_region_id(region_id: int):
sql_string = "SELECT id, name, region_id FROM city WHERE region_id=?"
prepared_statements = (region_id,)
with get_connection() as connection:
cursor = connection.cursor()
cursor.execute(sql_string, prepared_statements)
return [
CityEntity(
_id=get_item(row, index=0),
_name=get_item(row, index=1),
_region_id=get_item(row, index=2),
) for row in cursor.fetchall()
]
def mark_all_as_deleted(self):
sql_string = "UPDATE treoplan_product SET is_deleted=TRUE;"
connection = None
try:
connection = get_connection()
with connection.cursor() as cursor:
cursor.execute(sql_string)
connection.commit()
return cursor.lastrowid
finally:
if connection:
connection.close()
return False
def create(comment_entity: CommentEntity):
sql_string = "INSERT INTO comment(first_name, second_name, last_name, phone, email, text, city_id) VALUES (?, ?, ?, ?, ?, ?, ?)"
prepared_statements = (
comment_entity.first_name,
comment_entity.second_name,
comment_entity.last_name,
comment_entity.phone,
comment_entity.email,
comment_entity.text,
comment_entity.city_id
)
with get_connection() as connection:
cursor = connection.cursor()
cursor.execute(sql_string, prepared_statements)
connection.commit()
return cursor.lastrowid
def mark_all_as_deleted():
sql_string = "UPDATE itpartner_category SET is_deleted=TRUE;"
connection = None
try:
connection = get_connection()
with connection.cursor() as cursor:
cursor.execute(sql_string)
connection.commit()
return cursor.lastrowid
finally:
if connection:
connection.close()
return False
def mark_all_as_inactive():
sql_string = "UPDATE itpartner_product SET is_active=FALSE;"
connection = None
try:
connection = get_connection()
with connection.cursor() as cursor:
cursor.execute(sql_string)
connection.commit()
return cursor.lastrowid
finally:
if connection:
connection.close()
return False
def get_products(limit=1000, offset=0):
sql_string = """SELECT ip.part, ip.sku, ip.name, ip.price, ip.quantity, ii.url
FROM itpartner_product ip
LEFT JOIN (
SELECT url, product_sku
FROM itpartner_image
WHERE id IN (
SELECT min(id)
FROM itpartner_image
GROUP BY product_sku
)
) ii ON ip.sku = ii.product_sku
WHERE ip.is_deleted=FALSE AND ip.is_active=TRUE
LIMIT %s
OFFSET %s;"""
prepared_statements = (
limit,
offset,
)
connection = None
try:
connection = get_connection()
with connection.cursor() as cursor:
cursor.execute(sql_string, prepared_statements)
products = cursor.fetchall()
return [
ProductEntity(
_part=product[0],
_sku=product[1],
_name=product[2],
_price=product[3] if product[3] else '',
_quantity=product[4] if product[4] else '',
_url=product[5] if product[5] else '',
) for product in products
]
finally:
if connection:
connection.close()
return False
def get_stat():
sql_string = """SELECT region.id, region.name, COUNT(distinct comment.id) AS comment_count FROM region
INNER JOIN city ON city.region_id = region.id
INNER JOIN comment ON comment.city_id = city.id
GROUP BY region.id
HAVING COUNT(distinct comment.id) > 5;
"""
with get_connection() as connection:
cursor = connection.cursor()
cursor.execute(sql_string)
return [
RegionStatEntity(
_id=get_item(row, index=0),
_name=get_item(row, index=1),
_comment_count=get_item(row, index=2),
) for row in cursor.fetchall()
]
def get_active_products(self, limit=1000, offset=0):
sql_string = """SELECT tp.articul, tp.prid, tp.currency, tp.name, tp.price, tp.freenom, ti.url
FROM treoplan_product tp
LEFT JOIN (
SELECT url, articul
FROM treoplan_image
WHERE id IN (
SELECT min(id)
FROM treoplan_image
GROUP BY articul
)
) ti ON tp.articul = ti.articul
WHERE tp.is_deleted=FALSE AND tp.is_active=TRUE
LIMIT %s
OFFSET %s;"""
prepared_statements = (
limit,
offset,
)
connection = None
try:
connection = get_connection()
with connection.cursor() as cursor:
cursor.execute(sql_string, prepared_statements)
products = cursor.fetchall()
return [{
'articul': product[0],
'prid': product[1],
'currency': product[2],
'name': product[3],
'price': product[4] if product[4] else 0,
'freenom': product[5] if product[5] else '',
'url': product[6] if product[6] else '',
} for product in products]
finally:
if connection:
connection.close()
return False
def clear_price_by_supplier(self, supplier: str = 'ТРЕОЛАН'):
sql_string = """DELETE FROM `price` WHERE postavchik=%s;"""
prepared_statements = (supplier, )
connection = None
try:
connection = get_connection()
with connection.cursor() as cursor:
cursor.execute(sql_string, prepared_statements)
connection.commit()
return cursor.lastrowid
finally:
if connection:
connection.close()
return False
def get_stat(region_id: int):
sql_string = """SELECT city.id, city.name, COUNT(distinct comment.id) AS comment_count FROM city
INNER JOIN comment ON comment.city_id = city.id
WHERE city.region_id=?
GROUP BY city.id;
"""
prepared_statements = (region_id,)
with get_connection() as connection:
cursor = connection.cursor()
cursor.execute(sql_string, prepared_statements)
return [
CityStatEntity(
_id=get_item(row, index=0),
_name=get_item(row, index=1),
_comment_count=get_item(row, index=2),
) for row in cursor.fetchall()
]
def get_all():
sql_string = """SELECT comment.id, comment.first_name, comment.second_name, comment.last_name,
comment.phone, comment.email, comment.text AS region_name FROM comment
ORDER BY id DESC;
"""
with get_connection() as connection:
cursor = connection.cursor()
cursor.execute(sql_string)
return [
CommentEntity(
_first_name=get_item(row, index=1),
_last_name=get_item(row, index=3),
_text=get_item(row, index=6),
_id=get_item(row, index=0),
_second_name=get_item(row, index=2),
_phone=get_item(row, index=4),
_email=get_item(row, index=5),
) for row in cursor.fetchall()
]
def get_active_products_count():
sql_string = """SELECT COUNT(*) FROM `itpartner_product`
WHERE `itpartner_product`.`is_deleted`=FALSE
AND `itpartner_product`.`is_active`=TRUE;
"""
connection = None
try:
connection = get_connection()
with connection.cursor() as cursor:
cursor.execute(sql_string)
result = cursor.fetchone()
return pydash.get(result, '0', 0)
finally:
if connection:
connection.close()
return False
def create(image_entity: ImageEntity):
sql_string = "INSERT INTO itpartner_image(url, product_sku) VALUES (%s, %s);"
prepared_statements = (
image_entity.url,
image_entity.sku,
)
connection = None
try:
connection = get_connection()
with connection.cursor() as cursor:
cursor.execute(sql_string, prepared_statements)
connection.commit()
return cursor.lastrowid
finally:
if connection:
connection.close()
return False
def get_products_without_images(limit=1000):
sql_string = """SELECT `itpartner_product`.`sku` FROM `itpartner_product`
LEFT JOIN `itpartner_image` ON `itpartner_product`.`sku` = `itpartner_image`.`product_sku`
WHERE `itpartner_product`.`has_image`=True AND `itpartner_image`.`url` IS NULL LIMIT %s;"""
prepared_statements = (limit, )
connection = None
try:
connection = get_connection()
with connection.cursor() as cursor:
cursor.execute(sql_string, prepared_statements)
products = cursor.fetchall()
return [ProductEntity(_sku=product[0]) for product in products]
finally:
if connection:
connection.close()
return False
def create(price_entities: List[PriceEntity]):
sql_string = """INSERT INTO price(article, kod, name, cena, valuta, nalichie, postavchik, img, data_dobavleniya)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, CURDATE());"""
connection = None
try:
connection = get_connection()
with connection.cursor() as cursor:
for price_entity in price_entities:
counter = 0
prepared_statements = (
price_entity.article,
price_entity.kod,
price_entity.name,
price_entity.cena,
price_entity.valuta,
price_entity.nalichie,
price_entity.postavchik,
price_entity.img,
)
cursor.execute(sql_string, prepared_statements)
counter += 1
if counter == 10000:
connection.commit()
counter = 0
if counter != 0:
connection.commit()
finally:
if connection:
connection.close()
return False
def create_prices(self, prices):
sql_string = """INSERT INTO price(article, kod, name, cena, valuta, nalichie, postavchik, img, data_dobavleniya)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, CURDATE());"""
connection = None
try:
connection = get_connection()
with connection.cursor() as cursor:
for price in prices:
counter = 0
prepared_statements = (
price.get('article'),
price.get('kod'),
price.get('name'),
price.get('cena'),
price.get('valuta'),
price.get('nalichie'),
price.get('postavchik'),
price.get('img'),
)
cursor.execute(sql_string, prepared_statements)
counter += 1
if counter == 10000:
connection.commit()
counter = 0
if counter != 0:
connection.commit()
finally:
if connection:
connection.close()
return False
def create_image(self, articul: str, url: str):
sql_string = "INSERT INTO treoplan_image(url, articul) VALUES (%s, %s);"
prepared_statements = (
url,
articul,
)
connection = None
try:
connection = get_connection()
with connection.cursor() as cursor:
cursor.execute(sql_string, prepared_statements)
connection.commit()
return cursor.lastrowid
finally:
if connection:
connection.close()
return False
def get_products_without_images(self, limit=1000):
sql_string = """SELECT `treoplan_product`.`articul` FROM `treoplan_product`
LEFT JOIN `treoplan_image` ON `treoplan_product`.`articul` = `treoplan_image`.`articul`
WHERE `treoplan_image`.`url` IS NULL LIMIT %s;"""
prepared_statements = (limit, )
connection = None
try:
connection = get_connection()
with connection.cursor() as cursor:
cursor.execute(sql_string, prepared_statements)
products = cursor.fetchall()
return [product[0] for product in products]
finally:
if connection:
connection.close()
return False
def batch_update(self, positions):
sql_string = """INSERT INTO price(article, kod, name, cena, valuta, nalichie, postavchik, img, data_dobavleniya)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, CURDATE());"""
connection = None
try:
connection = get_connection()
with connection.cursor() as cursor:
for position in positions:
counter = 0
product_dict = {
'articul': position.get('articul'),
'name': position.get('name'),
'id': position.get('id'),
'prid': position.get('prid'),
'vendor': position.get('vendor'),
'currency': position.get('currency'),
'freenom': position.get('freenom'),
'price': position.get('price'),
}
self.update_or_create(cursor, product_dict)
counter += 1
if counter == 10000:
connection.commit()
counter = 0
if counter != 0:
connection.commit()
finally:
if connection:
connection.close()
return False
def __init__(self):
self.db = get_connection()
