将google sheets API调用更改为批处理

Question

第一次使用google(和所有) api调用时，尽管限制了速率，但我仍然不断地达到速率限制阈值。如何将以下代码更改为批处理格式以避免这种情况？

#API Call Function
from ratelimit import limits, sleep_and_retry
import requests
from googleapiclient.discovery import build
@sleep_and_retry
@limits(calls=1, period=4.5)
def pull_sheet_data(SCOPE,SPREADSHEET_ID,DATA_TO_PULL):
    creds = gsheet_api_check(SCOPE)
    service = build('sheets', 'v4', credentials=creds)
    sheet = service.spreadsheets()
    result = sheet.values().get(
        spreadsheetId=SPREADSHEET_ID,
        range=DATA_TO_PULL).execute()
    values = result.get('values', [])
    
    if not values:
        print('No data found.')
    else:
        rows = sheet.values().get(spreadsheetId=SPREADSHEET_ID,
                                  range=DATA_TO_PULL).execute()
        data = rows.get('values')
        print("COMPLETE: Data copied")
        return data

#list files in the active brews folder
activebrews = drive.ListFile({'q':"'0BxU70FB_wb-Da0x5amtYbUkybXc' in parents"}).GetList()
tabs = ['Brew Log','Fermentation Log','Centrifuge Log']

brewsheetdict ={}
#Pulls data from the entire spreadsheet tab.

    
        
for i in activebrews:
    for j in tabs:
        #set spreadsheet parameters
        SCOPE = ['https://www.googleapis.com/auth/drive.readonly']
        SPREADSHEET_ID = i['id']
        data = pull_sheet_data(SCOPE,SPREADSHEET_ID,j)
        dftitle = str(i['title'])
        dftab = str(j)
        dfname = dftitle+'_'+dftab
        brewsheetdict[dfname] = pd.DataFrame(data)

谢谢!

Answer 1

多亏了Tanaike的建议，我决定采用以下解决方案。还有一些我还没有解决的问题。也就是说，最终的API调用以每秒0.5次的速度发生，远远低于发布的限制，但任何更快的速度仍然会导致速率限制问题。此外，每次在列表的一半上执行for循环后完成的代码，需要重复执行。为了解决第二个问题，我包含了指定的行，用于在迭代列表项之后删除它们，因此每次重新运行代码时都会从最后一条不完整的记录开始。

import time    
        
SCOPE = ['https://www.googleapis.com/auth/drive.readonly']
recordcount=0
#if (current - start)/(recordcount) >= 1:
sleeptime=2.2
start=time.time()
for i in activebrews:
    SPREADSHEET_ID = i['id']
    for j in tabs:
        data = pull_sheet_data(SCOPE,SPREADSHEET_ID,j)
        dftitle = str(i['title'])
        dftab = str(j)
        dfname = dftitle+'_'+dftab
        brewsheetdict[dfname] = pd.DataFrame(data)
        recordcount+=1
        time.sleep(sleeptime)
        end=time.time()
        print(recordcount,dfname, (end-start)/recordcount)
    activebrews.remove(i)#remove list item after iterated over
    time.sleep(1)
 
brewsheetdata = open("brewsheetdata.pkl","wb")
pickle.dump(brewsheetdict,brewsheetdata)
brewsheetdata.close()