Problem with regexp python and sqlite

问题:

I try to check a string with a pattern using a regex with python on a sqlite database. I have problem when I try de search string having " with a patern using " For exemple:

cur.execute("insert into articles(id,subject) values (1,'aaa\"test\"')")
cur.execute("select id,subject from articles where id = 1")
print (cur.fetchall())

cur.execute("select subject from articles where  subject regexp '\"test\"' ")
print (cur.fetchall())

I should \" before regexp other way compiler dont like... syntaxe error

[(1, 'aaa"test"')]
[] <????? should found 

Somebody know how to do that ?

My regexp function :con.create_function("regexp", 2, regexp)

回答1:

Use parametrized sql. Then you don't need to escape the quotes yourself:

import sqlite3
import re

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

conn = sqlite3.connect(':memory:')
conn.create_function("REGEXP", 2, regexp)
cursor = conn.cursor()
cursor.execute('CREATE TABLE foo (bar TEXT)')
cursor.executemany('INSERT INTO foo (bar) VALUES (?)',[('aaa"test"',),('blah',)])
cursor.execute('SELECT bar FROM foo WHERE bar REGEXP ?',['"test"'])
data=cursor.fetchall()
print(data)

yields

[(u'aaa"test"',)]

回答2:

You can use triple escapes, or a raw string.

Your doing:

>>> print("select subject from articles where  subject regexp '\"test\"' ")
select subject from articles where  subject regexp '"test"'

Use a raw string, which is a r'string with a r in front':

>>> print(r"select subject from articles where  subject regexp '\"test\"' ")
select subject from articles where  subject regexp '\"test\"' 

Or triple escapes (\\\):

>>> print("select subject from articles where  subject regexp '\\\"test\\\"' ")
select subject from articles where  subject regexp '\"test\"'

回答3:

Another parameterized query example...

For the situation where you have to supply your own REGEX function to the database - I guess Python sqlite3 doesn't always have it set up by default.

In another example, the custom REGEX function is compiling the same expression for each and every match. There's a way around that. The example below also has a comment at the bottom of another way to define the REGEX operation.

You can get around compiling the expression every time the expression is used (for each match) for queries dealing with a lot of data by creating a custom function for each query and compiling the expression only once. Below self._conn is the database connection, and curs is a cursor from it.

    # Form an expression to match nicknames with the last 3 characters
    # varying.
    nick_expr = re.sub(r"[0-9_\-|]{0,3}$", r"[0-9_\-|]{0,3}$", nick)
    nick_expr = re.compile(nick_expr, re.I)

    # Create custom sqlite3 function using the compiled expression.
    self._conn.create_function("NICKEXPR",
                               1,
                               lambda nick: nick_expr.match(nick) != None)

    # Create temporary table from first pass query.
    curs.execute(
        """ CREATE TEMP TABLE temp_table1 AS
           SELECT  DISTINCT *
           FROM    users
           WHERE   NICKEXPR(nick)
               OR  host LIKE ?
               OR  (account<>'' AND account LIKE ?)
               OR  (address<>'' AND address=?)
       """, (host, account, address))

    # Set up the REGEXP operator/function for the sqlite3 database.
    #self._conn.create_function(
    #                       'REGEXP', 2, 
    #                       lambda exp, item : re.find(exp, item) != None)