如何在使用python和sqlite3的非ascii查询中获得不区分大小写的查询?

There are some obvious tips and resources about this, but I can't find any direct examples.

关于这一点有一些明显的提示和资源，但是我找不到任何直接的例子。

http://www.sqlite.org/faq.html#q18 says one can override the NOCASE collation, and the like, lower, and upper functions. I've done so, in a Django testcase, but in my test none of them are actually called.

http://www.sqlite.org/faq.html#q18表示可以覆盖NOCASE排序，以及类似的、下、上函数。在Django测试用例中，我已经这样做了，但是在我的测试中，没有一个真正被调用。

from django.test import TestCase

from django.contrib.sites.models import Site from basic.blog.models import Post, Settings

从django.contrib.sites。模型从basic.blog导入站点。模型导入后,设置

class CaseInsenstiveTest(TestCase):
    def setUp(self):
        self.site = Site.objects.create()
        self.settings = Settings.objects.create(site=self.site)
        Settings.get_current = classmethod(lambda cls: self.settings)

    def testLike(self):
        from django.db import connection

        Post.objects.all() # Sets up the connection

        def like(x, y, z=None):
            """Y LIKE X [ESCAPE Z]"""

            assert x.startswith('%') and x.endswith('%')
            like.called = True
            print "like(%r, %r, %r)" % (x, y, z)

            return x[1:-1].lower() in y.lower()
        like.called = False

        def lower(s):
            print "lower(%r)" % (s,)
            return s.lower()

        def upper(s):
            print "upper(%r)" % (s,)
            return s.upper()

        connection.connection.create_function('lower', 1, lower)
        connection.connection.create_function('upper', 1, upper)
        connection.connection.create_function('like', 3, like)

        def NOCASE(a, b):
            print "NOCASE(%r, %r)" % (a, b)
            return cmp(a.lower(), b.lower())
        connection.connection.create_collation('NOCASE', NOCASE)

        Post.objects.create(slug='foo', title='Foo')
        Post.objects.filter(title__icontains='foo')

it seems not a single of the registered functions or the collation are actually called. Can anyone point out what is wrong?

似乎没有一个已注册的函数或排序规则被实际调用。有人能指出是什么问题吗?

NOTE: I know the like function is not correct, yet. I'm just trying to figure out when what is called so I know what I need to override and how.

注意:我知道like函数是不正确的。我只是想弄清楚什么时候被调用，这样我就知道需要重写什么以及如何重写。

1 个解决方案

#1

It seems to work fine outside django:

《被解救的姜戈》似乎还不错:

So maybe you have a django problem? Are you sure the table fields were created using the NOCASE collation?

那么，也许你遇到了django的问题?您确定表字段是使用NOCASE排序规则创建的吗?

import sqlite3

def NOCASE(a, b):
    print 'comparing %r with %r...' % (a, b)
    return cmp(a.lower(), b.lower())

con = sqlite3.connect('')

cur = con.cursor()
cur.execute('CREATE TABLE foo (id INTEGER, text VARCHAR collate NOCASE)')
cur.executemany('INSERT INTO foo (id, text) VALUES (?, ?)', [
    (1, u'test'), (2, u'TEST'), (3, u'uest'), (4, u'UEST')])
con.commit()

con.create_collation('NOCASE', NOCASE)

cur = con.cursor()
cur.execute('SELECT * FROM foo ORDER BY text ASC')
print cur.fetchall()

Output:

输出:

comparing 'test' with 'TEST'...
comparing 'test' with 'uest'...
comparing 'TEST' with 'uest'...
comparing 'TEST' with 'UEST'...
comparing 'uest' with 'UEST'...
[(1, u'test'), (2, u'TEST'), (3, u'uest'), (4, u'UEST')]

Similarly, using a defined function works fine (same dataset)

类似地，使用一个定义好的函数(相同的数据集)

def my_lower(text):
    print "I'm lowering %r myself" % (text,)
    return text.lower()

con.create_function('lower', 1, my_lower)
cur.execute('SELECT lower(text) FROM foo')

The output:

输出:

I'm lowering u'test' myself
I'm lowering u'TEST' myself
I'm lowering u'uest' myself
I'm lowering u'UEST' myself
[(u'test',), (u'test',), (u'uest',), (u'uest',)]

Similarly, for LIKE operations, you have to register the function in its 2-arguments form (X LIKE Y) and in its 3-arguments form (X LIKE Y ESCAPE Z) if you plan to support both forms:

类似地，对于LIKE操作，您必须在它的2个参数形式(X LIKE Y)和它的3个参数形式(X LIKE Y ESCAPE Z)中注册该函数，如果您计划支持这两种形式:

def my_like(a, b, escape=None):
    print 'checking if %r matches %r' % (a, b)
    return b.lower().startswith(a[0].lower())

con.create_function('like', 2, my_like) # X LIKE Y
con.create_function('like', 3, my_like) # X LIKE Y ESCAPE Z
cur.execute('SELECT * FROM foo WHERE text LIKE ?', (u't%',))

yields the output:

输出:

checking if u't%' matches u'test'
checking if u't%' matches u'TEST'
checking if u't%' matches u'uest'
checking if u't%' matches u'UEST'
[(1, u'test'), (2, u'TEST')]

#1