python - 使用 google-cloud-python 从 BigQuery 以 JSON 格式获取结果

Question

我正在通过 google-cloud-python 查询 BigQuery如下:

client = bigquery.Client()

query = """SELECT * FROM `{dataset}.{table}`
  WHERE id=@id LIMIT 1""".format(dataset=dataset,
                                 table=table)

param = ScalarQueryParameter('id', 'STRING', id)
query = client.run_sync_query(query, query_parameters=[param])
query.use_legacy_sql = False
query.timeout_ms = 1000
query.run()

assert query.complete

try:
    results = query.rows[0]
except IndexError:
    results = None

这将返回如下数据:

[
    "Tue, 11 Apr 2017 03:18:52 GMT",
    "A132",
    "United Kingdom",
    [
        {
            "endDate": "2012-12-05",
            "startDate": "2011-12-27",
            "statusCode": "Terminated"
        }
    ]
]

重复字段已转换为 JSON。但我也希望将其余数据转换为 JSON。我可以通过检查 query.schema 自己实现这一点。但似乎这应该在库中，因为它已经发生在重复元素上。

如何使用此库获取格式为 JSON 的 BigQuery 查询结果？例如。:

{
    "timestamp": "Tue, 11 Apr 2017 03:18:52 GMT",
    "id": "A132",
    "country": "United Kingdom",
    [
        {
            "endDate": "2012-12-05",
            "startDate": "2011-12-27",
            "statusCode": "Terminated"
        }
    ]
}

Answer 1

事实证明，代码很简单:

field_names = [f.name for f in query.schema]

try:
    raw_results = query.rows[0]
    zipped_results = zip(field_names, raw_results)
    results = {x[0]: x[1] for x in zipped_results}
except IndexError:
    results = None