API payload to upsert multiple rows at a time

Developers Central
1

Hi,

Trying to build a payload to upsert multiple rows in this doc (zJTELzOhLB) and target_table (grid-k6FW1e9ZIS) using python and following https://coda.io/developers/apis/v1beta1#operation/upsertRows.

Untitled

But I’m stuck at the first step, getting a 400 Client Error: Bad Request for url:
https://coda.io/apis/v1beta1/docs/zJTELzOhLB/tables/grid-k6FW1e9ZIS/rows

requests.get('https://coda.io/apis/v1beta1/whoami', headers=headers).json() works so it’s not the API key.

And is it possible to update multiple rows in a single post: eg in the above target_table (grid-k6FW1e9ZIS) & target_column (c-AOniRSX61x), changing 1 to 100 and 22 to 99 with the lookup_column (c-_mQdN0yQZ0) as the row key?

here’s the payload used in the request:
{"rows": [{"cells": [{"column": "c-AOniRSX61x", "value": "100.0"}, {"column": "c-w0SawZ6cB2", "value": ""}, {"column": "c-_mQdN0yQZ0", "value": "a"}, {"column": "c-AOniRSX61x", "value": "999.0"}, {"column": "c-w0SawZ6cB2", "value": ""}, {"column": "c-_mQdN0yQZ0", "value": "ccc"}, {"column": "c-AOniRSX61x", "value": "10001.990234375"}, {"column": "c-w0SawZ6cB2", "value": ""}, {"column": "c-_mQdN0yQZ0", "value": "bb"}]}], "keyColumns": ["c-_mQdN0yQZ0"]}

and payload indented:
{
“rows”: [
{
“cells”: [
{
“column”: “c-AOniRSX61x”,
“value”: “100.0”
},
{
“column”: “c-w0SawZ6cB2”,
“value”: “”
},
{
“column”: “c-_mQdN0yQZ0”,
“value”: “a”
},
{
“column”: “c-AOniRSX61x”,
“value”: “999.0”
},
{
“column”: “c-w0SawZ6cB2”,
“value”: “”
},
{
“column”: “c-_mQdN0yQZ0”,
“value”: “ccc”
},
{
“column”: “c-AOniRSX61x”,
“value”: “10001.990234375”
},
{
“column”: “c-w0SawZ6cB2”,
“value”: “”
},
{
“column”: “c-_mQdN0yQZ0”,
“value”: “bb”
}
]
}
],
“keyColumns”: [
“c-_mQdN0yQZ0”
]
}

2

So the reason you are probably receiving the 400 error is due to the payload being too large for your table.

edit: unless you have typed in your table id or document id incorrectly which would be the simpler bad url syntax, but I doubt that is the issue

your table has 3 columns
so your payload should look like this:
payload = { 'rows': [ { 'cells': [ {'column': id, 'value': value }, {'column': id2, 'value': value2 }, {'column': id3, 'value': value3} ] }
if your want to insert multiple rows (which is what it looks like your trying to do, you need insert another object into rows ie:
payload = { 'rows': [ {'cells': [...]}, {'cells': [...]} ] }

This is most likely the issue.

3

Hi @Kilian, when you get an error 400, check the body for the details of the error. I was able to check error logs on our end, and this was the error you would have received:

SyntaxError: Unexpected token \" in JSON at position 0

So please make sure that you’re sending correct JSON up. As long as you provide a valid keyColumn, your approach should work.

P.S. For future reference, use three backticks (```) on a new line to insert code-formatted text.

4

Hi @Joshua_Upton & @oleg ,

Here’s the code but I’m struggling to convert the dataframe to the correct json structure - hope you can help?!


``` import pandas as pd
from collections import defaultdict
import json  # missing from original post

def generate_payload(df, columns, keycol):
    payload = defaultdict(list)
    row_array = defaultdict(list)
    for idx, row in df.iterrows():
        for col in columns:
            d = {}
            d['column'] = col
            d['value'] = str(row[col])
            row_array['cells'].append(d)
    payload['rows'].append(row_array)
    payload['keyColumns'] = keycol
    return json.dumps(payload)

testdf = pd.DataFrame({'c-AOniRSX61x': ['100.0','999.0','10001.990234375'],
                  'c-w0SawZ6cB2': ['','',''],
                  'c-_mQdN0yQZ0': ['a','ccc','bb']})

print(generate_payload(testdf, ['c-AOniRSX61x','c-w0SawZ6cB2','c-_mQdN0yQZ0'],['c-_mQdN0yQZ0']))
1 Like
5

@Kilian

first question, have you added import json into the file as well?
otherwise the json is going to return a NameError

also your current code is going to return a structure like (my dummy data, not yours):

payload = {
    'rows': [
        {
            'cells': [
                {'column': 'c-jilst955eu', 'value': 'test'},
                {'column': 'c-8KUlKa8_y-', 'value': '#039BC3'}
                {'column': 'c-jilst955eu', 'value': 'test2'},
                {'column': 'c-8KUlKa8_y-', 'value': '#99beE1'}
            ]
        }
    ]
}

what you should be trying to achieve is:

payload = {
    'rows': [
        {
            'cells': [
                {'column': 'c-jilst955eu', 'value': 'test'},
                {'column': 'c-8KUlKa8_y-', 'value': '#039BC3'}
            ]
        },
        {
            'cells': [
                {'column': 'c-jilst955eu', 'value': 'test2'},
                {'column': 'c-8KUlKa8_y-', 'value': '#99beE1'}
            ]
        }
    ]
}
1 Like
6

Thanks Joshua, added the missing import statement to the example for future ref, and I get the structure but I’m struggling to work out how to build it from the code…

7

Subtle change but I think this outputs what you want.

def generate_payload(df, columns, keycol):
    payload = defaultdict(list)
    row_array = defaultdict(list)
    for idx, row in df.iterrows():
        for col in columns:
            d = {}
            d['column'] = col
            d['value'] = str(row[col])
            row_array['cells'].append(d)
        payload['rows'].append(row_array)
        row_array = defaultdict(list)
    payload['keyColumns'] = keycol
    return json.dumps(payload, indent=2)
1 Like
8

Thanks, that’s solved the structure. And then for this message

'statusMessage': 'Bad Request', 'message': 'Unexpected token " in JSON at position 0'}

changed the return from

return json.dumps(payload)

to

return json.loads(json.dumps(payload))

to convert the string to dict - and we how have a method to upsert pandas dataframes to coda ( @Mikhail_Beliansky - would this be of use in your project?)

Thanks again Joshua

1 Like
9

@Kilian anything of use to the community is of use in the project :slight_smile: Feel free to submit a PR

10

Hi Kilian,

This formula is awesome, but i’m struggling with the encoding. I have “Tarjeta de crédito” and other values with UTF-8 encoding.

When I run this formula, the output is “Tarjeta de cr\u00e9dito”.

If I try with str(row[col]).encode(), I get the error “raise TypeError(f’Object of type {o.class.name} ’
180 f’is not JSON serializable’)”

If I try with row[col].encode(), I get ‘float’ object has no attribute ‘encode’.

I even tried with ternary operators and type() to just use encoding in strings, but no success. So, I ended up with no ideas, hehe.

Please help :pray:

11

I ended up replacing the encoded characteres for the correct one. It’s a quick fix, but if there is a better way, i would like to know.

If this can help someone, I searched the unicode for á, é, í, and replace it in my string.

This is my payload:

from collections import defaultdict
import json

#Esta es una custom function que genera la carga de datos a partir de un dataframe
def generate_payload(df, columns):
  payload = defaultdict(list)
  row_array = defaultdict(list)
  for idx, row in df.iterrows():
      for col in columns:
          d = {}
          d['column'] = col
          d['value'] = str(row[col])
          row_array['cells'].append(d)
      payload['rows'].append(row_array)
      row_array = defaultdict(list)
  return json.dumps(payload, indent=2)

#Here I do the replacing:
data = generate_payload(df, columns)
data = data.replace('\\u00e9','é')
data = data.replace('\\u00e1','á')
data = data.replace('\\u00ed','í')
#Converting the string to JSON helped me
payload = json.loads(data)