ARTICLE AD BOX
I am building a small Flask application that exposes a REST API for CVE data stored in SQLite.
The API supports filtering and pagination, but I am not sure if my totalRecords count logic is correct when filters are applied.
Expected behavior
totalRecords should return the total number of records after filters are applied
Pagination (page, per_page) should only affect the results, not the count
Actual behavior
Pagination works
Filters work
But I am unsure whether the COUNT(*) query is the correct or recommended way to calculate totalRecords in this scenario
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/<cve_id>') 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/<cve_id>') 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)is this the correct approach for calculating totalRecords when using filters and pagination in Flask + SQLite?
Is there a better or more efficient pattern for this use case?
