如何向Snowflake存储过程的数组类型参数传值？报错求助

问题场景 #

通过Python代码调用Snowflake存储过程，向ARRAY类型参数传递值时触发SQL编译错误，相关代码及数据如下：

执行逻辑代码 #

try:
    session = self.get_session()
    session.execute("BEGIN TRANSACTION;")
    if with_result:
        output = session.execute(query, values)
        result = output.mappings().all()
    session.execute("commit;")
    print("result = ", result)
    return result

调用存储过程的函数 #

def insert_data(self, data ):
    y = list(data)
    values = {'data_assets': [y]}
    query = "call data_table(:data_assets);"
    self.database.execute_query(query,values)

Snowflake存储过程定义 #

create or replace procedure data_table(data_val array)
returns Table()
language sql
{
  -- 存储过程内部逻辑
}

待传入的原始数据 #

k = [
    '{"path":["CATEGORY","PERSONAL  ","EMAIL","PERSONAL_MAIL","EMAIL"],"id":1,"size":50,"pers_id": 81945}',
    '{"path":["CATEGORY","PERSONAL  ","EMAIL","PERSONAL_MAIL","PHONE_NO"],"id":1,"size":70,"pers_id": 81945}',
    '{"path":["CATEGORY","PERSONAL  ","EMAIL","MANAGER","USER"],"id":1,"size":40,"pers_id": 81945}',
    '{"path":["CATEGORY","PERSONAL  ","EMAIL","PERSONAL_MAIL","VIN"],"id":1,"size":120,"pers_id": 81945}',
    '{"path":["CATEGORY","PERSONAL  ","EMAIL","PERSONAL_MAIL","USER"],"id":1,"size":500,"pers_id": 81945}',
    '{"path":["CATEGORY","PII","EMAIL","PERSONAL_MAIL","USER"],"id":1,"size":20,"pers_id": 81945}',
    '{"path":["CATEGORY","PERSONAL ","EMAIL","EMPLOYEE","USER"],"id":1,"size":70,"pers_id": 81945}',
    '{"path":["CATEGORY","PERSONAL  ","EMAIL","PERSONAL_MAIL","US_PHONE_NO"],"id":1,"size":80,"pers_id": 81945}',
    '{"path":["CATEGORY","PERSONAL  ","EMAIL","PERSONAL_MAIL","LOCATION"],"id":1,"size":5,"pers_id": 81945}',
    '{"path":["CATEGORY","PERSONAL  ","EMAIL","ADDRESS","USER"],"id":1,"size":90,"pers_id": 81945}',
    '{"path":["CATEGORY","PERSONAL  ","EMAIL","PERSONAL_MAIL","ADMIN"],"id":1,"size":1,"pers_id": 81945}'
]

错误信息 #

sqlalchemy.exc.ProgrammingError: (snowflake.connector.errors.ProgrammingError) 001003 (42000): SQL编译错误:
第1行第59位出现意外的'"path"'。
第1行第76位出现意外的'['。
第1行第87位出现意外的','。
第1行第134位出现意外的']'。

[SQL: call data_table(%(data_assets)s);]
[参数: {'data_assets': [['{"path":["CATEGORY","PERSONAL ","EMAIL","PERSONAL _MAIL","EMAIL"],"id":1,"size":50,"pers_id": 081945}', '{"datasour ... (1200字符截断) ... 081945}', ' {"path":["CATEGORY","PERSONAL ","EMAIL","PERSONAL _MAIL","ADMIN"],"id":1,"size":1,"pers_id": 081945} ']]}]

问题原因 #

1. 参数嵌套错误：代码中values = {'data_assets': [y]}将列表额外包裹了一层，传递给存储过程的是二维数组，但存储过程期望一维ARRAY类型参数。
2. 数据格式不匹配：传入的是JSON字符串列表，而非Snowflake ARRAY可直接解析的结构化数据，导致SQL编译时误将字符串内容识别为SQL语法片段。
3. 原始数据语法错误：初始传入的k变量缺少列表包裹，格式不符合Python数据规范。

修复方案 #

1. 修正参数嵌套问题 #

移除多余的列表包裹，直接传递一维列表：

def insert_data(self, data ):
    y = list(data)
    # 去掉外层[]，直接传递y
    values = {'data_assets': y}
    query = "call data_table(:data_assets);"
    self.database.execute_query(query,values)

2. 解析JSON字符串为Python对象（推荐） #

将JSON字符串转换为Python字典，让Snowflake连接器自动映射为正确的ARRAY格式：

import json

def insert_data(self, data ):
    # 解析每个JSON字符串并去除首尾空格
    parsed_data = [json.loads(item.strip()) for item in data]
    values = {'data_assets': parsed_data}
    query = "call data_table(:data_assets);"
    self.database.execute_query(query,values)

3. 修正原始数据格式 #

确保传入insert_data的是合法的Python列表，而非格式错误的字符串拼接（如前文修正后的k变量格式）。

验证说明 #

修改后，Snowflake连接器会将Python列表正确转换为SQL ARRAY类型，避免编译错误。若存储过程内部需处理JSON结构，解析后的字典也更便于后续逻辑操作。

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