Google spanner structs as parameter

Question

When using the spanner python client (but perhaps the problem is more general) it seems impossible to pass structs to the query as a parameter.

Consider following setup:

from google.cloud.proto.spanner.v1 import type_pb2
from google.cloud import spanner

spanner_client = spanner.Client()
instance = spanner_client.instance('myinstance')
database = instance.database('mydb')

If I want to translate following query into using paramaters

select * from UNNEST(ARRAY[STRUCT<foo INT64, bar INT64>(1, 2)])

By doing

STRUCT_TYPE = type_pb2.StructType()
FOO = type_pb2.StructType.Field(name='foo', type=type_pb2.Type(code=type_pb2.INT64))
BAR = type_pb2.StructType.Field(name='bar', type=type_pb2.Type(code=type_pb2.INT64))

STRUCT_TYPE.fields.extend([FOO, BAR])

database.execute_sql('select * from UNNEST(ARRAY[@struct])', 
    params={'struct': [1,2]}, 
    param_types={'struct': STRUCT_TYPE})

Would give me

TypeError: Parameter to MergeFrom() must be instance of same class: expected Type got StructType.

Is there any way to allow passing of structs?

Answer 1

You can't pass structs right now. This is something we're addressing but no firm timeline.