pythonflasksqlite3-python

Flask Select query returning a cursor object or an empty list instead of a tuple of values


Here is my code in app.py:

from flask import Flask, render_template, request, g

import sys
import sqlite3
import os

current_directory = os.path.dirname(os.path.abspath(__file__))

app = Flask(__name__)

@app.route('/student')
def student():
    connection = sqlite3.connect(current_directory + "/sss.db")
    cursor = connection.cursor()
    Lastname='Volley'
    Firstname='Vlad'
    result = cursor.execute("SELECT * FROM Students WHERE Firstname=? AND Lastname=?", (Firstname,Lastname))
    result.fetchone()
    res = list(result)
    connection.commit()
    connection.close()
    return render_template('student.html', res=res)

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

Additionally - in student html file - I'm trying to display the data just to ensure its there.
As you can see from the app file I'm converting the results from the query to a list. That way I can visualize it on the student html page, however it always returns an empty list.
Data is in the sqlite3 database.

Students HTML Code Snippet below:

 <div>
        <div class="container mr-t">
          <h1>Is this You?</h1>
          <div class="container">
              <p>{{res}}</p>
          </div>
          <form id="emailForm">
              <button type="submit">Find Email and Password</button>
              <button style="background-color: red;" type="submit">Clear</button>
          </form>
          <p id="result"></p>
        </div>
</div>

Below is an image of my db file: Here is a image of my database showing the data inside


Solution

  • The main issue is in this line result = cursor.execute("SELECT * FROM Students WHERE Firstname=? AND Lastname=?", (Firstname,Lastname)), which is why you're getting a cursor object. You don't need to assign the cursor.execute to a variable. We're only defining, inside the cursor object, which statement will be executed.

    And then, in the next line, result.fetchone(), you forgot to get the results in a variable. See my suggestion below.

    from flask import Flask, render_template, request, g
    
    import sys
    import sqlite3
    import os
    
    current_directory = os.path.dirname(os.path.abspath(__file__))
    
    app = Flask(__name__)
    
    @app.route('/student')
    def student():
        connection = sqlite3.connect(current_directory + "/sss.db")
        cursor = connection.cursor()
        Lastname='Volley'
        Firstname='Vlad'
        # we don't need to assign this to a variable
        cursor.execute("SELECT * FROM Students WHERE Firstname=? AND Lastname=?", (Firstname,Lastname))
        # now we get the results using the cursor object, which will execute the select statement
        result = cursor.fetchone()
        res = list(result)
        connection.commit()
        connection.close()
        return render_template('student.html', res=res)
    
    if __name__ == "__main__":
        app.debug=True
        app.run(port=3000)