Программисты, которые впервые сталкиваются с базой SQLIte3 задаются примерно таким вопросом:
Столкнулся с тем, что сравнение русскоязычных строк идет с учетом регистра. Искал инфу, как от этого избавиться, но не нашел подходящего решения. Пробовал выполнять
PRAGMA case_sensitive_like = false
, но не помогает. Можно ли как-то решить эту проблему или придется создавать отдельное поле, в котором будет одинаковый регистр?
Например:
Если в колонках базы данных сохранены значения русскоязычных строк "Мир" и "мир", то запрос SELECT * FROM tab WHERE tab.col = "Мир"
вернет только одну строку с колонкой "Мир". То же самое касается оператора базы данных LIKE
.
Для решения подобных задач объект Connection
модуля sqlite3 предоставляет механизм переопределения встроенных функций connect.create_function()
и правил сравнения connect.create_collation()
в пользовательском коде.
Выдержка из документации:
Из документации видно, что конфигурация SQLite по умолчанию поддерживает только сравнение символов ASCII без учета регистра. Причина этого заключается в том, что для полного сравнения Unicode без учета регистра и преобразования регистра требуются таблицы и логика, которые почти удвоили бы размер библиотеки SQLite.
Вместо полной поддержки регистра Unicode по умолчанию SQLite предоставляет возможность связываться с внешними процедурами сравнения и преобразования Unicode. Приложение может перегрузить встроенную последовательность сортировки NOCASE и встроенные функции
like()
,upper()
иlower()
, используя встроенные возможности SQLite. Разработчики могут написать свои собственные перегрузки на основе своих собственных подпрограмм сравнения с поддержкой Unicode, уже содержащихся в их проекте.
import sqlite3 from contextlib import closing # Переопределение функции преобразования к нижнему регистру def sqlite_lower(value_): return value_.lower() # Переопределение функции преобразования к верхнему геристру def sqlite_upper(value_): return value_.upper() # Переопределение правила сравнения строк def ignore_case_collation(value1_, value2_): if value1_.lower() == value2_.lower(): return 0 elif value1_.lower() < value2_.lower(): return -1 else: return 1 # Создание подключения к базе данных con = sqlite3.connect('db.sqlite') con.create_collation("NOCASE", ignore_case_collation) con.create_function("LOWER", 1, sqlite_lower) con.create_function("UPPER", 1, sqlite_upper) # Так же можно переопределить своей функцией оператор `LIKE` # con.create_function("LIKE", 2, some_function) with closing(connect.cursor()) as cur: # Регистронезависимой поиск русских слов при помощи LIKE cur.execute("SELECT * FROM books WHERE LOWER(name) LIKE ? \ or LOWER(autor) LIKE ?", (name.lower(), autor.lower(),)) # Сравнение поля без учета регистра cur.execute("SELECT * FROM books WHERE name = ? \ or autor = ? COLLATE NOCASE", (name, autor,))
Для того, чтобы сделать регистронезависимое уникальное поле в русской кодировке, то следует указать это при создании таблицы.
CREATE TABLE IF NOT EXISTS books ( id INTEGER PRIMARY KEY AUTOINCREMENT, autor TEXT NOT NULL DEFAULT '', name TEXT NOT NULL DEFAULT '', descript TEXT NOT NULL DEFAULT '', CONSTRAINT autor_name_unique UNIQUE (autor COLLATE NOCASE, name COLLATE NOCASE)
или
CREATE TABLE IF NOT EXISTS books ( id INTEGER PRIMARY KEY AUTOINCREMENT, autor TEXT NOT NULL DEFAULT '' COLLATE NOCASE, name TEXT NOT NULL DEFAULT '' COLLATE NOCASE, descript TEXT NOT NULL DEFAULT '', CONSTRAINT autor_name_unique UNIQUE (autor, name)
Ну и конечно при каждом соединении с базой данных указывать con.create_collation("NOCASE", ignore_case_collation)
как в примере выше.