CODEWITHSUNDEEP
djangopostgresqldjango-orm
Mihael Waschl
Mihael Waschl

Reputation: 350

Execute PostgreSql query in django

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

Upvotes: 0

Views: 8358

Answers (3)

felix
felix

Reputation: 486

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:

  1. Old String formatting
  2. Passing parameters into raw()

Upvotes: 1

shafikshaon
shafikshaon

Reputation: 6404

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

Upvotes: 3

Higor Rossato
Higor Rossato

Reputation: 2046

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.

Upvotes: 2

Related Questions