def add_categories_from_item(item_data: ItemData):
"""
Fetches the categories from Item Data and inserts them into the granular_categories, middle_categories and
main_categories tables.
:param item_data: An ItemData object that contains the required data to insert.
"""
main_cat = item_data.main_category
middle_cat = item_data.middle_category
granular_cat = item_data.granular_category
with GearbestMySQLManager(DATABASE_NAME) as sql_mgr:
if main_cat:
sql_mgr.execute_manipulation_query(
INSERT_INTO_MAIN_CATEGORY,
[main_cat.category_id, main_cat.name, main_cat.url])
if middle_cat:
sql_mgr.execute_manipulation_query(
INSERT_INTO_CHILD_CATEGORY.replace("*",
"middle_categories"),
[
middle_cat.category_id, middle_cat.name,
middle_cat.url, main_cat.category_id
])
sql_mgr.execute_manipulation_query(
INSERT_INTO_CHILD_CATEGORY.replace("*", "granular_categories"),
[
granular_cat.category_id, granular_cat.name,
granular_cat.url, middle_cat.category_id
])
def create_review_language_table(self):
"""
Method to create the review_language table if it doesn't exist.
"""
with GearbestMySQLManager(self.database) as mgr:
logger.info("Creating table if it doesn't exist for Review Languages.")
mgr.create_table(REVIEW_TABLE_QUERY)
def update_languages(self, api_key):
"""
Goes over the amount of rows defined by maxrows from the database and retrieves the language
for each review and inserts it into a new table.
"""
with GearbestMySQLManager(self.database) as mgr:
logger.info("Retrieving reviews...")
cur = mgr.execute_selection_query(SELECT_REVIEWS_QUERY, [self.max_rows])
reviews = cur.fetchall()
enricher = LanguageEnricher(api_key=api_key)
for review in reviews:
review_text = review["review_text"]
review_id = review["id"]
logger.info("Detecting Language for Review ID: {}".format(review_id))
try:
results = enricher.get_language(review_text)
detected_language, confidence = results["language_name"], results["confidence_percentage"]
mgr.execute_manipulation_query(INSERTION_QUERY, [review_id, detected_language, confidence])
logger.info("Language Detected for Review ID: {} is {}, with confidence {}".format(review_id,
detected_language,
confidence))
# Sleep for however long to match the allowed RPM.
except EnrichmentError as err:
logger.error("An error occured: {}".format(err))
finally:
time.sleep(self.allowed_rpm / 59)
def add_item(item_data: ItemData):
with GearbestMySQLManager(DATABASE_NAME) as sql_mgr:
sql_mgr.execute_manipulation_query(INSERT_ITEM_QUERY, [
item_data.item_id, item_data.item_name, item_data.item_url,
item_data.timestamp, item_data.granular_category.category_id,
item_data.item_description, item_data.item_brand,
item_data.item_rating, item_data.customer_reviews_count,
item_data.customer_answer_count
])
def add_item_cat_relationship(item_data: ItemData):
"""
Since Items can belong to many categories and many categories can belong to an item, this method manages
the weak entity table that encompasses this relationship. It feeds off ItemData an ItemData object.
:param item_data: The given ItemData object that contains the scraped elements for that specific item.
"""
with GearbestMySQLManager(DATABASE_NAME) as sql_mgr:
sql_mgr.execute_manipulation_query(
INSERT_INTO_ITEM_TO_CAT,
[item_data.item_id, item_data.granular_category.category_id])
def retrieve_last_seen_timestamp(self, item_id):
"""
Retrieves the last seen timestamp for that specific item id.
:param item_id: The item id in question.
:return: A datetime that represents the last seen review.
"""
max_timestamp_query = "SELECT max(post_timestamp) as max_timestamp FROM reviews WHERE item_id = %s"
with GearbestMySQLManager(DATABASE_NAME) as sql_mgr:
cur = sql_mgr.execute_selection_query(max_timestamp_query,
[item_id])
res = cur.fetchone().get("max_timestamp")
return res if res else None
def add_price_from_item(item_data: ItemData):
"""
Adds a price to the price history of an item from the given data.
:param item_data: ItemData object that contains the scraped data.
"""
price_history = item_data.price_history
for price in price_history:
if not isinstance(price, PriceData):
continue
with GearbestMySQLManager(DATABASE_NAME) as sql_mgr:
sql_mgr.execute_manipulation_query(INSERT_PRICE_QUERY, [
price.price, price.currency_type, price.scraped_timestamp,
price.discount, item_data.item_id
])
def add_reviews_from_item(item_data: ItemData):
"""
Manages the addition of reviews into the reviews table within the Gearbest database. Reviews are contained within
and ItemData object.
:param item_data: The given ItemData object.
"""
reviews = item_data.reviews
if not reviews:
return
with GearbestMySQLManager(DATABASE_NAME) as sql_mgr:
for review in reviews:
sql_mgr.execute_manipulation_query(INSERT_REVIEW_QUERY, [
review.user_id, review.user_name, review.review_title,
review.review_rating, review.review_attributes,
review.review_text, review.post_timestamp,
item_data.item_id
])
def main_db():
from database_classes.gearbest_mysql_manager import GearbestMySQLManager
with GearbestMySQLManager() as mgr:
mgr.create_and_set_database()
mgr.create_tables()