CODEWITHSUNDEEP
pythonhtmldatabasesqlitehtml-table
user4888
user4888

Reputation: 101

Python / SQL Not Displaying Connected Database to Table

I have a connected SQL database of flowers(genus,species,comname) populating a table that simply displays that information. I can only get the genus column to populate correctly but not the species / comname columns.

Working code - displays one column correctly:

update.html

<!--this is for the table -->
   <div class="container">
       <div class="row">
           <div class="col">
               <table id="table" border="1">
                   <tr>
                       <th class="cell">Genus</th>
                       <th class="cell">Species</th>
                       <th class="cell">Comname</th>
                   </tr>
                   <!--the next line with the python code works as long as you only want the genus information-->
                   {% for g in genus_update %}
                   <tr>
                       <td class="cell">{{g}}</td>
                       <!--<td class="cell">{{g}}</td>-->
                       <!--<td class="cell">{{c}}</td>-->
                   </tr>
                   {% endfor %}
               </table>
           </div>
           <div class="col">
               <!--the right column so that everything is lined up on the left side-->
           </div>
       </div>
   </div>

Trying to use a for loop for the others breaks the page (not certain why):

{% for s in species_update %}
  <tr>
    <td class="cell">{{s}}</td>
  </tr>
{% endfor %}

{% for c in comname_update %}
  <tr>
    <td class="cell">{{c}}</td>
  </tr>
{% endfor %}

Python.py:

from flask import Flask, render_template, request, g
import sqlite3

app = Flask (__name__)

# conn = sqlite3.connect('flowers.db')
# c = conn.cursor()

DATABASE = 'flowers.db'
def get_db():
   db = getattr(g, '_database', None)
   if db is None:
       db = g._database = sqlite3.connect(DATABASE)
   return db

@app.teardown_appcontext
def close_connection(exception):
   db = getattr(g, '_database', None)
   if db is not None:
       db.close()

@app.route('/')
def index():
   c = get_db().cursor()
   c.execute('SELECT COMNAME FROM FLOWERS')
   all_flowers = c.fetchall()
   return render_template("index.html", all_flowers=all_flowers)

@app.route('/update')
def update():
   c = get_db().cursor()
   # this just gets the data from the db
   c.execute('SELECT COMNAME FROM FLOWERS')
   comname_update = c.fetchall()
   c.execute('SELECT GENUS FROM FLOWERS')
   genus_update = c.fetchall()
   c.execute('SELECT SPECIES FROM FLOWERS')
   species_update = c.fetchall()
   zipped = zip(genus_update, species_update)
   return render_template("update.html", comname_update=comname_update, genus_update=genus_update, species_update=species_update, zipped=zipped)

@app.route('/profile/<name>')
def profile(name):
   return render_template("profile.html", name=name)


if __name__ == "__main__":
   app.run(debug=True)

Solved

Solution

html code:

{% for g, s, c in genus_flowers%}
                   <tr>
                       <td class="cell">{{g}}</td>
                       <td class="cell">{{s}}</td>
                       <td class="cell">{{c}}</td>

                   </tr>
{% endfor %}

python code:

@app.route('/update')
def update():
   c = get_db().cursor()
   # this just gets the data from the db

   c = get_db().cursor()
   c.execute('SELECT GENUS, SPECIES, COMNAME FROM FLOWERS')
   genus_flowers = c.fetchall()
   return render_template("update.html", genus_flowers=genus_flowers)

Upvotes: 0

Views: 140

Answers (2)

Sumukha Pk
Sumukha Pk

Reputation: 124

I'm not sure what exactly might be happening here but since I was in such a situation before I'd suggest you to test whether the data is being obtained from the database first rather than checking it directly for the part where Flask renders it. Ensure existence of data for the query being passed too.

Hope this will help in progress.

Upvotes: 0

Michael Calve
Michael Calve

Reputation: 21

I know in Django, another web framework for python, you have to reference the field in the object, not just the object itself. So if you do a Select *, instead of a Select 'field':

@app.route('/update')
def update():
   c = get_db().cursor()
   # this just gets the data from the db
   c.execute('SELECT * FROM FLOWERS')
   flowers = c.fetchall()
   zipped = zip(genus_update, species_update)
   return render_template("update.html", flowers=flowers, zipped=zipped)

Then you can then do the following:

<!--this is for the table -->
   <div class="container">
       <div class="row">
           <div class="col">
               <table id="table" border="1">
                   <tr>
                       <th class="cell">Genus</th>
                       <th class="cell">Species</th>
                       <th class="cell">Comname</th>
                   </tr>
                   <!--the next line with the python code works as long as you only want the genus information-->
                   {% for f in flowers %}
                   <tr>
                       <td class="cell">{{ f.genus }}</td>
                       <td class="cell">{{ f.species }}</td>
                       <td class="cell">{{ f.comname }}</td>
                   </tr>
                   {% endfor %}
               </table>
           </div>
           <div class="col">
           </div>
       </div>
   </div>

Upvotes: 1

Related Questions