sync_amz_data/sync_amz_data/public/sp_api_client.py

import clickhouse_connect
import time

import numpy as np
from pymysql import Timestamp
from sp_api.util import throttle_retry, load_all_pages
from sp_api.api import Orders,ListingsItems,Inventories,Reports
from sp_api.base import Marketplaces,ReportType,ProcessingStatus
import pandas as pd
import gzip
from io import BytesIO,StringIO
from datetime import datetime, timedelta,timezone
import pytz
import time
from sync_amz_data.public.amz_ad_client import shop_infos
from dateutil.parser import parse
import pymysql



class SpApiRequest:
    def __init__(self, credentials, marketplace,profile_id):
        self.credentials = credentials
        self.marketplace = marketplace
        self.shopInfo = shop_infos(profile_id)
        self.timezone = self.shopInfo['time_zone']

    def mysql_connect(self):
        conn = pymysql.connect(user="huangyifan",
        password="123456",
        host="192.168.1.18",
        database="amz_sp_api",
        port=3306)
        return conn
    def timeToLocalTime(self,para_time=None):
        if para_time is not None:
            if ":" not in para_time:
                para_time = para_time+' 0:0:0'
            report_USTime = datetime.strptime(para_time,"%Y-%m-%d %H:%M:%S")
        else:
            report_USTime = datetime.now(tz=pytz.timezone(self.timezone)) + timedelta(days=-1)
        USTime = pytz.timezone(self.timezone)
        ustime_end = USTime.localize(datetime(report_USTime.year,report_USTime.month,report_USTime.day,23,59,59,999999))
        ustime_start = USTime.localize(datetime(report_USTime.year,report_USTime.month,report_USTime.day,0,0,0))
        print(self.timezone+": ","\n",ustime_start,"-",ustime_end)
        # utctime = pytz.timezone("UTC")
        timezoneutc = pytz.timezone('UTC')
        startTimeutc = ustime_start.astimezone(timezoneutc)
        endTimeutc = ustime_end.astimezone(timezoneutc)
        formatTime = "%Y-%m-%dT%H:%M:%S+00:00"
        startTimeutc = startTimeutc.strftime(formatTime)
        endTimeutc = endTimeutc.strftime(formatTime)
        print("UTC: ","\n",startTimeutc,"-",endTimeutc)
        return startTimeutc,endTimeutc

    def timeDeal(self,orgTime):
        orgTime = parse(orgTime)
        timezone = pytz.timezone(self.timezone)
        shopTime = orgTime.astimezone(timezone)
        shopTime_datetime = datetime(shopTime.year,shopTime.month,shopTime.day,shopTime.hour,shopTime.minute,shopTime.second)
        return shopTime_datetime


    def nowTime(self):
        computerTimenow = datetime.now()
        localTimeNow = datetime.now(tz=pytz.timezone(self.timezone))
        utctimeNow = datetime.utcnow()
        return  {"computerTimenow":computerTimenow,"localTimeNow":localTimeNow,"utctimeNow":utctimeNow}

    def create_report(self,**kwargs):
        reportType = kwargs['reportType']
        reportOptions =kwargs.get("reportOptions")

        dataStartTime = datetime.now(tz=pytz.timezone(self.timezone)).strftime("%Y-%m-%dT%H:%M:%S") if kwargs.get("dataStartTime") is None else kwargs.get("dataStartTime")+"T00:00:00"
        dataEndTime = datetime.now(tz=pytz.timezone(self.timezone)).strftime("%Y-%m-%dT%H:%M:%S") if kwargs.get("dataEndTime") is None else kwargs.get("dataEndTime")+"T23:59:59"

        report = Reports(credentials=self.credentials, marketplace=self.marketplace)
        rel = report.create_report(
                        reportType=reportType,marketplaceIds=[self.marketplace.marketplace_id],reportOptions=reportOptions,
                        dataStartTime=dataStartTime,dataEndTime=dataEndTime
                        )
        reportId = rel.payload.get("reportId")
        print(reportId)
        return reportId

    def decompression(self,reportId):
        report = Reports(credentials=self.credentials, marketplace=self.marketplace)
        while True:
            time.sleep(15)
            reportId_info = report.get_report(reportId=reportId)
            # print(reportId_info.payload)
            print("please wait...")
            if reportId_info.payload.get("processingStatus")==ProcessingStatus.DONE:
                reportDocumentId = reportId_info.payload.get("reportDocumentId")
                rp_table = report.get_report_document(reportDocumentId=reportDocumentId,download=False)
                print(rp_table)
                if rp_table.payload.get('compressionAlgorithm') is not None:
                    df = pd.read_table(filepath_or_buffer=rp_table.payload['url'],compression={"method":'gzip'},encoding='iso-8859-1')
                    return df
                else:
                    df = pd.read_table(rp_table.payload.get("url"),encoding='iso-8859-1')
                    return df
            else:
                if reportId_info.payload.get("processingStatus") in [ProcessingStatus.CANCELLED,ProcessingStatus.FATAL]:
                    print("取消或失败")
                    break

            print("please wait...")

    def test_(self):
        conn = self.mysql_connect()
        cursor = conn.cursor()
        sql = "select * from amz_sp_api.productInfo"

        # query_rel = conn.query(sql)
        # cursor.execute(sql)
        # col = [i[0] for i in cursor.description]
        # rel = cursor.fetchall()
        # print(col)
        # print(rel)

        sql1 = "select * from amz_sp_api.orderReport where `promotion-ids` in ['', Null]"
        query_rel1 = conn.query(sql1)
        cursor.execute(sql1)
        col = [i[0] for i in cursor.description]
        rel1 = cursor.fetchall()
        df = pd.DataFrame(rel1,columns=col)
        df['ReportDate'] = df['ReportDate'].astype("datetime64[ns]")
        print(df.info())
        # print(df)
        bondary_date = (datetime.today() + timedelta(days=7))
        list_ = ['',None]
        df1 = df.query("ReportDate>@bondary_date or `promotion-ids` in @list_")
        print(df1)

    def data_deal(self,decom_df):
        decom_df['mainImageUrl'] = decom_df['seller-sku'].map(lambda x: self.get_mainImage_url(x))
        url_columns = [i for i in decom_df.columns if "url" in i.lower()]
        if len(url_columns) > 0:
            decom_df[url_columns] = decom_df[url_columns].astype("string")
        asin_columns = [i for i in decom_df.columns if 'asin' in i.lower()]
        if len(asin_columns) > 0:
            decom_df[asin_columns] = decom_df[asin_columns].astype("string")
        if 'pending-quantity' in decom_df.columns:
            decom_df['pending-quantity'] = decom_df['pending-quantity'].map(
                lambda x: 0 if pd.isna(x) or np.isinf(x) else x).astype("int32")
        deletecolumns = [i for i in decom_df.columns if 'zshop' in i.lower()]
        decom_df.drop(columns=deletecolumns, inplace=True)
        if 'quantity' in decom_df.columns:
            decom_df['quantity'] = decom_df['quantity'].map(lambda x: 0 if pd.isna(x) or np.isinf(x) else x).astype(
                "int32")
        decom_df['opendate_date'] = decom_df['open-date'].str.split(' ', expand=False).map(lambda x: x[0]).astype(
            'datetime64[ns]')
        if 'add-delete' in decom_df.columns:
            decom_df['add-delete'] = decom_df['add-delete'].astype('string', errors='ignore')
        if 'will-ship-internationally' in decom_df:
            decom_df['will-ship-internationally'] = decom_df['will-ship-internationally'].astype('string',errors='ignore')
        if 'expedited-shipping' in decom_df.columns:
            decom_df['expedited-shipping'] = decom_df['expedited-shipping'].astype('string',errors='ignore')
        decom_df['updateTime'] = datetime.now(tz=pytz.timezone(self.timezone))
        decom_df['timezone'] = self.timezone
        decom_df['item-description'] = decom_df['item-description'].str.slice(0,500)
        decom_df[decom_df.select_dtypes(float).columns] = decom_df[decom_df.select_dtypes(float).columns].fillna(0.0)
        decom_df[decom_df.select_dtypes(int).columns] = decom_df[decom_df.select_dtypes(int).columns].fillna(0)
        decom_df[decom_df.select_dtypes(datetime).columns] = decom_df[decom_df.select_dtypes(datetime).columns].astype(
            'string')
        decom_df.fillna('', inplace=True)
        return decom_df

    def GET_MERCHANT_LISTINGS_ALL_DATA(self,limit=None):
        para = {"reportType":ReportType.GET_MERCHANT_LISTINGS_ALL_DATA}
        reportid = self.create_report(**para)
        decom_df = self.decompression(reportid)
        print("连接数据库")
        conn = self.mysql_connect()
        cursor = conn.cursor()
        bondary_date = (datetime.today() + timedelta(days=-28)).strftime("%Y-%m-%d")
        cursor.execute(f"""select * from amz_sp_api.productInfo where (mainImageUrl is not null and mainImageUrl not in ('', ' ')) and 
                        (`seller-sku` not in ('',' ') and `seller-sku` is not null) and 
                        `updateTime`>='{bondary_date}'""") #`seller-sku`,`updateTime`,`mainImageUrl`
        col = [i[0] for i in cursor.description]
        query_rel = cursor.fetchall()
        print(query_rel[0])
        if len(query_rel)!=0:
            df = pd.DataFrame(query_rel,columns=col)
            listingid = df['listing-id'].to_numpy().tolist()
            decom_df = decom_df.query("`listing-id` not in @listingid")
            # print(f"delete * from amz_sp_api.productInfo where `listing-id` not in {tuple(listingid)}")
            cursor.execute(f"delete from amz_sp_api.productInfo where `listing-id` not in {tuple(listingid)}")
            conn.commit()

        if len(decom_df)==0:
            return "Done"

        if limit != None:
            decom_df = decom_df.iloc[:limit,:]
        print("getting mainImageInfo...")
        rowcount = 0
        while rowcount < len(decom_df):
            df_insert = decom_df.copy()
            df_insert = df_insert.iloc[rowcount:rowcount + 200, :]

            df_insert = self.data_deal(df_insert)
            list_df = df_insert.to_numpy().tolist()

            # print(list(conn.query("select * from amz_sp_api.orderReport")))
            sql = f"""
                        insert into amz_sp_api.productInfo
                        values (%s,%s,%s,%s,%s,%s,%s, %s,%s,%s,%s,%s,%s,%s, %s,%s,%s,%s,%s,%s,%s, %s,%s,%s,%s,%s,%s,%s)
                    """
            # print(sql)
            try:
                conn.begin()
                cursor.executemany(sql, list_df)
                conn.commit()
                print("插入中...")
                rowcount += 200
            except Exception as e:
                conn.rollback()
                print(e)
        print("全部完成")
        return decom_df

    def get_mainImage_url(self, sku):
        listingClient = ListingsItems(credentials=self.credentials, marketplace=self.marketplace)
        try:
            r1 = listingClient.get_listings_item(sellerId=self.shopInfo['advertiser_id'], sku=sku)
            img = r1.payload.get("summaries")[0].get("mainImage")
            img_url = None if img is None else img.get("link")
        except Exception as e:
            print(e)
            time.sleep(3)
            r1 = listingClient.get_listings_item(sellerId=self.shopInfo['advertiser_id'], sku=sku)
            img = r1.payload.get("summaries")[0].get("mainImage")
            img_url = None if img is None else img.get("link")
        return img_url

    def GET_FLAT_FILE_ALL_ORDERS_DATA_BY_ORDER_DATE_GENERAL(self):
        timezone_ = pytz.timezone(self.timezone)
        shopReportday = (datetime.now(tz=timezone_) + timedelta(days=-1)).strftime("%Y-%m-%d")
        # print(shopReportday)
        para = {"reportType":ReportType.GET_FLAT_FILE_ALL_ORDERS_DATA_BY_ORDER_DATE_GENERAL,"dataStartTime":shopReportday,"dataEndTime":shopReportday,"reportOptions":{"ShowSalesChannel":"true"}}
        reportid = self.create_report(**para) #{"ShowSalesChannel":"true"}
        decom_df = self.decompression(reportid)
        decom_df[["purchase-date","last-updated-date"]] = decom_df[["purchase-date","last-updated-date"]].applymap(lambda x: self.timeDeal(x) if pd.isna(x)==False or x != None else x)

        decom_df[decom_df.select_dtypes(float).columns] = decom_df[decom_df.select_dtypes(float).columns].fillna(0.0)
        decom_df[decom_df.select_dtypes(int).columns] = decom_df[decom_df.select_dtypes(int).columns].fillna(0)
        decom_df[decom_df.select_dtypes(datetime).columns] = decom_df[decom_df.select_dtypes(datetime).columns].astype('string')
        if "purchase-order-number" in decom_df.columns:
            decom_df['purchase-order-number'] = decom_df['purchase-order-number'].astype("string")
        decom_df.fillna('',inplace=True)
        # decom_df["ReportDate"] = parse(shopReportday)
        decom_df['timezone'] = self.timezone
        list_df = decom_df.to_numpy().tolist()
        print(list_df[0])
        # tuple_data = [tuple(i) for i in list_df]
        conn = self.mysql_connect()
        cursor = conn.cursor()
        # print(list(conn.query("select * from amz_sp_api.orderReport")))
        sql = f"""
            insert into amz_sp_api.orderReport
            values (%s,%s,%s,%s,%s,%s,%s, %s,%s,%s,%s,%s,%s,%s, %s,%s,%s,%s,%s,%s,%s, %s,%s,%s,%s,%s,%s,%s, %s,%s,%s,%s,%s,%s,%s)
        """
        # print(sql)
        try:
            conn.begin()
            cursor.executemany(sql,list_df)
            conn.commit()
            print("插入完成")
        except Exception as e:
            conn.rollback()
            print(e)

    @throttle_retry()
    @load_all_pages()
    def load_all_orders(self,**kwargs):
        """
        a generator function to return all pages, obtained by NextToken
        """
        return Orders(credentials=self.credentials, marketplace=Marketplaces.US).get_orders(**kwargs)

    @throttle_retry()
    @load_all_pages()
    def load_order_items(self,**kwargs):
        return Orders(credentials=self.credentials).get_order_items(**kwargs)


    def insert_(self, sleep_time=0.8):
        insert_list_orderBasic = []
        insert_list_orderDetail = []
        orderId_list = []
        row = 0
        dt_ = datetime.utcnow()
        LastUpdatedAfter = (datetime(dt_.year, dt_.month, dt_.day, dt_.hour, 0, 0, 0) - timedelta(hours=1)).isoformat()
        LastUpdatedBefore = (datetime(dt_.year, dt_.month, dt_.day, dt_.hour, 59, 59, 59) - timedelta(hours=1)).isoformat()
        print(LastUpdatedAfter, '-', LastUpdatedBefore)
        for page in self.load_all_orders(LastUpdatedAfter=LastUpdatedAfter, LastUpdatedBefore=LastUpdatedBefore):  #
            for order in page.payload.get('Orders'):
                # print(order)
                AmazonOrderId = str(order.get('AmazonOrderId'))
                orderId_list.append(AmazonOrderId)
                try:
                    items_ = self.load_order_items(order_id=str(order.get('AmazonOrderId')))
                except:
                    try:
                        time.sleep(3)
                        items_ = self.load_order_items(order_id=str(order.get('AmazonOrderId')))
                    except:
                        time.sleep(5)
                        items_ = self.load_order_items(order_id=str(order.get('AmazonOrderId')))
                # print(next(items_).payload)
                items_detail = next(items_).payload["OrderItems"][0]
                TaxCollection_Model, TaxCollection_ResponsibleParty = (None, None) if items_detail.get(
                    "TaxCollection") == None else (items_detail.get("TaxCollection").get("Model"),
                                                   items_detail.get("TaxCollection").get("ResponsibleParty"))
                ProductInfo_NumberOfItems = None if items_detail.get("ProductInfo") == None else int(
                    items_detail.get("ProductInfo").get("NumberOfItems"))
                BuyerInfo = None if items_detail.get("BuyerInfo") == None else str(items_detail.get("BuyerInfo"))
                CurrencyCode = None if items_detail.get("ItemPrice") == None else items_detail.get("ItemPrice").get(
                    "CurrencyCode")
                ItemTax_Amount = None if items_detail.get("ItemTax") == None else float(
                    items_detail.get("ItemTax").get("Amount"))
                QuantityShipped = None if items_detail.get("QuantityShipped") == None else int(
                    items_detail.get("QuantityShipped"))
                ItemPrice_Amount = None if items_detail.get("ItemPrice") == None else float(
                    items_detail.get("ItemPrice").get("Amount"))
                ASIN = None if items_detail.get("ASIN") == None else items_detail.get("ASIN")
                SellerSKU = None if items_detail.get("SellerSKU") == None else items_detail.get("SellerSKU")
                Title = None if items_detail.get("Title") == None else items_detail.get("Title")
                ShippingTax_Amount = None if items_detail.get("ShippingTax") == None else float(
                    items_detail.get("ShippingTax").get("Amount"))
                IsGift = None if items_detail.get("IsGift") == None else (
                    False if items_detail.get("IsGift") == 'false' else True)
                PriceDesignation = None if items_detail.get("PriceDesignation") == None else items_detail.get(
                    "PriceDesignation")
                ShippingPrice_Amount = None if items_detail.get("ShippingPrice") == None else float(
                    items_detail.get("ShippingPrice").get("Amount"))
                ShippingDiscount_Amount = None if items_detail.get("ShippingDiscount") == None else float(
                    items_detail.get("ShippingDiscount").get("Amount"))
                ShippingDiscountTax_Amount = None if items_detail.get("ShippingDiscountTax") == None else float(
                    items_detail.get("ShippingDiscountTax").get("Amount"))
                IsTransparency = None if items_detail.get("IsTransparency") == None else items_detail.get(
                    "IsTransparency")
                QuantityOrdered = None if items_detail.get("QuantityOrdered") == None else int(
                    items_detail.get("QuantityOrdered"))
                PromotionDiscountTax_Amount = None if items_detail.get("PromotionDiscountTax") == None else float(
                    items_detail.get("PromotionDiscountTax").get("Amount"))
                PromotionDiscount_Amount = None if items_detail.get("PromotionDiscount") == None else float(
                    items_detail.get("PromotionDiscount").get("Amount"))
                OrderItemId = None if items_detail.get("OrderItemId") == None else str(items_detail.get("OrderItemId"))
                temp_inserttime = datetime.now(tz=pytz.timezone(self.timezone)) + timedelta(hours=1)
                Inserthour_time = datetime(temp_inserttime.year, temp_inserttime.month, temp_inserttime.day,temp_inserttime.hour, 0, 0)
                insert_list_orderDetail.append(
                    [AmazonOrderId, TaxCollection_Model, TaxCollection_ResponsibleParty, ProductInfo_NumberOfItems,
                     BuyerInfo,
                     CurrencyCode, ItemTax_Amount, QuantityShipped, ItemPrice_Amount, ASIN, SellerSKU, Title,
                     ShippingTax_Amount, IsGift, PriceDesignation, ShippingPrice_Amount,
                     ShippingDiscount_Amount, ShippingDiscountTax_Amount, IsTransparency, QuantityOrdered,
                     PromotionDiscountTax_Amount, PromotionDiscount_Amount, OrderItemId, Inserthour_time,self.timezone])

                BuyerInfo_BuyerEmail = None if order.get('BuyerInfo') == None else order.get('BuyerInfo').get(
                    'BuyerEmail')
                AmazonOrderId = AmazonOrderId  # order.get('AmazonOrderId') == None else order.get('AmazonOrderId')

                EarliestDeliveryDate = None if order.get('EarliestDeliveryDate') == None else self.timeDeal(order.get('EarliestDeliveryDate'))

                EarliestShipDate = None if order.get('EarliestShipDate') == None else self.timeDeal(order.get('EarliestShipDate'))

                SalesChannel = None if order.get('SalesChannel') == None else order.get('SalesChannel')
                AutomatedShippingSettings_HasAutomatedShippingSettings = None if order.get('AutomatedShippingSettings') == None else order.get('AutomatedShippingSettings').get('HasAutomatedShippingSettings')
                OrderStatus = None if order.get('OrderStatus') == None else order.get('OrderStatus')
                NumberOfItemsShipped = None if order.get('NumberOfItemsShipped') == None else order.get('NumberOfItemsShipped')
                OrderType = None if order.get('OrderType') == None else order.get('OrderType')
                IsPremiumOrder = None if order.get('IsPremiumOrder') == None else order.get('IsPremiumOrder')
                IsPrime = None if order.get('IsPrime') == None else order.get('IsPrime')
                FulfillmentChannel = None if order.get('FulfillmentChannel') == None else order.get('FulfillmentChannel')
                NumberOfItemsUnshipped = None if order.get('NumberOfItemsUnshipped') == None else order.get('NumberOfItemsUnshipped')
                HasRegulatedItems = None if order.get('HasRegulatedItems') == None else order.get('HasRegulatedItems')
                IsReplacementOrder = None if order.get('IsReplacementOrder') == None else eval(order.get('IsReplacementOrder').capitalize())
                IsSoldByAB = None if order.get('IsSoldByAB') == None else order.get('IsSoldByAB')
                LatestShipDate = None if order.get('LatestShipDate') == None else self.timeDeal(order.get('LatestShipDate'))
                ShipServiceLevel = None if order.get('ShipServiceLevel') == None else order.get('ShipServiceLevel')
                DefaultShipFromLocationAddress_StateOrRegion = None if order.get('DefaultShipFromLocationAddress') == None else order.get('DefaultShipFromLocationAddress').get('StateOrRegion')
                DefaultShipFromLocationAddress_AddressLine1 = None if order.get('DefaultShipFromLocationAddress') == None else order.get('DefaultShipFromLocationAddress').get('AddressLine1')
                DefaultShipFromLocationAddress_Phone = None if order.get('DefaultShipFromLocationAddress') == None else order.get('DefaultShipFromLocationAddress').get('Phone')
                DefaultShipFromLocationAddress_PostalCode = None if order.get('DefaultShipFromLocationAddress') == None else order.get('DefaultShipFromLocationAddress').get('PostalCode')
                DefaultShipFromLocationAddress_City = None if order.get('DefaultShipFromLocationAddress') == None else order.get('DefaultShipFromLocationAddress').get('City')
                DefaultShipFromLocationAddress_CountryCode = None if order.get('DefaultShipFromLocationAddress') == None else order.get('DefaultShipFromLocationAddress').get('CountryCode')
                DefaultShipFromLocationAddress_Name = None if order.get('DefaultShipFromLocationAddress') == None else order.get('DefaultShipFromLocationAddress').get('Name')
                IsISPU = None if order.get('IsISPU') == None else order.get('IsISPU')
                MarketplaceId = None if order.get('MarketplaceId') == None else order.get('MarketplaceId')
                LatestDeliveryDate = None if order.get('LatestDeliveryDate') == None else self.timeDeal(order.get('LatestDeliveryDate'))
                PurchaseDate = None if order.get('PurchaseDate') == None else self.timeDeal(order.get('PurchaseDate'))
                ShippingAddress_StateOrRegion = None if order.get('ShippingAddress') == None else order.get('ShippingAddress').get('StateOrRegion')
                ShippingAddress_PostalCode = None if order.get('ShippingAddress') == None else order.get('ShippingAddress').get('PostalCode')
                ShippingAddress_City = None if order.get('ShippingAddress') == None else order.get('ShippingAddress').get('City')
                ShippingAddress_CountryCode = None if order.get('ShippingAddress') == None else order.get('ShippingAddress').get('CountryCode')
                IsAccessPointOrder = None if order.get('IsAccessPointOrder') == None else order.get('IsAccessPointOrder')
                PaymentMethod = None if order.get('PaymentMethod') == None else order.get('PaymentMethod')
                IsBusinessOrder = None if order.get('IsBusinessOrder') == None else order.get('IsBusinessOrder')
                OrderTotal_CurrencyCode = None if order.get('OrderTotal') == None else order.get('OrderTotal').get('CurrencyCode')
                OrderTotal_Amount = None if order.get('OrderTotal') == None else float(order.get('OrderTotal').get('Amount'))
                PaymentMethodDetails = None if order.get('PaymentMethodDetails') == None else order.get('PaymentMethodDetails')[0]
                IsGlobalExpressEnabled = None if order.get('IsGlobalExpressEnabled') == None else order.get('IsGlobalExpressEnabled')
                LastUpdateDate = None if order.get('LastUpdateDate') == None else self.timeDeal(order.get('LastUpdateDate'))
                ShipmentServiceLevelCategory = None if order.get('ShipmentServiceLevelCategory') == None else order.get('ShipmentServiceLevelCategory')
                SellerOrderId = None if order.get('SellerOrderId') == None else order.get('SellerOrderId')
                temp_inserttime =  datetime.now(tz=pytz.timezone(self.timezone))+timedelta(hours=1)
                Inserthour_time = datetime(temp_inserttime.year,temp_inserttime.month,temp_inserttime.day,temp_inserttime.hour,0,0)

                insert_list_orderBasic.append(
                    [BuyerInfo_BuyerEmail, AmazonOrderId, EarliestDeliveryDate, EarliestShipDate, SalesChannel,
                     AutomatedShippingSettings_HasAutomatedShippingSettings, OrderStatus,
                     NumberOfItemsShipped, OrderType, IsPremiumOrder, IsPrime, FulfillmentChannel,
                     NumberOfItemsUnshipped, HasRegulatedItems, IsReplacementOrder, IsSoldByAB, LatestShipDate,
                     ShipServiceLevel, DefaultShipFromLocationAddress_StateOrRegion,
                     DefaultShipFromLocationAddress_AddressLine1, DefaultShipFromLocationAddress_Phone,
                     DefaultShipFromLocationAddress_PostalCode,
                     DefaultShipFromLocationAddress_City, DefaultShipFromLocationAddress_CountryCode,
                     DefaultShipFromLocationAddress_Name, IsISPU, MarketplaceId, LatestDeliveryDate, PurchaseDate,
                     ShippingAddress_StateOrRegion, ShippingAddress_PostalCode, ShippingAddress_City,
                     ShippingAddress_CountryCode, IsAccessPointOrder, PaymentMethod, IsBusinessOrder,
                     OrderTotal_CurrencyCode,
                     OrderTotal_Amount, PaymentMethodDetails, IsGlobalExpressEnabled, LastUpdateDate,
                     ShipmentServiceLevelCategory, SellerOrderId, Inserthour_time,self.timezone])

                time.sleep(sleep_time)
                row += 1
                if row % 10 == 0:
                    print(f"receiving...received {row} rows")
        print(insert_list_orderBasic)
        print(insert_list_orderDetail)
if __name__ == '__main__':
    aws_credentials = {
        'refresh_token': 'Atzr|IwEBIMxC7d17ZYBTcNe-zfnbk-TEC-40uIlSRmGAH_sfNozcA7RdSt4iBdUorC2GC_uoUmIY4oGhHGT621el1my0YaABEvuGn4eIe1EFxrHYLM1fljnQxHQjgGKwTb48cMdHNxOiMd8_CbvLYL_NT3E_zTPKCSQjqa8zxTkddBjL-5stlTRzhVHmB2Ox5-6XgvpSnoBtlZqkI96rYmKi63f0NTQ2e9IV3cNqXli8X9_DYGgZlpl60qE56A-ZNy4otv4myR5kqY2bTll0c-ynLtqI5ukDwPwO369b_Ie4kniRd7Or-Ip6jPyTIdfghLV4AVrxbvE',
        'lwa_app_id': 'amzn1.application-oa2-client.1f9d3d4747e14b22b4b598e54e6b922e',  # 卖家中心里面开发者资料LWA凭证
        'lwa_client_secret': 'amzn1.oa2-cs.v1.3af0f5649f5b8e151cd5bd25c10f2bf3113172485cd6ffc52ccc6a5e8512b490',
        'aws_access_key': 'AKIARBAGHTGOZC7544GN',
        'aws_secret_key': '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',
        'role_arn': 'arn:aws:iam::070880041373:role/Amazon_SP_API_ROLE'
    }
    sp_ = SpApiRequest(aws_credentials,Marketplaces.US,'3006125408623189')
    # sp_.GET_MERCHANT_LISTINGS_ALL_DATA()
    sp_.GET_MERCHANT_LISTINGS_ALL_DATA()
    # sp_.mysql_connect().commit()
    # sp_.decompression("1532408019678")
    # sp_.GET_MERCHANT_LISTINGS_ALL_DATA()
    # sp_.timeToLocalTime()
    # print(type(sp_))