def __init__(self, auth_info):
self.db_conn = MySQLdb.connect(DATABASE['HOST'], DATABASE['USER'],
DATABASE['PASSWORD'], DATABASE['NAME'])
self.auth_info = auth_info
self.product_public = Products(self.auth_info['AWSAccessKeyId'],
self.auth_info['SecretKey'],
self.auth_info['SellerId'],
self.auth_info['ShopSite'])
def __init__(self, auth_info_ship, data_base):
self.db_conn = pymysql.connect(data_base['HOST'], data_base['USER'],
data_base['PASSWORD'],
data_base['NAME'])
self.auth_info = auth_info_ship
self.product_public = Products(self.auth_info['AWSAccessKeyId'],
self.auth_info['SecretKey'],
self.auth_info['SellerId'],
self.auth_info['ShopSite'])
def ListProducts(query):
api = Products(
access_key=access_key,
secret_key=secret_key,
account_id=account_id,
region=region,
)
api._use_feature_mwsresponse = True
response = api.list_matching_products(marketplaceid="ATVPDKIKX0DER",
query=query)
print(response.parsed)
def get_product_info_by_seller_sku(self, auth_info, seller_sku_list):
region = auth_info['ShopName'].split('/')[0].split('-')[-1]
product_public = Products(auth_info['AWSAccessKeyId'],
auth_info['SecretKey'],
auth_info['SellerId'],
region=region)
try:
product_info_response = product_public.get_matching_product_for_id(auth_info['MarketplaceId'], 'SellerSKU', seller_sku_list)
product_info_response_dic = product_info_response._response_dict
except Exception as e:
print e
time.sleep(2) # 防止超请求限制，重新提交
product_info_response = product_public.get_matching_product_for_id(auth_info['MarketplaceId'], 'SellerSKU', seller_sku_list)
product_info_response_dic = product_info_response._response_dict
return product_info_response_dic
def get_asin_from_product(self, product_id_type, barcode, wk_asin=None,**kwargs):
Type=dict(ProductIdType).get(product_id_type)
Value = Type == 'ASIN' and wk_asin or barcode
try:
data = self._get_mws('Products').get_matching_product_for_id(
self.mws_marketplace_id,type=Type,id=[Value]).parsed
if extract_item(data.get('status',{}))=='Success':
Products = data.get('Products',{})
if len(Products):
Product = Products.get('Product',{})
if isinstance(Product,list):
Product = Product[0]
asin = Product.get('Identifiers',{}).get('MarketplaceASIN',{}).get('ASIN',{})
return extract_item(asin)
# if isinstance(Products,list):
# Products = Products[0]
# if isinstance(Products,dict):
# asin = Products.get('Product',{}).get('Identifiers',{}).get('MarketplaceASIN',{}).get('ASIN',{})
# return extract_item(asin)
except MWSError as me:
_logger.error("MWSError-get_asin_from_product---%r---",me)
except Exception as e:
_logger.error("MWSError-get_asin_from_product---%r---",e)
def _get_product_object() -> Products: # pragma: no cover
"""
Creates a MWS Product object from mws library
Returns:
Products object
"""
return Products(access_key=os.getenv('MWS_ACCESS_KEY'),
secret_key=os.getenv('MWS_SECRET_KEY'),
account_id=os.getenv('SELLER_ID'))
def __init__(self, auth_info_feed, db_conncetion=connection):
self.auth_info = auth_info_feed
self.feed_public = Feeds(self.auth_info['AWSAccessKeyId'],
self.auth_info['SecretKey'],
self.auth_info['SellerId'],
region=self.auth_info['ShopSite'],
proxy_host=self.auth_info['IP'])
self.product_public = Products(self.auth_info['AWSAccessKeyId'],
self.auth_info['SecretKey'],
self.auth_info['SellerId'],
region=self.auth_info['ShopSite'],
proxy_host=self.auth_info['IP'])
self.db_conn = db_conncetion
def _get_mws(self,obj):
mws_access_key, mws_secret_key = self.mws_access_key, self.mws_secret_key
mws_merchant_id, mws_marketplace_id = self.mws_merchant_id, self.mws_marketplace_id
region, domain = self.region, self.domain
if obj == 'Sellers':
return Sellers(mws_access_key, mws_secret_key, mws_merchant_id, region=region, domain=domain)
elif obj == 'Reports':
return Reports(mws_access_key, mws_secret_key, mws_merchant_id, region=region, domain=domain)
elif obj == 'Products':
return Products(mws_access_key, mws_secret_key, mws_merchant_id, region=region, domain=domain)
elif obj == 'Feeds':
return Feeds(mws_access_key, mws_secret_key, mws_merchant_id, region=region, domain=domain)
elif obj == 'Orders':
return Orders(mws_access_key, mws_secret_key, mws_merchant_id, region=region, domain=domain,version='2013-09-01')
def GetProduct(asin):
api = Products(
access_key=access_key,
secret_key=secret_key,
account_id=account_id,
region=region,
)
#https://www.onlinejobs.ph/jobseekers/job/470624
#https://www.onlinejobs.ph/jobseekers/job/470512
api._use_feature_mwsresponse = True
#result = api.get_competitive_pricing_for_asin(marketplaceid="ATVPDKIKX0DER",asins=asin)
#return result.parsed
tmp = api.list_matching_products(marketplaceid="ATVPDKIKX0DER",
query="017754398666")
#print(tmp.parsed)
response1 = api.get_lowest_offer_listings_for_asin(
marketplaceid="ATVPDKIKX0DER", asins=asin)
response = api.get_matching_product(marketplaceid="ATVPDKIKX0DER",
asins=asin)
print("@#@#")
#print(response.parsed)
test = {}
similar = {}
try:
test = response.parsed.Product.Relationships.VariationParent.Identifiers.MarketplaceASIN.ASIN
similar = GetProductInfo(test, response.parsed, False)
except:
print("DDDDD")
return {
'Original': GetProductInfo(asin, response.parsed, True),
'Similar': similar,
'Data': tmp.parsed
}
class GetProductInfoByAsin:
"""
按asin值获取产品图片及主、变体关系
"""
def __init__(self, auth_info):
self.db_conn = MySQLdb.connect(DATABASE['HOST'], DATABASE['USER'],
DATABASE['PASSWORD'], DATABASE['NAME'])
self.auth_info = auth_info
self.product_public = Products(self.auth_info['AWSAccessKeyId'],
self.auth_info['SecretKey'],
self.auth_info['SellerId'],
self.auth_info['ShopSite'])
def get_product_info(self, asin_list):
try:
product_info_response = self.product_public.get_matching_product(
self.auth_info['MarketplaceId'], asin_list)
product_info_response_dic = product_info_response._response_dict
except Exception as e:
logging.error(e)
time.sleep(10) # 防止超请求限制，重新提交
product_info_response = self.product_public.get_matching_product(
self.auth_info['MarketplaceId'], asin_list)
product_info_response_dic = product_info_response._response_dict
return product_info_response_dic
def update_parent_asin(self, main_asin, child_asin):
cursor = self.db_conn.cursor()
sql = "update %s set parent_asin = '%s' where asin1 = '%s' and shopname = '%s'" \
% (self.auth_info['table_name'], main_asin, child_asin, self.auth_info['ShopName'])
print 'update_parent_asin sql is: %s' % sql
logging.debug('update_parent_asin sql is: %s' % sql)
cursor.execute(sql)
cursor.execute('commit;')
cursor.close()
def connect_image(self, asin, image_url):
cursor = self.db_conn.cursor()
sql = "update %s set image_url = '%s' where asin1 = '%s' and shopname = '%s'" \
% (self.auth_info['table_name'], image_url, asin, self.auth_info['ShopName'])
print 'connect_image sql is: %s' % sql
logging.debug('connect_image sql is: %s' % sql)
cursor.execute(sql)
cursor.execute('commit;')
cursor.close()
def deal_worker(self, product):
main_asin = product['ASIN']['value']
print 'main_asin is: %s' % main_asin
logging.debug('main_asin is: %s' % main_asin)
try:
image_url = product['Product']['AttributeSets']['SmallImage'][
'URL']['value']
except Exception as e:
print e
image_url = product['Product']['AttributeSets']['ItemAttributes'][
'SmallImage']['URL']['value']
print 'image_url is: %s' % image_url
logging.debug('image_url is: %s' % image_url)
# parent -> child
if product['Product']['Relationships'].has_key('VariationChild'):
print product['Product']['Relationships']
child_list = product['Product']['Relationships'][
'VariationChild'] # 多个变体时为列表,单个变体时为字典
if isinstance(child_list, list): # 多个变体
for child in child_list:
child_asin = child['Identifiers']['MarketplaceASIN'][
'ASIN']['value']
print ' child_asin is: %s' % child_asin
logging.debug('child_asin is: %s' % child_asin)
self.update_parent_asin(main_asin, child_asin)
else: # 单个变体
child_asin = child_list['Identifiers']['MarketplaceASIN'][
'ASIN']['value']
print ' child_asin is: %s' % child_asin
logging.debug('child_asin is: %s' % child_asin)
self.update_parent_asin(main_asin, child_asin)
# child -> parent
if product['Product']['Relationships'].has_key('VariationParent'):
parent_asin = product['Product']['Relationships'][
'VariationParent']['Identifiers']['MarketplaceASIN']['ASIN'][
'value']
print 'parent_asin is: %s' % parent_asin
logging.debug('parent_asin is: %s' % parent_asin)
self.update_parent_asin(parent_asin, main_asin)
self.connect_image(main_asin, image_url)
def update_db_by_product_info(self, product_info):
product_list = product_info[
'GetMatchingProductResult'] # 提交多个asin时为列表，单个时为字典
if isinstance(product_list, list):
for product in product_list:
try:
self.deal_worker(product)
except Exception as e:
print 'exception product is：%s' % str(product)
logging.debug('exception product is：%s' % str(product))
logging.error('traceback.format_exc():\n%s' %
traceback.format_exc())
print e
continue
else:
try:
self.deal_worker(product_list)
except Exception as e:
logging.debug('exception product list is：%s' %
str(product_list))
logging.error('traceback.format_exc():\n%s' %
traceback.format_exc())
print 'exception product is：%s' % str(product_list)
print e
def refresh_product(self):
cursor = self.db_conn.cursor()
sql_product_parent = "update %s set parent_asin = asin1 where status = 'Incomplete' and shopname = '%s'" \
% (self.auth_info['table_name'], self.auth_info['ShopName'])
sql_product_multiple = "update %s set product_type = 1 where parent_asin is not null and shopname = '%s'" \
% (self.auth_info['table_name'], self.auth_info['ShopName'])
print 'sql_product_parent: %s' % sql_product_parent
logging.debug('sql_product_parent: %s' % sql_product_parent)
cursor.execute(sql_product_parent)
cursor.execute('commit;')
print 'sql_product_multiple: %s' % sql_product_multiple
logging.debug('sql_product_multiple: %s' % sql_product_multiple)
cursor.execute(sql_product_multiple)
cursor.execute('commit;')
cursor.close()
def get_parent_asin_and_image(self, seller_sku_list=None):
cursor = self.db_conn.cursor()
if seller_sku_list is None:
sql = "select asin1,seller_sku, status from %s where shopname = '%s'" \
% (self.auth_info['table_name'], self.auth_info['ShopName'])
else:
seller_sku = str(seller_sku_list)
seller_sku = seller_sku.replace('[u', '')
seller_sku = seller_sku.replace('[', '')
seller_sku = seller_sku.replace(']', '')
seller_sku = seller_sku.replace(", u'", ", '")
print 'seller_sku is: %s' % seller_sku
sql = "select asin1, seller_sku, status from %s where shopname = '%s' and seller_sku in (%s)" \
% (self.auth_info['table_name'], self.auth_info['ShopName'], seller_sku)
print 'get product sql is: %s' % sql
logging.debug('get product sql is: %s' % sql)
cursor.execute(sql)
asin_status_obj = cursor.fetchall()
cursor.close()
if asin_status_obj is not None:
asin_list = []
i_count = 0
for asin_status in asin_status_obj:
if asin_status[0] is not None:
i_count += 1
asin_list.append(asin_status[0])
if i_count % 10 == 0:
asin_list = list(set(asin_list))
print 'asin_list is: %s' % str(asin_list)
logging.debug('asin_list is: %s' % str(asin_list))
product_info = self.get_product_info(asin_list)
logging.debug('product_info is: %s' %
str(product_info))
self.update_db_by_product_info(product_info)
asin_list = []
else:
print "asin is null, get product info by seller_sku"
logging.debug(
"asin is null, get product info by seller_sku")
print "seller_sku is: %s" % asin_status[1]
logging.debug("seller_sku is: %s" % asin_status[1])
sku_obj = GetProductInfoBySellerSku(
self.auth_info, DATABASE)
sku_obj.refresh_data_by_seller_sku(asin_status[1])
print asin_list
asin_list = list(set(asin_list))
print 'asin_list is: %s' % str(asin_list)
logging.debug('asin_list is: %s' % str(asin_list))
if asin_list:
product_info = self.get_product_info(asin_list)
logging.debug('product_info is: %s' % str(product_info))
self.update_db_by_product_info(product_info)
self.refresh_product()
class GetProductInfoBySellerSku:
"""
按seller_sku值获取产品图片及主、变体关系
"""
def __init__(self, auth_info, DATABASE):
self.db_conn = MySQLdb.connect(DATABASE['HOST'], DATABASE['USER'],
DATABASE['PASSWORD'], DATABASE['NAME'])
self.auth_info = auth_info
self.product_public = Products(self.auth_info['AWSAccessKeyId'],
self.auth_info['SecretKey'],
self.auth_info['SellerId'],
self.auth_info['ShopSite'])
def get_product_info_by_seller_sku(self, seller_sku):
try:
product_info_response = self.product_public.get_matching_product_for_id(
self.auth_info['MarketplaceId'], 'SellerSKU', [seller_sku])
product_info_response_dic = product_info_response._response_dict
except Exception as e:
print e
time.sleep(2) # 防止超请求限制，重新提交
product_info_response = self.product_public.get_matching_product_for_id(
self.auth_info['MarketplaceId'], 'SellerSKU', [seller_sku])
product_info_response_dic = product_info_response._response_dict
return product_info_response_dic
def update_asin(self, this_asin, image_url, seller_sku):
cursor = self.db_conn.cursor()
try:
sql = "update %s set asin1 = '%s' , image_url = '%s' where seller_sku ='%s' and shopname = '%s'" \
% (self.auth_info['table_name'], this_asin, image_url, seller_sku, self.auth_info['ShopName'])
print 'update_parent_asin sql is: %s' % sql
logging.debug('update_parent_asin sql is: %s' % sql)
cursor.execute(sql)
cursor.execute('commit;')
cursor.close()
except Exception as e:
cursor.close()
print e
logging.error('traceback.format_exc():\n%s' %
traceback.format_exc())
def execute_db(self, sql):
cursor = self.db_conn.cursor()
try:
cursor.execute(sql)
cursor.execute('commit;')
cursor.close()
except Exception as e:
cursor.close()
print e
def update_db_by_product_info(self, product_info, seller_sku):
main_asin = product_info['GetMatchingProductForIdResult']['Products'][
'Product']['Identifiers']['MarketplaceASIN']['ASIN']['value']
image_url = product_info['GetMatchingProductForIdResult']['Products'][
'Product']['AttributeSets']['ItemAttributes']['SmallImage']['URL'][
'value']
self.update_asin(main_asin, image_url, seller_sku)
if product_info['GetMatchingProductForIdResult']['Products'][
'Product']['Relationships'].has_key('VariationChild'):
child_list = product_info['GetMatchingProductForIdResult'][
'Products']['Product']['Relationships']['VariationChild']
if isinstance(child_list, list):
for child in child_list:
child_asin = child['Identifiers']['MarketplaceASIN'][
'ASIN']['value']
sql_child = "update %s set parent_asin = '%s' where asin1 ='%s' and shopname = '%s'" \
% (self.auth_info['table_name'], main_asin, child_asin, self.auth_info['ShopName'])
print 'sql_child is: %s' % sql_child
logging.debug('sql_child_1 is: %s' % sql_child)
self.execute_db(sql_child)
else:
child_asin = child_list['Identifiers']['MarketplaceASIN'][
'ASIN']['value']
sql_child = "update %s set parent_asin = '%s' where asin1 ='%s' and shopname = '%s'" \
% (self.auth_info['table_name'], main_asin, child_asin, self.auth_info['ShopName'])
print 'sql_child is: %s' % sql_child
logging.debug('sql_child_2 is: %s' % sql_child)
self.execute_db(sql_child)
if product_info['GetMatchingProductForIdResult']['Products'][
'Product']['Relationships'].has_key('VariationParent'):
parent_asin = product_info['GetMatchingProductForIdResult'][
'Products']['Product']['Relationships']['VariationParent'][
'Identifiers']['MarketplaceASIN']['ASIN']['value']
sql_parent = "update %s set parent_asin = '%s' where asin1 ='%s' and shopname = '%s'" \
% (self.auth_info['table_name'], parent_asin, main_asin, self.auth_info['ShopName'])
print 'parent_asin is: %s' % parent_asin
print 'sql_parent is: %s' % sql_parent
logging.debug('parent_asin is: %s' % parent_asin)
logging.debug('sql_parent is: %s' % sql_parent)
self.execute_db(sql_parent)
def refresh_data_by_seller_sku(self, seller_sku):
product_info = self.get_product_info_by_seller_sku(seller_sku)
print "get product_info by seller_sku is: %s " % str(product_info)
logging.debug("get product_info by seller_sku is: %s " %
str(product_info))
if product_info['GetMatchingProductForIdResult']['status'][
'value'] == 'Success':
self.update_db_by_product_info(product_info, seller_sku)
else:
print 'get product_info by seller_sku error'
logging.debug('get product_info by seller_sku error')
def GetProductInfo(asin, parsed, show):
api = Products(
access_key=access_key,
secret_key=secret_key,
account_id=account_id,
region=region,
)
print("######")
if os.path.exists('./1.txt'):
print("ASDASDASD")
else:
f = open("./1.txt", "a")
f.write(parsed.Product)
f.close()
print(parsed.Product)
height = parsed.Product.AttributeSets.ItemAttributes.ItemDimensions.Height.value
weight = parsed.Product.AttributeSets.ItemAttributes.ItemDimensions.Weight.value
length = parsed.Product.AttributeSets.ItemAttributes.ItemDimensions.Length.value
width = parsed.Product.AttributeSets.ItemAttributes.ItemDimensions.Width.value
fees = getFees(asin, height, weight, length, width)
api._use_feature_mwsresponse = True
fulfillment = api.get_my_price_for_asin(marketplaceid="ATVPDKIKX0DER",
asins=asin)
result = api.get_competitive_pricing_for_asin(
marketplaceid="ATVPDKIKX0DER", asins=asin)
parseInfo(asin)
contents = ""
with open('result.txt', 'r', encoding="utf8") as f:
contents = f.read()
infos = getInfos(contents)
seller_rank = ""
for rank in infos[4]:
seller_rank += rank + " "
parseAmazon(infos[0], infos[1], infos[2])
contents = ""
with open('asd.htm', 'r', encoding="utf8") as f:
contents = f.read()
amount = ''
start = False
for i in range(0, len(contents)):
if start == False and contents[i] == '>' and contents[
i + 1] == ',' and contents[i +
3] == 't' and contents[i +
4] == 'h':
start = True
i += 12
if start == True:
amount += contents[i]
if start == True and contents[i + 1] == '<':
break
try:
amount = amount.slit(",")[0]
except:
print("")
#amount = amount.split(",")[0]
if show == True:
data = {
"Inventory": amount,
"Price": infos[3],
"SellerRank": seller_rank,
'Image': infos[5],
'SellersName': infos[6],
'ASIN': asin,
'Parsed': parsed
}
else:
data = {
"Inventory": amount,
"Price": infos[3],
"SellerRank": seller_rank,
'Image': infos[5],
'SellersName': infos[6],
'Fees': json.loads(fees),
'CompetitivePricing': result.parsed
}
return data
def GetProductFromJS(upc):
api = Products(
access_key=access_key,
secret_key=secret_key,
account_id=account_id,
region=region,
)
api._use_feature_mwsresponse = True
#result = api.get_competitive_pricing_for_asin(marketplaceid="ATVPDKIKX0DER",asins=asin)
#return result.parsed
Original = []
Similar = []
response = ""
tmp = ""
data = {}
fba = 0
merch = 0
try:
tmp = api.list_matching_products(marketplaceid="ATVPDKIKX0DER",
query=upc)
the_asin = tmp.parsed.Products.Product.Identifiers.MarketplaceASIN.ASIN
response = api.get_matching_product(marketplaceid="ATVPDKIKX0DER",
asins=the_asin)
response1 = api.get_lowest_offer_listings_for_asin(
marketplaceid="ATVPDKIKX0DER", asins=the_asin)
for i in range(
0,
len(response1.parsed.Product.LowestOfferListings.
LowestOfferListing)):
if "Amazon" in response1.parsed.Product.LowestOfferListings.LowestOfferListing[
i].Qualifiers.FulfillmentChannel:
fba += 1
else:
merch += 1
Original = GetProductInfo(the_asin, response.parsed, True)
except Exception as ee:
print(ee)
try:
test = response.parsed.Product.Relationships.VariationParent.Identifiers.MarketplaceASIN.ASIN
Similar = GetProductInfo(test, response.parsed, False)
except Exception as ee:
print(ee)
return {
'Original': Original,
'Similar': Similar,
'Data': {
'FBA': fba,
'Merchant': merch
}
}
class GetShippingPrice:
def __init__(self, auth_info_ship, data_base):
self.db_conn = pymysql.connect(data_base['HOST'], data_base['USER'],
data_base['PASSWORD'],
data_base['NAME'])
self.auth_info = auth_info_ship
self.product_public = Products(self.auth_info['AWSAccessKeyId'],
self.auth_info['SecretKey'],
self.auth_info['SellerId'],
self.auth_info['ShopSite'])
def close_db_conn(self):
try:
if self.db_conn:
self.db_conn.close()
except Exception as ex:
print ex
logging.error(
'class GetShippingPrice close db connection failed!')
logging.error('traceback.format_exc():\n%s' %
traceback.format_exc())
def get_price_info_by_seller_sku(self, seller_sku):
if isinstance(seller_sku, str):
seller_sku = [seller_sku]
try:
product_info_response = self.product_public.get_competitive_pricing_for_sku(
self.auth_info['MarketplaceId'], seller_sku)
product_info_response_dic = product_info_response._response_dict
except Exception as e:
print e
time.sleep(10) # 防止超请求限制，重新提交
product_info_response = self.product_public.get_competitive_pricing_for_sku(
self.auth_info['MarketplaceId'], seller_sku)
product_info_response_dic = product_info_response._response_dict
return product_info_response_dic
def get_seller_sku_list(self):
try:
cursor = self.db_conn.cursor()
sql = "select seller_sku, status from %s where shopname = '%s' and refresh_status != 1 and seller_sku in('_((!${:10108','_((!${:10113')" \
% (self.auth_info['table_name'], self.auth_info['ShopName'])
print 'get product sql is: %s' % sql
logging.debug('get product sql is: %s' % sql)
cursor.execute(sql)
seller_sku_obj = cursor.fetchall()
cursor.close()
if seller_sku_obj is not None:
seller_sku_list = list()
i_count = 0
for seller_sku in seller_sku_obj:
if seller_sku[0] is not None:
i_count += 1
seller_sku_list.append(seller_sku[0])
if i_count % 20 == 0:
seller_sku_list = list(set(seller_sku_list))
print 'seller_sku_list is: %s' % str(
seller_sku_list)
logging.debug('seller_sku_list is: %s' %
str(seller_sku_list))
price_info = self.get_price_info_by_seller_sku(
seller_sku_list)
logging.debug('product_info is: %s' %
str(price_info))
self.update_db_by_price_info(price_info)
seller_sku_list = []
print seller_sku_list
seller_sku_list = list(set(seller_sku_list))
print 'seller_sku_list is: %s' % str(seller_sku_list)
logging.debug('seller_sku_list is: %s' % str(seller_sku_list))
if seller_sku_list:
price_info = self.get_price_info_by_seller_sku(
seller_sku_list)
logging.debug('product_info is: %s' % str(price_info))
self.update_db_by_price_info(price_info)
except Exception as e:
print e
logging.debug(
'----------------------------------shipping_price fail-----------------------------------------------'
)
logging.error('traceback.format_exc():\n%s' %
traceback.format_exc())
def update_db_by_price_info(self, price_info):
resp_obj = price_info.get('GetCompetitivePricingForSKUResult')
if isinstance(resp_obj, list):
resp_list = resp_obj
for resp_each in resp_list:
print resp_each.get('status').get('value')
logging.debug('get_price_status is: %s' %
resp_each.get('status').get('value'))
if resp_each.get('status').get('value') == 'Success':
sku = resp_each.get('Product').get('Identifiers').get(
'SKUIdentifier').get('SellerSKU').get('value')
if resp_each.get('Product').get('CompetitivePricing').get(
'CompetitivePrices').get(
'CompetitivePrice') is not None:
ship_price_info = resp_each.get('Product').get(
'CompetitivePricing').get('CompetitivePrices').get(
'CompetitivePrice')
print ship_price_info
if isinstance(ship_price_info, list):
ship_price = ship_price_info[0].get('Price').get(
'Shipping').get('Amount').get('value')
else:
ship_price = ship_price_info.get('Price').get(
'Shipping').get('Amount').get('value')
# ship_price = resp_each.get('Product').get('CompetitivePricing').get('CompetitivePrices').get('CompetitivePrice').get('Price').get('Shipping').get('Amount').get('value')
else:
ship_price = 0.00
price_sql = "update %s set shipping_price = %s where shopname = '%s' and seller_sku = '%s'" % (
self.auth_info['table_name'], ship_price,
self.auth_info['ShopName'], sku)
print price_sql
logging.debug('price_sql is: %s' % price_sql)
self.execute_db(price_sql)
rank_list = resp_each.get('Product').get(
'SalesRankings').get('SalesRank')
sale_rank = None
if isinstance(rank_list, list):
for rank in rank_list:
if rank.get('ProductCategoryId').get(
'value') == 'kitchen_display_on_website':
sale_rank = rank.get('Rank').get('value')
break
else:
sale_rank = rank_list.get('Rank').get('value')
sale_rank_sql = "update %s set sale_rank = %s where shopname = '%s' and seller_sku = '%s'" % (
self.auth_info['table_name'], sale_rank,
self.auth_info['ShopName'], sku)
logging.debug('sale_rank_sql is: %s' % sale_rank_sql)
self.execute_db(sale_rank_sql)
elif isinstance(resp_obj, dict):
resp_dict = resp_obj
logging.debug('get_price_status is: %s' %
resp_dict.get('status').get('value'))
if resp_dict.get('status').get('value') == 'Success':
sku = resp_dict.get('Product').get('Identifiers').get(
'SKUIdentifier').get('SellerSKU').get('value')
if resp_dict.get('Product').get('CompetitivePricing').get(
'CompetitivePrices').get(
'CompetitivePrice') is not None:
ship_price = resp_dict.get('Product').get(
'CompetitivePricing').get('CompetitivePrices').get(
'CompetitivePrice').get('Price').get(
'Shipping').get('Amount').get('value')
else:
ship_price = 0.00
price_sql = "update %s set shipping_price = %s where shopname = '%s' and seller_sku = '%s'" % (
self.auth_info['table_name'], ship_price,
self.auth_info['ShopName'], sku)
print price_sql
logging.debug('price_sql is: %s' % price_sql)
self.execute_db(price_sql)
rank_list = resp_dict.get('Product').get('SalesRankings').get(
'SalesRank')
sale_rank = None
if isinstance(rank_list, list):
for rank in rank_list:
if rank.get('ProductCategoryId').get(
'value') == 'kitchen_display_on_website':
sale_rank = rank.get('Rank').get('value')
break
else:
sale_rank = rank_list.get('Rank').get('value')
sale_rank_sql = "update %s set sale_rank = %s where shopname = '%s' and seller_sku = '%s'" % (
self.auth_info['table_name'], sale_rank,
self.auth_info['ShopName'], sku)
logging.debug('sale_rank_sql is: %s' % sale_rank_sql)
self.execute_db(sale_rank_sql)
def execute_db(self, sql):
print 'sql is: %s' % sql
class GetProductInfoBySellerSku:
"""
按seller_sku值获取产品图片及主、变体关系
"""
def __init__(self, auth_info, DATABASE):
self.db_conn = MySQLdb.connect(DATABASE['HOST'],
DATABASE['USER'],
DATABASE['PASSWORD'],
DATABASE['NAME'])
self.auth_info = auth_info
self.product_public = Products(self.auth_info['AWSAccessKeyId'],
self.auth_info['SecretKey'],
self.auth_info['SellerId'],
self.auth_info['ShopName'].split('/')[0].split('-')[-1])
def get_product_info_by_seller_sku(self, seller_sku):
try:
product_info_response = self.product_public.get_matching_product_for_id(self.auth_info['MarketplaceId'], 'SellerSKU', [seller_sku])
product_info_response_dic = product_info_response._response_dict
except Exception as e:
print e
time.sleep(10) # 防止超请求限制，重新提交
product_info_response = self.product_public.get_matching_product_for_id(self.auth_info['MarketplaceId'], 'SellerSKU', [seller_sku])
product_info_response_dic = product_info_response._response_dict
return product_info_response_dic
def update_asin(self, this_asin, image_url, seller_sku):
cursor = self.db_conn.cursor()
try:
sql = "update t_online_info_amazon set asin1 = '%s' , image_url = '%s' where seller_sku ='%s' and shopname = '%s'" \
% (this_asin, image_url, seller_sku, self.auth_info['ShopName'])
print 'update_parent_asin sql is: %s' % sql
cursor.execute(sql)
cursor.execute('commit;')
cursor.close()
except Exception as e:
cursor.close()
print e
def execute_db(self, sql):
cursor = self.db_conn.cursor()
try:
cursor.execute(sql)
cursor.execute('commit;')
cursor.close()
except Exception as e:
cursor.close()
print e
def update_db_by_product_info(self, product_info, seller_sku):
main_asin = product_info['GetMatchingProductForIdResult']['Products']['Product']['Identifiers']['MarketplaceASIN']['ASIN']['value']
image_url = product_info['GetMatchingProductForIdResult']['Products']['Product']['AttributeSets']['ItemAttributes']['SmallImage']['URL']['value']
self.update_asin(main_asin, image_url, seller_sku)
if product_info['GetMatchingProductForIdResult']['Products']['Product']['Relationships'].has_key('VariationChild'):
child_list = product_info['GetMatchingProductForIdResult']['Products']['Product']['Relationships']['VariationChild']
if isinstance(child_list, list):
for child in child_list:
child_asin = child['Identifiers']['MarketplaceASIN']['ASIN']['value']
sql_child = "update t_online_info_amazon set parent_asin = '%s' where asin1 ='%s' and shopname = '%s'" \
% (main_asin, child_asin, self.auth_info['ShopName'])
print 'sql_child is: %s' % sql_child
self.execute_db(sql_child)
else:
child_asin = child_list['Identifiers']['MarketplaceASIN']['ASIN']['value']
sql_child = "update t_online_info_amazon set parent_asin = '%s' where asin1 ='%s' and shopname = '%s'" \
% (main_asin, child_asin, self.auth_info['ShopName'])
print 'sql_child is: %s' % sql_child
self.execute_db(sql_child)
if product_info['GetMatchingProductForIdResult']['Products']['Product']['Relationships'].has_key('VariationParent'):
parent_asin = product_info['GetMatchingProductForIdResult']['Products']['Product']['Relationships']['VariationParent']['Identifiers']['MarketplaceASIN']['ASIN']['value']
sql_parent = "update t_online_info_amazon set parent_asin = '%s' where asin1 ='%s' and shopname = '%s'" \
% (parent_asin, main_asin, self.auth_info['ShopName'])
print 'parent_asin is: %s' % parent_asin
print 'sql_parent is: %s' % sql_parent
self.execute_db(sql_parent)
def refresh_data_by_seller_sku(self, seller_sku):
product_info = self.get_product_info_by_seller_sku(seller_sku)
self.update_db_by_product_info(product_info, seller_sku)