Python SQLite removes/escapes part of regular expression pattern in user defined function
<p>I did a simple replace implementation for regular expressions in python sqlite3:</p>

<pre><code>import sqlite3, re

db = sqlite3.connect(':memory:')
c = db.cursor()
c.executescript("""
create table t2 (n REAL, v TEXT, t TEXT);
insert into t2 VALUES (6, "F", "ef");
insert into t2 VALUES (1, "A", "aa");
insert into t2 VALUES (2, "B", "be");
insert into t2 VALUES (4, "D", "de");
insert into t2 VALUES (5, "E", "ee");
insert into t2 VALUES (3, "C", "ze");
    """);
db.commit()

def preg_replace(string, pattern, replace):
    return re.sub(pattern, replace, string)

db.create_function('replace',1,preg_replace)

c = db.cursor()
# This does not do anything
c.execute("UPDATE t2 SET t=replace(t,?,?)",('e$','ee'))
db.commit()

c = db.cursor()
c.execute("select * from t2")
print c.fetchall()

// This makes 'be' to 'bee'
print preg_replace("be","e$","ee")
</code></pre>

<p>My problem is now that my UPDATE command does not replace 'e' at the end of the table entries.<br>
If I use just 'e' as a pattern it works fine ('be' ends up as 'bee').<br>
If I manually change 'be' in the table to 'be$', it gets modiefied to 'bee'.</p>

<p>However, I can replace the 'e' at the end of the string if I use the preg_replace function directly.</p>

<p>I do not understand why. is there some string escaping going on when the input for my user defined function goes to sqlite? Thanks a lot in advance.</p>

<p>PS: Running Python 2.7.3</p>

<p>You must tell <a href="https://docs.python.org/2/library/sqlite3.html#sqlite3.Connection.create_function" rel="nofollow">create_function()</a> how many parameters the function has.</p>

<p>Your <code>preg_replace</code> will be used if the SQL calls <code>replace()</code> with one parameter.</p>

python sqlite sqlite3