我尝试在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
3条答案
按热度按时间t40tm48m1#
您可以使用游标运行自定义SQL查询。
有关详细信息,请参阅文档
4si2a6ki2#
我不明白为什么你不能用Django ORM来做这些。应该是这样的:
或者您查找确切的值,删除
__icontains
查找。lawou6xi3#
也许这能帮你省点时间
问题在于原始SQL字符串
具有字符串格式化运算符:
%s
和%a
在其中。因此
raw
方法需要一个params
参数(带有一个元组,表示用什么来替换那些格式化操作符),当元组中的元素数量小于操作符数量时,它抛出一个IndexError
。您需要使用
%%
转义%
,即参考文献:
1.旧的字符串格式
1.将参数传入raw()