[SOLVED] The method `run_in_transaction` of Spanner client library for Python returned a message "Transaction is not begun."

The method `run_in_transaction` of Spanner client library for Python returned a message "Transaction is not begun."

After I obtain my authenticate key with gcloud CLI, I try to execute the below Python code to create a table in my instance of Spanner. However, Spanner returned a message "Transaction is not begun."

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

def _create_table(transaction):
  """
  a transaction for creating a table
  """
  query = (
      'CREATE TABLE test_table (id INT64, data STRING(MAX)) PRIMARY KEY (id)'
  )
  transaction.execute_sql(query)

# access to Spanner
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)

# execute a query with run_in_transaction
try:
    database.run_in_transaction(_create_table)
except GoogleAPICallError as ex:
  print(ex)
except ValueError as ex:
  print(ex)

I expect that a transaction will begin when the method Database.run_in_transaction(transaction) is invoked.

Please tell me how to fix the above code.

Solution

Spanner does not support executing DDL statements in a transaction. Instead, you should use the spanner_client.database_admin_api. update_database_ddl function to execute DDL statements.

A full example for executing a DDL statement with the Python client can be found here: https://cloud.google.com/spanner/docs/getting-started/python#add-column-client-library-Python