如何使用原始SQL插入ForeignKey字段？ [英] How to insert into a ForeignKey field using raw SQL?

问题描述

我有类似以下的表（Django模型定义，并带有一个postgres数据库）：

I've got tables like the following (Django model definition, with a postgres database underlying it):

class Person(models.Model):
    id = models.IntegerField(primary_key=True)
    name = models.CharField(max_length=300)     
class Owner(models.Model):
    id = models.IntegerField()
    person = models.ForeignKey(Person) 

一个Python脚本，用于从CSV文件设置数据库。原始文件列出了具有整数ID和整数个人字段的所有者，该字段映射到Person.id中的整数。

I use a Python script to set up my database from CSV files. The raw files list Owners with an integer id and an integer 'person' field, which maps to the integer in Person.id.

但是，鉴于Owner中的 person列希望使用Person对象，我该如何编写原始SQL字符串以将值插入Owner？

However, given that the 'person' column in Owner expects a Person object, how do I write a raw SQL string to insert value into Owner?

owner_id = 665
person_id = 330
sql_string = 'INSERT INTO owner (id, person) VALUES (' +
sql_string += owner_id + ', ' + ???? + ');'

推荐答案

您没有说为什么需要在原始SQL中执行此操作。而且我也不明白为什么在调用PK字段时在SQL中使用 structidx 和 person id - person 的基础列名称为 person_id 。因此，您的代码应该是：

You don't say why you need to do this in raw SQL. And I also don't understand why you're using structidx and person in the SQL when your PK field is called id - and the underlying column name for person is person_id. So your code should be:

sql_string = "INSERT INTO owner (`id`, `person_id`) VALUES (%s, %s)"
cursor = connection.cursor()
cursor.execute(sql_string, (665, 330))

请注意，使用Python db-api的引号功能（如我在此处）始终是避免SQL注入攻击的好习惯。

Note that it's always good practice to use the Python db-api's quoting functionality, as I have here, to avoid SQL injection attacks.

这篇关于如何使用原始SQL插入ForeignKey字段？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！