CODEWITHSUNDEEP
jsondjangodjango-serializer
andriy
andriy

Reputation: 29

How to serialize sql query to json?

I'm trying to serialize sql query to json:

cursor.execute("""SELECT b.tonode, n.name, b.fromnode FROM zusers u JOIN znode n
                                                    ON u.code = n.created_by
                                                JOIN zbond b
                                                    ON b.code = n.code
                                                WHERE u.userid = %s""", [user_id] )

data = serializers.serialize('json', rows, fields = ('id', 'name', 'parent'))

But it doesn't work. Is it possible to serialize not model's objects, but sql query?

Upvotes: 1

Views: 10296

Answers (5)

Animik Kur
Animik Kur

Reputation: 1

try this code

def cont(request):
   try:
      get = cursor.execute('SELECT id, number FROM tblContract')
      qs = get.fetchall()
      data = []
      for obj in qs:
          item = {
                'id': obj.id,
                'number': obj.number 
                }
          data.append(item)
      return JsonResponse({'data': data})
   except:
      return JsonResponse({'data': 'errorrr'})  
   finally:
      con.close

Upvotes: 0

Usha K
Usha K

Reputation: 1

cursor = connection.cursor()
cursor.execute("""SELECT b.tonode, n.name, b.fromnode FROM zusers u JOIN znode n ON u.code = n.created_by JOIN zbond b ON b.code = n.code WHERE u.userid = %s""", [user_id])
rows = cursor.fetchall()
result = []
keys = ('id','name','parent',)
for row in rows:
    result.append(dict(zip(keys,row)))
json_data = json.dumps(result)
return HttpResponse(json_data, content_type="application/json")

Upvotes: 0

Suresha Karadi
Suresha Karadi

Reputation: 1

try this code

    cursor = connection.cursor()
    cursor.execute("""SELECT b.tonode, n.name, b.fromnode FROM zusers u JOIN znode n ON u.code = n.created_by JOIN zbond b ON b.code = n.code WHERE u.userid = %s""", [user_id])
    cursor.fetchall()
    json_data = dictfetchall(cursor)
    return HttpResponse(json_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()
     ]

Upvotes: 0

Saeed Ghareh Daghi
Saeed Ghareh Daghi

Reputation: 1205

try this code:

cursor = connection.cursor()
cursor.execute("""SELECT b.tonode, n.name, b.fromnode FROM zusers u JOIN znode n ON u.code = n.created_by JOIN zbond b ON b.code = n.code WHERE u.userid = %s""", [user_id])
rows = cursor.fetchall()
result = []
keys = ('id','name','parent',)
for row in rows:
    result.append(dict(zip(keys,row)))
json_data = json.dumps(result)
return HttpResponse(json_data, content_type="application/json")

Upvotes: 3

Hasan Ramezani
Hasan Ramezani

Reputation: 5194

base on doc:

Actually, the second argument can be any iterator that yields Django model instances, but it’ll almost always be a QuerySet

try this:

query = """SELECT b.tonode, n.name, b.fromnode FROM zusers u JOIN znode n
                                                    ON u.code = n.created_by
                                                JOIN zbond b
                                                    ON b.code = n.code
                                                WHERE u.userid = %s"""%user_id

data = serializers.serialize('json', YourModel.objects.raw(query), fields=('id', 'name', 'parent'))

Upvotes: 2

Related Questions