sqlite3 : create function regexp with python

Question

Python 3.6. I'm trying to create a REGEXP function for sqlite3. I have the error : OperationalError: wrong number of arguments to function REGEXP()

Here is my code :

import sqlite3
import re

def fonctionRegex(mot):
    patternRecherche = re.compile(r"\b"+mot.lower()+"\\b")
    return patternRecherche.search(item) is not None

dbName = 'bdd.db'
connexion = sqlite3.connect(dbName)
leCursor = connexion.cursor()
connexion.create_function("REGEXP", 1, fonctionRegex)

mot = 'trump'
data = leCursor.execute('SELECT * FROM tweet WHERE texte REGEXP ?',mot).fetchall()

Thanks

JOHN_16 · Accepted Answer · 2018-09-21 11:37:08Z

You do something wrong. That is more correct example

import sqlite3
import re


def functionRegex(value, pattern):
    c_pattern = re.compile(r"\b" + pattern.lower() + r"\b")
    return c_pattern.search(value) is not None


connection = sqlite3.connect(':memory:')
cur = connection.cursor()
cur.execute('CREATE TABLE tweet(msg TEXT)')
cur.execute('INSERT INTO tweet VALUES("This is a test message")')
cur.execute('INSERT INTO tweet VALUES("Another message")')

connection.create_function("REGEXP", 2, functionRegex)

print(cur.execute('SELECT * FROM tweet WHERE REGEXP(msg, ?)', ('test',)).fetchall())
print(cur.execute('SELECT * FROM tweet WHERE REGEXP(msg, ?)', ('message',)).fetchall())
print(cur.execute('SELECT * FROM tweet WHERE ? REGEXP msg', ('message',)).fetchall())

will print

[('This is a test message',)]
[('This is a test message',), ('Another message',)]
[('This is a test message',), ('Another message',)]

CL. · Accepted Answer · 2018-04-27 16:20:16Z

2

The documentation says:

The REGEXP operator is a special syntax for the regexp() user function. … the "X REGEXP Y" operator will be implemented as a call to "regexp(Y,X)".

Your function must have two parameters.

answered Apr 27, 2018 at 16:20

CL.

182k18 gold badges241 silver badges282 bronze badges

2 Comments

TmSmth Over a year ago

So i have to write data = leCursor.execute('SELECT * FROM tweet WHERE texte pattern REGEXP ?',mot).fetchall() and connexion.create_function("REGEXP", 2, fonctionRegex) ?

CL. Over a year ago

The function is fonctionRegex.

Collectives™ on Stack Overflow

sqlite3 : create function regexp with python

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related