PDA

View Full Version : How to create a custom function in a QSqlDatabase: regex in sqlite and pyqt



kmgrds
14th May 2012, 14:11
hello!

We define the python type REGEXP function in sqlite and python following this example on stackoverflow: Problem with regexp python and sqlite
(http://stackoverflow.com/questions/5365451/problem-with-regexp-python-and-sqlite)
How can we do the same thing in PyQT, ie. with a QSqlDatabase?

More precisely, we use the REGEXP function to create a view:


Create view temp as select * from somewhere where columnname REGEXP 'myregex';

This works well, as long as we do the select from python. We would like to show the result in a QTableView (via a QSqlTableModel filled with the view). As the view uses the REGEXP, we would have to link the python regex function to the QSqlDatabase.

Is there a way of doing that?

Thanks in advance!

miraks
14th August 2012, 16:44
Hi kmgrds,

Do you have the answer because I would like to do the same?

kmgrds
16th August 2012, 00:03
hi miraks,
no sorry, no answers. also tried on http://stackoverflow.com/questions/10549380/how-to-create-custom-function-in-a-qsqldatabase-regex-in-sqlite-and-pyqt but didn't received any answer.
we finally couldn't manage to get complete regex search going directly from qt.
so if ever you find something, let us know...
best
kmgrds

Ashkan_s
25th September 2012, 16:41
I Don't how to that in PyQt, in C++ Qt:

You need to wirte a function to do pattern matching for you, example:

void qtregexp(sqlite3_context* ctx, int argc, sqlite3_value** argv)
{
QRegExp regex;
QString str1((const char*)sqlite3_value_text(argv[0]));
QString str2((const char*)sqlite3_value_text(argv[1]));

regex.setPattern(str1);
regex.setCaseSensitivity(Qt::CaseInsensitive);

bool b = str2.contains(regex);

if (b)
{
sqlite3_result_int(ctx, 1);
}
else
{
sqlite3_result_int(ctx, 0);
}
}
Then you need to get the handle to the SQLite database somehow, one way is to use SQLite's API, example:


sqlite3 *sldb;
sqlite3_open(":memory:", &sldb);
Last step is a call to sqlite3_create_function, example:
sqlite3_create_function(sldb, "REGEXP", 2, SQLITE_UTF8, NULL, &qtregexp, NULL, NULL);
Hope this helps

Ashkan_s
26th September 2012, 16:41
Changing
sqlite3_create_function(sldb, "REGEXP", 2, SQLITE_UTF8, NULL, &qtregexp, NULL, NULL); to
sqlite3_create_function(sldb, "regexp", 2, SQLITE_UTF8, NULL, &qtregexp, NULL, NULL);
is better, it matches the SQLite's documentations.