def save(self): """ Class method to save Item record in DB """ logger.info("Saving Item...") # Verify for update if self.item_uuid: if not Item.exists({'item_uuid': self.item_uuid}): # If wants to update but wrong UUID, return Error raise errors.ApiError(70006, "Cannot update, UUID not in DB!") # Verify for insert elif Item.exists({'gtin': self.gtin}): self.message = 'Item already exists!' self.item_uuid = Item.get(self.gtin)[0]['item_uuid'] return True # Load model m_item = g._db.model('item', 'item_uuid') if self.item_uuid: m_item.item_uuid = self.item_uuid m_item.gtin = self.gtin try: m_item.checksum = int(self.gtin[-1]) except: m_item.checksum = None m_item.name = self.name m_item.description = self.description m_item.last_modified = str(datetime.datetime.utcnow()) try: self.message = "Correctly {} Item!".format('updated' \ if self.item_uuid else 'stored') m_item.save() self.item_uuid = m_item.last_id logger.info(self.message) except Exception as e: logger.error(e) raise errors.ApiError(70002, "Issues saving in DB!")
def get(_val, by='gtin', _cols=['item_uuid'], limit=None): """ Static method to get Item info Params: ----- _val : str Value to query from by : str Column to query in _cols : list Columns to retrieve limit : int Elements to limit query Returns: ----- _items : list List of elements """ _cols = ','.join(_cols) if _cols else 'item_uuid' _query = "SELECT {} FROM item WHERE {} IN {}" \ .format(_cols, by, tuplify(_val)) if limit: _query += ' LIMIT {}'.format(limit) logger.debug(_query) try: _items = g._db.query(_query).fetch() logger.debug("Got {} items".format(len(_items))) except Exception as e: logger.error(e) raise errors.ApiError(70003, "Issues fetching elements in DB") return _items
def it_list(**kwargs): """ Static method to get all items by query Params: ----- kwargs : dict Extra arguments, such as (p, ipp, cols, etc..) Returns: ----- _resp : list List of product objects """ logger.debug('Querying items') logger.debug('fetching: {}'.format(kwargs)) # Format columns if kwargs['cols']: _cols = ','.join([x for x in \ (Item.__base_q \ + kwargs['cols'].split(',')) \ if x in Item.__attrs__]) else: _cols = ','.join([x for x in Item.__base_q]) # Format querying keys _keys = 'WHERE item_uuid IS NOT NULL' # Format paginators _p = int(kwargs['p']) if _p < 1: _p = 1 _ipp = int(kwargs['ipp']) if _ipp > 10000: _ipp = 10000 # Order by statement if 'orderby' in kwargs: _orderby = kwargs['orderby'] if kwargs['orderby'] else 'item_uuid' else: _orderby = 'item_uuid' if _orderby not in Item.__base_q: _orderby = 'item_uuid' # Build query _qry = """SELECT {} FROM item {} ORDER BY {} OFFSET {} LIMIT {} """\ .format(_cols, _keys, _orderby, (_p - 1)*_ipp, _ipp) logger.debug(_qry) # Query DB try: _resp = g._db.query(_qry).fetch() logger.info("Found {} items".format(len(_resp))) except Exception as e: logger.error(e) logger.warning("Issues fetching elements in DB!") if APP_MODE == "CONSUMER": return False if APP_MODE == "SERVICE": raise errors.ApiError(70003, "Issues fetching elements in DB") return _resp
def it_count(): """ Static method to count existent Items """ _keys = 'WHERE item_uuid IS NOT NULL' # Build query _qry = """SELECT COUNT(*) as n_items FROM item {} """\ .format(_keys) logger.debug(_qry) # Query DB try: _resp = g._db.query(_qry).fetch() logger.info("Found {} items".format(_resp[0]['n_items'])) except Exception as e: logger.error(e) logger.warning("Issues fetching elements in DB!") if APP_MODE == "CONSUMER": return False if APP_MODE == "SERVICE": raise errors.ApiError(70003, "Issues fetching elements in DB") return _resp[0]['n_items']
def delete(i_uuid): """ Static method to delete Item Params: ----- i_uuid : str Item UUID to delete Returns: ----- resp : bool Transaction status """ logger.debug("Deleting Item...") if not Item.exists({'item_uuid': i_uuid}): return {'message': "Item UUID not in DB!"} try: g._db.query("DELETE FROM item WHERE item_uuid='{}'" \ .format(i_uuid)) except Exception as e: logger.error(e) raise errors.ApiError(70004, "Could not apply transaction in DB") return {'message': "Item ({}) correctly deleted!".format(i_uuid)}
def query(_by, **kwargs): """ Static method to query by defined column values Params: ----- _by : str Key from which query is performed kwargs : dict Extra arguments, such as (p, ipp, cols, etc..) Returns: ----- _resp : list List of product objects """ logger.debug('Querying by: {}'.format(_by)) logger.debug('fetching: {}'.format(kwargs)) # Format columns if kwargs['cols']: _cols = ','.join([x for x in \ (kwargs['cols'].split(',') \ + Item.__base_q) \ if x in Item.__attrs__]) else: _cols = ','.join([x for x in Item.__base_q]) # Format querying keys if kwargs['keys']: _keys = 'WHERE ' + _by + ' IN ' + str(tuplify(kwargs['keys'])) else: if _by != 'item_uuid': _keys = 'WHERE {} IS NULL'.format(_by) else: _keys = '' # Format paginators _p = int(kwargs['p']) if _p < 1: _p = 1 _ipp = int(kwargs['ipp']) # if _ipp > 10000: # _ipp = 10000 # Order by statement if 'orderby' in kwargs: _orderby = kwargs['orderby'] if kwargs['orderby'] else 'item_uuid' else: _orderby = 'item_uuid' if _orderby not in Item.__base_q: _orderby = 'item_uuid' # Build query _qry = """SELECT {} FROM item {} ORDER BY {} OFFSET {} LIMIT {} """\ .format(_cols, _keys, _orderby, (_p - 1)*_ipp, _ipp) logger.debug(_qry) # Query DB try: _resp = g._db.query(_qry).fetch() logger.info("Found {} items".format(len(_resp))) except Exception as e: logger.error(e) logger.warning("Issues fetching elements in DB!") if APP_MODE == "CONSUMER": return False if APP_MODE == "SERVICE": raise errors.ApiError(70003, "Issues fetching elements in DB") return _resp
def details(u_type, _uuid): """ Method to query details from related Product or Item Params: ----- u_type : str `item_uuid` or `product_uuid` _uuid : str UUID to query info Returns: ----- _details : dict Product/Item Details """ item_uuid = None # Fetch info from all retailers try: if u_type == 'item_uuid': _qry = """SELECT i.name, i.gtin, i.description, p.product_uuid, p.item_uuid as item_uuid, p.images, p.ingredients, p.source, s.hierarchy, s.retailer as show_label, s.name as r_name FROM product p INNER JOIN source s ON (p.source = s.key) INNER JOIN item i ON (i.item_uuid = p.item_uuid) WHERE p.{} = '{}'; """.format(u_type, _uuid) else: _qry = """SELECT p.name, p.gtin, p.description, p.product_uuid, p.images, p.ingredients, p.source, s.hierarchy, s.retailer as show_label, s.name as r_name, p.item_uuid as item_uuid FROM product p INNER JOIN source s ON (p.source = s.key) WHERE p.{} = '{}'; """.format(u_type, _uuid) logger.debug(_qry) info_rets = pd.read_sql(_qry, g._db.conn).to_dict(orient='records') logger.debug(info_rets) item_uuid = info_rets[0].get('item_uuid') if not info_rets: raise errors.ApiError(70008, "Not existing elements in DB!") except Exception as e: logger.error(e) logger.warning("Issues fetching retailers info: {}".format(_uuid)) info_rets = [] # Fetch Attributes if info_rets: info_attrs = Item.fetch_attrs( [str(z['product_uuid']) for z in info_rets]) else: info_attrs = [] # Aux vars prov, brand = {'name': '', 'key': ''}, {'name': '', 'key': ''} ingreds, attrs, categs = [], [], [] ingred_options, categ_options = [], [] # Fetch Provider, Brand, Categories for k in info_attrs: if k['class_key'] == 'provider': if len(k['attr']) > len(prov['name']): prov = {'name': k['attr'], 'key': k['attr_key']} elif k['class_key'] == 'brand': if k['source'] == 'byprice': brand = {'name': k['attr'], 'key': k['attr_key']} elif k['class_key'] == 'ingredient': if k['source'] == 'byprice': ingreds.append(k['attr']) else: ingred_options.append(k['attr']) elif k['class_key'] == 'category': if k['source'] == 'byprice': categs.append(k['attr']) else: categ_options.append(k['attr']) else: attrs.append(k) # Fetch Attributes if info_rets: categs += Item.fetch_categs( [str(z['product_uuid']) for z in info_rets]) # Add Normalized Ingredietns _normalized_attrs = Item.fetch_normalized_attrs(u_type, _uuid) ingreds += _normalized_attrs['ingredients'] if _normalized_attrs['brand']: brand = _normalized_attrs['brand'] # Filter info from no valid retailers df_rets = pd.DataFrame(info_rets) if 'source' in df_rets.columns: df_rets = df_rets[~df_rets.source. isin(['ims', 'plm', 'gs1', 'nielsen'])] # df_rets = df_rets[df_rets.show_label == 1] if df_rets.empty: raise errors.ApiError(70003, "Issues fetching elements in DB", 404) data = { 'name': sorted(df_rets['name'].tolist(), key=lambda x: len(x) if x else 0, reverse=True)[0].strip().capitalize(), u_type: _uuid, 'names': df_rets['name'].tolist(), 'description': sorted(df_rets['description'].dropna().tolist(), key=lambda x: len(x) if x else 0, reverse=True), 'gtin': sorted(df_rets['gtin'].dropna().tolist(), key=lambda x: len(x) if x else 0)[0] \ if len(df_rets['gtin'].dropna()) > 0 \ else '', 'retailers': df_rets['r_name'].tolist(), 'attributes': attrs, 'ingredients': ingreds, 'ingredients_options': ingred_options, 'brand': brand, 'provider': prov, 'categories': categs, 'categories_options': categ_options } if item_uuid: data.update({'item_uuid': item_uuid}) return data