How to catch spanner transaction errors

Question

I am currently working on spanner emulator for my local development and eventually will use actual instances. I am trying to run a bad SQL query expecting to catch and throw an error message for the users.

When I run the below query using cloud, I see HTTPError

gcloud spanner databases execute-sql --project=local-gcp-project testdb --instance=Emulator --sql='SELECT SingerId, AlbumId, AlbumTitle FROM Albums'

(gcloud.spanner.databases.execute-sql) HTTPError 400: {"error":"Table not found: Albums [at 1:43]\nSELECT SingerId, AlbumId, AlbumTitle FROM Albums\n^","code":3,"message":"Table not found: Albums [at 1:43]\nSELECT SingerId, AlbumId, AlbumTitle FROM Albums\n^"}

When I use the python client library to run the same query I do not see any errors and I see inside try getting printed always.

spanner_client = spanner.Client()
instance = spanner_client.instance("Emulator")
database = instance.database("testdb")

def execute_transaction(transaction):
   query = "SELECT SingerId, AlbumId, AlbumTitle FROM Albums"
   transaction.execute_sql(sql=query)
try:
  database.run_in_transaction(execute_transaction)
  print("inside try")
except GoogleAPICallError as e:
  print("inside e")

Any idea what I might be missing?

Answer 1

execute_sql returns a StreamedResultSet, which gets evaluated lazily. You won't see the error until you iterate through the result. Changing your example to read the result set into a list:

from google.cloud import spanner_v1 as spanner
from google.api_core.exceptions import GoogleAPICallError

spanner_client = spanner.Client()
instance = spanner_client.instance("Emulator")
database = instance.database("testdb")

def execute_transaction(transaction):
    query = "SELECT SingerId, AlbumId, AlbumTitle FROM Albums"
    return list(transaction.execute_sql(sql=query))

try:
    database.run_in_transaction(execute_transaction)
    print("inside try")  # never gets here
except GoogleAPICallError as ex:
    print(ex)  # 400 Table not found: Albums...