Python3.6为SQLite3创建REGEXP函数时遇参数错误问题求助

Hey there, let's fix that OperationalError you're hitting with SQLite3's REGEXP function. The root issue here is how SQLite expects the REGEXP function to work, plus a couple of small missteps in your code.

What's Going Wrong? #

SQLite's REGEXP operator relies on a user-defined function that takes two arguments:

1. The regex pattern you want to match
2. The string you're checking against it

When you write a query like column REGEXP 'trump', SQLite internally calls REGEXP('trump', column_value). But you created your function with only 1 parameter (connexion.create_function("REGEXP", 1, fonctionRegex)), which is why you get the "wrong number of arguments" error.

On top of that, your fonctionRegex uses an undefined variable item—that would cause another error once you fix the parameter count.

Fixed Code #

Here's the corrected version of your code, with key improvements:

import sqlite3
import re

# Updated function: takes two arguments (pattern, string_to_check)
def fonctionRegex(pattern, string_to_check):
    # Escape special regex characters to avoid injection issues
    safe_pattern = re.escape(pattern.lower())
    # Compile regex with word boundaries
    pattern_recherche = re.compile(rf"\b{safe_pattern}\b")
    # Check if pattern matches the target string
    return pattern_recherche.search(string_to_check.lower()) is not None

dbName = 'bdd.db'
connexion = sqlite3.connect(dbName)
leCursor = connexion.cursor()

# Create REGEXP function with 2 arguments (not 1!)
connexion.create_function("REGEXP", 2, fonctionRegex)

mot = 'trump'
# Example query (replace 'your_table' and 'your_column' with actual names)
data = leCursor.execute('SELECT * FROM your_table WHERE your_column REGEXP ?', (mot,))

# Fetch and process results
results = data.fetchall()
for row in results:
    print(row)

# Clean up the connection
connexion.close()

Key Fixes Breakdown: #

• Adjusted fonctionRegex to accept two parameters (matches SQLite's expected REGEXP function signature)
• Added re.escape() to safely handle patterns with special regex characters (like . or *)
• Updated create_function to specify 2 as the number of arguments
• Fixed the undefined item variable by using the second function parameter
• Used a parameterized query (safer than string concatenation for passing values)

内容的提问来源于stack exchange，提问作者TmSmth