I'm using
QSqlTableModelwith aQSortFilterProxyModelto display and filter data from an SQLite database. But my search results are missing rows — it seems like only part of the database is loaded. How do I makeQSqlTableModelload all the data so filtering works correctly?
If you've ever used QSqlTableModel to display database contents in a QTableView, you might have run into a puzzling problem: your table only shows a portion of your data, and filtering with QSortFilterProxyModel misses rows you know exist. This happens because Qt's SQL models use lazy loading — they don't fetch all the rows from the database at once.
Let's look at why this happens and how to fix it.
Why Only 256 Rows?
When you call .select() on a QSqlTableModel, Qt doesn't immediately pull every row from the database into memory. Instead, it fetches rows in batches (typically 256 at a time). This is a performance optimization — if your database has millions of rows, loading them all at once could be very slow and use a lot of memory.
The model provides two methods to manage this:
canFetchMore()— returnsTrueif there are more rows available to load from the database.fetchMore()— loads the next batch of rows.
Normally, the QTableView handles this automatically: as you scroll down, it triggers fetchMore() to load additional rows. But here's the catch — if you're using a QSortFilterProxyModel to filter or search the data, the proxy model can only filter rows that have already been fetched. Rows still sitting in the database, not yet loaded into the model, are invisible to your filter.
This means your search results can appear incomplete, especially if the rows you're looking for haven't been scrolled into view yet.
Fetching All Rows
The solution is to force the model to load all available rows before you start filtering. You can do this with a simple loop that calls fetchMore() until canFetchMore() returns False:
while self.model.canFetchMore():
self.model.fetchMore()
Place this right after your call to .select() in your model initialization. Here's what that looks like in context:
def initialized_model(self):
self.model.setTable("commands")
self.model.setEditStrategy(QSqlTableModel.EditStrategy.OnManualSubmit)
self.model.select()
# Fetch all remaining rows from the database.
while self.model.canFetchMore():
self.model.fetchMore()
After this loop completes, all rows from the table are loaded into the model, and self.model.rowCount() will return the full count. Your QSortFilterProxyModel will now be able to filter across the entire dataset.
When Should You Use This?
This approach works well when your database table is a reasonable size — hundreds or even a few thousand rows. For tables of that size, loading everything into memory is perfectly fine and gives you reliable filtering and sorting.
If your table contains hundreds of thousands or millions of rows, loading everything at once may not be practical. In that case, you might want to push filtering down to the database level using setFilter() on the QSqlTableModel itself, which generates a SQL WHERE clause, rather than relying on QSortFilterProxyModel to filter in memory.
A Complete Working Example
The following example creates a small SQLite database in memory, populates it with sample data, and displays it in a QTableView with a search box for filtering. All rows are fetched up front so the filter works across the full dataset.
import sys
from PyQt6.QtCore import Qt, QSortFilterProxyModel
from PyQt6.QtSql import QSqlDatabase, QSqlQuery, QSqlTableModel
from PyQt6.QtWidgets import (
QApplication,
QLineEdit,
QMainWindow,
QTableView,
QVBoxLayout,
QWidget,
)
def create_sample_database():
"""Create an in-memory SQLite database with sample data."""
db = QSqlDatabase.addDatabase("QSQLITE")
db.setDatabaseName(":memory:")
if not db.open():
print("Could not open database")
sys.exit(1)
query = QSqlQuery()
query.exec(
"""
CREATE TABLE commands (
id INTEGER PRIMARY KEY AUTOINCREMENT,
category TEXT,
command_alias TEXT,
command TEXT,
description TEXT
)
"""
)
# Insert enough rows to exceed the default fetch limit of 256.
categories = ["Network", "System", "Files", "Display", "Audio"]
for i in range(500):
category = categories[i % len(categories)]
query.exec(
f"INSERT INTO commands (category, command_alias, command, description) "
f"VALUES ('{category}', 'cmd_{i}', 'command_{i}', "
f"'Description for command number {i}')"
)
return db
class MainWindow(QMainWindow):
def __init__(self):
super().__init__()
self.setWindowTitle("QSqlTableModel — Fetch All Rows")
self.resize(600, 400)
# Set up the model.
self.model = QSqlTableModel()
self.initialize_model()
# Set up the proxy model for filtering.
self.proxy_model = QSortFilterProxyModel()
self.proxy_model.setSourceModel(self.model)
self.proxy_model.setFilterCaseSensitivity(
Qt.CaseSensitivity.CaseInsensitive
)
self.proxy_model.setFilterKeyColumn(-1) # Search all columns.
# Set up the table view.
self.table_view = QTableView()
self.table_view.setModel(self.proxy_model)
self.table_view.setSortingEnabled(True)
self.table_view.horizontalHeader().setStretchLastSection(True)
# Set up the search box.
self.search_box = QLineEdit()
self.search_box.setPlaceholderText("Search...")
self.search_box.textChanged.connect(self.update_filter)
# Row count label to show how many rows are loaded.
self.status_label = QLineEdit()
self.status_label.setReadOnly(True)
self.update_status()
# Layout.
layout = QVBoxLayout()
layout.addWidget(self.search_box)
layout.addWidget(self.table_view)
layout.addWidget(self.status_label)
container = QWidget()
container.setLayout(layout)
self.setCentralWidget(container)
def initialize_model(self):
self.model.setTable("commands")
self.model.setEditStrategy(
QSqlTableModel.EditStrategy.OnManualSubmit
)
self.model.select()
# Fetch all rows so filtering works on the complete dataset.
while self.model.canFetchMore():
self.model.fetchMore()
self.model.setHeaderData(0, Qt.Orientation.Horizontal, "ID")
self.model.setHeaderData(1, Qt.Orientation.Horizontal, "Category")
self.model.setHeaderData(2, Qt.Orientation.Horizontal, "Alias")
self.model.setHeaderData(3, Qt.Orientation.Horizontal, "Command")
self.model.setHeaderData(4, Qt.Orientation.Horizontal, "Description")
def update_filter(self, text):
self.proxy_model.setFilterFixedString(text)
self.update_status()
def update_status(self):
total = self.model.rowCount()
visible = self.proxy_model.rowCount()
self.status_label.setText(
f"Showing {visible} of {total} rows"
)
def main():
app = QApplication(sys.argv)
db = create_sample_database()
window = MainWindow()
window.show()
result = app.exec()
db.close()
sys.exit(result)
if __name__ == "__main__":
main()
Run this example and try typing into the search box. You'll see that filtering works across all 500 rows immediately. If you remove the while self.model.canFetchMore() loop from initialize_model, you'll notice the row count drops to 256 and search results become incomplete — that's the lazy loading behavior in action.
Summary
Qt's SQL models load data lazily in batches for performance reasons. This is usually fine for display, but it can cause incomplete results when you filter with QSortFilterProxyModel. To ensure your filters see the full dataset, call fetchMore() in a loop after select():
self.model.select()
while self.model.canFetchMore():
self.model.fetchMore()
For very large datasets, consider using QSqlTableModel.setFilter() to filter at the database level instead, which avoids loading all rows into memory. To learn more about sorting and filtering table data, see our tutorial on sort & filter tables with QSortFilterProxyModel. For a deeper understanding of Qt's model/view framework, check out the PyQt6 ModelView architecture tutorial. You can also build a search bar widget to provide a polished filtering interface for your users.
Bring Your PyQt/PySide Application to Market
Stuck in development hell? I'll help you get your project focused, finished and released. Benefit from years of practical experience releasing software with Python.
