FR: Adding additional functions in the API library

[freedomofnet]

Is your feature request related to a problem? Please describe.

The current Google.cloud.spanner library has very limited functionalities compared to other db libraries (e.g., bigquery) and some of the key APIs are missing. For example,

get_table
table
list_tables
schema
query
execute

I would like to have the above functions added to the library.

Describe the solution you'd like

These functions could be implementable via the INFORMATION_SCHEMA query syntax.

[larkee]

Hi there,

Thank you for filing this issue. I've been working on preparing the 2.0.0 release of the library which has blocked me from doing any work on this request. However, that work is nearly complete which means I'll be free for feature work again. As such, I wanted to get some more details on the APIs you mentioned.

Would you be able to explain what the expectation is for each of these APIs and give a code example of the usage? From looking over the BigQuery documentation, it seems the way the APIs are structured is quite different and so I'm not sure having a Table object would make as much sense in Spanner.

As a note, I believe the functionality of query and execute are implemented however the way to call them may differ. For example, this shows how to run a SQL query against a Spanner database:

from google.cloud import spanner

client = spanner.Client()
instance = client.instance('instance-id')
database = instance.database('database-id')

with database.snapshot() as snapshot:
 results = snapshot.execute_sql("SQL QUERY")

 for row in results:
 ...

Please let me know what the intended usage is so I can better consider what the best option is. 👍

[tswast]

Regarding Table, this would map to INFORMATION_SCHEMA queries for fetching table metadata (schema/columns being the most important piece). Perhaps there could be a get_table and list_tables method which just executes relevant information schema queries?

[tswast]

The intended use case is for an Ibis connector, which provides runtime helpers for generating and validating analytical queries.

P.S. I agree that query / execute are a bit nonsensical asks. The existing execute_sql command is sufficient. (I think this was included in the Ibis project scope due to the need to convert the streamed result set into a pandas dataframe: googleapis/google-cloud-python#15927)

[larkee]

Thanks for your patience!

As part of the Django connector work, a dbapi adapter was implemented and now lives in this repo under google/cloud/spanner_dbapi. This adapter adds some of the functionalities you mentioned.

Unfortunately, I don't have any cycles to work on any missing methods you may need. However, I am happy to review any PRs you may open in regards to this issue.