No data available in table | Flask/Python/Sqlite-CodePudding

I am trying to use a simple app to display database information using Python, Flask and SQLite. I have three files server_table.html, server_table.py and base.html. My SQLite database testdb.db is constructed successfully and I am able to pull/push data to it. The columns and data type are correct for the database model.

However, when I run server_table.py, the webpage renders successfully but I receive a No data available in table message where the table data should be displayed.

No other messages are received.

Code I am using:

server_table.html

{% extends "base.html" %}

{% block content %}
<table id="data" >
    <thead>
        <tr>
            <th>Name</th>
            <th>Age</th>
            <th>Address</th>
            <th>Phone Number</th>
            <th>Email</th>
        </tr>
    </thead>
    <tbody>
    </tbody>
</table>
{% endblock %}

{% block scripts %}
<script>
    $(document).ready(function () {
        $('#data').DataTable({
            ajax: '/api/data',
            serverSide: true,
            columns: [
                { data: 'name' },
                { data: 'age' },
                { data: 'address', orderable: false },
                { data: 'phone', orderable: false },
                { data: 'email' }
            ],
        });
    });
</script>
{% endblock %}

base.html

<!doctype html>
<html>

<head>
    <title>{{ title }}</title>
    <link href="https://cdn.jsdelivr.net/npm/[email protected]/dist/css/bootstrap.min.css" rel="stylesheet"
        integrity="sha384- 0n0xVW2eSR5OomGNYDnhzAbDsOXxcvSN1TPprVMTNDbiYZCxYbOOl7 AMvyTG2x" crossorigin="anonymous">
    <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.25/css/dataTables.bootstrap5.css">
</head>

<body>
    <div >
        <h1>{{ title }}</h1>
        <hr>
        {% block content %}{% endblock %}
    </div>
    <script type="text/javascript" charset="utf8" src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
    <script type="text/javascript" charset="utf8"
        src="https://cdn.datatables.net/1.10.25/js/jquery.dataTables.js"></script>
    <script type="text/javascript" charset="utf8"
        src="https://cdn.datatables.net/1.10.25/js/dataTables.bootstrap5.js"></script>
    {% block scripts %}{% endblock %}
</body>

</html>

server_table.py

from flask import Flask, render_template, request

from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///testdb.db"
app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False
db = SQLAlchemy(app)


class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(64), index=True)
    age = db.Column(db.Integer, index=True)
    address = db.Column(db.String(256))
    phone = db.Column(db.String(20))
    email = db.Column(db.String(120))

    def to_dict(self):
        return {
            "name": self.name,
            "age": self.age,
            "address": self.address,
            "phone": self.phone,
            "email": self.email,
        }


db.create_all()


@app.route("/")
def index():
    return render_template("server_table.html", title="Server-Driven Table")


@app.route("/api/data")
def data():
    query = User.query

    # search filter
    search = request.args.get("search[value]")
    if search:
        query = query.filter(
            db.or_(User.name.like(f"%{search}%"), User.email.like(f"%{search}%"))
        )
    total_filtered = query.count()

    # sorting
    order = []
    i = 0
    while True:
        col_index = request.args.get(f"order[{i}][column]")
        if col_index is None:
            break
        col_name = request.args.get(f"columns[{col_index}][data]")
        if col_name not in ["name", "age", "email"]:
            col_name = "name"
        descending = request.args.get(f"order[{i}][dir]") == "desc"
        col = getattr(User, col_name)
        if descending:
            col = col.desc()
        order.append(col)
        i  = 1
    if order:
        query = query.order_by(*order)

    # pagination
    start = request.args.get("start", type=int)
    length = request.args.get("length", type=int)
    query = query.offset(start).limit(length)

    # response
    return {
        "data": [user.to_dict() for user in query],
        "recordsFiltered": total_filtered,
        "recordsTotal": User.query.count(),
        "draw": request.args.get("draw", type=int),
    }


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

CodePudding user response：

Please provide some guidance on how I can get this working.

Faced with a "full-stack" problem like this, you need to isolate the problem to a single layer: the front end, the back end, or the database.

Start by running the application with your browser's dev-tools' network tab open. Find the request that should be fetching the data, click on it and check the contents of the response. If the data is present then there is something wrong with your front end code. You will need to use the browser's Javascript debugger and / or console.log statements to find out what's going wrong.

If the data isn't in the response, the problem must be further down the stack. Use a database client* to open the database and query the table(s) to verify that the expected data is actually present. For SQLite especially, make sure your application is definitely connecting to the correct database, it's easy to make a mistake with the path.

If the data is in the database, but not in the response sent to the browser then the problem must be in the Python layer. As with the Javascript layer, you can use a debugger and logging, print or assert statements to check what is happening in the code. Check that the query uses the expected SQL and generates the expected output. If necessary remove all filters from the query and add them back one-by-one.

* SQLite comes with a command line client (sqlite3 on Linux, sqlite3.exe on Windows) that can connect with sqlite3 <filename> and run SQL queries; there are GUI clients too, or you run code to query the database from the Python REPL, though this is more work and risks carrying over any implicit assumptions from your back end code.