Python操作SQLite时UPDATE/INSERT字符串遇fts4模块缺失错误求助

Hey there, let's tackle this confusing error you're hitting. It's super weird that the same code works on other Python versions and Linux, right? Let's break down why this happens and walk through the fixes.

Why the Error Pops Up #

The core issue comes down to how SQLite is compiled with different Python versions and platforms:

• On Linux and newer Python releases (3.6.5+, 2.7.12), the bundled SQLite library includes support for full-text search modules (FTS3/FTS4) via the SQLITE_ENABLE_FTS3 and SQLITE_ENABLE_FTS4 compile flags.
• But Python 3.5.2 on Windows ships with a SQLite build that doesn't have these flags enabled. When SQLite parses your SQL statement (even a simple UPDATE with a quoted date), older versions can accidentally trigger a check for FTS modules—leading to that head-scratching "no such module: fts4" error, even though you're not using FTS features at all.

Quick Fixes (No Compilation Required) #

1. Switch to Parameterized Queries #

Instead of hardcoding values directly into your SQL string, use parameterized placeholders. This avoids the problematic parsing that triggers the FTS module check and also protects against SQL injection:

import sqlite3
db = sqlite3.connect('test.db')
cursor = db.cursor()
# Use ? as placeholders for dynamic values
cursor.execute("UPDATE info SET date = ? WHERE id = ?", ('2003-03-14 12:34:20', 10))
db.commit()  # Don't forget to save your changes!
db.close()

This is the fastest workaround and a best practice for database queries anyway.

2. Upgrade Your Python Version #

The simplest long-term fix is to upgrade to Python 3.6 or newer on Windows. Newer Python versions bundle SQLite with FTS3/FTS4 support enabled by default, so this error will vanish entirely.

If You Must Stick to Python 3.5.2: Compile Custom SQLite with FTS Support #

If upgrading isn't an option, you'll need to build a custom SQLite DLL with FTS modules enabled and replace the one bundled with your Python installation:

• Step 1: Download the latest stable SQLite source code from the official SQLite repository.
• Step 2: Compile the DLL with the required flags. For example, using MSVC:

  cl /DSQLITE_ENABLE_FTS3=1 /DSQLITE_ENABLE_FTS4=1 /LD sqlite3.c -o sqlite3.dll
  

• Step 3: Navigate to your Python 3.5.2 installation folder, find the DLLs directory, and replace the existing sqlite3.dll with your compiled version (make a backup of the original first to avoid breaking things!).

Double-Check Your Table Structure #

Just to rule out edge cases, confirm your info table is a regular table (not an FTS4 virtual table). Run this query to verify:

cursor.execute("PRAGMA table_info(info);")
print(cursor.fetchall())

If it's a standard table with a DATE or TEXT field for date, the fixes above will work perfectly.

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