在django中执行PostgreSQL查询

vhmi4jdf  于 2023-02-04  发布在  PostgreSQL
关注(0)|答案(3)|浏览(144)

我尝试在Djnago中执行PostgreSQL查询,但遇到一些问题。我想执行此查询:

SELECT * FROM data_affectedproductversion 
WHERE vendor_name LIKE 'cisco' 
AND product_name LIKE 'adaptive%security%appliance%' 
AND version='9.1(7)16'

如果我在pgAdmin查询编辑器中执行它,它可以工作,但是当我尝试用django执行它时,它不工作。

results = AffectedProductVersion.objects.raw("SELECT * FROM data_affectedproductversion WHERE vendor_name LIKE 'cisco' AND product_name LIKE 'adaptive%security%appliance%software' AND version='9.1(7)16';")
for result in results:
    print(result)

这是追溯

Traceback (most recent call last):
  File "<console>", line 1, in <module>
  File "venv\lib\site-packages\django\db\models\query.py", line 1339, in __iter__
    self._fetch_all()
  File "venv\lib\site-packages\django\db\models\query.py", line 1326, in _fetch_all
    self._result_cache = list(self.iterator())
  File "venv\lib\site-packages\django\db\models\query.py", line 1349, in iterator
    query = iter(self.query)
  File "venv\lib\site-packages\django\db\models\sql\query.py", line 96, in __iter__
    self._execute_query()
  File "venv\lib\site-packages\django\db\models\sql\query.py", line 130, in _execute_query
    self.cursor.execute(self.sql, params)
  File "venv\lib\site-packages\django\db\backends\utils.py", line 100, in execute
    return super().execute(sql, params)
  File "venv\lib\site-packages\django\db\backends\utils.py", line 68, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "venv\lib\site-packages\django\db\backends\utils.py", line 77, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "venv\lib\site-packages\django\db\backends\utils.py", line 85, in _execute
    return self.cursor.execute(sql, params)
IndexError: tuple index out of range

你知道我做错了什么吗?我该如何将这个PostgreSQL查询转换为Django ORM查询?
从django.db导入模型从django.utils导入时区
我的网站models.py:

class DataNist(models.Model):
    description = models.TextField()
    file  =models.CharField(max_length=50)
    date = models.DateTimeField(default=timezone.now)
    severity = models.CharField(max_length=10)
    exp_score = models.DecimalField(null=True, max_digits=5, decimal_places=1)
    impact_score = models.DecimalField(null=True, max_digits=5, decimal_places=1)
    cvss_score = models.DecimalField(null=True, max_digits=5, decimal_places=1)
    published_date = models.IntegerField()
    last_modified = models.IntegerField()
    cve = models.CharField(max_length=30)
    cve_url = models.CharField(max_length=1000)

    def __str__(self):
        return self.file

    class Meta:
        verbose_name_plural = 'Ranljivosti'

class AffectedProductVersion(models.Model):
    data = models.ForeignKey(DataNist, on_delete=models.CASCADE)
    vendor_name = models.CharField(max_length=100)
    product_name = models.CharField(max_length=100)
    version = models.CharField(max_length=150)

    class Meta:
        index_together = (
            ('vendor_name', 'product_name', 'version')
        )

    def __str__(self):
        return self.vendor_name + '-' + self.product_name
t40tm48m

t40tm48m1#

您可以使用游标运行自定义SQL查询。

from django.db import connection
raw_query = "SELECT * FROM data_affectedproductversion 
WHERE vendor_name LIKE 'cisco' 
AND product_name LIKE 'adaptive%security%appliance%' 
AND version='9.1(7)16'"

cursor = connection.cursor()
cursor.execute(raw_query)
cursor.fetchall()

有关详细信息,请参阅文档

4si2a6ki

4si2a6ki2#

我不明白为什么你不能用Django ORM来做这些。应该是这样的:

results = AffectedProductVersion.objects.filter(vendor_name__icontains='cisco', product_name__icontains='adaptive%security%appliance%', version__icontains='9.1(7)16')

或者您查找确切的值,删除__icontains查找。

lawou6xi

lawou6xi3#

也许这能帮你省点时间
问题在于原始SQL字符串

"SELECT * FROM data_affectedproductversion WHERE vendor_name LIKE 'cisco' AND product_name LIKE 'adaptive%security%appliance%software' AND version='9.1(7)16';"

具有字符串格式化运算符:%s%a在其中。
因此raw方法需要一个params参数(带有一个元组,表示用什么来替换那些格式化操作符),当元组中的元素数量小于操作符数量时,它抛出一个IndexError
您需要使用%%转义%,即

"SELECT * FROM data_affectedproductversion WHERE vendor_name LIKE 'cisco' AND product_name LIKE 'adaptive%%security%%appliance%%software' AND version='9.1(7)16';"

参考文献

1.旧的字符串格式
1.将参数传入raw()

相关问题