A flask project - Selected rows from a query don't appear on the webpage

Question

I want to create a database where I want to let the user to select between IDs extracted from MySQL databases.

I am facing a problem that the result doesn't appear on the webpage, I thought it was a problem of font color, but then after assigning the black color to the words on the tag the same problem still appears.

First of all here the app.py file written in python and Flask

from flask import Flask, render_template, request
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy import create_engine

app = Flask(__name__)

@app.route("/")
def index():

    dialect = "mysql"
    username = "root"
    psw = ""
    host="localhost"
    dbname = "cyclic_championship"

    engine = create_engine(f"{dialect}://{username}:{psw}@{host}/{dbname}")
    
    try:
        con = engine.connect()
        query1 = "SELECT CID FROM CYCLIST"
        query2 = "SELECT SID FROM STAGE"
        result1 = con.execute(query1)
        result2 = con.execute(query2)
        con.close()
        return render_template("index.html", rows=result1,rowss=result2)
    except SQLAlchemyError as e:
        error = str(e.__dict__['orig'])
        return render_template('error.html', error_message=error)

app.run(debug=False, port=5001)

then here you can find the file populateDB.sql that I made sure to run before run app.py and I tested the connection to the databases and everything sounds fine.

SET default_storage_engine=InnoDB;
SET FOREIGN_KEY_CHECKS=1;


USE cyclic_championship;


-- insert data values

START TRANSACTION;

INSERT INTO TEAM VALUES (20, 'Milano',1901, 'Milano');
INSERT INTO TEAM VALUES (21, 'Fiorentina',1975, ' Napoli');
INSERT INTO TEAM VALUES (22, 'Torino',1950, 'Torino');

INSERT INTO CYCLIST VALUES (1, 'Vespucci','Tarantino', 'Italy',20,1917);
INSERT INTO CYCLIST VALUES (2, 'Luca','Spaventa', 'Italy',20,1919);

INSERT INTO CYCLIST VALUES (3, 'Rocco','Soficcini', 'Germany',21,1944);
INSERT INTO CYCLIST VALUES (4, 'Guiseppe','Amaro', 'Germany',21,1946);

INSERT INTO CYCLIST VALUES (5, 'Luciano','Dalla', 'Spain',22,1930);
INSERT INTO CYCLIST VALUES (6, 'Tuto','Cutogno', 'Spain',22,1931);

INSERT INTO STAGE VALUES (1,1,'Bari','Napoli',25000,25,7);
INSERT INTO STAGE VALUES (2,1,'Torino','Milano',30000,30,6);
INSERT INTO STAGE VALUES (3,2,'Venezia','Bologna',17000,35,8);
INSERT INTO STAGE VALUES (4,2,'Catania','Calabria',16000,40,9);

INSERT INTO INDIVIDUAL_RANKING VALUES (1,1,1,1);
INSERT INTO INDIVIDUAL_RANKING VALUES (2,1,1,2);
INSERT INTO INDIVIDUAL_RANKING VALUES (3,3,2,1);
INSERT INTO INDIVIDUAL_RANKING VALUES (4,3,2,2);
        
COMMIT;

Lastly, I will share with you a screenshot and the code of index.html

Screenshot of index.html

the code of index.html before running the script:

<html>
<head>
<title>Hello World</title>
</head>
<body>
<h1 style="text-align:center"> Cyclist Position by stage</h1>

<form action="/">

    <table style="border:0px solid black;margin-left:auto;margin-right:auto;background-color: lightgray;width: 350px;height: 250px;">
    <tr >
        <td style="text-align:center"><label for="cyclist" >Cyclist Id:</label></td>
    
    </tr>
    <tr>
    <td style="text-align:center;">
        <select title="cyclist" id="cyclist" name="cyclist" style="width: 225px;">
            {% for row in rows %}
                <option value="{{ row['cyclist'] }}" style="color:#000000">{{ row['cyclist'] }}</option>
            {% endfor %}
        </select>
    </td>
    </tr>
    <tr>
    <td style="text-align:center"><label for="stage">Stage Id:</label></td>
    </tr>
    <tr>
    <td style="text-align:center">
        <select title="stage" id="stage" name="stage" style="width: 225px;color: black;">
            {% for x in rowss %}
                <option value="{{ x['stage'] }}" style="color: black;">{{ x['stage'] }}</option>
            {% endfor %}
        </select>
    </td>
    </tr>
    <tr>
    <td style="text-align:center"><input type="submit" value="Send" style="background-color:lightgreen;width: 225px;border: 1px;"></td>
    </tr>
  </form>
</table>


</body>
</html>

the content of index.html after running app.py and test it.

<html><head>
<title>Hello World</title>
</head>
<body data-new-gr-c-s-check-loaded="14.1062.0" data-gr-ext-installed="">
<h1 style="text-align:center"> Cyclist Position by stage</h1>

<form action="/">

    <table style="border:0px solid black;margin-left:auto;margin-right:auto;background-color: lightgray;width: 350px;height: 250px;">
    <tbody><tr>
        <td style="text-align:center"><label for="cyclist">Cyclist Id:</label></td>
    
    </tr>
    <tr>
    <td style="text-align:center;">
        <select title="cyclist" id="cyclist" name="cyclist" style="width: 225px;">
            
                <option value="" style="color:#000000"></option>
            
                <option value="" style="color:#000000"></option>
            
                <option value="" style="color:#000000"></option>
            
                <option value="" style="color:#000000"></option>
            
                <option value="" style="color:#000000"></option>
            
                <option value="" style="color:#000000"></option>
            
        </select>
    </td>
    </tr>
    <tr>
    <td style="text-align:center"><label for="stage">Stage Id:</label></td>
    </tr>
    <tr>
    <td style="text-align:center">
        <select title="stage" id="stage" name="stage" style="width: 225px;color: black;">
            
                <option value="" style="color: black;"></option>
            
                <option value="" style="color: black;"></option>
            
                <option value="" style="color: black;"></option>
            
                <option value="" style="color: black;"></option>
            
        </select>
    </td>
    </tr>
    <tr>
    <td style="text-align:center"><input type="submit" value="Send" style="background-color:lightgreen;width: 225px;border: 1px;"></td>
    </tr>
  
</tbody></table>

As you can see that select tag contains empty items/elements which is something strange!

Kindly I am asking for any help

Thanks in advance!

Try a loop for printing the results in the terminal to check if your data are alright and locate the problem for x in result1: print (x,x.__class__) — , Jun 05 '22 at 17:26
@Empty the result is the following ! (1,) (2,) (3,) (4,) (5,) (6,) — Ali H., Jun 05 '22 at 17:42
Perhaps you should try either `x[0]` for both loops in the template - because each row in the resultsets will be a single-element tuple, or `x['cid']` / `x['sid']` because you want to use the names of the _columns_, not the _tables_. — snakecharmerb, Jun 05 '22 at 18:24

score 0 · Answer 1 · answered Jun 05 '22 at 18:07

From your reply Jinja2 seems unable to render your SQL Result objects , I would refer you to this

How to convert SQLAlchemy row object to a Python dict?

Converting your results to dicts may solve your problem.

return render_template("index.html", rows=result1.__dict__,rowss=result2.__dict__)

A flask project - Selected rows from a query don't appear on the webpage

1 Answers1