Приложение My Flask предоставляет REST API для данных CVE, хранящихся в SQLite. API поддерживает фильтрацию и разбиение на страницы, но правильна ли моя логика подсчета totalRecords при применении фильтров?
totalRecords должен возвращать общее количество записей после применения фильтров. Пагинация (page, per_page) должно влиять только на результаты, а не на количество. Пагинация работает. Фильтры работают. Я не уверен, является ли запрос COUNT(*) правильным способом вычисления totalRecords.
from flask import Flask, render_template, request, jsonify, redirect, url_for
from database import get_db_connection, init_db
import json
from datetime import datetime, timedelta
app = Flask(__name__)
# --- Frontend Routes ---
@app.route('/')
def index():
return redirect(url_for('cve_list'))
@app.route('/cves/list')
def cve_list():
return render_template('index.html')
@app.route('/cves/')
def cve_details(cve_id):
conn = get_db_connection()
cve = conn.execute(
'SELECT * FROM cves WHERE id = ?', (cve_id,)
).fetchone()
conn.close()
if cve:
cve_data = dict(cve)
cve_data['details'] = json.loads(cve_data['details'])
return render_template('details.html', cve=cve_data)
else:
return "CVE Not Found", 404
# --- REST API Routes ---
@app.route('/api/cves')
def get_cves_api():
# Filtering Parameters
cve_id_filter = request.args.get('cve_id')
year_filter = request.args.get('year')
score_filter = request.args.get('score')
days_filter = request.args.get('last_modified_days')
# Pagination Parameters
page = int(request.args.get('page', 1))
per_page = int(request.args.get('per_page', 10))
offset = (page - 1) * per_page
query = "SELECT * FROM cves WHERE 1=1"
params = []
if cve_id_filter:
query += " AND id = ?"
params.append(cve_id_filter)
if year_filter:
query += " AND id LIKE ?"
params.append(f'CVE-{year_filter}-%')
if score_filter:
query += " AND baseScore >= ?"
params.append(float(score_filter))
if days_filter:
date_n_days_ago = (
datetime.now() - timedelta(days=int(days_filter))
).isoformat()
query += " AND lastModified >= ?"
params.append(date_n_days_ago)
# Get Total Count (for pagination)
count_query = query.replace("SELECT *", "SELECT COUNT(*)")
conn = get_db_connection()
total_records = conn.execute(count_query, params).fetchone()[0]
# Add Limit/Offset
query += " LIMIT ? OFFSET ?"
params.extend([per_page, offset])
rows = conn.execute(query, params).fetchall()
conn.close()
results = []
for row in rows:
results.append({
"id": row['id'],
"sourceIdentifier": row['sourceIdentifier'],
"published": row['published'],
"lastModified": row['lastModified'],
"vulnStatus": row['vulnStatus']
})
return jsonify({
"totalRecords": total_records,
"results": results,
"page": page,
"perPage": per_page
})
@app.route('/api/cves/')
def get_cve_detail_api(cve_id):
conn = get_db_connection()
row = conn.execute(
'SELECT details FROM cves WHERE id = ?', (cve_id,)
).fetchone()
conn.close()
if row:
return jsonify(json.loads(row['details']))
return jsonify({"error": "Not found"}), 404
if __name__ == '__main__':
init_db()
app.run(debug=True, port=5000)
Подробнее здесь: https://stackoverflow.com/questions/798 ... -incorrect
