custom PostgreSQL query execute to django app

Question

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 ?

@HenryWoody that error show me in the page from the browser if I visit this link.any idea ? — Mar
– Mar, Commented Mar 14, 2019 at 23:48
I think there should still be a traceback with the error even in the browser, but if not can you include the traceback from the console/terminal that's running your server? — Henry
– Henry, Commented Mar 14, 2019 at 23:49
Request Method: GET Request URL: 127.0.0.1:8000/data Django Version: 1.11.3 Exception Type: AttributeError Exception Value: 'tuple' object has no attribute '_meta' Exception Location: Python27\Lib\site-packages\django\contrib\gis\serializers\geojson.py in start_object, line 35 if that help you I use this code in views.py — Mar
– Mar, Commented Mar 14, 2019 at 23:53

Henry · Accepted Answer · 2019-03-15 00:28:07Z

1

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:

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.
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.

edited Mar 15, 2019 at 0:28

answered Mar 15, 2019 at 0:06

Henry

15.9k7 gold badges51 silver badges69 bronze badges

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

7 Comments

Mar Over a year ago

not I take this error In order to allow non-dict objects to be serialized set the safe parameter to False in browser. in the line return JsonResponse(data)

Henry Over a year ago

@Mar oh right, since data will be a list, not a dictionary you will have to set safe=False I'll update.

Henry Over a year ago

@Mar Actually changed to HttpResponse but calling json.dumps before sending. The safe=False flag is a little spooky

Mar Over a year ago

you are amazing look to work,but its not geojson but a simple json

Henry Over a year ago

@Mar what database are you using?

|

KangDo · Accepted Answer · 2020-06-18 15:44:48Z

1

    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)

answered Jun 18, 2020 at 15:44

KangDo

799 bronze badges

Comments

Javier Gallego · Accepted Answer · 2021-10-20 00:18:45Z

0

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)

answered Oct 20, 2021 at 0:18

Javier Gallego

1841 silver badge11 bronze badges

Collectives™ on Stack Overflow

custom PostgreSQL query execute to django app

3 Answers 3

7 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related