Python sqlite and regex.

[Julien ARNOUX]

Hi,
I'd like to use regular expressions in sqlite query, I using apsw module
but it doesn't work...Can you help me ?
My script:

import apsw
import re

path = 'db/db.db3'

#regexp function (extract from python-list discusion)
def regexp(expr, item):
reg = re.compile(expr )
return reg.match(item) is not None

con = apsw.Connection (path)
#create function
con.createscala rfunction("REGE XP", regexp)
cur = con.cursor()
#exampl
cur.execute("se lect foo from test where foo regex 'aa.[0-9])")

and the error is:

cur.execute('se lect foo from test where foo regex tata')
apsw.SQLError: SQLError: near "regex": syntax error

Thanks

[Dan Sommers]

On Fri, 19 May 2006 14:47:10 +0200,
Julien ARNOUX <ju***********@ ext.cri74.org> wrote:

cur.execute("se lect foo from test where foo regex 'aa.[0-9])") and the error is: cur.execute('se lect foo from test where foo regex tata')
apsw.SQLError: SQLError: near "regex": syntax error

I think you're missing a closing quote on that regex; or perhaps that's
an extra closing parenthesis at the end.

Also, it's probably best to let the database module do any escaping you
may need. For example:

fooregex = r'aa.[0-9]'
sql = 'select foo from test where foo regex %s'
cur.execute( sql, tuple( fooregex ) )

See the DP API spec for more information.

Regards,
Dan

--
Dan Sommers
<http://www.tombstoneze ro.net/dan/>
"I wish people would die in alphabetical order." -- My wife, the genealogist

[Matt Good]

SQLite3 already has a REGEXP function, so you don't need to create your
own.

As Dan mentioned you also have a problem in your expression: 'aa.[0-9])
You need a closing quote on the expression, and you need to match the
close paren with an open paren, or remove it.

Also, in case you weren't aware, there will be a "sqlite3" module in
Python 2.5 based on pysqlite 2.2: http://initd.org/tracker/pysqlite

Using pysqlite will make it easier to move to the Python 2.5 sqlite3
module if that's important to you.

-- Matt Good

[Gerhard Häring]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Matt Good wrote:

SQLite3 already has a REGEXP function, so you don't need to create your
own. [...]

Yes, but SQLite does not include a regular expression engine, and thus
according to the SQLite docs you need to register a REGEXP function in
order to make the REGEXP operator work:

"""
The REGEXP operator is a special syntax for the regexp() user function. No
regexp() user function is defined by default and so use of the REGEXP
operator will normally result in an error message. If a user-defined
function named "regexp" is defined at run-time, that function will be
called in order to implement the REGEXP operator.
"""

- -- Gerhard
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFEbfbQdIO 4ozGCH14RAqqqAJ 49/9Kpi8xA6AyGB0tV J/JcU4MczgCgoIsW
gdYgUl9ge63CiHq j4Mzgpns=
=1ZDV
-----END PGP SIGNATURE-----

[Paul McGuire]

"Gerhard Häring" <[e-mail] > wrote in message
news:ma******** *************** *************** *@python.org...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Matt Good wrote:

SQLite3 already has a REGEXP function, so you don't need to create your
own. [...]

Yes, but SQLite does not include a regular expression engine, and thus
according to the SQLite docs you need to register a REGEXP function in
order to make the REGEXP operator work:

"""
The REGEXP operator is a special syntax for the regexp() user function. No
regexp() user function is defined by default and so use of the REGEXP
operator will normally result in an error message. If a user-defined
function named "regexp" is defined at run-time, that function will be
called in order to implement the REGEXP operator.
"""

This is very interesting. So I *could* define my own regexp function that
processes not regular expressions, but say, glob-like strings, which are
usually much easier for end users to work with (very basic wild-carding
where '*' matches one or more characters, and '?' matches any single
character - maybe add '#' to match any single digit and '@' to match any
single alpha character).

-- Paul

[Paul McGuire]

"Paul McGuire" <pt***@austin.r r._bogus_.com> wrote in message
news:hu******** ***********@tor nado.texas.rr.c om...

where '*' matches one or more characters, and '?' matches any single

oops, I meant '*' matches zero or more characters.

In many applications, these tests are sufficient for most user queries. And
this eliminates the problem of teaching application users how to create
queries using the full regular expression syntax.

-- Paul

[John Salerno]

Paul McGuire wrote:

"Paul McGuire" <pt***@austin.r r._bogus_.com> wrote in message
news:hu******** ***********@tor nado.texas.rr.c om...

where '*' matches one or more characters, and '?' matches any single

oops, I meant '*' matches zero or more characters.

'?' also matches 0 characters

[Paul McGuire]

"John Salerno" <jo******@NOSPA Mgmail.com> wrote in message
news:4A******** **********@news .tufts.edu...

Paul McGuire wrote:

"Paul McGuire" <pt***@austin.r r._bogus_.com> wrote in message
news:hu******** ***********@tor nado.texas.rr.c om...

where '*' matches one or more characters, and '?' matches any single

oops, I meant '*' matches zero or more characters.

'?' also matches 0 characters

Maybe it does, and maybe it doesn't... :)

Here is my test with the glob module (in my directory, there is a file named
PUTTY.RND):

glob.glob('PUTT Y.RND') ['PUTTY.RND'] glob.glob('PUTT Y.RN?') ['PUTTY.RND'] glob.glob('PUTT Y.RN?D') [] glob.glob('PUTT Y.RN?') ['PUTTY.RND'] glob.glob('PUTT Y.RND?') [] glob.glob('PUTT ?.RND') ['PUTTY.RND'] glob.glob('PUTT Y?.RND') [] glob.glob('PUTT ?Y.RND') []

Looks like '?' does *not* match zero characters in glob. On the other hand,
in the Windows console window, it appears that '?' *does* match zero
characters.

Of course, you could write your regexp() routine to interpret '?' any way
you wanted.

-- Paul

[Dan Sommers]

On Fri, 19 May 2006 17:44:45 GMT,
"Paul McGuire" <pt***@austin.r r._bogus_.com> wrote:

"Gerhard Häring" <[e-mail] > wrote in message
news:ma******** *************** *************** *@python.org...

"""
The REGEXP operator is a special syntax for the regexp() user
function. No regexp() user function is defined by default and so use
of the REGEXP operator will normally result in an error message. If a
user-defined function named "regexp" is defined at run-time, that
function will be called in order to implement the REGEXP operator.
"""

This is very interesting. So I *could* define my own regexp function
that processes not regular expressions, but say, glob-like strings,
which are usually much easier for end users to work with (very basic
wild-carding where '*' matches one or more characters, and '?' matches
any single character - maybe add '#' to match any single digit and '@'
to match any single alpha character).

Doesn't SQL already have lightweight wildcards?

SELECT somefield FROM sometable WHERE someotherfield LIKE '%foo%'

Regards,
Dan

--
Dan Sommers
<http://www.tombstoneze ro.net/dan/>
"I wish people would die in alphabetical order." -- My wife, the genealogist

[Paul McGuire]

"Dan Sommers" <[e-mail] > wrote in message
news:m2******** ****@unique.fqd n...

On Fri, 19 May 2006 17:44:45 GMT,
"Paul McGuire" <pt***@austin.r r._bogus_.com> wrote:

"Gerhard Häring" <[e-mail] > wrote in message
news:ma******** *************** *************** *@python.org...

"""
The REGEXP operator is a special syntax for the regexp() user
function. No regexp() user function is defined by default and so use
of the REGEXP operator will normally result in an error message. If a
user-defined function named "regexp" is defined at run-time, that
function will be called in order to implement the REGEXP operator.
"""

This is very interesting. So I *could* define my own regexp function
that processes not regular expressions, but say, glob-like strings,
which are usually much easier for end users to work with (very basic
wild-carding where '*' matches one or more characters, and '?' matches
any single character - maybe add '#' to match any single digit and '@'
to match any single alpha character).

Doesn't SQL already have lightweight wildcards?

SELECT somefield FROM sometable WHERE someotherfield LIKE '%foo%'

Yes it does - '%' is like '*', and '_' is like '?'. But it is rare for
application users to be familiar with these. In my experience, users are
more likely to have seen '*' and '?'.

The reason I keep citing end/application users, as opposed to developers, is
for the case where the user has filled in some kind of wildcard search
field, to be passed to SQL in a query. Woe to the app designer who figures
that users want to enter a regular expression in such a field. '%' and '_'
wildcards a little better, but frankly, I think '*' and '?' looks more like
a wildcards than '%' and '_'.

-- Paul