Is it posible to find index-row and values with QRegExp with search name?

Question

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?

Answer 1

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())