python - Python：通过具有多个页面的 API 迭代到 Excel 电子表格

问题描述

也许我使用了错误的 excel 编写器，但我试图从服务器中提取数据，然后将其组织到 excel 中。我最多可以提取 500 条记录，服务器有 5000 条记录，所以通常我一次只提取 500 条。我开始研究编辑我的代码，使其在所有 10 个页面中自动完成该过程，并将其全部发送到 Excel。

下面的代码是我最接近完成此任务的代码。它遍历所有页面，我进行打印以查看它是否执行此操作，但是在返回函数上，然后将其发送到电子表格，我只看到 500 条记录的最后一页。我想用 xlxswriter 做什么？有没有更好的选择，或者有没有办法修复我的程序来完成这个任务？

任何帮助将不胜感激。下面的代码不是完整的代码，只是处理excel问题的一部分。

import requests
from datetime import datetime, timedelta
import json
import time
import subprocess
import sys
import os
import xlsxwriter


def label_search(query):
    company = input('\nEnter ID: ')
    size = 500
    offset = -501
    position = '&offset='
    api_call_response = requests.get(base_url +
                                     query + company + size
                                     + position + str(offset),
                                     headers=api_call_headers,
                                     verify=False)
    for value in api_call_response:
        while offset <= 5000:
            api_call_response
            offset += 500
            print(base_url +query + company + size + position + str(offset))
        return(api_call_response).json()


def lookup():
    try:
        label = input("\nPlease provide label name: ")
        label_info = label_search(label)
        if len(label) == 0:
            print("\nPlease provide a proper label name: ")
        else:
            search = label_info
            display_wb_all(search)
    except requests.exceptions.ConnectionError:
        print_pause("\nCouldn't connect to server! Check Network")
        menu()


def display_wb_all(search):
    row = 0
    col = 0
    wbname = input('Name your workbook: ')
    avwb = xlsxwriter.Workbook(wbname + '.xlsx')
    wsname = avwb.add_worksheet('WorkSheet1')
    cell_format = avwb.add_format()
    cell_format.set_bg_color('silver')
    cell_format.set_align('center')
    cell_format.set_align('vcenter')
    body_format = avwb.add_format()
    body_format.set_align('center')
    body_format.set_align('vcenter')
    body_format.set_bg_color('white')
    wsname.write('A1', 'Field name', cell_format)
    wsname.write('B1', 'Field name', cell_format)
    wsname.write('C1', 'Field name', cell_format)
    wsname.write('D1', 'Field name', cell_format)
    wsname.write('E1', 'Field name', cell_format)
    wsname.write('F1', 'Field name', cell_format)
    wsname.write('G1', 'Field name', cell_format)
    wsname.write('H1', 'Field name', cell_format)
    wsname.write('I1', 'Field name', cell_format)
    wsname.set_column('A:A', 35)
    wsname.set_column('B:B', 24)
    wsname.set_column('C:C', 10)
    wsname.set_column('D:D', 35)
    wsname.set_column('E:E', 19)
    wsname.set_column('F:F', 8)
    wsname.set_column('G:G', 19)
    wsname.set_column('H:H', 18)
    wsname.set_column('I:I', 11)
    for entry in search['items']:
        siteName = entry['id']
        s = entry['time']
        a = int(s or 0)/1000.0
        r = (datetime.datetime.fromtimestamp(a).strftime
             ('%Y-%m-%d %H:%M:%S'))
        try:
            state = entry['device'][0]['state']
        except TypeError:
            state = 'No Data Found'
        try:
            sim = entry['device'][0]['identifier']
        except TypeError:
            sim = 'No Data Found'
        try:
            platform = entry['device'][0]['mobile']
        except TypeError:
            platform = 'No Data Found'
        try:
            tech = entry['device'][0]['technology']
        except TypeError:
            tech = 'No Data Found'
        bytestotal = entry['usage']['bytesTotal']
        timestamp = entry['usage']['timestamp']
        ts = int(timestamp or 0)/1000.0
        x = (datetime.datetime.fromtimestamp(ts).strftime
             ('%Y-%m-%d %H:%M:%S'))
        limit = entry['dataUsage']['limit']
        row += 1
        wsname.write(row, col, siteName, body_format)
        wsname.write(row, col + 1, r, body_format)
        wsname.write(row, col + 2, state, body_format)
        wsname.write(row, col + 3, sim, body_format)
        wsname.write(row, col + 4, platform, body_format)
        wsname.write(row, col + 5, tech, body_format)
        wsname.write(row, col + 6, bytestotal, body_format)
        wsname.write(row, col + 7, x, body_format)
        wsname.write(row, col + 8, limit, body_format)
    avwb.close()
    print(os.getcwd())

标签： pythonexcelapiiterationspreadsheet

python - Python：通过具有多个页面的 API 迭代到 Excel 电子表格

问题描述

解决方案

推荐阅读