Search code examples
phppythondjangodjango-viewsdjango-database

USE Raw SQL in Django

I recently migrated from PHP to django. I am creating a project which works on django.. I am used to write custom sql in php and hence i want to use raw() to filter results from my database in django.

However i am not able to fully understand how django works..

Please find my code below.

Currently i am receiving the below mentioned output

[('11677795635',), ('12345',)]

I want to use some for loop and print results in the below mentioned format.

  • 11677795635
  • 12345

    • can you please help me on how to for loop in django ..

    In PHP,
    the same was possible by 

    $query=mysql_query("SELECT  abcdashboard_customerinfo.customerbuyingid FROM abcdashboard_customerinfo WHERE abcdashboard_customerinfo.customerbuyingid in (select DISTINCT abcdashboard_orders.customerid from abcdashboard_orders)");
$queryrun=mysql_num_rows($query);    
for ($f=0; $f <$queryrun; $f++)
    { 
       ${'customer'.$f}=mysql_result($query,$f, 'customerbuyingid');
       echo ${'customer'.$f};
     }

    models.py

    class customerinfo(models.Model):
    customerbuyingid = models.CharField(max_length=300, default='invalid customer id in database')
    customername = models.CharField(max_length=300, default='invalid customer name in database')
    customerphonenumber = models.CharField(max_length=12, default='0000000000')
    customermailid= models.CharField(max_length=80, default='[email protected]')

class orders(models.Model):
    orderid = models.CharField(max_length=200)
    orderstatus = models.CharField(max_length=10)
    externalpurchaseid = models.CharField(max_length=100)
    externalstatus = models.CharField(max_length=100)
    customerid = models.CharField(max_length=100)
    ops = models.CharField(max_length=100)
    orderdate = models.DateField()
    updatedon = models.DateTimeField(default=timezone.now)

    views.py

    def index(request):
    all_customer = customerinfo.objects.all()
    cursor = connection.cursor()
    cursor.execute('''SELECT abcdashboard_customerinfo.customerbuyingid FROM abcdashboard_customerinfo WHERE abcdashboard_customerinfo.customerbuyingid in (select DISTINCT abcdashboard_orders.customerid from abcdashboard_orders) ''')
    row = cursor.fetchall()
    print (row)  
    context = {"row": row}
    return render(request, "abcdashboard/index.html", context)

    Index.html

    <html>
<head>
    <title>

    </title>

</head>

<body>
    <ul>
<h2 align="center"> SQL Queries display </align> </h2>

{% block content %}

{{ row }}

{% endblock %}
    </ul>

</body>

</html>
    Solution

  • Just replace:

    in views.py:

    row = cursor.fetchall()
print (row)  
context = {"row": row}

    with:

    ids = []
for row in cursor.fetchall():
    id = row[0]
    ids.append(id)
context = {'rows': ids}
...

    in index.html:

    {{ row }}

    with

    {% for id in rows %}
{{ id }}
{% endfor %}