Update a Synapse Table or View from csv using a TableUpdateTransactionRequest

tableUpdateTransaction.py

#!/usr/bin/env python

import json
import random
import string
import csv
import tempfile

import synapseclient

syn = synapseclient.login(silent=True)

# Get existing data from a view or table
tableId = 'syn8332069'
res = syn.tableQuery("select * from {tableId}".format(tableId=tableId))

# Read and do some update
foo = list(csv.DictReader(file(res.filepath)))
foo[0]['testAnnot'] = ''.join(random.choice(string.ascii_uppercase + string.digits) for _ in range(5))

# Write to a file
with tempfile.NamedTemporaryFile(suffix='.csv', delete=False) as of:
  wr = csv.DictWriter(of, fieldnames=foo[0].keys())
  wr.writeheader()
  wr.writerows(foo)
  of.flush()
  fh = synapseclient.multipart_upload.multipart_upload(syn, of.name)

# Create requests - an UploadToTableRequest is what we want (and were using previously)
uploadRequest = {u'tableId': tableId, u'linesToSkip': 0, u'concreteType': u'org.sagebionetworks.repo.model.table.UploadToTableRequest', u'uploadFileHandleId': fh, 
                 u'csvTableDescriptor': {u'escapeCharacter': u'\\', u'isFirstLineHeader': True, u'separator': u',', u'lineEnd': '\n', u'quoteCharacter': u'"'}}

# But now we need to wrap that in a TableUpdateTransactionRequest, which is more flexible (and supports other updates)
request = {u'concreteType': u'org.sagebionetworks.repo.model.table.TableUpdateTransactionRequest',
           u'entityId': tableId,
           u'changes': [uploadRequest]}

# Do the update, waiting for async 
endpoint = 'https://repo-prod.prod.sagebase.org/repo/v1'
uri = '/entity/{tableId}/table/transaction/async/'.format(tableId=tableId)
result = syn._waitForAsync(uri, request, endpoint)

# The above fxn _waitForAsync does these steps - otherwise you have to keep running the restGET
# startUri = '/entity/{tableId}/table/transaction/async/start'.format(tableId=tableId)
# q = syn.restPOST(startUri, body=json.dumps(request), endpoint=endpoint)
# 
# getUri = '/entity/{tableId}/table/transaction/async/get/{token}'.format(tableId=tableId, token=q['token'])
# result = syn.restGET(getUri, endpoint=endpoint)