Python Sqlite3中LOWER/UPPER函数多语言字符转换失效问题求助

问题重现 #

以下代码运行后，SQLite的LOWER和UPPER函数无法正确处理非英语字符：

import sqlite3

connection = sqlite3.connect('test.db')
cursor = connection.cursor()
cursor.execute("DROP TABLE `products`")
cursor.execute('''CREATE TABLE IF NOT EXISTS products (
    id INTEGER PRIMARY KEY, 
    name TEXT
)''')

cursor.execute("INSERT INTO products (name) VALUES (?)", ('Päärynä',))
cursor.execute("INSERT INTO products (name) VALUES (?)", ('Банан',))
cursor.execute("INSERT INTO products (name) VALUES (?)", ('Banana',))
connection.commit()
cursor.execute(
    "SELECT LOWER(name), UPPER(name), name FROM products WHERE name='Päärynä' OR name='Banana' OR name='Банан'")
result = cursor.fetchall()
connection.close()
print(result)

运行结果：

[('päärynä', 'PääRYNä', 'Päärynä'), ('Банан', 'Банан', 'Банан'), ('banana', 'BANANA', 'Banana')]

可见俄语Банан的大小写无变化，芬兰语Päärynä的大写转换不完整，这是因为SQLite默认的LOWER/UPPER仅支持ASCII字符集的转换。

解决方案 #

方案1：启用SQLite ICU扩展

SQLite的ICU（International Components for Unicode）扩展提供完整的Unicode大小写转换支持，步骤如下：

1. 确保系统已安装ICU库（不同系统可通过包管理器安装，如Ubuntu用apt install libicu-dev，macOS用brew install icu4c）。
2. 在Python代码中加载ICU扩展，使用其提供的专用转换函数：

import sqlite3

connection = sqlite3.connect('test.db')
# 开启扩展加载权限
connection.enable_load_extension(True)
try:
    # 加载ICU扩展，文件名根据系统和ICU版本调整
    # Windows示例：connection.load_extension('icudt68.dll')
    # Linux示例：connection.load_extension('libicu.so.68')
    # macOS示例：connection.load_extension('/usr/local/opt/icu4c/lib/libicuio.dylib')
    connection.load_extension('libicu.so.68')
except Exception as e:
    print(f"加载ICU扩展失败: {e}")
connection.enable_load_extension(False)

cursor = connection.cursor()
cursor.execute("DROP TABLE `products`")
cursor.execute('''CREATE TABLE IF NOT EXISTS products (
    id INTEGER PRIMARY KEY, 
    name TEXT
)''')

cursor.execute("INSERT INTO products (name) VALUES (?)", ('Päärynä',))
cursor.execute("INSERT INTO products (name) VALUES (?)", ('Банан',))
cursor.execute("INSERT INTO products (name) VALUES (?)", ('Banana',))
connection.commit()

# 使用ICU提供的icu_lower/icu_upper函数
cursor.execute(
    "SELECT icu_lower(name), icu_upper(name), name FROM products WHERE name='Päärynä' OR name='Banana' OR name='Банан'")
result = cursor.fetchall()
connection.close()
print(result)

运行后会得到正确结果：

[('päärynä', 'PÄÄRYNÄ', 'Päärynä'), ('банан', 'БАНАН', 'Банан'), ('banana', 'BANANA', 'Banana')]

方案2：在Python层面处理大小写转换

如果无法加载ICU扩展，可先查询原始数据，再通过Python的字符串方法处理（Python原生支持Unicode大小写转换）：

import sqlite3

connection = sqlite3.connect('test.db')
cursor = connection.cursor()
cursor.execute("DROP TABLE `products`")
cursor.execute('''CREATE TABLE IF NOT EXISTS products (
    id INTEGER PRIMARY KEY, 
    name TEXT
)''')

cursor.execute("INSERT INTO products (name) VALUES (?)", ('Päärynä',))
cursor.execute("INSERT INTO products (name) VALUES (?)", ('Банан',))
cursor.execute("INSERT INTO products (name) VALUES (?)", ('Banana',))
connection.commit()

# 查询原始数据
cursor.execute(
    "SELECT name FROM products WHERE name='Päärynä' OR name='Banana' OR name='Банан'")
raw_results = cursor.fetchall()
# 在Python中处理大小写转换
processed_results = [(name.lower(), name.upper(), name) for (name,) in raw_results]
connection.close()
print(processed_results)

该方案优点是兼容性强，无需依赖扩展；缺点是数据量较大时，全部拉取到Python处理会有性能损耗。

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