Snowflake执行Insert语句报错：值列表与列列表不匹配

问题场景 #

在完成Snowflake University的ESS-DABW徽章3第5课（待处理订单应用优化：完善ORDERS表定义）时，执行插入语句及Streamlit应用生成的插入操作均失败，错误提示插入值数量与表列数不匹配。

ORDERS表定义代码 #

alter table SMOOTHIES.PUBLIC.ORDERS 
add column order_uid integer --adds the column
default smoothies.public.order_seq.nextval  --sets the value of the column to sequence
constraint order_uid unique enforced; --makes sure there is always a unique value in the column

create or replace table smoothies.public.orders (
       order_uid integer default smoothies.public.order_seq.nextval,
       order_filled boolean default false,
       name_on_order varchar(100),
       ingredients varchar(200),
       constraint order_uid unique (order_uid),
       order_ts timestamp_ltz default current_timestamp()
);

错误的Insert语句 #

insert into smoothies.public.orders 
values ('Guava Cantaloupe Elderberries ', 'Abhi');

错误信息 #

Insert value list does not match column list expecting 5 but got 2

Streamlit应用相关代码 #

# Import python packages
import streamlit as st
from snowflake.snowpark.functions import col
from snowflake.snowpark.context import get_active_session

# Write directly to the app
st.title(":cup_with_straw: Customize Your Smoothie! :cup_with_straw:")
st.write(
    """Choose the fruits you want in your custom Smoothie!
    """
)

name_on_order= st.text_input('Name on Smoothie')
st.write('The name on your Smoothie will be:', name_on_order)


session= get_active_session()
my_dataframe = session.table("smoothies.public.fruit_options").select(col('Fruit_name'))
#st.dataframe(data=my_dataframe, use_container_width=True)
ingredients_list = st.multiselect('Choose up to 5 ingredients:', my_dataframe
)

if ingredients_list:
    #st.write(ingredients_list)
    #st.text(ingredients_list)
    ingredients_string= ''
    for fruit_chosen in ingredients_list:
        ingredients_string+=fruit_chosen + ' '
    #st.write(ingredients_string)

    my_insert_stmt = """ insert into smoothies.public.orders
        values ('""" + ingredients_string + """','""" + name_on_order+ """')"""

    #st.write(my_insert_stmt)
    #st.stop()
    time_to_insert= st.button('Submit Order')
    if time_to_insert:
        session.sql(my_insert_stmt).collect()
        st.success('Your Smoothie is ordered!', icon="✅")

问题原因与修复方案 #

原因 #

ORDERS表共定义了5列：order_uid、order_filled、name_on_order、ingredients、order_ts。使用INSERT ... VALUES语法时，如果不指定目标列名，Snowflake会要求按表列的定义顺序提供所有列的值；仅提供2个值时，就会触发列数不匹配的错误。

修复方式 #

1. 修正SQL插入语句（指定目标列名）

明确写出要插入的列名，Snowflake会自动用默认值填充其他列：

insert into smoothies.public.orders (name_on_order, ingredients)
values ('Abhi', 'Guava Cantaloupe Elderberries ');

注意：值的顺序要与指定的列名顺序对应。

2. 修正Streamlit代码中的插入语句

修改生成SQL的逻辑，添加目标列名，并调整值的顺序与列名对应：

my_insert_stmt = """ insert into smoothies.public.orders (name_on_order, ingredients)
    values ('""" + name_on_order + """','""" + ingredients_string + """')"""

调整后，插入时会将用户输入的姓名和配料对应到正确的列，其余列使用表定义的默认值填充。

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