I have this (simplified) case, with 2 tables related in this way:
CREATE TABLE a (ida STRING(36) NOT NULL, name STRING(15)) PRIMARY KEY (ida);
CREATE TABLE b (idb INT64 NOT NULL, ida STRING(36) NOT NULL) PRIMARY KEY (ida, idb)
INTERLEAVE IN PARENT a ON DELETE CASCADE;
where my ida is a UUID4 id format that I am generating from my code (Python 3).
In my case a batch of a few thousands "tuples" (idb, name) is sent to my service.
If
idbdoes not exist in the tablebthen create auuid4and do the following inserts:my_uuid_1 = uuid.uuid4().__str__() # generated via Python3idb = 123 # received from the requestINSERT a (ida, name) VALUES (my_uuid_1, 'John')INSERT b (idb, ida) VALUES (123, my_uuid_1)If
idbexists in the tablebthen just update the tableawith the eventual new name.
Now this process needs to be run in a batch way for multiple records that can generate the situation just described. In order to do this with CloudSpanner I have been looking to the functionality:
def _unit_of_work(transaction):
try:
transaction.insert_or_update(
table=table,
columns=columns,
values=values,
)
except BadRequest as err:
logging.error(f'Error: ${err.args}')
raise
spanner_database = spanner_instance.database(database_id=my_database_id)
spanner_database.run_in_transaction(_unit_of_work)
but I cannot see a way to use it with the condition from before. I am looking in the right direction or there is a better way to do it?