How to create Django context from dictionaries made from Postgres database

Question

I'm new to Django. I'm using Mezzanine 4.2.3 (Django 1.10.8 under the hood according to requirements.txt). I have a Postgres database of details about movies. I want to display 10 movies on a page.

I tried asking about this method but couldn't get a suitable solution, so now I'm trying to to write my own SQL query. But, I don't know how to create the context.

In the code below, I first randomly collect ten countries from a list of countries. Next, I iterate over the list and use each country to acquire a row in a Postgres database. Then, I make a dictionary from each row and become stuck at trying to make the context for the template.

import psycopg2
from psycopg2 import sql
from .countries import countries # Just a Python list of countries
import random

class MoviesListView(generic.ListView):
    model = Movies

    connection = psycopg2.connect([DB DETAILS])
    c = connection.cursor()

    def get_context_data(self, **kwargs):

        random_countries = []
        while len(random_countries) < 10:
            choice = random.choice(countries)
            if choice not in random_countries:
                random_countries.append(choice.lower())
            else:
                pass

        for country in random_countries:

            get_rows = "SELECT * FROM movies WHERE LOWER(production_countries) LIKE %s LIMIT 1;"

            c.execute(get_rows, ('%' + country + '%',))
            rows = c.fetchall()

            for i in rows:
                row_dict = {}
                row_dict['id'] = i[0]
                row_dict['original_title'] = i[3]
                row_dict['release_date'] = i[6]
                row_dict['production_countries'] = i[8]

                # Point at which I'm stuck. 
                # Context().push(row_dict)    
                # return Movies.objects.raw(get_rows, ('%' + country + '%',))

    template_name = r"movies_crud/movies_list.html"

I read the Django documentation on Context() and I thought either Context().push() or Context.update() would help create the context. But it's not working out.

I also tried .raw() but it won't work because I'm trying to generate the context dynamically with a for loop.

How do we make a Django context in this case?

Update: To give an idea of what I'm trying to do, the template looks like this:

{% for object in object_list %}
    {{ object.title }}
{% endfor %}

It displays 10 movie titles.

PS: I worked on this problem for 16 hours straight and read what I could before asking this question. So, it's a minor thing but, it's not nice that someone drops a downvote without leaving a single word to explain it.

But each value such as original_title, release_date, will be replaced. Actually I am not sure this is what you want — Lemayzeur, Feb 22 '18 at 04:15

JPG · Accepted Answer · 2018-02-22T05:14:27.607

try this,

def get_context_data(self, **kwargs):
    random_countries = []
    while len(random_countries) < 10:
        choice = random.choice(countries)
        if choice not in random_countries:
            random_countries.append(choice.lower())
        else:
            pass

    result_list = []  # updated here <<
    for country in random_countries:

        get_rows = "SELECT * FROM movies WHERE LOWER(production_countries) LIKE %s LIMIT 1;"

        c.execute(get_rows, ('%' + country + '%',))
        rows = c.fetchall()

        for i in rows:
            row_dict = {}
            row_dict['id'] = i[0]
            row_dict['original_title'] = i[3]
            row_dict['release_date'] = i[6]
            row_dict['production_countries'] = i[8]
            result_list.append(row_dict)  # updated here <<
    return result_list  # updated here <<

UPDATE
I think get_context_data() method returns dict object while I tried to return a list object. That may be the reason why the exception occurred.So update the get_context_data() as below,

def get_context_data(self, **kwargs):
    random_countries = []
    while len(random_countries) < 10:
        choice = random.choice(countries)
        if choice not in random_countries:
            random_countries.append(choice.lower())
        else:
            pass

    result_list = []
    for country in random_countries:

        get_rows = "SELECT * FROM movies WHERE LOWER(production_countries) LIKE %s LIMIT 1;"

        c.execute(get_rows, ('%' + country + '%',))
        rows = c.fetchall()

        for i in rows:
            row_dict = {}
            row_dict['id'] = i[0]
            row_dict['original_title'] = i[3]
            row_dict['release_date'] = i[6]
            row_dict['production_countries'] = i[8]
            result_list.append(row_dict)

    return {"result": result_list}  # UPDATE IS HERE <<

thus, get_context_data() returns a dict as below,

{
    "result": [
    {
        "id":1,
        "original_title":"title 1",
        "release_date":"date",
        "production_countries":"contries"

    },
    {
        "id":2,
        "original_title":"title 2",
        "release_date":"date",
        "production_countries":"contries"

    },
    {
        "id":3,
        "original_title":"title 3",
        "release_date":"date",
        "production_countries":"contries"

    },
    .
    .
    .

    ]

}

Hi. Thanks. But it gives me this error: `dictionary update sequence element #0 has length 11; 2 is required`. — nusantara, Feb 22 '18 at 04:10
Can you show the value of `row_dict` just before the exception occured ? — JPG, Feb 22 '18 at 04:13
The `for` loop ends well and `results_list` is populated properly. But I get the error when I `runserver` and go to the page. The page is just `{% for object in object_list %} {{ object.original_title }} {% endfor %}`. — nusantara, Feb 22 '18 at 04:24
This error raised up because you trying to update `dict` object by using a wrong sequence (`list` or `tuple`) structure — JPG, Feb 22 '18 at 04:28
I read through [this](https://stackoverflow.com/questions/14302248/dictionary-update-sequence-element-0-has-length-3-2-is-required) but I don't know how to adapt it to my case yet. — nusantara, Feb 22 '18 at 04:40
use `return {"result": result_list}` instead of `return result_list` — JPG, Feb 22 '18 at 04:54
This works. Thank you so much! On hindsight this makes a lot of sense. I guess some obvious things still need pointing out. — nusantara, Feb 22 '18 at 13:28

Lemayzeur · Answer 2 · 2018-02-22T12:31:46.210

I'd do it like that: I can send the rows through context() `

def get_context_data(self, **kwargs):

    random_countries = []
    while len(random_countries) < 10:
        choice = random.choice(countries)
        if choice not in random_countries:
            random_countries.append(choice.lower())
        else:
            pass
    objects_list = []
    for country in random_countries:

        get_rows = "SELECT * FROM movies WHERE LOWER(production_countries) LIKE %s LIMIT 1;"

        c.execute(get_rows, ('%' + country + '%',))
        rows = c.fetchall()

        for row in rows:
            object_list.append(row)
    return { "objects_list":objects_list }

Just a tag filter to get the given ID:

from django import template

register = template.Library()

@register.filter
def get(obj,i):
    return obj[i]

In your template, you can do so:

{% for row in objects_list %}
    {% for i in row %}
        <label>id</label> {{i|get:0}}
        <label>original_title</label> {{i|get:3}}
        <label>release_date</label>  {{i|get:6}}
        <label>production_countries</label> {{i|get:8}}
    {% endfor %}
{% endfor %}

`rows` meaning `rows = c.fetchall()`? Does that mean I place `context = { "rows":rows }` within the `for` loop? If that's the case, it will only contain one row, right? — nusantara, Feb 22 '18 at 04:35
Hi. This looks like it could work but I haven't explored the tag filter fully. I'm going with Jerin's reply below because it works right off the bat at the moment. I'll compare the two approaches later. Thanks so much for replying! — nusantara, Feb 22 '18 at 13:27

How to create Django context from dictionaries made from Postgres database

2 Answers2