def _get_deduction_dict(): hours_ahead = timeHelper.getTimeAheadOfNowHours( FINAL_DISCOUNT_RECENCY_HOURS, format='%Y-%m-%d %H:%M:%S') debug_sku_str = "" if not IF_DEBUG_SKU else " and a.sku_id=%s" % DEBUG_SKU_ID sql_deduction = ''' select * from ( select sku_id, max(single_discount_rate) as max_deduction_ratio FROM jd_analytic_promo_deduction_latest group by sku_id -- having max(single_discount_rate)>0 ) a left join jd_analytic_promo_deduction_latest b on a.sku_id = b.sku_id and ABS(a.max_deduction_ratio-b.single_discount_rate)<0.001 where origin_time>'%s' %s ''' % (hours_ahead, debug_sku_str) retrows_deduction = dbhelper.executeSqlRead( sql_deduction, is_dirty=True, isolation_type='read-committed') for row in retrows_deduction: row['content_deduction'] = row['content'] dict_deduction = rows_helper.transform_retrows_to_dict( retrows_deduction, 'sku_id') return dict_deduction
def _get_deduction_dict(): hours_ahead = timeHelper.getTimeAheadOfNowHours(FINAL_DISCOUNT_RECENCY_HOURS,format='%Y-%m-%d %H:%M:%S') debug_sku_str = "" if not IF_DEBUG_SKU else " and a.sku_id=%s" %DEBUG_SKU_ID sql_deduction = ''' select * from ( select sku_id, max(single_discount_rate) as max_deduction_ratio FROM jd_analytic_promo_deduction_latest group by sku_id -- having max(single_discount_rate)>0 ) a left join jd_analytic_promo_deduction_latest b on a.sku_id = b.sku_id and ABS(a.max_deduction_ratio-b.single_discount_rate)<0.001 where origin_time>'%s' %s ''' %(hours_ahead, debug_sku_str) retrows_deduction = dbhelper.executeSqlRead(sql_deduction, is_dirty=True, isolation_type='read-committed') for row in retrows_deduction: row['content_deduction'] = row['content'] dict_deduction = rows_helper.transform_retrows_to_dict(retrows_deduction, 'sku_id') return dict_deduction
def _get_rating_dict(): debug_sku_str = "" if not IF_DEBUG_SKU else " where sku_id=%s" %DEBUG_SKU_ID sql_deduction = ''' select * from jd_analytic_item_rating_diff %s ''' %(debug_sku_str) retrows_deduction = dbhelper.executeSqlRead(sql_deduction, is_dirty=True) dict_deduction = rows_helper.transform_retrows_to_dict(retrows_deduction, 'sku_id') return dict_deduction
def _get_item_firstseen_dict(): debug_sku_str = "" if not IF_DEBUG_SKU else " where sku_id=%s" %DEBUG_SKU_ID sql_deduction = ''' select sku_id,first_seen_date from jd_item_firstseen %s ''' %(debug_sku_str) retrows_deduction = dbhelper.executeSqlRead(sql_deduction, is_dirty=True) dict_deduction = rows_helper.transform_retrows_to_dict(retrows_deduction, 'sku_id') return dict_deduction
def _get_rating_dict(): debug_sku_str = "" if not IF_DEBUG_SKU else " where sku_id=%s" % DEBUG_SKU_ID sql_deduction = ''' select * from jd_analytic_item_rating_diff %s ''' % (debug_sku_str) retrows_deduction = dbhelper.executeSqlRead(sql_deduction, is_dirty=True) dict_deduction = rows_helper.transform_retrows_to_dict( retrows_deduction, 'sku_id') return dict_deduction
def _get_item_firstseen_dict(): debug_sku_str = "" if not IF_DEBUG_SKU else " where sku_id=%s" % DEBUG_SKU_ID sql_deduction = ''' select sku_id,first_seen_date from jd_item_firstseen %s ''' % (debug_sku_str) retrows_deduction = dbhelper.executeSqlRead(sql_deduction, is_dirty=True) dict_deduction = rows_helper.transform_retrows_to_dict( retrows_deduction, 'sku_id') return dict_deduction
def _load_catalog_map_as_dict_key_category_id_prefix(): global g_catalog_map if len(g_catalog_map) > 0: return g_catalog_map sql = "select * from jd_catalog_map" retrows = dbhelper.executeSqlRead(sql) cdict = rows_helper.transform_retrows_to_dict(retrows, "category_id") g_catalog_map = cdict return g_catalog_map
def _get_gift_dict(): hours_ahead = timeHelper.getTimeAheadOfNowHours(FINAL_DISCOUNT_RECENCY_HOURS,format='%Y-%m-%d %H:%M:%S') debug_sku_str = "" if not IF_DEBUG_SKU else " and sku_id=%s" %DEBUG_SKU_ID # gift_valued表中, dt是原始爬取时间(其他表是origin_time) sql_deduction = ''' select * from jd_analytic_promo_gift_valued where dt>'%s' %s ''' %(hours_ahead, debug_sku_str) # print sql_deduction retrows_deduction = dbhelper.executeSqlRead(sql_deduction, is_dirty=True, isolation_type='read-committed') dict_deduction = rows_helper.transform_retrows_to_dict(retrows_deduction, 'sku_id') return dict_deduction
def _get_discount_dict(): hours_ahead = timeHelper.getTimeAheadOfNowHours(FINAL_DISCOUNT_RECENCY_HOURS,format='%Y-%m-%d %H:%M:%S') debug_sku_str = "" if not IF_DEBUG_SKU else " and sku_id=%s" %DEBUG_SKU_ID sql_deduction = ''' select * from jd_analytic_promo_discount_latest where origin_dt>'%s' %s ''' %(hours_ahead, debug_sku_str) retrows_deduction = dbhelper.executeSqlRead(sql_deduction, is_dirty=True, isolation_type='read-committed') for row in retrows_deduction: row['content_discount'] = row['content'] dict_deduction = rows_helper.transform_retrows_to_dict(retrows_deduction, 'sku_id') #print dict_deduction['264212'] return dict_deduction
def _get_gift_dict(): hours_ahead = timeHelper.getTimeAheadOfNowHours( FINAL_DISCOUNT_RECENCY_HOURS, format='%Y-%m-%d %H:%M:%S') debug_sku_str = "" if not IF_DEBUG_SKU else " and sku_id=%s" % DEBUG_SKU_ID # gift_valued表中, dt是原始爬取时间(其他表是origin_time) sql_deduction = ''' select * from jd_analytic_promo_gift_valued where dt>'%s' %s ''' % (hours_ahead, debug_sku_str) # print sql_deduction retrows_deduction = dbhelper.executeSqlRead( sql_deduction, is_dirty=True, isolation_type='read-committed') dict_deduction = rows_helper.transform_retrows_to_dict( retrows_deduction, 'sku_id') return dict_deduction
def _get_discount_dict(): hours_ahead = timeHelper.getTimeAheadOfNowHours( FINAL_DISCOUNT_RECENCY_HOURS, format='%Y-%m-%d %H:%M:%S') debug_sku_str = "" if not IF_DEBUG_SKU else " and sku_id=%s" % DEBUG_SKU_ID sql_deduction = ''' select * from jd_analytic_promo_discount_latest where origin_dt>'%s' %s ''' % (hours_ahead, debug_sku_str) retrows_deduction = dbhelper.executeSqlRead( sql_deduction, is_dirty=True, isolation_type='read-committed') for row in retrows_deduction: row['content_discount'] = row['content'] dict_deduction = rows_helper.transform_retrows_to_dict( retrows_deduction, 'sku_id') #print dict_deduction['264212'] return dict_deduction
def _load_category_map(): sql = 'select category_id_prefix,category_prefix_name from jd_category_show' retrows = dbhelper.executeSqlRead(sql) retdict = rows_helper.transform_retrows_to_dict(retrows,'category_prefix_name') return retdict
def _load_category_map(): sql = 'select category_id_prefix,category_prefix_name from jd_category_show' retrows = dbhelper.executeSqlRead(sql) retdict = rows_helper.transform_retrows_to_dict(retrows, 'category_prefix_name') return retdict