As of January 1, 2020 this library no longer supports Python 2 on the latest released version. Library versions released prior to that date will continue to be available. For more information please visit Python 2 support on Google Cloud.

Transaction API

Spanner read-write transaction support.

class google.cloud.spanner_v1.transaction.Transaction(session)[source]

Implement read-write transaction semantics for a session.

Parameters

session (Session) – the session used to perform the commit

Raises

ValueError – if session has an existing transaction

__enter__()[source]

Begin with block.

__exit__(exc_type, exc_val, exc_tb)[source]

End with block.

batch_update(statements, request_options=None)[source]

Perform a batch of DML statements via an ExecuteBatchDml request.

Parameters
  • statements (Sequence[Union[ str, Tuple[str, Dict[str, Any], Dict[str, Union[dict, types.Type]]]]]) – List of DML statements, with optional params / param types. If passed, ‘params’ is a dict mapping names to the values for parameter replacement. Keys must match the names used in the corresponding DML statement. If ‘params’ is passed, ‘param_types’ must also be passed, as a dict mapping names to the type of value passed in ‘params’.

  • request_options (google.cloud.spanner_v1.types.RequestOptions) – (Optional) Common options for this request. If a dict is provided, it must be of the same form as the protobuf message RequestOptions.

Return type

Tuple(status, Sequence[int])

Returns

Status code, plus counts of rows affected by each completed DML statement. Note that if the status code is not OK, the statement triggering the error will not have an entry in the list, nor will any statements following that one.

begin()[source]

Begin a transaction on the database.

Return type

bytes

Returns

the ID for the newly-begun transaction.

Raises

ValueError – if the transaction is already begun, committed, or rolled back.

commit(return_commit_stats=False, request_options=None)[source]

Commit mutations to the database.

Parameters
  • return_commit_stats (bool) – If true, the response will return commit stats which can be accessed though commit_stats.

  • request_options (google.cloud.spanner_v1.types.RequestOptions) – (Optional) Common options for this request. If a dict is provided, it must be of the same form as the protobuf message RequestOptions.

Return type

datetime

Returns

timestamp of the committed changes.

Raises

ValueError – if there are no mutations to commit.

committed = None

Timestamp at which the transaction was successfully committed.

delete(table, keyset)

Delete one or more table rows.

Parameters
  • table (str) – Name of the table to be modified.

  • keyset (Keyset) – Keys/ranges identifying rows to delete.

execute_sql(sql, params=None, param_types=None, query_mode=None, query_options=None, request_options=None, partition=None, retry=<_MethodDefault._DEFAULT_VALUE: <object object>>, timeout=<_MethodDefault._DEFAULT_VALUE: <object object>>, data_boost_enabled=False)

Perform an ExecuteStreamingSql API request.

Parameters
  • sql (str) – SQL query statement

  • params (dict, {str -> column value}) – values for parameter replacement. Keys must match the names used in sql.

  • param_types (dict[str -> Union[dict, types.Type]]) – (Optional) maps explicit types for one or more param values; required if parameters are passed.

  • query_mode (QueryMode) – Mode governing return of results / query plan. See: QueryMode.

  • query_options – (Optional) Query optimizer configuration to use for the given query. If a dict is provided, it must be of the same form as the protobuf message QueryOptions

  • request_options (google.cloud.spanner_v1.types.RequestOptions) – (Optional) Common options for this request. If a dict is provided, it must be of the same form as the protobuf message RequestOptions.

  • partition (bytes) – (Optional) one of the partition tokens returned from partition_query().

  • retry (Retry) – (Optional) The retry settings for this request.

  • timeout (float) – (Optional) The timeout for this request.

  • data_boost_enabled – (Optional) If this is for a partitioned query and this field is set true, the request will be executed via offline access. If the field is set to true but the request does not set partition_token, the API will return an INVALID_ARGUMENT error.

Return type

StreamedResultSet

Returns

a result set instance which can be used to consume rows.

Raises

ValueError – for reuse of single-use snapshots, or if a transaction ID is already pending for multiple-use snapshots.

execute_update(dml, params=None, param_types=None, query_mode=None, query_options=None, request_options=None, *, retry=<_MethodDefault._DEFAULT_VALUE: <object object>>, timeout=<_MethodDefault._DEFAULT_VALUE: <object object>>)[source]

Perform an ExecuteSql API request with DML.

Parameters
  • dml (str) – SQL DML statement

  • params (dict, {str -> column value}) – values for parameter replacement. Keys must match the names used in dml.

  • param_types (dict[str -> Union[dict, types.Type]]) – (Optional) maps explicit types for one or more param values; required if parameters are passed.

  • query_mode (QueryMode) –

    Mode governing return of results / query plan. See: QueryMode.

  • query_options (QueryOptions or dict) – (Optional) Options that are provided for query plan stability.

  • request_options (google.cloud.spanner_v1.types.RequestOptions) – (Optional) Common options for this request. If a dict is provided, it must be of the same form as the protobuf message RequestOptions.

  • retry (Retry) – (Optional) The retry settings for this request.

  • timeout (float) – (Optional) The timeout for this request.

Return type

int

Returns

Count of rows affected by the DML statement.

insert(table, columns, values)

Insert one or more new table rows.

Parameters
  • table (str) – Name of the table to be modified.

  • columns (list of str) – Name of the table columns to be modified.

  • values (list of lists) – Values to be modified.

insert_or_update(table, columns, values)

Insert/update one or more table rows.

Parameters
  • table (str) – Name of the table to be modified.

  • columns (list of str) – Name of the table columns to be modified.

  • values (list of lists) – Values to be modified.

partition_query(sql, params=None, param_types=None, partition_size_bytes=None, max_partitions=None, *, retry=<_MethodDefault._DEFAULT_VALUE: <object object>>, timeout=<_MethodDefault._DEFAULT_VALUE: <object object>>)

Perform a PartitionQuery API request.

Parameters
  • sql (str) – SQL query statement

  • params (dict, {str -> column value}) – values for parameter replacement. Keys must match the names used in sql.

  • param_types (dict[str -> Union[dict, types.Type]]) – (Optional) maps explicit types for one or more param values; required if parameters are passed.

  • partition_size_bytes (int) – (Optional) desired size for each partition generated. The service uses this as a hint, the actual partition size may differ.

  • max_partitions (int) – (Optional) desired maximum number of partitions generated. The service uses this as a hint, the actual number of partitions may differ.

  • retry (Retry) – (Optional) The retry settings for this request.

  • timeout (float) – (Optional) The timeout for this request.

Return type

iterable of bytes

Returns

a sequence of partition tokens

Raises

ValueError – for single-use snapshots, or if a transaction ID is already associated with the snapshot.

partition_read(table, columns, keyset, index='', partition_size_bytes=None, max_partitions=None, *, retry=<_MethodDefault._DEFAULT_VALUE: <object object>>, timeout=<_MethodDefault._DEFAULT_VALUE: <object object>>)

Perform a PartitionRead API request for rows in a table.

Parameters
  • table (str) – name of the table from which to fetch data

  • columns (list of str) – names of columns to be retrieved

  • keyset (KeySet) – keys / ranges identifying rows to be retrieved

  • index (str) – (Optional) name of index to use, rather than the table’s primary key

  • partition_size_bytes (int) – (Optional) desired size for each partition generated. The service uses this as a hint, the actual partition size may differ.

  • max_partitions (int) – (Optional) desired maximum number of partitions generated. The service uses this as a hint, the actual number of partitions may differ.

  • retry (Retry) – (Optional) The retry settings for this request.

  • timeout (float) – (Optional) The timeout for this request.

Return type

iterable of bytes

Returns

a sequence of partition tokens

Raises

ValueError – for single-use snapshots, or if a transaction ID is already associated with the snapshot.

read(table, columns, keyset, index='', limit=0, partition=None, request_options=None, data_boost_enabled=False, *, retry=<_MethodDefault._DEFAULT_VALUE: <object object>>, timeout=<_MethodDefault._DEFAULT_VALUE: <object object>>)

Perform a StreamingRead API request for rows in a table.

Parameters
  • table (str) – name of the table from which to fetch data

  • columns (list of str) – names of columns to be retrieved

  • keyset (KeySet) – keys / ranges identifying rows to be retrieved

  • index (str) – (Optional) name of index to use, rather than the table’s primary key

  • limit (int) – (Optional) maximum number of rows to return. Incompatible with partition.

  • partition (bytes) – (Optional) one of the partition tokens returned from partition_read(). Incompatible with limit.

  • request_options (google.cloud.spanner_v1.types.RequestOptions) – (Optional) Common options for this request. If a dict is provided, it must be of the same form as the protobuf message RequestOptions. Please note, the transactionTag setting will be ignored for snapshot as it’s not supported for read-only transactions.

  • retry (Retry) – (Optional) The retry settings for this request.

  • timeout (float) – (Optional) The timeout for this request.

  • data_boost_enabled – (Optional) If this is for a partitioned read and this field is set true, the request will be executed via offline access. If the field is set to true but the request does not set partition_token, the API will return an INVALID_ARGUMENT error.

Return type

StreamedResultSet

Returns

a result set instance which can be used to consume rows.

Raises

ValueError – for reuse of single-use snapshots, or if a transaction ID is already pending for multiple-use snapshots.

replace(table, columns, values)

Replace one or more table rows.

Parameters
  • table (str) – Name of the table to be modified.

  • columns (list of str) – Name of the table columns to be modified.

  • values (list of lists) – Values to be modified.

rollback()[source]

Roll back a transaction on the database.

update(table, columns, values)

Update one or more existing table rows.

Parameters
  • table (str) – Name of the table to be modified.

  • columns (list of str) – Name of the table columns to be modified.

  • values (list of lists) – Values to be modified.