Spanner should have helpful aliases for param types

[theacodes]

Having to do:

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

instance = spanner_client.instance(instance_id)
database = instance.database(database_id)

params = {
 'start': start,
 'end': end
}
param_types = {
 'start': type_pb2.Type(code=type_pb2.STRING),
 'end': type_pb2.Type(code=type_pb2.STRING)
}
results = database.execute_sql(
 "SELECT AlbumId, AlbumTitle, MarketingBudget "
 "FROM Albums@{FORCE_INDEX=AlbumsByAlbumTitle} "
 "WHERE AlbumTitle >= @start AND AlbumTitle < @end",
 params=params, param_types=param_types)

Is rather awkward, it would be much more friendly to have:

from google.cloud import spanner

instance = spanner_client.instance(instance_id)
database = instance.database(database_id)

params = {
 'start': start,
 'end': end
}
param_types = {
 'start': spanner.ParamTypes.STRING,
 'end': spanner.ParamTypes.STRING
}
results = database.execute_sql(
 "SELECT AlbumId, AlbumTitle, MarketingBudget "
 "FROM Albums@{FORCE_INDEX=AlbumsByAlbumTitle} "
 "WHERE AlbumTitle >= @start AND AlbumTitle < @end",
 params=params, param_types=param_types)

or similar

[tseaver]

@jonparrott Aliasing the "scalar" types is easy (PR #3670). What do you envision for array / struct?

[theacodes]

@tseaver would list and dict work, e.g.:

param_types = {
 tags: list,
 metadata: dict
}

[theacodes]

Oh nevermind, ignore my last comment, I'm being thick.

What does it look like today to if you need to specify an array / struct?

[tseaver]

@jonparrott For arrays, you have to pass the type of the elements:

array_type = type_pb2.Type(
 code=type_pb2.ARRAY, array_element_type=type_pb2.Type(code=type_pb2.INT64))

For structs, you have to construct / pass a StructType describing the members:

struct_type_pb = type_pb2.StructType(fields=[
 type_pb2.StructType.Field(name='name', type=type_pb2.Type(code=type_pb2.STRING)),
 type_pb2.StructType.Field(name='age', type=type_pb2.Type(code=type_pb2.INT64)),
])
my_struct_type = type_pb2.Type(code=type_pb2.STRUCT, struct_type=struct_type_pb)

[theacodes]

@tseaver that's pretty rough. Is it possible to make it into something like:

from google.cloud.spanner import types

array_type = types.Array(types.INT64)

struct_type = types.Struct(
 types.StructField('name', types.STRING),
 types.StructField('age', types.INT64)),
)

[tseaver]

@jonparrott Assuming you meant to have the open square bracket after types.Struct (the order matters) that should be straightforward.

[theacodes]

Actually I didn't, I meant for types.Struct to just use *args. But yeah, let's do it. :)

[tseaver]

@jonparrott Should we name those factories ArrayParamType / StructParamType (to be equivalent to the FOO_PARAM_TYPE aliases?

[theacodes]

Sure, if that's all they're used for.

[tseaver]

Conceptually they describe types of returned cell values, too, but we don't expose that.