Python Forum

Hi

Im trying to do a regex search on an sqlite database where the expressions are held in a list. I have the connection working as shown by the first example in the code below but when I try to use the second example it fails. I presume its to do with formatting of the expression. The error is shown underneath.

Id be very grateful for any help as I cant see the problem

import sqlite3, re

def regexp(expr, item):
    reg = re.compile(expr)
    return reg.search(item) is not None

database = "d:/Python/Wordfeud/Dictionary.db"
conn = sqlite3.connect(database)
conn.create_function("REGEXP", 2, regexp)
cursor = conn.cursor()

#This works
cursor.execute('SELECT Word FROM Tbwords WHERE word REGEXP ?',['^.?.?P.?.?.?RE.?.?C.?.?.'])
data=cursor.fetchone()
print(data)

#This doesnt
list=['.?.?P.?.?.?RE.?.?C.?.?.']
regex=list[0]
re_end="$']" #end of re mask
re_start="['^" #beginning of re mask
regex=re_start+regex+re_end

sqltxt="'SELECT Word FROM Tbwords WHERE word REGEXP ?'," + regex
cursor.execute(sqltxt)
data=cursor.fetchone()
print(data)

================== RESTART: D:\Python\Wordfeud\Forumpost.py ==================
('APPARENCIES',)
Traceback (most recent call last):
File "D:\Python\Wordfeud\Forumpost.py", line 26, in <module>
cursor.execute(sqltxt)
sqlite3.OperationalError: near "'SELECT Word FROM Tbwords WHERE word REGEXP ?'": syntax error
>>>

So you want to serialize the arguments to cursor.execute. You could do

import ast
argstxt = "('SELECT Word FROM Tbwords WHERE word REGEXP ?', ['^.?.?P.?.?.?RE.?.?C.?.?.'])"
args = ast.literal_eval(argstxt)
cursor.execute(*args)

(Dec-01-2021, 08:27 PM)bontwoody Wrote: [ -> ]
#This works
cursor.execute('SELECT Word FROM Tbwords WHERE word REGEXP ?',['^.?.?P.?.?.?RE.?.?C.?.?.'])

That's how cursors work. You pass the query, with params to be replaced as "?", and then the values that should be substituted in.

Quote:

#This doesnt
list=['.?.?P.?.?.?RE.?.?C.?.?.']
regex=list[0]
re_end="$']" #end of re mask
re_start="['^" #beginning of re mask
sqltxt="'SELECT Word FROM Tbwords WHERE word REGEXP ?'," + regex
cursor.execute(sqltxt)

You're not passing an argument to the cursor for the value in this example. You've modified your query to end with WHERE word REGEXP ?',regex

(Dec-01-2021, 09:55 PM)Gribouillis Wrote: [ -> ]So you want to serialize the arguments to cursor.execute. You could do
import ast
argstxt = "('SELECT Word FROM Tbwords WHERE word REGEXP ?', ['^.?.?P.?.?.?RE.?.?C.?.?.'])"
args = ast.literal_eval(argstxt)
cursor.execute(*args)

Brilliant just what I required thanks

bontwoody

Gribouillis

nilamo

bontwoody