How to get a column of a specific row from an existing SQLite Database to use in a Flask application?

Question

I am developing a Flask application and I have a webpage where I want to display an existing SQLite Database table I have.

Here is my python file:

import random
from flask import Flask, redirect, render_template, request, session
import sys; sys.path
import sqlite3
import pandas as pd 

app = Flask(__name__)

sql_connect = sqlite3.connect('questions.sqlite3', check_same_thread = False)

cursor = sql_connect.cursor()

@app.route("/database")
def database():
    MCQTable = cursor.execute("SELECT * FROM MCQ;").fetchall()
    return render_template("database.html", MCQTable = MCQTable)

Here is my html code in /database route, where my columns in my table are "question", "ChoiceA", "ChoiceB", "ChoiceC", "ChoiceD", "CorrectAnswer":

<table style="width: 100%">
        <tr>
            <th>Question</th>
            <th>ChoiceA</th>
            <th>ChoiceB</th>
            <th>ChoiceC</th>
            <th>ChoiceD</th>
            <th>Correct Answer</th>
        </tr>
        {% for row in MCQTable %}
        <tr>
            <th>{{ row["question"] }}</th>
            <th>{{ row["ChoiceA"] }}</th>
            <th>{{ row["ChoiceB"] }}</th>
            <th>{{ row["ChoiceC"] }}</th>
            <th>{{ row["ChoiceD"] }}</th>
            <th>{{ row["CorrectAnswer"] }}</th>
        </tr>
        {% endfor %}
    </table>

However, when I did a test, the row["column"] doesn't work. I know that there are no problems in my SQL table because I did a test before where I selected a column instead of the whole table in my python and it worked fine. However, I want to do it this way as I think it is more efficient.

DinoCoderSaurus · Accepted Answer · 2021-03-08 12:52:55Z

1

fetchall returns a list of tuples, not dictionaries, therefore there are no "keys". Consider using the built-in row factory so you can access columns by name.

answered Mar 8, 2021 at 12:52

DinoCoderSaurus

6,5502 gold badges13 silver badges17 bronze badges

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

1 Comment

Aditya Shivapooja Over a year ago

Ok thanks, I didn't know fetchall returns a list of tuples. I ended up replacing row["question"] with row [1] and it worked from accessing the item in the tuple. Thanks!

Andrew Clark · Accepted Answer · 2021-03-08 04:25:02Z

0

A popular solution is to use SQLAlchemy. If you use SQLAlchemy in your project, you can do things such as:

data_selected = MCQ.query.filter_by(*insert conditional statement here in order to find the row you care about*-> i.e.column_name='XXX').first()

Or you can also do .all() if you want to select all the results

answered Mar 8, 2021 at 4:25

Andrew Clark

8807 silver badges13 bronze badges

1 Comment

Andrew Clark Over a year ago

Did this answer help you?

Collectives™ on Stack Overflow

How to get a column of a specific row from an existing SQLite Database to use in a Flask application?

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related