I want to find the row-index with searching word using with QSortFilterProxyModel
and QtCore.QRegExp
.
I want to create a list with “mobile” and “email” columns only from the row which is finding from the variable of QRegExp
.
Below is example code:
from PyQt5 import QtCore, QtSql db = QtSql.QSqlDatabase.addDatabase("QSQLITE") db.setDatabaseName("users.db") if db.open(): query = QtSql.QSqlQuery() query.exec_("""CREATE TABLE IF NOT EXISTS user_name(name TEXT, age TEXT, mobile TEXT, mail TEXT)""") model = QtSql.QSqlTableModel() model.setTable("table") model.select() proxy = QtCore.QSortFilterProxyModel() proxy.setSourceModel(model) query.exec_("INSERT into users VALUES ('name1', 29, 123, 'Mail1.com')") query.exec_("INSERT into users VALUES ('name2', 30, 456, 'Mail2.com')") query.exec_("INSERT into users VALUES ('name3', 31, 789, 'Mail3.com')") search_name = "name2" search = QtCore.QRegExp(search_name) proxy.setFilterRegExp(search) list_a = []
I want to print the row.index
and insert the value of “mobile” and “email” of the row into list_a
.
How is it possible or is there any other solution for Request?
Advertisement
Answer
It is not necessary to create a model to filter elements based on a regex since the Qt sqlite driver allows to use the regex
function enabling it through QSQLITE_ENABLE_REGEXP
using setConnectOptions()
method:
import sys from PyQt5 import QtSql db = QtSql.QSqlDatabase.addDatabase("QSQLITE") db.setDatabaseName("users.db") db.setConnectOptions("QSQLITE_ENABLE_REGEXP=1") if not db.open(): sys.exit(-1) query = QtSql.QSqlQuery() query.exec_( """CREATE TABLE IF NOT EXISTS user_name(name TEXT, age TEXT, mobile TEXT, mail TEXT)""" ) query.exec_("INSERT into user_name VALUES ('name1', 29, 123, 'Mail1.com')") query.exec_("INSERT into user_name VALUES ('name2', 30, 456, 'Mail2.com')") query.exec_("INSERT into user_name VALUES ('name3', 31, 789, 'Mail3.com')") query_filter = QtSql.QSqlQuery() query_filter.prepare("SELECT rowid, * FROM user_name WHERE regexp(?, name) ") query_filter.addBindValue("name1") if query_filter.exec_(): record = query_filter.record() while query_filter.next(): print("=======") for i in range(record.count()): print(record.fieldName(i), query_filter.value(i)) else: print(query_filter.lastError().text())
If you still want to use QSqlTableModel then you can also use the setFilter method:
import sys from PyQt5 import QtSql db = QtSql.QSqlDatabase.addDatabase("QSQLITE") db.setDatabaseName("users.db") db.setConnectOptions("QSQLITE_ENABLE_REGEXP=1") if not db.open(): sys.exit(-1) query = QtSql.QSqlQuery() query.exec_( """CREATE TABLE IF NOT EXISTS user_name(name TEXT, age TEXT, mobile TEXT, mail TEXT)""" ) model = QtSql.QSqlTableModel() model.setTable("user_name") model.select() query.exec_("INSERT into user_name VALUES ('name1', 29, 123, 'Mail1.com')") query.exec_("INSERT into user_name VALUES ('name2', 30, 456, 'Mail2.com')") query.exec_("INSERT into user_name VALUES ('name3', 31, 789, 'Mail3.com')") model.setFilter("regexp('%s', name)" % ("name1")) model.select() for i in range(model.rowCount()): r = model.record(i) print("=====") for j in range(r.count()): print(r.fieldName(j), r.value(j))
If you still want to use QSqlTableModel + QSQSortFilterProxyModel then apart from the filter you have to map the position of the rows:
import sys from PyQt5 import QtCore, QtSql db = QtSql.QSqlDatabase.addDatabase("QSQLITE") db.setDatabaseName("users.db") if not db.open(): sys.exit(-1) query = QtSql.QSqlQuery() query.exec_( """CREATE TABLE IF NOT EXISTS user_name(name TEXT, age TEXT, mobile TEXT, mail TEXT)""" ) model = QtSql.QSqlTableModel() model.setTable("user_name") model.select() proxy = QtCore.QSortFilterProxyModel() proxy.setSourceModel(model) query.exec_("INSERT into user_name VALUES ('name1', 29, 123, 'Mail1.com')") query.exec_("INSERT into user_name VALUES ('name2', 30, 456, 'Mail2.com')") query.exec_("INSERT into user_name VALUES ('name3', 31, 789, 'Mail3.com')") model.select() search_name = "name2" search = QtCore.QRegExp(search_name) proxy.setFilterRegExp(search) for i in range(proxy.rowCount()): r = model.record() print("=====") print("row", proxy.mapToSource(proxy.index(i, 0)).row()) for j in range(r.count()): index = proxy.index(i, j) print(r.fieldName(j), index.data())