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 sku_to_inventory(logger):
sql = "select sku,Seller,MarketplaceId,quantity from sku where quantity is not NULL;"
try:
conn = mysqlconn.mysqlconn(**db_config)
cur = conn.cursor()
cur.execute(sql)
data = cur.fetchall()
cur.close()
except:
logger.exception("db Error")
else:
for i in data:
_data = {
'SellerSKU': i[0],
'Seller': i[1],
'MarketplaceId': i[2],
'InStockSupplyQuantity': i[3],
}
try:
mysqlconn.db_update(conn, _data,
['SellerSKU', 'Seller', 'MarketplaceId'],
'inventory')
except:
logger.exception(_data)
finally:
conn.close()
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 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