从嵌套的Json对象将某些数据值写入CSV

Question

我试图从一个通过API调用的嵌套JSON对象中获取一些客户数据到一个平面文件中。你知道吗

我不是一个开发人员，我做这个作为一个宠物项目，因为我喜欢学习，所以请对我和ELI5容易。你知道吗

我已经能够从for语句中获得所需的输出，并且可以将JSON写入CSV，但是无法将所需的输出写入CSV。你知道吗

下面的代码使用如下所示的打印功能在终端中输出所需的格式：

订阅ID |电子邮件|客户ID |付款方式|客户名称

import stripe
import json

stripe.api_key = "SomeKey"

cust_list = json.loads(str(stripe.Customer.list(limit=100)))

for item in cust_list['data']:

    try:
         print(item['subscriptions']['data'][0]['id'], "|", item['email'], "|", item['id'], "|",  item['sources']['data'][0]['object'], "|",  item['metadata']['Subaccount'])

    except:

        print(item['subscriptions']['data'][0]['id'], "|",  item['email'], "|",  item['id'],)

这是JSON数据的结构，如果是TMI：

'"{
  ""data"": [
    {
      ""account_balance"": 0,
      ""address"": null,
      ""balance"": 0,
      ""created"": IIIIII,
      ""currency"": ""usd"",
      ""default_source"": ""IIIIIIIIIII"",
      ""delinquent"": false,
      ""description"": ""IIIIIII"",
      ""discount"": null,
      ""email"": ""IIIIIIII"",
      ""id"": ""IIIIIIIII"",
      ""invoice_prefix"": ""IIIIIIIIIIIIII"",
      ""invoice_settings"": {
        ""custom_fields"": null,
        ""default_payment_method"": null,
        ""footer"": null
      },
      ""livemode"": IIIIIIIII,
      ""metadata"": {},
      ""name"": ""Mutualink"",
      ""object"": ""customer"",
      ""phone"": null,
      ""preferred_locales"": [],
      ""shipping"": null,
      ""sources"": {
        ""data"": [
          {
            ""address_city"": null,
            ""address_country"": null,
            ""address_line1"": null,
            ""address_line1_check"": null,
            ""address_line2"": null,
            ""address_state"": null,
            ""address_zip"": null,
            ""address_zip_check"": null,
            ""brand"": ""IIIIIIIIIIII"",
            ""country"": ""IIIIIIII"",
            ""customer"": ""IIIIIIIIII"",
            ""cvc_check"": ""pass"",
            ""dynamic_last4"": null,
            ""exp_month"": IIIIIIII,
            ""exp_year"": IIIII,
            ""fingerprint"": ""IIIIIIIII"",
            ""funding"": ""IIIIIIIII"",
            ""id"": ""IIIIIIIIII"",
            ""last4"": ""IIIIIIIII"",
            ""metadata"": {},
            ""name"": null,
            ""object"": ""IIIIIIIII"",
            ""tokenization_method"": null
          }
        ],
        ""has_more"": IIIIIIIII,
        ""object"": ""IIIIIIIII"",
        ""total_count"": 1,
        ""url"": ""IIIIIIIIIIIIIII""
      },
      ""subscriptions"": {
        ""data"": [
          {
            ""application_fee_percent"": null,
            ""billing"": ""IIIIIIIIIIIII"",
            ""billing_cycle_anchor"": IIIIIIIIII,
            ""billing_thresholds"": null,
            ""cancel_at"": null,
            ""cancel_at_period_end"": IIIIIIIII,
            ""canceled_at"": null,
            ""collection_method"": ""IIIIIIIIII"",
            ""created"": IIIIIIIII,
            ""current_period_end"": IIIIIIIII,
            ""current_period_start"": IIIIIIIII,
            ""customer"": ""IIIIIIIII"",
            ""days_until_due"": null,
            ""default_payment_method"": null,
            ""default_source"": null,
            ""default_tax_rates"": [],
            ""discount"": null,
            ""ended_at"": null,
            ""id"": ""IIIIIIIII"",
            ""items"": {
              ""data"": [
                {
                  ""billing_thresholds"": IIIIIIIII,
                  ""created"": IIIIIIIII,
                  ""id"": ""IIIIIIIII"",
                  ""metadata"": {},
                  ""object"": ""IIIIIIIII"",
                  ""plan"": {
                    ""active"": true,
                    ""aggregate_usage"": ""IIIIIIIII"",
                    ""amount"": 1,
                    ""billing_scheme"": ""IIIIIIIII"",
                    ""created"": IIIIIIIII,
                    ""currency"": ""IIIIIIIII"",
                    ""id"": ""IIIIIIIII"",
                    ""interval"": ""IIIIIIIII"",
                    ""interval_count"": 1,
                    ""livemode"": true,
                    ""metadata"": {},
                    ""nickname"": ""IIIIIIIII"",
                    ""object"": ""IIIIIIIII"",
                    ""product"": ""IIIIIIIII"",
                    ""tiers"": null,
                    ""tiers_mode"": null,
                    ""transform_usage"": null,
                    ""trial_period_days"": null,
                    ""usage_type"": ""IIIIIIIII""
                  },
                  ""subscription"": ""IIIIIIIII"",
                  ""tax_rates"": []
                }
              ],
              ""has_more"": false,
              ""object"": ""list"",
              ""total_count"": 1,
              ""url"": ""IIIIIIIII""
            },
            ""latest_invoice"": ""IIIIIIIII"",
            ""livemode"": true,
            ""metadata"": {},
            ""object"": ""IIIIIIIII"",
            ""pending_setup_intent"": null,
            ""plan"": {
              ""active"": true,
              ""aggregate_usage"": ""IIIIIIIII"",
              ""amount"": 1,
              ""billing_scheme"": ""IIIIIIIII"",
              ""created"": IIIIIIIII,
              ""currency"": ""IIIIIIIII"",
              ""id"": ""IIIIIIIII"",
              ""interval"": ""month"",
              ""interval_count"": 1,
              ""livemode"": true,
              ""metadata"": {},
              ""nickname"": ""IIIIIIIII"",
              ""object"": ""IIIIIIIII"",
              ""product"": ""IIIIIIIII"",
              ""tiers"": null,
              ""tiers_mode"": null,
              ""transform_usage"": null,
              ""trial_period_days"": null,
              ""usage_type"": ""IIIIIIIII""
            },
            ""quantity"": 1,
            ""schedule"": null,
            ""start"": IIIIIIIII,
            ""start_date"": IIIIIIIII,
            ""status"": ""active"",
            ""tax_percent"": null,
            ""trial_end"": null,
            ""trial_start"": null
          }
        ],
        ""has_more"": false,
        ""object"": ""list"",
        ""total_count"": 1,
        ""url"": ""IIIIIIIII""
      },
      ""tax_exempt"": ""none"",
      ""tax_ids"": {},
      ""tax_info"": null,
      ""tax_info_verification"": null
    }
  ],
  ""has_more"": true,
  ""object"": ""list"",
  ""url"": ""IIIIIIIII""
}"
}

这里的代码自己输出，因为我需要它在终端，但我找不到一种方法来采取这一点，并把它放入一个文件。现在，我只是将终端的输出复制到csv表中。你知道吗

我正在寻找关于该怎么做的指导。i、 先做A，做x，y，z，然后做B，做e，f，g

再次抱歉，如果你问这个不合适的话。我一直在寻找一个解决办法，但我没有成功，所以我想我会分享我的具体情况。你知道吗

Answer 1

您需要打开一个文件，并在遍历时对其进行写入。像这样：

import stripe
import json

stripe.api_key = "SomeKey"

cust_list = json.loads(str(stripe.Customer.list(limit=100)))
with open("csv.csv", "w") as f:
    for item in cust_list["data"]:
        try:
            f.write(
                item["subscriptions"]["data"][0]["id"]
                + " | "
                + item["email"]
                + " | "
                + item["id"]
                + "  | "
                + item["sources"]["data"][0]["object"]
                + " | "
                + item["metadata"]["Subaccount"]
            )

        except:

            f.write(
                item["subscriptions"]["data"][0]["id"]
                + " | "
                + item["email"]
                + " | "
                + item["id"]
            )

请注意，我将“|”替换为“|”进行格式化，并将元组替换为字符串。您可以通过使用fstrings或.format而不是+使它变得更好。你知道吗

Answer 2

您可以将所需的格式化字符串指定给变量，并将该变量的内容写入文件，而无需打印数据。你知道吗

也许你要找的就是这样的东西：

import stripe
import json

stripe.api_key = "SomeKey"

cust_list = json.loads(str(stripe.Customer.list(limit=100)))

output = ""
for item in cust_list['data']:

    # Save the formatted string to output
    try:
        output = str(item['subscriptions']['data'][0]['id']) + " | " + str(item['email']) + " | " + str(item['id']) + " | " + str(item['sources']['data'][0]['object']) + " | " + str(item['metadata']['Subaccount'])
    except KeyError:
        output = str(item['subscriptions']['data'][0]['id']) + " | " + str(item['email']) + " | " + str(item['id'])
    except: 
        # Handle exception as you see fit

# Write output to the file
with open("out_file.csv", "w") as f:
    f.write(output)

我想指出的是，如果要写入CSV（逗号分隔值）文件，最好使用,分隔符，而不是|，在这种情况下，以下内容就足够了：

# Save the formatted string to output
try:
    output = str(item['subscriptions']['data'][0]['id']) + "," + str(item['email']) + "," + str(item['id']) + "," + str(item['sources']['data'][0]['object']) + "," + str(item['metadata']['Subaccount'])
except KeyError:
    output = str(item['subscriptions']['data'][0]['id']) + "," + str(item['email']) + "," + str(item['id'])
except: 
    # Handle exception as you see fit

Answer 3

您可以将每个项存储在一个列表中，并使用二维数组来表示每个值。列表中的每个条目都将作为行。你知道吗

举个例子，如果你想要一个包含两列的csv，比如“Name”，“Age”，它看起来应该是这样的。。你知道吗

[['姓名'，'年龄']，['阿农'，22]，['简'，59]]

然后使用名为csv的导入将其写入文件。你知道吗