时间戳

首页 > 解决方案 > 将谷歌表格 API 调用更改为批处理

python - 将谷歌表格 API 调用更改为批处理

问题描述

第一次使用 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)

谢谢！

标签： pythonpandasgoogle-sheetsgoogle-api

解决方案

感谢 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()

推荐阅读