python3 sqlite and regex query

Question

I use python3 to search an exact word in a sqlite3 database. When I do it with SQLite database browser it works perfectly. But when I do it with python3, it throws: sqlite3.OperationalError: user-defined function raised exception. (I will use this code for an app to search in a treeview)

Here is the code:

import sqlite3
import re


def regexp(expr, item):
    reg = re.compile(expr)
    return reg.search(item) is not None
conn = sqlite3.connect('/database.db')
conn.create_function("REGEXP", 2, regexp)
cursor = conn.cursor()
cursor.execute('SELECT * FROM table WHERE column REGEXP ?',['(?i)(?<=\s|^|\W)word(?=\s|$|\W)'])
data=cursor.fetchall()
print(data)

I updated my code as:

import sqlite3
import re
def regexp
def regexp(expr, item):
    try:
        pattern = re.compile(expr, re.I)

        return re.search(pattern, str) is not None
    except Exception as e:
        print(e)


conn = sqlite3.connect('database.db')
conn.create_function("regexp", 2, regexp)
cursor = conn.cursor()
try:
    cursor.execute(r'SELECT * FROM table WHERE column REGEXP ?',[r'(?i)(?<=\s|^|\W)word(?=\s|$|\W)'])
    data=cursor.fetchall()
    print(data)
except Exception as e:
    print(e)

And it throws : look-behind requires fixed-width pattern

Did you try to write first parameter in create_function in lowercase as "regex" ? — Ptit Xav, Apr 22 '21 at 11:27

Wiktor Stribiżew · Accepted Answer · 2021-04-23T09:13:54.707

1

The (?i)(?<=\s|^|\W)word(?=\s|$|\W) pattern contains a lookbehind with alternatives, two of which match a single-char pattern and one of them matches no char (^ only asserts the position at the start of string).

The problem is that Python re does not support lookbehinds of non-fixed length. Let's try to refactor the regex:

(?i)(?<=[\s\W]|^)word(?=[\s\W]|$): Look how single-char matching patterns are grouped with the help of a character class. We now clearly see that [\s\W] can be re-written as \W because \W matches whitespaces. So, the regex can look like
(?i)(?<=\W|^)word(?=\W|$): Better, but it is still not what we need, the alternatives do not match the same amount of chars. However, at this point I can clearly see you want to match a word in between non-word chars or start/end of string.

So, you can use

(?i)(?<!\w)word(?!\w)

with (?<!\w) negative lookbehind and (?!\w) negative lookahead both containing a single \w (no more alternation operator necessary!), which is basically

(?i)\bword\b

with mere word boundaries (in Python code, r'(?i)\bword\b').

However, the pattern variant with the lookarounds can be prefereble in cases the word is a variable, when you use it like

fr'(?i)(?<!\w){re.escape(word)}(?!\w)'

As far as the code is concerned, you have a typo in the regexp method: str cannot be used as the input argument to re.search. You can fix it by using

def regexp(expr, item):
    try:
        return re.search(expr, item, re.I) is not None
    except Exception as e:
        print(e)

See demo:

edited Apr 23 '21 at 09:13

answered Apr 22 '21 at 17:15

Wiktor Stribiżew

607,720
39
448
563

Thank you so much for your help ! Now after I replace this, it throws me a new error "expected string or bytes-like object" – Béa Apr 22 '21 at 17:29
Ok.. but how to solve this problem?? this is related to python because the query works on sqlite browser – Béa Apr 22 '21 at 17:59
@Béa See updated code for `regexp` method. – Wiktor Stribiżew Apr 22 '21 at 18:04
Thank u..But I still receive "expected string or bytes-like object" ... In SQLte the column is a TEXT type so I don't understand – Béa Apr 23 '21 at 09:10
I write the same code but it gives me a lot of expected string or bytes-like object errors ... – Béa Apr 23 '21 at 09:20
@Béa That means the `item` is not of type string. Try `return re.search(expr, str(item), re.I) is not None` – Wiktor Stribiżew Apr 23 '21 at 09:23
1

Thank you soo soo much ! it works !! So happy – Béa Apr 23 '21 at 09:28

python3 sqlite and regex query

1 Answers1