Show in a html template the result of a sql query using django

Question

I want to show in a template, a query result using django and mssql.

Query works fine:

cursor.execute("SELECT DISP FROM INDTIMEN WHERE PAIS_SERV_PRODID = 'VEN_UY_POS' AND AÑO = 2019 AND MES = 10 AND INDIS != 0")
**lista**=cursor.fetchall()

the result of the query in de mssql is = 0.773

but when i write the variable lista in the html tamplate, the browser show [(Decimal('0.773'), )]

how can I do to show only the result 0.773

Answer 1

In your views.py template definition, create a query variable and return it like this:

def your_template(request):
    cursor = connection.cursor()
    try:
        cursor.execute("SELECT DISP FROM INDTIMEN WHERE PAIS_SERV_PRODID = 'VEN_UY_POS' AND AÑO = 2019 AND MES = 10 AND INDIS != 0")
    finally:
        cursor.close()
    query = cursor.fetchall()
    return render(request, 'folder/template.html',{'query': query})

In your template.html file, use a for loop in order to display the result of the query as html:

    {% for obj in query %}
        <p>{{obj.0}}</p>
    {% endfor %}

Note: If your query displays only one result, you can change the code to this:

def your_template(request):
    cursor = connection.cursor()
    try:
        cursor.execute("SELECT DISP FROM INDTIMEN WHERE PAIS_SERV_PRODID = 'VEN_UY_POS' AND AÑO = 2019 AND MES = 10 AND INDIS != 0")
    finally:
        cursor.close()
    query = cursor.fetchone()
    return render(request, 'folder/template.html',{'query': query})

In your template.html file, display the result of the query as html:

        <p>{{obj.0}}</p>

Answer 2

Django cursor.fetchall() returns a list of tuples. No names, etc. Just rows with the same columns you asked for. As stated in the documentation:

https://docs.djangoproject.com/en/2.2/topics/db/sql/#executing-custom-sql-directly

So, in your template, you'll want to iterate all the rows and display the first item in the tuple for each row. That will give you "DISP". In this example, I assume your view has passed a context variable named lista which holds the result of your fetchall():

<ol>    
  {% for row in lista %}
    <li>{{ row.0 }}</li>
  {% endfor %}
</ol>

This will display your result as an ordered list on the page.

Answer 3

The reason why cursor.fetchall() is returning [(Decimal('0.773'), )] is because your query may have multiple rows/columns; and it will use tuples to manage rows and columns.

Here, your query has only one row and one column, so you can use:

lista = cursor.fetchall()[0][0]

Answer 4

As fetch_all returns a list you need to access the first position of the list through lista[0] or call fetch_one instead.