def get_summary_info(account, startdate):
"""从数据库中提取订单数据,汇总出 总销售额,总订单数量 和 订单号
account 指要查询的帐号
startdate 指要查询的开始时间
函数返回total_sales, total_orders, order_list"""
# 选出表orders中每个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)"
# 选出 指定账户指定时间之后的非pending状态的 订单
sql = "select t.AmazonOrderId,t.Amount,t.CurrencyCode from (%s) as t " \
"where t.PaidDate>='%s' and t.OrderStatus<>'Pending'"
sql = sql % (newest_record, startdate)
sql = sql + " and t.Seller='" + account + "'"
# 取数据
conn = mysqlconn.mysqlconn(db='amazon')
cur = conn.cursor()
cur.execute(sql)
data = cur.fetchall()
conn.commit()
# 删除没有Amount的订单（如果某个订单的amount为None,判断为被取消的订单）
# data形式如[('001', 899, 'USD'), ('002', 49, 'CAD')]
data = [i for i in data if i[1] is not None]
total_sales, total_orders, order_list = _summary_info(data)
return total_sales, total_orders, order_list
def get_order_items(account):
"""通过ListOrderItems获取订单所包含的具体商品信息
挑出数据库中 在orders表中而不在orderitems表中 的AmazonOrderId
通过ListOrderItems获取订单具体信息,加入orderitems表里
"""
conn = mysqlconn.mysqlconn(db='amazon')
cur = conn.cursor()
# 挑出需要请求具体信息的AmazonOrderId
date = datetime.datetime.now() - datetime.timedelta(weeks=1)
cur.execute(
"select distinct AmazonOrderId from orders where Seller='%s' and LastUpdateDate>'%s'"
% (account, 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 from orderitems")
data = cur.fetchall()
data_oi = [i[0] for i in data]
conn.close()
id_list = [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]
item_list = []
for i in id_list:
item_list += _get_order_items(account, i)
return item_list
def sum_sales(account, startdate):
conn = mysqlconn.mysqlconn()
cur = conn.cursor()
sql = "select distinct AmazonOrderId,Amount,CurrencyCode from amazon_orders " + \
"where seller='%s' and PaidDate>'%s' and OrderStatus<>'Pending'"
sql = sql % (account, startdate)
cur.execute(sql)
data = cur.fetchall()
conn.close()
file = os.path.join(os.path.dirname(__file__), "exchange_rate.json")
with open(file, 'r') as f:
exchange_rate = json.load(f)
order_sum = 0
amount_sum = 0
if data:
for i in data:
# 判断是否为None
if i[1]:
code = i[2]
order_sum += 1
amount_sum += i[1] * exchange_rate[code]
amount_sum = amount_sum / exchange_rate["USD"]
result = "订单数量： " + str(order_sum) + "\n" + \
"订单总金额(USD)： " + str(round(amount_sum, 2))
return result
def flush_info_to_db(Account, t):
conn = mysqlconn.mysqlconn(**db_config)
# orders
try:
# CreatedAfter
conn.connect() if not conn.open else 1
r_oc = update_orders(conn, Account, CreatedAfter=t)
# LastUpdatedAfter
conn.connect() if not conn.open else 1
r_ol = update_orders(conn, Account, LastUpdatedAfter=t)
except:
r_oc = 0
r_ol = 0
# orderitems
try:
conn.connect() if not conn.open else 1
r_oi = update_orderitems(conn, Account)
except:
r_oi = 0
conn.close()
return r_oc, r_ol, r_oi
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 _query(selected_field, conditions, sql=None):
if not sql:
sql = "select " + selected_field + " from orders " \
"left join orderitems on orders.AmazonOrderId=orderitems.AmazonOrderId " \
"left join inventory on orderitems.SellerSKU=inventory.SellerSKU " \
"and orders.Seller=inventory.Seller"
if conditions:
sql = sql + " where " + conditions
# order the data by orders.LastUpdateDate to ease the later handling
sql = sql + " order by orders.AmazonOrderId, orders.LastUpdateDate;"
try:
conn = mysqlconn.mysqlconn(db='DB_NAME')
cur = conn.cursor()
cur.execute(sql)
data = cur.fetchall()
except:
raise QueryException
else:
result = []
# delete the duplicate data
for i in data:
if i not in result:
result.append(i)
return result
finally:
conn.close()
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 get_item_info(account, order_list):
"""从orderitems和inventory表中取出商品信息
account 指要查询的帐号
order_list 指要从orderitems中查询的订单, 类型:list
"""
# 取出order_list中订单号所涉及的产品的信息
if order_list:
sql = "select orderitems.SellerSKU,AmazonOrderId,QuantityOrdered,InStockSupplyQuantity" \
" from orderitems left join inventory on orderitems.SellerSKU=inventory.SellerSKU"
sql = sql + " where AmazonOrderId in %s;"
sql = sql % str(tuple(order_list)).replace(",)", ")")
# 取数据
conn = mysqlconn.mysqlconn(db='amazon')
cur = conn.cursor()
cur.execute(sql)
data = cur.fetchall()
conn.close()
else:
data = ()
skus, orderitems, total_items = _format_data(data)
# 取出未涉及到的其他产品的库存信息
sku_list = SKU[account]
sku_list = [i for i in sku_list if i not in skus]
sku_list = tuple(sku_list)
sql = "select SellerSKU,null,null,InStockSupplyQuantity from inventory where SellerSKU in %s" % str(
sku_list)
conn = mysqlconn.mysqlconn(db='amazon')
cur = conn.cursor()
cur.execute(sql)
data = cur.fetchall()
conn.close()
for i in data:
orderitems.append({
'SellerSKU': i[0],
'QuantityOrdered': i[2],
'InStockSupplyQuantity': i[3]
})
return orderitems, total_items
def create_orders_table():
create_table_sql = """CREATE TABLE IF NOT EXISTS `amazon_orders` (
`AmazonOrderId` varchar(50) NOT NULL,
`LastUpdateDate` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`ASIN` varchar(50) NOT NULL,
`SellerSKU` varchar(50) DEFAULT NULL,
`PaidDate` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`LatestShipDate` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`OrderType` varchar(50) DEFAULT NULL,
`PurchaseDate` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`BuyerEmail` varchar(100) DEFAULT NULL,
`IsReplacementOrder` varchar(10) DEFAULT NULL,
`NumberOfItemsShipped` int(10) DEFAULT NULL,
`ShipServiceLevel` varchar(100) DEFAULT NULL,
`OrderStatus` varchar(50) DEFAULT NULL,
`SalesChannel` varchar(100) DEFAULT NULL,
`ShippedByAmazonTFM` varchar(10) DEFAULT NULL,
`IsBusinessOrder` varchar(10) DEFAULT NULL,
`LatestDeliveryDate` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`NumberOfItemsUnshipped` int(10) DEFAULT NULL,
`PaymentMethodDetail` varchar(50) DEFAULT NULL,
`BuyerName` varchar(100) DEFAULT NULL,
`EarliestDeliveryDate` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`CurrencyCode` varchar(10) DEFAULT NULL,
`Amount` float DEFAULT NULL,
`IsPremiumOrder` varchar(10) DEFAULT NULL,
`EarliestShipDate` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`MarketplaceId` varchar(50) DEFAULT NULL,
`FulfillmentChannel` varchar(50) DEFAULT NULL,
`PurchaseOrderNumber` varchar(50) DEFAULT NULL,
`PaymentMethod` varchar(50) DEFAULT NULL,
`City` varchar(100) DEFAULT NULL,
`AddressType` varchar(50) DEFAULT NULL,
`PostalCode` varchar(50) DEFAULT NULL,
`StateOrRegion` varchar(100) DEFAULT NULL,
`Phone` varchar(50) DEFAULT NULL,
`CountryCode` varchar(50) DEFAULT NULL,
`Name` varchar(100) DEFAULT NULL,
`AddressLine1` varchar(500) DEFAULT NULL,
`AddressLine2` varchar(500) DEFAULT NULL,
`IsPrime` varchar(10) DEFAULT NULL,
`ShipmentServiceLevelCategory` varchar(50) DEFAULT NULL,
`SellerOrderId` varchar(50) DEFAULT NULL,
`Seller` varchar(100) DEFAULT NULL,
`RequestId_1` varchar(50) DEFAULT NULL,
`RequestId_2` varchar(50) DEFAULT NULL,
PRIMARY KEY (`AmazonOrderId`,`LastUpdateDate`,`ASIN`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;"""
conn = mysqlconn.mysqlconn()
r = conn.cursor().execute(create_table_sql)
conn.close()
return r
def main(logger, Account, t=None):
if not t:
t = datetime.datetime.now() - datetime.timedelta(hours=3)
t = t.strftime("%Y-%m-%d %H:%M:%S")
conn = mysqlconn.mysqlconn(**db_config)
# orders
try:
logger.info(Account['Seller'] + "\torders update start")
# CreatedAfter
conn.connect() if not conn.open else 1
logger.info("CreatedAfter")
r_oc = update_orders(conn, Account, CreatedAfter=t)
# LastUpdatedAfter
conn.connect() if not conn.open else 1
logger.info("LastUpdatedAfter")
r_ol = update_orders(conn, Account, LastUpdatedAfter=t)
except:
logger.exception(Account['Seller'] + "\torders update Error")
r_oc = 0
r_ol = 0
# orderitems
try:
conn.connect() if not conn.open else 1
logger.info(Account['Seller'] + "\torderitems update start")
r_oi = update_orderitems(conn, Account)
except:
logger.exception(Account['Seller'] + "\torderitems update Error")
r_oi = 0
# price
try:
conn.connect() if not conn.open else 1
logger.info(Account['Seller'] + "\tprice update start")
r_p = update_myprice(conn, Account)
except:
logger.exception(Account['Seller'] + "\tprice update Error")
r_p = 0
# inventory
try:
conn.connect() if not conn.open else 1
logger.info(Account['Seller'] + "\tinventory update start")
r_i = update_inventory(conn, Account)
except:
logger.exception(Account['Seller'] + "\tinventory update Error")
r_i = 0
conn.close()
return r_oc, r_ol, r_oi, r_i, r_p
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 create_db():
conn = mysqlconn.mysqlconn(**db_config)
try:
cur = conn.cursor()
for sql in [
amazon_db_sql, orders_table_sql, orderitems_table_sql,
inventory_table_sql, sku_table_sql, price_table_sql
]:
cur.execute(sql)
cur.close()
except:
raise
finally:
conn.close()
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 summary(seller, startdate):
conn = mysqlconn.mysqlconn(**db_config)
try:
inventory_info = get_inventory(conn, seller)
order_info = get_order_info(conn, seller, startdate)
# 获取汇率信息
file = os.path.join(os.path.dirname(__file__),
"CurrencyRate/exchange_rate.json")
with open(file, 'r') as f:
exchange_rate = json.load(f)
order_set = set()
total_sales = 0
total_items = 0
for i in order_info:
flag = 0 # the flag that the order has been handled
if i[4]:
order_set.add(i[0])
total_items += i[4]
total_sales += i[1] * exchange_rate[i[2]]
for j in range(len(inventory_info)):
if inventory_info[j]['SellerSKU'] == i[3]:
inventory_info[j]['QuantityOrdered'] += i[4]
flag = 1
if not flag:
inventory_info.append({
'SellerSKU': i[3],
'QuantityOrdered': i[4],
'InStockSupplyQuantity': 0
})
total_sales = total_sales / exchange_rate['USD']
total_sales = round(total_sales, 2)
total_orders = len(order_set)
orderitems = sorted(inventory_info,
key=lambda x: x['QuantityOrdered'],
reverse=True)
return orderitems, total_sales, total_orders, total_items
except:
raise
finally:
conn.close()
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 main(logger, Account, market_list=tuple(), ReportType=None):
if not market_list:
market_list = tuple([
Account[key] for key in Account if key.startswith('MarketplaceId')
])
if not ReportType:
ReportType = "_GET_FLAT_FILE_OPEN_LISTINGS_DATA_"
conn = mysqlconn.mysqlconn(**db_config)
result = []
for MarketplaceId in market_list:
try:
logger.info(MarketplaceId + "\t" + ReportType + "\tStart")
result.append(
update_mysku(conn, Account, MarketplaceId, ReportType))
except:
logger.exception(MarketplaceId + "\t" + ReportType + "\tError")
conn.close()
return result
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()
# -*- coding: utf-8 -*-
"""
Created at: 18-4-28 上午10:52
@Author: Qian
"""
from my_modules import mysqlconn
# add the father dir into the path
import sys
sys.path.append('..')
from BasicConfig.mysql_info import db_config
__all__ = ['SKU']
conn = mysqlconn.mysqlconn(**db_config)
cur = conn.cursor()
cur.execute("select Seller,MarketplaceId,sku from sku;")
data = cur.fetchall()
conn.close()
SKU = {}
for i in data:
if i[0] not in SKU:
SKU[i[0]] = {}
if i[1] not in SKU[i[0]]:
SKU[i[0]][i[1]] = []
SKU[i[0]][i[1]].append(i[2])
def get_orders_from_db(account,
startdate=None,
enddate=None,
type="new and paid"):
"""从数据库中提取某段时间内的订单记录
如果startdate未给定，默认是前一天00：00：00
如果enddate未给定，默认None
type可选,默认是"new and paid",值可以为"paid", "new and paid", "unpaid", "new but unpaid"
使用示例：
>>> d = get_orders_from_db("J", type="new but unpaid")
>>> d
"""
if not startdate:
if account == "F":
startdate = datetime.datetime.now() - datetime.timedelta(days=1)
startdate = datetime.datetime.strftime(startdate,
"%Y-%m-%d 00:00:00")
else:
startdate = datetime.datetime.now()
startdate = datetime.datetime.strftime(startdate,
"%Y-%m-%d %H:%M:%S")
# 选出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)"
if enddate:
sql = "select * from (%s) t where t.PaidDate>='%s' and t.LastUpdateDate<'%s'" % (
newest_record, startdate, enddate)
else:
sql = "select * from (%s) as t where t.PaidDate>='%s'" % (
newest_record, startdate)
if type == "new and paid":
# type为None，则返回 new and paid
sql = sql + " and t.OrderStatus<>'Pending'"
elif type == "paid":
# 返回所有paid订单
sql = "select * from (%s) as t where t.OrderStatus<>'Pending'" % newest_record
elif type == "unpaid":
# 返回所有unpaid订单
sql = "select * from (%s) as t where t.OrderStatus='Pending'" % newest_record
elif type == "new but unpaid":
# 返回new and unpaid订单
sql = "select * from (%s) as t where t.PurchaseDate>='%s'" % (
newest_record, startdate)
sql = sql + " and t.OrderStatus='Pending'"
else:
raise Exception("No such type")
# 选出指定卖家的订单
if account != "all":
sql = sql + " and Seller='%s'" % account
conn = mysqlconn.mysqlconn()
cur = conn.cursor()
cur.execute(sql)
data = cur.fetchall()
conn.close()
return list(data)
# -*- coding: utf-8 -*-
"""
Created at: 18-3-7 上午05:42
@Author: Qian
"""
from my_modules import mysqlconn
__all__ = ['SKU']
conn = mysqlconn.mysqlconn(db='amazon')
cur = conn.cursor()
cur.execute("select sku,Seller from sku where IsFBA='True';")
data = cur.fetchall()
conn.close()
SKU = {}
for i in data:
if i[1] in SKU:
SKU[i[1]].append(i[0])
else:
SKU[i[1]] = [
i[0],
]
