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
