html-表| Flask/Python/Sqlite中没有可用数据
我正在尝试使用一个简单的应用程序来使用 Python、Flask 和 SQLite 显示数据库信息。我有三个文件 server_table.html、server_table.py 和 base.html。我的 SQLite 数据库 testdb.db 构建成功，我可以向它拉/推数据。列和数据类型对于数据库模型是正确的。
但是，当我运行 server_table.py 时，网页渲染成功，但我收到一条No data available in table消息，应该显示表格数据。
没有收到其他消息。
我正在使用的代码：
server_table.html
{% extends "base.html" %}
{% block content %}
<table id="data" class="table table-striped">
<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/bootstrap@5.0.1/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 class="container">
<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()
