Execute PostgreSql query in django

Question

I am trying to execute PostgreSQL query in Djnago but I have some problems. I would like to execute this query:

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

It works if I execute it in pgAdmin query editor, but when I try to execute it with django it does not work. I tried something like this:

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)

This is the traceback

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

Any idea what I am doing wrong and how can I transform this PostgreSQL query to Django ORM query?

from django.db import models from django.utils import timezone

My 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

i don't think there is anything wrong in query

rahul.m
– rahul.m

2019-05-21 13:17:50 +00:00
Commented May 21, 2019 at 13:17 — rahul.m
– rahul.m, Commented May 21, 2019 at 13:17
post your model code

rahul.m
– rahul.m

2019-05-21 13:18:10 +00:00
Commented May 21, 2019 at 13:18 — rahul.m
– rahul.m, Commented May 21, 2019 at 13:18
I add it to my question @c.grey

Mihael Waschl
– Mihael Waschl

2019-05-21 13:19:48 +00:00
Commented May 21, 2019 at 13:19 — Mihael Waschl
– Mihael Waschl, Commented May 21, 2019 at 13:19

shafikshaon · Accepted Answer · 2019-05-22 07:28:13Z

3

You can run custom SQL query using cursor. Like this

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()

For more info see the docs

edited May 22, 2019 at 7:28

answered May 21, 2019 at 13:30

shafikshaon

6,5025 gold badges36 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Higor Rossato · Accepted Answer · 2019-05-21 13:42:28Z

2

I don't see why you can't do that using Django ORM. Should be something like:

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

or you look for the exact values removing the __icontains lookup.

answered May 21, 2019 at 13:42

Higor Rossato

2,0461 gold badge12 silver badges15 bronze badges

Comments

felix · Accepted Answer · 2023-02-02 16:49:39Z

maybe this saves someone a few minutes

The problem is the raw SQL string

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

has string formatting operators: %s and %a in it.

So the raw method is expecting a params argument (with a tuple of what to replace those formatting operators with). When the amount of elements on the tuple is smaller than the amount of operators, it throws an IndexError.

You need to escape % with %%, i.e.

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

References:

Collectives™ on Stack Overflow

Execute PostgreSql query in django

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related