def sku_to_inventory():
sql = "select sku,Seller,quantity,asin from sku where IsFBA<>'True';"
try:
conn = mysqlconn.mysqlconn(db='amazon')
cur = conn.cursor()
cur.execute(sql)
data = cur.fetchall()
for i in data:
try:
mysqlconn.db_insert(
conn, {
'SellerSKU': i[0],
'Seller': i[1],
'InStockSupplyQuantity': i[2],
'ASIN': i[3],
}, 'inventory')
except pymysql.err.IntegrityError:
mysqlconn.db_update(
conn, {
'SellerSKU': i[0],
'Seller': i[1],
'InStockSupplyQuantity': i[2],
'ASIN': i[3],
}, ['SellerSKU', 'Seller'], 'inventory')
finally:
conn.close()
def update_mysku(conn, Account, MarketplaceId, ReportType):
data, ReportId = get_mysku(Account=Account, MarketplaceId=MarketplaceId, ReportType=ReportType)
# filter the data using pandas.DataFrame
df = DataFrame(data[1:], columns=data[0])
columns = [i for i in sku_fields if i in data[0]]
df = df[columns]
df['Seller'] = Account['Seller']
df['MarketplaceId'] = MarketplaceId
df['ReportId'] = ReportId
result = []
# delete the old sku
cur = conn.cursor()
cur.execute('delete from sku where MarketplaceId="%s" and Seller="%s";' % (MarketplaceId, Account['Seller']))
conn.commit()
cur.close()
# update sku to db
for i in range(df.shape[0]):
dct = {}
for j in df.columns:
dct[j] = df[j][i] if df[j][i] != '' else 'NULL'
try:
mysqlconn.db_insert(conn, dct, 'sku')
except Exception as e:
result.append(e)
pass
return result
def update_orderitems(conn, Account, orderid_list=tuple()):
if not orderid_list:
# 挑出需要请求具体信息的AmazonOrderId
date = datetime.datetime.now() - datetime.timedelta(weeks=4)
cur = conn.cursor()
cur.execute("select distinct AmazonOrderId from orders where Seller='%s' and LastUpdateDate>'%s'" % (Account['Seller'], date.strftime("%Y-%m-%d %H:%M:%S")))
data = cur.fetchall()
data_o = [i[0] for i in data]
cur.execute("select distinct AmazonOrderId,ItemPrice_Amount from orderitems")
data = cur.fetchall()
data_oi = [i[0] for i in data]
cur.close()
orderid_list = tuple([i for i in data_o if i not in data_oi] + [i[0] for i in data if i[1] is None and i[0] in data_o])
for id in orderid_list:
orderitem_list = get_orderitems(Account=Account, AmazonOrderId=id)
for item in orderitem_list:
# insert进数据库
try:
mysqlconn.db_insert(conn, item, 'orderitems')
except mysqlconn.pymysql.err.IntegrityError:
mysqlconn.db_update(conn, item, ['AmazonOrderId', 'SellerSKU'], 'orderitems')
return 1
def handle_order_items(item_list):
"""将item_list中的数据加入数据库"""
conn = mysqlconn.mysqlconn(db='amazon')
for item in item_list:
# insert进数据库
try:
mysqlconn.db_insert(conn, item, 'orderitems')
except pymysql.err.IntegrityError:
mysqlconn.db_update(conn, item, ['AmazonOrderId', 'SellerSKU'],
'orderitems')
conn.close()
return 1
def handle_inventory(inventory_list):
"""将inventory_list中的数据加入数据库"""
conn = mysqlconn.mysqlconn(db='amazon')
for inventory in inventory_list:
if inventory.get('Condition'):
# Condition 可能是mysql内部保留字, 所以加上 `` 符号insert和update才不会报错
inventory['`Condition`'] = inventory.pop('Condition')
try:
mysqlconn.db_insert(conn, inventory, 'inventory')
except pymysql.err.IntegrityError:
mysqlconn.db_update(conn, inventory, ['SellerSKU', 'Seller'],
'inventory')
conn.close()
return 1
def up_products(account):
result = []
report = get_product_report(account, '_GET_FLAT_FILE_OPEN_LISTINGS_DATA_')
conn = mysqlconn.mysqlconn(db='amazon')
for i in range(report.shape[0]):
dct = {}
for j in report.columns:
dct[j] = report[j][i] if report[j][i] != '' else 'NULL'
try:
mysqlconn.db_insert(conn, dct, 'sku')
except pymysql.err.IntegrityError:
mysqlconn.db_update(conn, dct, ['sku', 'Seller'], 'sku')
except Exception as e:
result.append(e)
pass
conn.close()
return result
def handle_orders(order_list):
"""处理得到的订单
如果在数据库中已经有该订单(相同AmazonOrderId),且LastUpdatedDate一样,则跳过
如果在数据库中已经有该订单(相同AmazonOrderId),LastUpdatedDate不一样,则insert进数据库,并判断PaidDate
如果在数据库中没有该订单,insert进数据库
"""
conn = mysqlconn.mysqlconn(db='amazon')
cur = conn.cursor()
for order in order_list:
# 选出该AmazonOrderId的最新的记录
newest_record = "select * from orders as a where not exists" +\
"(select 1 from orders as b where b.AmazonOrderId=a.AmazonOrderId " +\
"and b.LastUpdateDate>a.LastUpdateDate)"
sql = "select * from (%s) as t where t.AmazonOrderId='%s'" % (
newest_record, order['AmazonOrderId'])
cur.execute(sql)
data = cur.fetchall()
# 如果此次请求结果中的LastUpdateDate约数据库中的一样,说明已在数据库中记录，不用做任何处理
if data and str(data[0][1]) == order['LastUpdateDate']:
continue
# 处理PaidDate
if order['OrderStatus'] == 'Pending':
order['PaidDate'] = "0000-00-00 00:00:00"
else:
# 如果数据库里有该订单的PaidDate,则用数据库中的PaidDate,否则用此次请求结果中的LastUpdateDate
if data and (data[0][4] is not None):
order['PaidDate'] = str(data[0][4])
else:
order['PaidDate'] = order['LastUpdateDate']
# insert进数据库
try:
mysqlconn.db_insert(conn, order, 'orders')
except pymysql.err.IntegrityError:
pass
conn.close()
return 1 # 1表示函数执行成功
def update_myprice(conn, Account, market_list=tuple(), sku_list=dict()):
if not market_list:
market_list = tuple([Account[key] for key in Account if key.startswith('MarketplaceId')])
if not sku_list and SKU.get(Account['Seller']):
sku_list = SKU[Account['Seller']]
for MarketplaceId in market_list:
if not sku_list.get(MarketplaceId):
continue
for sku in sku_list.get(MarketplaceId):
myprice_list = get_myprice(Account=Account, MarketplaceId=MarketplaceId, SKU=sku)
for myprice in myprice_list:
# insert进数据库
try:
mysqlconn.db_insert(conn, myprice, 'price')
except mysqlconn.pymysql.err.IntegrityError:
mysqlconn.db_update(conn, myprice, ['SellerSKU', 'SellerId', 'MarketplaceId'], 'price')
return 1
def update_inventory(conn, Account, market_list=tuple(), sku_list=dict()):
if not market_list:
market_list = tuple([Account[key] for key in Account if key.startswith('MarketplaceId')])
if not sku_list and SKU.get(Account['Seller']):
sku_list = SKU[Account['Seller']]
for MarketplaceId in market_list:
if not sku_list.get(MarketplaceId):
continue
for sku in sku_list.get(MarketplaceId):
inventory_list = get_inventory(Account=Account, MarketplaceId=MarketplaceId, SKU=sku)
for inventory in inventory_list:
if inventory.get('Condition'):
# Condition 可能是mysql内部保留字, 所以加上 `` 符号insert和update才不会报错
inventory['`Condition`'] = inventory.pop('Condition')
try:
mysqlconn.db_insert(conn, inventory, 'inventory')
except mysqlconn.pymysql.err.IntegrityError:
if is_FBA(conn, Account['SellerId'], MarketplaceId, sku):
mysqlconn.db_update(conn, inventory, ['SellerSKU', 'Seller', 'MarketplaceId'], 'inventory')
return 1
def get_lu_orders(account,
last_updated_after="0000-00-00 00:00:00",
tz='UTC+8'):
"""获取CreateAfter之后的订单
last_updated_after未给定或者为"0000-00-00 00:00:00"，则默认为utc当前时间
last_updated_after给定，则根据所在时区tz,会自动转成UTC时间
tz指参数last_updated_after时间所在的时区
"""
# 初始化CreateAfter的值
if last_updated_after == "0000-00-00 00:00:00":
last_updated_after = get_utc_time()
else:
last_updated_after = get_utc_time(last_updated_after, tz)
# 获取订单信息
lo = uc.ListOrders(Account=account, LastUpdatedAfter=last_updated_after)
if lo.request_method == 'POST':
page = requests.post(lo.url, data=lo.payload)
else:
raise Exception(lo.request_method)
# 解析订单信息（xml字符串）
root = ET.fromstring(
page.text.replace(
' xmlns="https://mws.amazonservices.com/Orders/2013-09-01"', ''))
# next_token = root.find('ListOrdersResult').find('NextToken')
request_id = root.find('ResponseMetadata').find('RequestId').text
orders = root.find('ListOrdersResult').find('Orders').findall('Order')
conn = mysqlconn.mysqlconn()
cur = conn.cursor()
for order in orders:
order_info = parse_to_dict(order)
order_info = flat_dict(order_info)
order_info = format_data(order_info)
order_info['RequestId_1'] = request_id
# 将卖家名称加入进去
order_info["Seller"] = account
# 选出AmazonOrderId和ASIN的最新的记录
newest_record = "select * from amazon_orders as a where not exists" +\
"(select 1 from amazon_orders as b where b.AmazonOrderId=a.AmazonOrderId " +\
"and b.ASIN=a.ASIN and b.LastUpdateDate>a.LastUpdateDate)"
sql = "select * from (%s) as t where t.AmazonOrderId='%s'" % (
newest_record, order_info['AmazonOrderId'])
cur.execute(sql)
data = cur.fetchall()
# 如果此次请求结果中的LastUpdateDate约数据库中的一样,说明已在数据库中记录,本次循环不用做任何处理
if data and str(data[0][1]) == order_info['LastUpdateDate']:
continue
# 处理PaidDate
if order_info['OrderStatus'] == 'Pending':
order_info['PaidDate'] = "0000-00-00 00:00:00"
else:
# 如果数据库里有该订单的PaidDate,则用数据库中的PaidDate,否则用此次请求结果中的LastUpdateDate
if data and (data[0][4] is not None):
order_info['PaidDate'] = str(data[0][4])
else:
order_info['PaidDate'] = order_info['LastUpdateDate']
# 如果数据库里有该订单的信息,说明不是新订单,不需要再去请求订单所包含的商品,否则需要。
if data:
for i in range(len(data)):
order_info_cp = order_info.copy()
order_info_cp['ASIN'] = data[i][2]
order_info_cp['SellerSKU'] = data[i][3]
order_info_cp['RequestId_2'] = data[i][41]
try:
# 更新到数据库
mysqlconn.db_insert(conn, order_info_cp, 'amazon_orders')
except pymysql.err.IntegrityError:
pass
else:
# 请求订单所包含的商品信息
order_items = get_order_items(account, order_info['AmazonOrderId'])
for i in range(len(order_items)):
order_info_cp = order_info.copy()
order_info_cp['ASIN'] = order_items[i]['ASIN']
order_info_cp['SellerSKU'] = order_items[i]['SellerSKU']
order_info_cp['RequestId_2'] = order_items[i]['RequestId_2']
try:
# 更新到数据库
mysqlconn.db_insert(conn, order_info_cp, 'amazon_orders')
except pymysql.err.IntegrityError:
pass
conn.close()