Need help iterating through each row in SQLite DB to render to html template

Question

I'm using the Django framework with SQLite DB. I'm using SQL queries in my views.py and connecting that data to render on the frontend in my dashboard.html template.

I'm having trouble iterating through each row in my database table 'scrapeddata'. When I view the dashboard, the data being rendered is all bunched up together in one div. I'm hoping to get each row in the database to render in one div each. I tried placing a for loop in my views.py but when I do that, I only get one row from the database rendered on dashboard.html. Any help is appreciated, thank you.

views.py:

def dashboard(request):
    connection = sqlite3.connect('db.sqlite3')
    cursor = connection.cursor()
    
    col1 = '''SELECT companyname FROM scrapeddata ORDER BY companyname ASC'''
    cursor.execute(col1)
    companyname = cursor.fetchall()

    col2 = '''SELECT name FROM scrapeddata'''
    cursor.execute(col2)
    name = cursor.fetchall()

    col3 = '''SELECT portfolio FROM scrapeddata'''
    cursor.execute(col3)
    portfolio = cursor.fetchall()
    
    people_data = []
    
    data = {
        'companyname': companyname, 
        'name': name, 
        'portfolio': portfolio
        }

    people_data.append(data)
        
    connection.close()
        
    if request.user.is_authenticated:
        return render(request, 'dashboard.html', {'people_data':people_data}) 
    else :
        return redirect('/login')

dashboard.html: Here's the for loop in the template.

  {% for data in people_data %}
     <div>
        <p>{{ data.companyname }}</p>
        <p>{{ data.name }}</p>
        <p>{{ data.portfolio }}</p>

     </div>
  {% endfor %}

you're not iterating through the fetched data from the fetchall() calls. If you do that then you should be able to build out a list. Template should work after that — Taylor Cochran
– Taylor Cochran, Commented Sep 29, 2021 at 22:39

Yilmaz · Accepted Answer · 2021-09-29 23:08:17Z

2

THe problem is you have only one item in people_data list:

people_data = []

data = {
    'companyname': companyname, 
    'name': name, 
    'portfolio': portfolio
    }

 people_data.append(data)

you are just appending one dictionary, and each in data dictionary is a list of items. because cursor.fetchAll() returns a list.

Since companyName,name and portfolio are fetched from same table, their length is same. So in views.py

people_data = []

for i in range(len(companyName)):
     data = {
            'companyname': companyname[i], 
             'name': name[i], 
             'portfolio': portfolio[i]
         }
     people_data.append(data)

this should work

answered Sep 29, 2021 at 23:08

Yilmaz

51k19 gold badges226 silver badges278 bronze badges

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

5 Comments

sumiaj Over a year ago

Thank you so much. I appreciate you explaining. This worked perfectly. :)

sumiaj Over a year ago

I just realized that it's not rendering the same data from each row, per each div. It's mixing them. Any recommended fixes? It's saying len() only takes one argument.

Yilmaz Over a year ago

@sumiaj remove this "ORDER BY companyname ASC" from the first col1

sumiaj Over a year ago

Interesting, I had no idea that could affect it. I did this and it got partially fixed. I then had companyname remaining as the one not matching. I fixed this in my scraping code. I had companyname as UNIQUE and changed it to TEXT in my cursor.execute statement. Thanks again for you help @Yilmaz :)

Yilmaz Over a year ago

@sumiaj I hope everything is fully fixed. I have to deserve my accepted answer badge :)

Collectives™ on Stack Overflow

Need help iterating through each row in SQLite DB to render to html template

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related