如何查询扳手并获取元数据，尤其是列的名称? [英] how to query spanner and get metadata, especially columns' names?

问题描述

我正在尝试在Spanner上查询自定义SQL并将结果转换为Pandas Dataframe，因此我需要数据和列名，但是找不到找到列名的方法.

I'm trying to query custom SQL on Spanner and convert the results into a Pandas Dataframe, so I need data and column names, but I can't find a way to get the column names.

根据文档，我可以使用元数据获取列或字段属性，但这不起作用.

According to the documentation, I can get columns using metadata or fields properties, but this doesn't work.

我尝试运行查询事务并获取快照，但是我只获取了数据行.

I tried to run a query transaction and also to get a snapshot, but I just get a data row.

from google.cloud import spanner
from google.cloud.spanner_v1.streamed import StreamedResultSet

def query_transaction(instance_id, database_id, query_param):

    spanner_client = spanner.Client.from_service_account_json("PATH_XXXXX")
    database = spanner_client.instance(instance_id).database(database_id)

    def run_transaction(transaction):
        query = query_param
        results: StreamedResultSet = transaction.execute_sql(query)
        print("type", type(results))
        print("metadata", results.stats)
        for row in results:
            print(row)

    database.run_in_transaction(run_transaction)

def query_snapshot(instance_id, database_id, query):

    spanner_client = spanner.Client.from_service_account_json("PATH_XXXXX")
    database = spanner_client.instance(instance_id).database(database_id)

    with database.snapshot() as snapshot:
        results: StreamedResultSet = snapshot.execute_sql(query)

        print("metadata", results.metadata)
        print("type", type(results))

        for row in results:
            print(row)


spanner_id = "XXXXXXX"
base_id = "XXXXXXXX"
query ="SELECT * FROM XXXXX LIMIT 5"

spanner.query_snapshot(spanner_id, base_id, query)
spanner.query_transaction(spanner_id, base_id, query)

我可以迭代结果并获取行，但是元数据始终为None.

I can iterate the results and get rows, but metadata always is None.

推荐答案

在元数据可用之前，您必须至少获取一行.因此，如果您要更改代码的顺序，以便首先获取数据(或至少一些数据)，然后获取元数据，那么它应该可以工作.

You must fetch at least one row before the metadata are available. So if you were to change the order of your code so that you first fetch the data (or at least some data), and then get the metadata, it should work.

results: StreamedResultSet = snapshot.execute_sql(query)
print("metadata", results.metadata)
for row in results:
    print(row)

对此:

results: StreamedResultSet = snapshot.execute_sql(query)
for row in results:
    print(row)
print("metadata", results.metadata)

那您应该获取元数据.

还要注意，结果集统计信息( results.stats )仅在对查询进行概要分析时可用.如上例所示，当您仅执行查询时，该字段将始终为空.

Also note that result set statistics (results.stats) is only available when you are profiling a query. When you are just executing the query, as you are in your above example, this will always be empty.

这篇关于如何查询扳手并获取元数据，尤其是列的名称?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！