如何正确格式化从S3 Bucket拉取的CSV为指定JSON格式？

问题场景 #

从S3存储桶拉取最新CSV文件，转换为指定格式的JSON数组，但当前输出存在大量多余转义字符，无法得到预期格式。

示例CSV数据 #

Ticker    Exchange    Date    Open    High    Low     Close   Volume
  6A        BATS    12/2/2021   0.9     0.95    0.83    0.95    1200
  6B        BATS    12/3/2021   1       1.3     0.9     1.2     1500
  6C        BATS    12/4/2021   1.2     1.3     1.1     1.1     1300

原实现代码 #

import json
import pandas as pd
import boto3

s3 = boto3.client('s3')
object_list = []
bucket_name = 'bats-candles'

paginator = s3.get_paginator("list_objects_v2")
page_iterator = paginator.paginate(Bucket=bucket_name)
for result in page_iterator:
    object_list += filter(lambda obj: obj['Key'].endswith('.csv'), result['Contents'])
object_list.sort(key=lambda x: x['LastModified'])

A = (object_list[-1]['Key'])
full_path = f"s3://{bucket_name}/{A}"
print(full_path)
print(A)

raw_df = pd.read_csv(full_path)

print(bucket_name, full_path)

df = raw_df.to_json(orient="records")

response = {
    'body': json.dumps(df, indent=4, separators=(',', ':'))
}

pretty_json = json.dumps(response)
print(pretty_json)

当前错误输出 #

{"body": "\"[{\\\"Ticker\\\":\\\"6A\\\",\\\"Exchange\\\":\\\"BATS\\\",\\\"Date\\\":\\\"12\\\\/2\\\\/2021\\\",\\\"Open\\\":0.9,\\\"High\\\":0.95,\\\"Low\\\":0.83,\\\"Close\\\":0.95,\\\"Volume\\\":1200}

期望输出格式 #

[
        {
            "Ticker":"6A",
            "Exchange":"BATS",
            "Date":"12/2/2021",
            "Open":0.9,
            "High":0.95,
            "Low":0.83,
            "Close":0.95,
            "Volume":1200
        },
        {
            "Ticker":"6B",
            "Exchange":"BATS",
            "Date":"12/3/2021",
            "Open":1.0,
            "High":1.3,
            "Low":0.9,
            "Close":1.2,
            "Volume":1500
        },
        {
            "Ticker":"6C",
            "Exchange":"BATS",
            "Date":"12/4/2021",
            "Open":1.2,
            "High":1.3,
            "Low":1.1,
            "Close":1.1,
            "Volume":1300
        } ]

问题原因 #

原代码存在双重序列化问题：

1. raw_df.to_json(orient="records")已经生成了JSON格式的字符串
2. 后续又调用json.dumps(df)对该字符串再次序列化，导致转义字符翻倍
3. 最后对整个response对象执行json.dumps，进一步加重转义问题

正确实现方案 #

方案一：直接输出格式化后的JSON数组 #

如果不需要外层response结构，直接生成目标格式：

import json
import pandas as pd
import boto3

s3 = boto3.client('s3')
object_list = []
bucket_name = 'bats-candles'

paginator = s3.get_paginator("list_objects_v2")
page_iterator = paginator.paginate(Bucket=bucket_name)
for result in page_iterator:
    object_list += filter(lambda obj: obj['Key'].endswith('.csv'), result['Contents'])
object_list.sort(key=lambda x: x['LastModified'])

A = (object_list[-1]['Key'])
full_path = f"s3://{bucket_name}/{A}"
print(full_path)
print(A)

raw_df = pd.read_csv(full_path)

print(bucket_name, full_path)

# 直接生成带缩进的JSON数组字符串
pretty_json = raw_df.to_json(orient="records", indent=4)
print(pretty_json)

方案二：保留response结构且避免转义 #

如果需要外层response对象，先将JSON字符串转为Python原生对象，再序列化整个结构：

import json
import pandas as pd
import boto3

s3 = boto3.client('s3')
object_list = []
bucket_name = 'bats-candles'

paginator = s3.get_paginator("list_objects_v2")
page_iterator = paginator.paginate(Bucket=bucket_name)
for result in page_iterator:
    object_list += filter(lambda obj: obj['Key'].endswith('.csv'), result['Contents'])
object_list.sort(key=lambda x: x['LastModified'])

A = (object_list[-1]['Key'])
full_path = f"s3://{bucket_name}/{A}"
print(full_path)
print(A)

raw_df = pd.read_csv(full_path)

print(bucket_name, full_path)

# 将DataFrame转为Python列表对象，再序列化
df_records = raw_df.to_dict(orient="records")
response = {
    'body': json.dumps(df_records, indent=4, separators=(',', ':'))
}

pretty_json = json.dumps(response)
print(pretty_json)

关键说明 #

• to_json(orient="records", indent=4)可以直接生成带格式化缩进的JSON数组字符串，无需额外调用json.dumps
• 若要将JSON数组作为response的body值，必须先将JSON字符串转为Python原生对象（列表+字典），再对整个response序列化，否则会触发双重转义

内容的提问来源于stack exchange，提问作者bob