Psycopg2执行INSERT跨表取值触发NotNullViolation非空约束错误如何解决

• 子查询无匹配结果：两个SELECT子查询的WHERE条件和传入的df1.iloc[0, 0]、df1.iloc[0, 1]没有在TABLE_TWO、TABLE_THREE中匹配到对应记录，子查询返回null值，插入到有非空约束的字段时触发报错。
• 字段名拼写错误：错误提示违规的字段是LOT_ID_ONE，但你INSERT语句中写的字段名是LT_ID_ONE，有可能是把TABLE_ONE的实际字段名拼写错误，差了字母O。
• 大小写匹配问题：PostgreSQL中被双引号包裹的表名、字段名会严格区分大小写，需要确认你写的表名、字段名的大小写和数据库中的实际定义完全一致。
• 关联数据未提交：如果TABLE_TWO、TABLE_THREE的匹配记录是你在同个数据库连接中刚刚插入还未提交的，当前查询无法读到未提交的数据，也会导致子查询返回空。

第一步：定位根因 #

先把df1.iloc[0, 0]、df1.iloc[0, 1]两个值取出来，直接在数据库中执行下面的SQL，确认是否有返回结果：

SELECT "LT_ID_ONE" FROM "Database"."TABLE_TWO" WHERE "LT_NUM_ONE" = '替换为你取到的第一个值';
SELECT "LT_ID_TWO" FROM "Database"."TABLE_THREE" WHERE "LT_NUM_TWO" = '替换为你取到的第二个值';

如果没有返回结果，先核对传入值的类型、格式是否和字段定义匹配，比如是否存在数值/字符串类型不匹配、值带多余空格、特殊字符的问题。
同时执行\d "Database"."TABLE_ONE"确认表的实际字段名，和INSERT语句中的字段名保持一致。

第二步：优化代码实现 #

方案1：先查询校验再插入，提前拦截空值问题 #

cur = con.cursor()

# 校验第一个ID是否存在
cur.execute('SELECT "LT_ID_ONE" FROM "Database"."TABLE_TWO" WHERE "LT_NUM_ONE" = %s', (df1.iloc[0, 0],))
res1 = cur.fetchone()
if not res1:
    raise ValueError(f"LT_NUM_ONE={df1.iloc[0, 0]} 在TABLE_TWO中无匹配记录")
lt_id_one = res1[0]

# 校验第二个ID是否存在
cur.execute('SELECT "LT_ID_TWO" FROM "Database"."TABLE_THREE" WHERE "LT_NUM_TWO" = %s', (df1.iloc[0, 1],))
res2 = cur.fetchone()
if not res2:
    raise ValueError(f"LT_NUM_TWO={df1.iloc[0, 1]} 在TABLE_THREE中无匹配记录")
lt_id_two = res2[0]

# 执行插入
cur.execute('INSERT INTO "Database"."TABLE_ONE" ("LT_ID_ONE", "LT_ID_TWO") VALUES (%s, %s)', (lt_id_one, lt_id_two))
con.commit()

方案2：改用INSERT ... SELECT语法，避免空值报错 #

cur = con.cursor()
db_insert = """INSERT INTO "Database"."TABLE_ONE" ("LT_ID_ONE", "LT_ID_TWO")
SELECT t2."LT_ID_ONE", t3."LT_ID_TWO"
FROM "Database"."TABLE_TWO" t2
CROSS JOIN "Database"."TABLE_THREE" t3
WHERE t2."LT_NUM_ONE" = %s AND t3."LT_NUM_TWO" = %s
"""
insert_values = (df1.iloc[0, 0], df1.iloc[0, 1])
cur.execute(db_insert, insert_values)
con.commit()

# 可通过rowcount判断插入结果，返回1为成功，返回0为无匹配记录未插入
if cur.rowcount == 0:
    print("无匹配关联记录，未插入数据")

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