Python Requests取数入Snowflake后PowerBI格式报错及数据扁平化问题

1. JSON格式适配问题 #

直接存Python原生字典到Snowflake会导致PowerBI识别异常——Python字典默认用单引号，但JSON规范要求双引号。别用eval做无用功，正确处理方式是：

• 用json.dumps(data)把Python字典转成标准双引号JSON字符串
• 存入Snowflake时，对应字段类型设为VARIANT（Snowflake专为半结构化数据设计的类型），而非普通字符串类型

这样PowerBI连接Snowflake时，能直接识别VARIANT字段内的合法JSON结构，不会因引号格式报错。

代码示例：

import requests
import json
import snowflake.connector

# 拉取源数据
resp = requests.get(url, headers={"Authorization": auth_token})
data_list = resp.json()["data"]

# 转换为Snowflake兼容的JSON字符串格式
snowflake_ready_data = [{"structured_data": json.dumps(item)} for item in data_list]

# 写入Snowflake
conn = snowflake.connector.connect(
    user="your_user",
    password="your_pwd",
    account="your_account",
    warehouse="your_warehouse",
    database="your_db",
    schema="your_schema"
)
cursor = conn.cursor()
cursor.execute("CREATE TABLE IF NOT EXISTS DATA_TABLE (structured_data VARIANT)")
cursor.executemany("INSERT INTO DATA_TABLE (structured_data) VALUES (%(structured_data)s)", snowflake_ready_data)
conn.commit()
conn.close()

2. 嵌套数据扁平化问题 #

用pd.json_normalize时，必须保留原始数据的关联标识（比如每条数据的索引，或数据本身带的唯一ID），才能把扁平化后的嵌套表和主表关联起来。针对你的示例数据，高效处理步骤如下：

代码示例：

import pandas as pd

# 示例数据
sample_data = {
    'data': [{
        'name': 'alvin',
        'age' : '20',
        'addresses':[{ 'house' : '187', 'street' : 'lindsly', 'city' : 'abc', 'country' : 'netherlands'}],
        'communications': [{ 'signaling' : True, 'confirm' : False, 'phone' : '0123456'}, { 'signaling' : True, 'confirm' : True, 'phone' : '0945785'}],
        'careDegree': 'NO',
        'dob': '1911-11-01T09:00:00.000Z',
        'partnerIds': [{'asvTeamNumbers': [], 'onlineTherapyContract': [], '_id': '6e31c', 'patientId': 'ZXWB8', 'extra': 'ID'}],
    }]
}

# 生成主表，添加record_id作为关联键
main_df = pd.json_normalize(sample_data['data'])
main_df['record_id'] = main_df.index

# 扁平化addresses，关联record_id
addresses_df = pd.json_normalize(
    sample_data['data'],
    record_path='addresses',
    meta=[['name']],
    record_prefix='address_'
)
addresses_df['record_id'] = addresses_df.index

# 扁平化communications，关联record_id
comm_df = pd.json_normalize(
    sample_data['data'],
    record_path='communications',
    meta=[['name']],
    record_prefix='comm_'
)
comm_df['record_id'] = comm_df.index

# 扁平化partnerIds，关联record_id
partner_df = pd.json_normalize(
    sample_data['data'],
    record_path='partnerIds',
    meta=[['name']],
    record_prefix='partner_'
)
partner_df['record_id'] = partner_df.index

# 按需合并主表与嵌套表（支持多对多关联场景）
merged_df = main_df.merge(addresses_df, on='record_id').merge(comm_df, on='record_id').merge(partner_df, on='record_id')

13000行数据用这个方法完全没问题，pd.json_normalize是矢量化操作，比apply类循环快得多。

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