Python操作PostgreSQL写入外键数据报错：列number_circulation不存在

解析数据后尝试存入两个关联表，CirculationData每条记录对应EventData的15条记录，CirculationData数据插入成功，但EventData插入时触发报错。

模型代码（models.py） #

class CirculationData(models.Model):
    number_circulation = models.BigIntegerField(primary_key=True)
    status = models.CharField(blank=True, null=True)
    datetime = models.DateTimeField(blank=True, null=True)


class EventData(models.Model):
    circulation = models.ForeignKey(CirculationData, 
    on_delete=models.CASCADE, related_name="events")
    number = models.IntegerField()
    match = models.CharField(blank=True, null=True)
    liga = models.CharField(blank=True, null=True)
    score = models.CharField(blank=True, null=True)
    result = models.CharField(blank=True, null=True)

插入脚本代码 #

<not significant start of script>
number_circulation = int(data_1.find_all("td")[1].text)
status = data_2.find("span").text
datetime = translate_in_datetime(data_1.find_all("td")[5].text)

data_1 = [number_circulation, status, datetime]

cursor.execute(
    "INSERT INTO app_circulationdata(number_circulation, status, datetime) VALUES (%s, %s, %s)", data_1)
conn.commit()

for value in soup.find_all("tr", class_="line"):
    number = int(value.find("td").text)
    match = value.find("div", class_="text-nowrap font-weight-bold").text.strip()
    liga = value.find("p").text
    score = value.find_all("td")[6].text
    result = value.find_all("td")[7].text

    cursor.execute(
    "INSERT INTO app_eventdata (number, match, liga, score, result, circulation_id)"
           " VALUES (number, match, liga, score, result, number_circulation)")

    conn.commit()

cursor.close()
conn.close()

错误信息 #

Traceback (most recent call last):
File "/", line 61, in 
cursor.execute(
psycopg2.errors.UndefinedColumn: column "number_circulation" does not exist
LINE 1: ...nt_pool_2, percent_book_2, score, result) VALUES (number_cir...

问题核心是：直接在SQL语句中写Python变量名，PostgreSQL会将其识别为数据库列名，而非Python变量的实际值。

方法1：使用参数化查询（推荐，避免SQL注入） #

和插入CirculationData的逻辑保持一致，用%s作为占位符，将变量值作为参数传入：

cursor.execute(
    "INSERT INTO app_eventdata (number, match, liga, score, result, circulation_id)"
    " VALUES (%s, %s, %s, %s, %s, %s)",
    (number, match, liga, score, result, number_circulation)
)

方法2：字符串格式化（不推荐，存在风险） #

若必须用字符串拼接，需将变量值正确嵌入SQL语句，但此方式易因字段含特殊字符报错，且有SQL注入风险：

cursor.execute(
    f"INSERT INTO app_eventdata (number, match, liga, score, result, circulation_id)"
    f" VALUES ({number}, '{match}', '{liga}', '{score}', '{result}', {number_circulation})"
)

额外优化：批量提交提升性能 #

循环内每次调用conn.commit()会降低效率，可将所有EventData插入操作完成后统一提交：

for value in soup.find_all("tr", class_="line"):
    # 提取数据逻辑
    cursor.execute(参数化SQL语句, 参数列表)
# 循环结束后一次性提交
conn.commit()

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