CODEWITHSUNDEEP
pythondjangopostgresqlpython-2.7
Mar
Mar

Reputation: 701

custom PostgreSQL query execute to django app

i have two models with relationship one to many in django app using app using postgres/postgis database. i have create one not easy query in database and pgadmin panel and works correct.

here the query :

select string_agg(distinct app_work.id::text, ', ') AS code_work,string_agg(distinct app_work.stage, ', ') 
AS stage,string_agg(distinct app_work.dfield_work, ', ') AS dfield,app_point.geom  
from app_point, app_work where app_point.id=app_work.point_field_id GROUP BY app_point.id;

now i want to use this query(i need that results from this query) in my django app to create a geojson or json export. i am not sure how convert this query using django method objects and queries(like point.objects.all()) i try to use custom postgres query like this :

models.py

class point(models.Model):
    geom = models.MultiPointField(srid=4326)
    objects = models.GeoManager()

    def __unicode__(self):
        return str(self.id)

    class meta:
        verbose_name_plural="point_info"


class work(models.Model):
        dfield_work = models.CharField(max_length=100,blank=True, null=True)
    stage = models.CharField(max_length=100,blank=True, null=True)
    field1 = models.CharField(max_length=100,blank=True, null=True)
    field2 = models.CharField(max_length=100,blank=True, null=True)
    point_field= models.ForeignKey('point', blank=True, null=True)

    def __unicode__(self):
        return str(self.id)

vews.py

from django.db import connection


def points(request):
    cursor = connection.cursor()
    cursor.execute("""seselect string_agg(distinct app_work.id::text, ', ') AS code_work,string_agg(distinct app_work.stage, ', ') 
AS stage,string_agg(distinct app_work.dfield_work, ', ') AS dfield,app_point.geom  
from app_point, app_work where app_point.id=app_work.point_field_id GROUP BY app_point.id from log_point, log_work where log_point.id=log_work.point_field_id GROUP BY log_point.id""")
    row = cursor.fetchall()
    print row
    data = serialize('geojson', row)
    return HttpResponse(data,content_type='json')

in the print row i take a correct list results

but not working and i have this error :

'tuple' object has no attribute '_meta'

any idea how to fix it ?

Upvotes: 1

Views: 1536

Answers (3)

Javier Gallego
Javier Gallego

Reputation: 184

You can use cursor.execute(raw_query) to execute query on django application.

sample_query = 'SELET COUNT(*) FROM operation_084'
cursor = connection.cursor()
cursor.execute(sample_query)

Upvotes: 0

KangDo
KangDo

Reputation: 79

    SELECT "id", "LchMatchedTradeRef", "abc" from operation_091 
    UNION 
    SELECT "id", "LchMatchedTradeRef", '' abc FROM operation_084

when you need to run this query in your Django app, you need to follow like bellow.

    empty_var = "''"
    raw_query = 'SELECT "id", "LchMatchedTradeRef", "abc" FROM operation_091 UNION SELECT "id", "LchMatchedTradeRef", ' + empty_var + ' abc FROM operation_084'

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

Upvotes: 1

Henry
Henry

Reputation: 15732

The Django geojson serialize method expects a queryset (as can be seen from usage here in the docs) rather than a tuple. From the source, we can see that the Serializer(JSONSerializer) class is designed to "Convert a queryset to GeoJSON" and expects objects that it can call ._meta on (i.e. Django models). Thus when you pass a tuple (the output of cursor.fetchall()) to serialize you get the error 'tuple' object has no attribute '_meta'.

Since you are using a raw SQL query there is really no need to use the geojson serializer as it's designed to convert Python objects to JSON. You can instead convert the output of your SQL query to JSON and send that as the response.

Here is an example:

import json
from django.db import connection
from django.http import HttpResponse


query_text = """
SELECT
    string_agg(distinct app_work.id::text, ', ') AS code_work,
    string_agg(distinct app_work.stage, ', ') AS stage,
    string_agg(distinct app_work.dfield_work, ', ') AS dfield,
    app_point.geom
FROM
    app_point, app_work
WHERE
    app_point.id = app_work.point_field_id
GROUP BY
    app_point.id;
"""

def points(request):
    with connection.cursor() as cursor:
        cursor.execute(query_text)
        data = dictfetchall(cursor)

    stringified_data = json.dumps(data)
    return HttpResponse(stringified_data, content_type="application/json")


def dictfetchall(cursor):
    "Return all rows from a cursor as a dict"
    columns = [col[0] for col in cursor.description]
    return [
        dict(zip(columns, row))
        for row in cursor.fetchall()
    ]

(dictfetchall from the the Django SQL docs)

A couple things to note in the above code:

  1. This is fairly important: using a with for the cursor so that the connection is closed when we are done querying the database. You can also close the connection manually, but the with statement handles it for us.

  2. Using the dictfetchall function, we convert the output of the SQL query to a Python dictionary (ready to become JSON). There is no need to use the Django serializer since we are using raw Python datatypes instead of Python Objects or Django Models.

Upvotes: 1

Related Questions