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