cloud spanner python client delete failed with INT64 type

Question

I'm trying to pass delete requests on a table with INT64 typed keys, and I receive the following error: Invalid value for column key1 in table INT_KEYS: Expected INT64

is there a specific way to type the key ranges when requesting a delete operation? I'm able to insert data just fine using the same inputs, but the delete request fails. I've written the following short code to illustrate

keys = ['key1', 'key2']
ins_values = [[2, 5],
              [3, 6],
              [4, 7]]
del_values = [[2, 5],
              [4, 7]]

for table_name in ['STR_KEYS', 'INT_KEYS']:
    with database.batch() as batch:
        result = batch.insert_or_update(
            table_name, keys, ins_values
        )
    print 'completed insert in table %s' % table_name

    with database.batch() as batch:
        key_ranges = list()
        for value_list in del_values:
            key_range = spanner.KeyRange(start_closed=value_list, end_closed=value_list)
            key_ranges.append(key_range)
        key_set = spanner.KeySet(keys=[keys], ranges=key_ranges)
        result = batch.delete(
            table_name, key_set
        )
    print 'deleted data from table %s' % table_name

and I obtain the output below

completed insert in table STR_KEYS
deleted data from table STR_KEYS
completed insert in table INT_KEYS
Traceback (most recent call last):
File "/Users/thibault/PycharmProjects/spanner-local/testIntDelete.py", line 89, in <module>
table_name, key_set
File "/usr/local/Cellar/python/2.7.14/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/google/cloud/spanner_v1/database.py", line 409, in __exit__
self._batch.commit()
File "/usr/local/Cellar/python/2.7.14/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/google/cloud/spanner_v1/batch.py", line 156, in commit
options=options)
File "/usr/local/Cellar/python/2.7.14/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/google/cloud/spanner_v1/gapic/spanner_client.py", line 845, in commit
return self._commit(request, options)
File "/usr/local/Cellar/python/2.7.14/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/google/gax/api_callable.py", line 452, in inner
return api_caller(api_call, this_settings, request)
File "/usr/local/Cellar/python/2.7.14/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/google/gax/api_callable.py", line 438, in base_caller
return api_call(*args)
File "/usr/local/Cellar/python/2.7.14/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/google/gax/api_callable.py", line 376, in inner
return a_func(*args, **kwargs)
File "/usr/local/Cellar/python/2.7.14/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/google/gax/retry.py", line 127, in inner
' classified as transient', exception)
google.gax.errors.RetryError: RetryError(Exception occurred in retry method that was not classified as transient, caused by <_Rendezvous of RPC that terminated with (StatusCode.FAILED_PRECONDITION, Invalid value for column key1 in table INT_KEYS: Expected INT64.)>)

Answer 1

With the delete API, you only need to specify the primary key values in "keys" or "ranges", not the values. I'm inferring that your schema for INT_KEYS is something like:

CREATE TABLE INT_KEYS ( key1 INT64 NOT NULL, key2 INT64 NOT NULL, ) PRIMARY KEY (key1, key2);

In this case, to delete the rows inserted you'd want something like:

with database.batch() as batch:
    key_ranges = list()
    for value_list in del_values:
        key_range = spanner.KeyRange(start_closed=value_list, end_closed=value_list)
        key_ranges.append(key_range)
    key_set = spanner.KeySet(ranges=key_ranges)
    result = batch.delete(
        table_name, key_set
    )

Note that you don't need to (and should not try to) specify the column names via the "keys" attribute; that is used to specify specific rows to delete.

FWIW, your code worked for STR_KEYS because ("key1", "key2") can successfully be interpreted as the primary key of a row that doesn't exist (key1=key1, key2=key2), and Spanner does not report an error when deleting non-existent rows.