Key Value Pair output

[UtiliseIT]

Hi,

My api currently has an output of [1, "Apple"], [2, "Banana"], [3, "Orange"] and I've been told it should be a key value pair. My Return code is

return {
'statusCode': 200,
'headers': {'Content-Type': 'application/json'},
"body": json.dumps(rows)
}

What do I need to do to fix this?

Thanks in advance

Todd

[buran]

It's fair to guess that rows is returned from query to database, so it is list/tuple of tuples. You need to convert it to dict, before pass to json.dumps()

import json
rows = [(1, "Apple"), (2, "Banana"), (3, "Orange")]
print(json.dumps(rows))
print(json.dumps(dict(rows)))

Output:
[[1, "Apple"], [2, "Banana"], [3, "Orange"]]
{"1": "Apple", "2": "Banana", "3": "Orange"}
>>>

[UtiliseIT]

It's fair to guess that rows is returned from query to database, so it is list/tuple of tuples. You need to convert it to dict, before pass to json.dumps()

import json
rows = [(1, "Apple"), (2, "Banana"), (3, "Orange")]
print(json.dumps(rows))
print(json.dumps(dict(rows)))

Output:
[[1, "Apple"], [2, "Banana"], [3, "Orange"]]
{"1": "Apple", "2": "Banana", "3": "Orange"}
>>>

Thanks Buran,

You are correct in that this is results from a query. The code also executes inside a AWS Lambda function.

Ive modified my code to

return {
        'statusCode': 200,
        'headers': {'Access-Control-Allow-Origin': '*',
        'Content-Type': 'application/json'},
        "body": json.dumps(dict((rows))
    }

But am getting syntax error

Im told that output should be

{
"body": [{
"Id”: "1”,
"Fruit”: "Apple”
},{
"Id”: "2”,
"Fruit”: "Banana”
}]
}

[buran]

the error is because you have extra opening bracket between dict and row.
if you need to get different json as body - construct the list of dicts

import json
rows = [(1, "Apple"), (2, "Banana"), (3, "Orange")]
json_rows = [dict(zip(('Id', 'Fruit'), (str(key), value))) for key, value in rows]
print(json.dumps(json_rows))

Output:
[{"Id": "1", "Fruit": "Apple"}, {"Id": "2", "Fruit": "Banana"}, {"Id": "3", "Fruit": "Orange"}]
>>>

[UtiliseIT]

Thanks buran,

Im now getting error

"errorMessage": "dictionary update sequence element #0 has length 7; 2 is required",
"errorType": "ValueError",

Sorry I don't understand what you mean "construct the list of dicts"

Cheers

Todd

[buran]

Im now getting error

"errorMessage": "dictionary update sequence element #0 has length 7; 2 is required",
"errorType": "ValueError",

I don't understand that error. Post your full code as well as full traceback you get

I have added example to my previous answer already

[UtiliseIT]

OK here is my full code

Before worrying about the error message. 1st question would be if i have the correct format for line 23

Then next question would be if I have it in the right place.

I am using the Official Python Connector for MySQL.

Thanks in advance.

import os
import sys
import logging
sys.path.insert(0, '/opt')
import json
import mysql.connector


logger = logging.getLogger()
logger.setLevel(logging.INFO)


logger.info("SUCCESS: Connection to DB instance succeeded")

def lambda_handler(event, context):
    cnx = mysql.connector.connect(user=os.environ['user'], database=os.environ['database'], host=os.environ['host'], password=os.environ['password'])
    cursor = cnx.cursor()
        
    query = ("SELECT distinctrow UPC, ItemFriendlyNames, ItemName, Brand, Model, Retailer, ItemID FROM my.view order by UPC, Model, Retailer")
        
    cursor.execute(query)
    rows = cursor.fetchall()
    json_rows = [dict(zip(('UPC', 'ItemFriendlyNames', 'ItemName', 'Brand', 'Model', 'Retailer', 'ItemID'), (str(key), value, value, value, value, value, value))) for key, value in rows]
    
    cursor.close()
    cnx.close()

    return {
        'statusCode': 200,
        'headers': {'Access-Control-Allow-Origin': '*',
        'Content-Type': 'application/json'},
         "body": json.dumps(rows)
    }
    
    
    
    

[buran]

Each row in your actual query has 7 elements, while in your sample data had just 2. In the sample it was easy to asign each of these elements to different [meaningful] variable name. In the real query you can do the same - unpack each row into 7 different variables. Or you can do something different.

Let's focus just on line 23 and how you can replace it (any of the following would do):

json_rows = [dict(zip(('UPC', 'ItemFriendlyNames', 'ItemName', 'Brand', 'Model', 'Retailer', 'ItemID'),
                      (str(upc), item_friendly_name, item_name, brand, model, retailer, item_id)))
                    for upc, item_friendly_name, item_name, brand, model, retailer, item_id in rows]

json_rows = [dict(zip(('UPC', 'ItemFriendlyNames', 'ItemName', 'Brand', 'Model', 'Retailer', 'ItemID'), map(str, row))) for row in rows]

json_rows = [dict(zip(('UPC', 'ItemFriendlyNames', 'ItemName', 'Brand', 'Model', 'Retailer', 'ItemID'), (str(key), *values))) for key, *values in rows]

there are also other ways to do that... E.g. using map will cast to str all elements in the row. if there are some values that are not str and you need to keep them then you need to use something different

some more thoughts.
1. Instead of using cursor.fetchall() you can iterate directly over the cursor
2. instead of converting upc to str in the list comprehension (the line 23) you can cast it to string in the sql statement directly

"SELECT distinctrow CAST(UPC AS CHAR), ItemFriendlyNames, ItemName, Brand, Model, Retailer, ItemID FROM my.view order by UPC, Model, Retailer"

Also note that on line 32 you need to dump json_rows, not just rows

[UtiliseIT]

Thank you Buran,

Can you elaborate more on your comment regarding cursor.fetchall

Appreciate it

[buran]

from my last alternative
you can replace

rows = cursor.fetchall()
json_rows = [dict(zip(('UPC', 'ItemFriendlyNames', 'ItemName', 'Brand', 'Model', 'Retailer', 'ItemID'), (str(key), *values))) for key, *values in rows]

with just

json_rows = [dict(zip(('UPC', 'ItemFriendlyNames', 'ItemName', 'Brand', 'Model', 'Retailer', 'ItemID'), (str(key), *values))) for key, *values in cursor]

here from the docs:

After executing the query, the MySQL server is ready to send the data. The result set could be zero rows, one row, or 100 million rows. Depending on the expected volume, you can use different techniques to process this result set. In this example, we use the cursor object as an iterator. The first column in the row is stored in the variable first_name, the second in last_name, and the third in hire_date.

i.e. you can use cursor as iterator instead of using cursor.fetchone(), cursor.fetchall(), cursor.fetchmany()