def old_ali_1688_goods_insert_into_new_table(self, data, pipeline):
data_list = data
tmp = GoodsItem()
tmp['main_goods_id'] = data_list.get('main_goods_id')
tmp['goods_id'] = data_list['goods_id'] # 官方商品id
tmp['goods_url'] = data_list['goods_url']
tmp['username'] = data_list['username']
now_time = get_shanghai_time()
tmp['create_time'] = now_time # 操作时间
tmp['modify_time'] = now_time # 修改时间
tmp['shop_name'] = data_list['company_name'] # 公司名称
tmp['title'] = data_list['title'] # 商品名称
tmp['link_name'] = data_list['link_name'] # 卖家姓名
# 设置最高价price， 最低价taobao_price
tmp['price'] = Decimal(data_list['price']).__round__(2)
tmp['taobao_price'] = Decimal(data_list['taobao_price']).__round__(2)
tmp['price_info'] = data_list['price_info'] # 价格信息
# print(tmp['price'], print(tmp['taobao_price']))
# print(tmp['price_info'])
spec_name = []
for item in data_list['sku_props']:
tmp_dic = {}
tmp_dic['spec_name'] = item.get('prop')
spec_name.append(tmp_dic)
tmp['detail_name_list'] = spec_name # 标签属性名称
"""
得到sku_map
"""
tmp['price_info_list'] = data_list.get('sku_map') # 每个规格对应价格及其库存
tmp['all_img_url'] = data_list.get('all_img_url') # 所有示例图片地址
tmp['p_info'] = data_list.get('property_info') # 详细信息
tmp['div_desc'] = data_list.get('detail_info') # 下方div
tmp['site_id'] = 2 # 阿里1688
tmp['is_delete'] = data_list['is_delete']
# tmp['my_shelf_and_down_time'] = data_list.get('my_shelf_and_down_time')
# tmp['delete_time'] = data_list.get('delete_time')
# print('------>>> | 待存储的数据信息为: |', tmp)
params = self._get_db_insert_params(item=tmp)
if tmp.get('main_goods_id') is not None:
sql_str = r'insert into dbo.GoodsInfoAutoGet(GoodsID, GoodsUrl, UserName, CreateTime, ModfiyTime, ShopName, GoodsName, LinkName, Price, TaoBaoPrice, PriceInfo, SKUName, SKUInfo, ImageUrl, DetailInfo, PropertyInfo, SiteID, IsDelete, MainGoodsID) values(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)'
else:
sql_str = r'insert into dbo.GoodsInfoAutoGet(GoodsID, GoodsUrl, UserName, CreateTime, ModfiyTime, ShopName, GoodsName, LinkName, Price, TaoBaoPrice, PriceInfo, SKUName, SKUInfo, ImageUrl, DetailInfo, PropertyInfo, SiteID, IsDelete) values(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)'
result = pipeline._insert_into_table(sql_str=sql_str, params=params)
return result
def to_right_and_update_data(self, data, pipeline):
'''
实时更新数据
:param data:
:param pipeline:
:return:
'''
data_list = data
tmp = GoodsItem()
tmp['goods_id'] = data_list['goods_id'] # 官方商品id
now_time = get_shanghai_time()
tmp['modify_time'] = now_time # 修改时间
tmp['shop_name'] = data_list['shop_name'] # 公司名称
tmp['title'] = data_list['title'] # 商品名称
tmp['sub_title'] = data_list['sub_title'] # 商品子标题
tmp['link_name'] = '' # 卖家姓名
tmp['account'] = data_list['account'] # 掌柜名称
tmp['all_sell_count'] = data_list['sell_count'] # 月销量
# 设置最高价price， 最低价taobao_price
tmp['price'] = Decimal(data_list['price']).__round__(2)
tmp['taobao_price'] = Decimal(data_list['taobao_price']).__round__(2)
tmp['price_info'] = [] # 价格信息
tmp['detail_name_list'] = data_list['detail_name_list'] # 标签属性名称
"""
得到sku_map
"""
tmp['price_info_list'] = data_list.get(
'price_info_list') # 每个规格对应价格及其库存
tmp['all_img_url'] = data_list.get('all_img_url') # 所有示例图片地址
tmp['p_info'] = data_list.get('p_info') # 详细信息
tmp['div_desc'] = data_list.get('div_desc') # 下方div
tmp['is_delete'] = data_list.get('is_delete') # 逻辑删除, 未删除为0, 删除为1
tmp['shelf_time'] = data_list.get('shelf_time', '')
tmp['delete_time'] = data_list.get('delete_time', '')
tmp['is_price_change'] = data_list.get('_is_price_change')
tmp['price_change_info'] = data_list.get('_price_change_info')
params = self._get_db_update_params(item=tmp)
# 改价格的sql
# sql_str = r'update dbo.GoodsInfoAutoGet set ModfiyTime = %s, ShopName=%s, Account=%s, GoodsName=%s, SubTitle=%s, LinkName=%s, Price=%s, TaoBaoPrice=%s, PriceInfo=%s, SKUName=%s, SKUInfo=%s, ImageUrl=%s, PropertyInfo=%s, DetailInfo=%s, SellCount=%s, MyShelfAndDownTime=%s, delete_time=%s, IsDelete=%s, IsPriceChange=%s, PriceChangeInfo=%s where GoodsID = %s'
# 不改价格的sql
if tmp['delete_time'] == '':
sql_str = 'update dbo.GoodsInfoAutoGet set ModfiyTime = %s, ShopName=%s, Account=%s, GoodsName=%s, SubTitle=%s, LinkName=%s, PriceInfo=%s, SKUName=%s, SKUInfo=%s, ImageUrl=%s, PropertyInfo=%s, DetailInfo=%s, SellCount=%s, IsDelete=%s, IsPriceChange=%s, PriceChangeInfo=%s, shelf_time=%s where GoodsID = %s'
elif tmp['shelf_time'] == '':
sql_str = 'update dbo.GoodsInfoAutoGet set ModfiyTime = %s, ShopName=%s, Account=%s, GoodsName=%s, SubTitle=%s, LinkName=%s, PriceInfo=%s, SKUName=%s, SKUInfo=%s, ImageUrl=%s, PropertyInfo=%s, DetailInfo=%s, SellCount=%s, IsDelete=%s, IsPriceChange=%s, PriceChangeInfo=%s, delete_time=%s where GoodsID = %s'
else:
sql_str = 'update dbo.GoodsInfoAutoGet set ModfiyTime = %s, ShopName=%s, Account=%s, GoodsName=%s, SubTitle=%s, LinkName=%s, PriceInfo=%s, SKUName=%s, SKUInfo=%s, ImageUrl=%s, PropertyInfo=%s, DetailInfo=%s, SellCount=%s, IsDelete=%s, IsPriceChange=%s, PriceChangeInfo=%s, shelf_time=%s, delete_time=%s where GoodsID = %s'
pipeline._update_table_2(sql_str=sql_str,
params=params,
logger=self.my_lg)
def to_right_and_update_data(self, data, pipeline):
data_list = data
tmp = GoodsItem()
tmp['goods_id'] = data_list['goods_id'] # 官方商品id
now_time = get_shanghai_time()
# tmp['deal_with_time'] = now_time # 操作时间
tmp['modify_time'] = now_time # 修改时间
tmp['shop_name'] = data_list['company_name'] # 公司名称
tmp['title'] = data_list['title'] # 商品名称
tmp['link_name'] = data_list['link_name'] # 卖家姓名
# 设置最高价price， 最低价taobao_price
tmp['price'] = Decimal(data_list['price']).__round__(2)
tmp['taobao_price'] = Decimal(data_list['taobao_price']).__round__(2)
tmp['price_info'] = data_list['price_info'] # 价格信息
# print(tmp['price'], print(tmp['taobao_price']))
# print(tmp['price_info'])
spec_name = []
for item in data_list['sku_props']:
tmp_dic = {}
tmp_dic['spec_name'] = item.get('prop')
spec_name.append(tmp_dic)
tmp['detail_name_list'] = spec_name # 标签属性名称
"""
得到sku_map
"""
tmp['price_info_list'] = data_list.get('sku_map') # 每个规格对应价格及其库存
tmp['all_img_url'] = data_list.get('all_img_url') # 所有示例图片地址
tmp['p_info'] = data_list.get('property_info') # 详细信息
tmp['div_desc'] = data_list.get('detail_info') # 下方div
tmp['is_delete'] = data_list.get('is_delete')
tmp['my_shelf_and_down_time'] = data_list.get('my_shelf_and_down_time')
tmp['delete_time'] = data_list.get('delete_time')
tmp['is_price_change'] = data_list.get('_is_price_change')
tmp['price_change_info'] = data_list.get('_price_change_info')
# print('------>>> | 待存储的数据信息为: |', tmp)
params = self._get_db_update_params(item=tmp)
# 改价格的sql语句
# sql_str = r'update dbo.GoodsInfoAutoGet set ModfiyTime = %s, ShopName=%s, GoodsName=%s, LinkName=%s, Price=%s, TaoBaoPrice=%s, PriceInfo=%s, SKUName=%s, SKUInfo=%s, ImageUrl=%s, DetailInfo=%s, PropertyInfo=%s, MyShelfAndDownTime=%s, delete_time=%s, IsDelete=%s, IsPriceChange=%s, PriceChangeInfo=%s where GoodsID = %s'
# 不改价格的sql语句
sql_str = r'update dbo.GoodsInfoAutoGet set ModfiyTime = %s, ShopName=%s, GoodsName=%s, LinkName=%s, PriceInfo=%s, SKUName=%s, SKUInfo=%s, ImageUrl=%s, DetailInfo=%s, PropertyInfo=%s, MyShelfAndDownTime=%s, delete_time=%s, IsDelete=%s, IsPriceChange=%s, PriceChangeInfo=%s where GoodsID = %s'
pipeline._update_table(sql_str=sql_str, params=params)