小贝子编程

如何分页SQLite数据库?



我编写了简单的代码，用于在表中显示来自SQLite数据库的数据。这个表已经包含了浏览整个数据库内容的页面。

问题是现在整个数据库一次加载到内存中。如何将对服务器的查询拆分为页面?第一部分，即在页面首次运行时加载和显示有限数量的结果，并不困难:

list_of_games = list_of_games.order_by(GamesDatabase.date_added)[0:10]

但是如何使页面发送查询下一个结果(例如11-20)，并显示他们只有按下按钮后(例如"下一页")?我想创建两个单独的按钮:"下一页"和"上一页"，这将负责

我正在使用Python与Flask (with SQLalchemy)， SQLite.

PYTHON

@app.route("/test")
def test():
list_of_games = GamesDatabase.query
# print(request.args)
if "rating_min" in request.args:
rating_min = request.args.get("rating_min", 1, int)
list_of_games = list_of_games.filter(GamesDatabase.rating >= rating_min)
if "rating_max" in request.args:
rating_max = request.args.get("rating_max", 100, int)
list_of_games = list_of_games.filter(GamesDatabase.rating <= rating_max)
if "genre" in request.args:
list_of_games = list_of_games.filter(GamesDatabase.genre == request.args["genre"].upper())
list_of_games = list_of_games.order_by(GamesDatabase.date_added)

# args = []
# if request.args:
#     for arg, val in request.args.items():
#         args.append({"name": arg, "value": val})

return render_template("test.html", list_of_games=list_of_games) #args=args


{% extends "base.html" %}
{% block content %}

<head>
<title> Bootstrap SORT table Example </title>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.16.0/umd/popper.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.2/js/bootstrap.min.js"></script>
<link rel="stylesheet" href="https://cdn.datatables.net/1.10.22/css/dataTables.bootstrap4.min.css">
<script src="https://cdn.datatables.net/1.10.22/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/1.10.22/js/dataTables.bootstrap4.min.js"></script>
</head>
<body>
<button class="btn btn-primary" onclick="document.location='{{url_for('index')}}'"style="margin-left: 790px;">go back</button>

<div class="container" style="width:60%">
<h2>all games </h2>
<table class="table table-striped table-bordered" id="sortTable">
<thead>
<tr>
<th>Name</th>
<th>Genre</th>
<th>Rating</th>
<th>Price</th>
</tr>
</thead>
<tbody>
{% for game in list_of_games %}
<tr>
<td>{{ game.name}}</td>
<td>{{ game.genre }}</td>
<td>{{ game.rating }}%</td>
<td>${{ game.price }}</td>
</tr>
{% endfor %}
</tbody>
</table>
</div>
<form action="" method="GET">
{%  for arg in args %}
<input type="hidden" name="{{ arg.name }}" value="{{ arg.value }} ">
{% endfor %}
<button type="submit">next_page </button>
</form>

<script>
$('#sortTable').DataTable();
</script>
</body>
<center>
<form action="" method="GET" >
<div class="input-group" style="width: 30%">
<div class="input-group-prepend">
<span class="input-group-text"  id="rating">Rating</span>
</div>
<input type="number" id="rating_min"  placeholder="min: 1" class="form-control" min="1" max="100"  name="rating_min" />
<input type="number" id="rating_max"  placeholder="max: 100" class="form-control" min="1" max="100" name="rating_max" />
</div>
<div class="input-group" style="width: 30%">
<div class="input-group-prepend">
<span class="input-group-text" id="genre_">Genre </span>
</div>
<input id="genre" placeholder="RPG" class="form-control" type="text" name="genre" required/>
</div>
<button class="btn btn-primary" onclick="document.location='{{url_for('test')}}'" style="margin-left: 0px;">submit</button>
</form>
</center>
<br>

{% endblock %}

您应该看一下flask-sqlalchemy的paginate函数。它返回一个类型为Pagination的对象，这使您的工作更容易。因此，您可以逐页请求所有数据，您将收到创建按钮所需的所有信息。

然而，因为你也在你的例子中使用数据表，我已经写了一个例子，你如何使用AJAX和上面提到的函数使用数据表的分页。为此，我还使用Flask-Marshmallow和Marshmallow-SQLAlchemy将数据转换为JSON。

from flask import (
Flask,
jsonify,
render_template,
request
)
from flask_sqlalchemy import SQLAlchemy
from flask_marshmallow import Marshmallow
import random
app = Flask(__name__)
db = SQLAlchemy(app)
ma = Marshmallow(app)
class Game(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String)
genre = db.Column(db.String)
rating = db.Column(db.Integer)
price = db.Column(db.Integer)
class GameSchema(ma.SQLAlchemyAutoSchema):
class Meta:
model = Game
with app.app_context():
db.drop_all()
db.create_all()
games = [Game(
name=f'Game-{i+1}',
genre=random.choice(['RPG', 'Sports', 'Shooter']),
rating=random.randint(0,100),
price=i
) for i in range(100)]
db.session.add_all(games)
db.session.commit()

@app.route('/')
def index():
genres = [genre for genre, *_ in Game.query.order_by(Game.genre).with_entities(Game.genre).distinct().all()]
return render_template('index.html', **locals())
@app.route('/data')
def data():
draw = request.args.get('draw', 0, int)
length = request.args.get('length', 10, int)
start = request.args.get('start', 0, int)
query = Game.query.filter(Game.rating.between(
request.args.get('rating_min', 0, type=int),
request.args.get('rating_max', 100, type=int)
))
genre = request.args.get('genre')
if genre:
query = query.filter_by(genre=genre)
games = query.paginate(int(start/length) + 1, length)
games_schema = GameSchema(many=True)
data = games_schema.dump(games.items)
return jsonify(
draw=draw,
recordsTotal=games.total,
recordsFiltered=games.total,
data=data
)

<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title></title>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-1BmE4kWBq78iYhFldvKuhfTAU6auU8tT94WrHftjDbrCEXSU1oBoqyl2QvZ6jIW3" crossorigin="anonymous">
<link href="https://cdn.datatables.net/1.12.0/css/dataTables.bootstrap5.min.css" rel="stylesheet" type="text/css">
</head>
<body>
<div class="container">
<form name="search" class="my-4">
<div class="row">
<div class="col-md-4">
<div class="input-group mb-3">
<span class="input-group-text"  id="rating">Rating</span>
<input
type="number"
name="rating_min"
id="rating_min"
class="form-control"
min="0" max="100" value="0"
/>
<input
type="number"
name="rating_max"
id="rating_max"
class="form-control"
min="1" max="100" value="100"
/>
</div>
</div>
<div class="col-md-6">
<div class="input-group mb-3">
<span class="input-group-text" id="genre_">Genre </span>
<select class="form-select" name="genre">
<option value>Choose a genre</option>
{% for genre in genres -%}
<option value="{{genre}}">{{genre}}</option>
{% endfor -%}
</select>
</div>
</div>
<div class="col-md-2">
<div class="d-grid gap-2">
<button class="btn btn-primary">Submit</button>
</div>
</div>
</div>
</form>
<div class="my-4">
<table class="table table-striped table-bordered" id="sortTable" style="width: 100%">
<thead>
<tr>
<th>Name</th>
<th>Genre</th>
<th>Rating</th>
<th>Price</th>
</tr>
</thead>
<tbody>
</tbody>
</table>
</div>
</div>
<script src="https://code.jquery.com/jquery-3.6.0.min.js" integrity="sha256-/xUj+3OJU5yExlq6GSYGSHk7tPXikynS7ogEvDej/m4=" crossorigin="anonymous"></script>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/js/bootstrap.bundle.min.js" integrity="sha384-ka7Sk0Gln4gmtz2MlQnikT1wXgYsOg+OMhuP+IlRH9sENBO0LRn5q+8nbTov4+1p" crossorigin="anonymous"></script>
<script src="https://cdn.datatables.net/1.11.5/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/1.12.0/js/dataTables.bootstrap5.min.js"></script>
<script type="text/javascript">
$(document).ready(function() {
let data = {};
const table = $('#sortTable').DataTable({
processing: true,
serverSide: true,
searching: false,
ordering: false,
pagingType: 'simple',
ajax: {
url: {{ url_for('data') | tojson }},
dataSrc: 'data',
data: function (d) {
d = $.extend(d, data);
}
},
columns: [
{ data: 'name' },
{ data: 'genre' },
{ data: 'rating' },
{ data: 'price' },
]
});
$('form[name="search"]').submit(function(evt) {
evt.preventDefault();
$.each($(this).serializeArray(), function(index, value) {
data[value.name] = value.value;
});
table.ajax.reload();
});
$('#rating_min, #rating_max').change(function(evt) {
const val0 = $('#rating_min').val();
const val1 = $('#rating_max').val();
if (parseInt(val0) >= parseInt(val1)) {
$('#rating_min').val(val1-1);
$('#rating_max').val(val1);
}
});
});
</script>
</body>
</html>

相关内容

最新更新


热门标签：

javascript python java c# php android html jquery c++ css ios sql mysql arrays asp.net json python-3.x ruby-on-rails .net sql-server django objective-c excel regex ruby linux ajax iphone xml vba spring asp.net-mvc database wordpress string postgresql wpf windows xcode bash git oracle list vb.net multithreading eclipse algorithm macos powershell visual-studio image forms numpy scala function api selenium