Python+Streamlit实现带外键的SQL插入语句问题解决

原代码存在以下问题导致逻辑不符合预期：

• SQL语法错误：生硬拼接INSERT VALUES和SELECT语句，单表INSERT的字段列表错误使用了表别名前缀，且字段名写错（原表是create_date，代码里写的是created_date）
• 参数不匹配：参数列表混入了无意义的db值，占位符数量和实际需要插入的字段数不对应
• 逻辑缺失：没有关联status_type表做用户输入状态文本到ID的转换，直接把文本值传给了INT类型的外键字段
• 无合法性校验：没有判断用户选择的状态值是否在status_type表中存在，容易产生脏数据

方案1：单条INSERT...SELECT语句完成（推荐） #

不需要提前单独查询状态ID，一条SQL即可完成插入，自动完成状态文本到ID的匹配，同时天然过滤不存在的状态值：

# 注意：此处status_type_input是前端传回的、用户选中的状态文本值
query = '''
INSERT INTO info (ID, name, nickname, mother_name, birthdate, status_type, create_date)
SELECT ?, ?, ?, ?, ?, st.ID, ?
FROM status_type st
WHERE st.status = ?
'''
# 参数顺序严格对应SELECT后的占位符：ID、姓名、昵称、母亲姓名、生日、创建日期、用户选中的状态文本
args = (ID, name, nickname, mother_name, birthdate, current_date, status_type_input)

cursor = con.cursor()
cursor.execute(query, args)

# 校验插入结果，影响行数为0说明选中的状态值不存在
if cursor.rowcount == 0:
    st.error('所选状态无效，请重新选择')
else:
    con.commit()
    st.success('Record added Successfully')

方案2：前端直接传状态ID（性能最优） #

最省事的方案是在渲染下拉选择框时，直接把选项值绑定为status_type表的ID，显示文本绑定状态名，用户提交时直接传回ID，不需要后端做转换：

# 后端渲染下拉选项的参考逻辑
cursor.execute("SELECT ID, status FROM status_type")
# 组装选项：每个选项的value对应ID，显示文字对应status字段值
status_options = [{"value": row[0], "label": row[1]} for row in cursor.fetchall()]

这种场景下插入SQL就是最普通的INSERT语句，直接把传回的ID作为参数传入即可：

query = '''
INSERT INTO info (ID, name, nickname, mother_name, birthdate, status_type, create_date)
VALUES (?, ?, ?, ?, ?, ?, ?)
'''
args = (ID, name, nickname, mother_name, birthdate, status_type_id_from_frontend, current_date)

原info表建表语句存在语法错误（逗号位置错误），建议修正后增加外键约束，从数据库层面保证数据一致性，修正后的建表语句：

CREATE TABLE info (
  ID int(11) NOT NULL,
  name varchar(50) NULL,
  nickname varchar(50) NULL,
  mother_name varchar(50) NULL,
  birthdate date NULL,
  status_type int,
  create_date date,
  CONSTRAINT fk_info_status_type FOREIGN KEY (status_type) REFERENCES status_type(ID)
);

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