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]

Re: Python sqlite and regex.

On Fri, 19 May 2006 14:47:10 +0200,
Julien ARNOUX <julien.arnoux@ ext.cri74.org> wrote:
[color=blue]
> cur.execute("se lect foo from test where foo regex 'aa.[0-9])")[/color]
[color=blue]
> and the error is:[/color]
[color=blue]
> cur.execute('se lect foo from test where foo regex tata')
> apsw.SQLError: SQLError: near "regex": syntax error[/color]

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]

Re: Python sqlite and regex.

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]

Re: Python sqlite and regex.

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

Matt Good wrote:[color=blue]
> SQLite3 already has a REGEXP function, so you don't need to create your
> own. [...][/color]

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]

Re: Python sqlite and regex.

"Gerhard Häring" <gh@ghaering.de > wrote in message
news:mailman.59 66.1148057291.2 7775.python-list@python.org ...[color=blue]
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Matt Good wrote:[color=green]
> > SQLite3 already has a REGEXP function, so you don't need to create your
> > own. [...][/color]
>
> 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.
> """
>[/color]

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]

Re: Python sqlite and regex.

"Paul McGuire" <ptmcg@austin.r r._bogus_.com> wrote in message
news:hunbg.4684 5$CH2.29994@tor nado.texas.rr.c om...
[color=blue]
> where '*' matches one or more characters, and '?' matches any single[/color]

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]

Re: Python sqlite and regex.

Paul McGuire wrote:[color=blue]
> "Paul McGuire" <ptmcg@austin.r r._bogus_.com> wrote in message
> news:hunbg.4684 5$CH2.29994@tor nado.texas.rr.c om...
>[color=green]
>> where '*' matches one or more characters, and '?' matches any single[/color]
>
> oops, I meant '*' matches zero or more characters.[/color]

'?' also matches 0 characters

[Paul McGuire]

Re: Python sqlite and regex.

"John Salerno" <johnjsal@NOSPA Mgmail.com> wrote in message
news:4Anbg.2134 $No6.46516@news .tufts.edu...[color=blue]
> Paul McGuire wrote:[color=green]
> > "Paul McGuire" <ptmcg@austin.r r._bogus_.com> wrote in message
> > news:hunbg.4684 5$CH2.29994@tor nado.texas.rr.c om...
> >[color=darkred]
> >> where '*' matches one or more characters, and '?' matches any single[/color]
> >
> > oops, I meant '*' matches zero or more characters.[/color]
>
> '?' also matches 0 characters[/color]

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):[color=blue][color=green][color=darkred]
>>> glob.glob('PUTT Y.RND')[/color][/color][/color]
['PUTTY.RND'][color=blue][color=green][color=darkred]
>>> glob.glob('PUTT Y.RN?')[/color][/color][/color]
['PUTTY.RND'][color=blue][color=green][color=darkred]
>>> glob.glob('PUTT Y.RN?D')[/color][/color][/color]
[][color=blue][color=green][color=darkred]
>>> glob.glob('PUTT Y.RN?')[/color][/color][/color]
['PUTTY.RND'][color=blue][color=green][color=darkred]
>>> glob.glob('PUTT Y.RND?')[/color][/color][/color]
[][color=blue][color=green][color=darkred]
>>> glob.glob('PUTT ?.RND')[/color][/color][/color]
['PUTTY.RND'][color=blue][color=green][color=darkred]
>>> glob.glob('PUTT Y?.RND')[/color][/color][/color]
[][color=blue][color=green][color=darkred]
>>> glob.glob('PUTT ?Y.RND')[/color][/color][/color]
[][color=blue][color=green][color=darkred]
>>>[/color][/color][/color]

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]

Re: Python sqlite and regex.

On Fri, 19 May 2006 17:44:45 GMT,
"Paul McGuire" <ptmcg@austin.r r._bogus_.com> wrote:
[color=blue]
> "Gerhard Häring" <gh@ghaering.de > wrote in message
> news:mailman.59 66.1148057291.2 7775.python-list@python.org ...[/color]
[color=blue][color=green]
>> """
>> 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.
>> """[/color][/color]
[color=blue]
> 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).[/color]

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]

Re: Python sqlite and regex.


"Dan Sommers" <me@privacy.net > wrote in message
news:m2u07lq2s5 .fsf@unique.fqd n...[color=blue]
> On Fri, 19 May 2006 17:44:45 GMT,
> "Paul McGuire" <ptmcg@austin.r r._bogus_.com> wrote:
>[color=green]
> > "Gerhard Häring" <gh@ghaering.de > wrote in message
> > news:mailman.59 66.1148057291.2 7775.python-list@python.org ...[/color]
>[color=green][color=darkred]
> >> """
> >> 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.
> >> """[/color][/color]
>[color=green]
> > 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).[/color]
>
> Doesn't SQL already have lightweight wildcards?
>
> SELECT somefield FROM sometable WHERE someotherfield LIKE '%foo%'
>[/color]

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

[Matt Good]

Re: Python sqlite and regex.

Oops, sorry about the confusion regarding the built-in REGEXP. That's
kind of disappointing. It would appear that the user-defined regexp
function in the original post should work assuming the SQL and regex
syntax errors are corrected.

However, there *is* a GLOB built-in to SQLite 3 that has a default
registered implementation (though it can be overriden by a user-defined
"glob" method if necessary).

[Dan Sommers]

Re: Python sqlite and regex.

On Fri, 19 May 2006 18:52:38 GMT,
"Paul McGuire" <ptmcg@austin.r r._bogus_.com> wrote:
[color=blue]
> "Dan Sommers" <me@privacy.net > wrote in message
> news:m2u07lq2s5 .fsf@unique.fqd n...[/color]
[color=blue][color=green]
>> Doesn't SQL already have lightweight wildcards?
>>
>> SELECT somefield FROM sometable WHERE someotherfield LIKE '%foo%'[/color][/color]
[color=blue]
> 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 '?'.[/color]
[color=blue]
> The reason I keep citing end/application users, as opposed to
> developers ...[/color]

Users, application users, end users, etc., vary over time. Since this
is c.l.p., though, we'd better just agree to disagree over which user(s)
may or may not be familiar with which (or any) wildcard conventions.
[color=blue]
> ... 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
> ...[/color]

Woe to the app designer who fails to target the app to the users, make
it clear to the users what is expected in any given field, and accept
"constructi ve criticism" from unhappy users. ;-)

Knowing your users is one of the most important factors in software
design.
[color=blue]
> ... '%' and '_' wildcards a little better, but frankly, I think '*'
> and '?' looks more like a wildcards than '%' and '_'.[/color]

Ah, but then you're not an end user, are you? ;-)

Regards,
Dan

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

[Ben Finney]

Re: Python sqlite and regex.

John Salerno <johnjsal@NOSPA Mgmail.com> writes:
[color=blue]
> Paul McGuire wrote:[color=green]
> > "Paul McGuire" <ptmcg@austin.r r._bogus_.com> wrote in message
> > news:hunbg.4684 5$CH2.29994@tor nado.texas.rr.c om...
> >[color=darkred]
> >> where '*' matches one or more characters, and '?' matches any
> >> single[/color]
> >
> > oops, I meant '*' matches zero or more characters.[/color]
>
> '?' also matches 0 characters[/color]

Not in globs. In a glob, '?' matches any one character, '*' matches
any zero or more characters.

In a regex, '.' matches any one character, '?' matches the preceding
atom zero or one times, '*' matches the preceding atom zero or more
times, and '+' matches the preceding atom one or more times.

They're quite different syntaxes, but confusingly similar in
appearance.

On most GNU+Linux systems, these two commands get the relevant manual
pages:

$ man 7 glob
$ man 7 regex

--
\ "When I get real bored, I like to drive downtown and get a |
`\ great parking spot, then sit in my car and count how many |
_o__) people ask me if I'm leaving." -- Steven Wright |
Ben Finney