如何在django中使用sqlite3数据库中的全文搜索？ [英] How to use full-text search in sqlite3 database in django?

问题描述

我正在使用具有固定数据库内容的sqlite3数据库的django应用程序。通过固定我的意思是数据库的内容不会随着时间而改变。该模型是这样的：

I am working on a django application with sqlite3 database, that has a fixed database content. By fixed I mean the content of the db won't change over time. The model is something like this:

class QScript(models.Model):
    ch_no = models.IntegerField()
    v_no = models.IntegerField()
    v = models.TextField()

表中有大约6500条记录。给出一个可能会丢失一些单词的文本，或者是一些拼写错误的单词，我需要确定它的 ch_no 和 v_no 。例如，如果db中有一个 v 字段，那么文本这是一个例子，一个给定的文本，如This一个egsample verse应该给我从数据库的 ch_no 和 v_no 。这可以使用全文搜索来完成。

There are around 6500 records in the table. Given a text that may have some words missing, or some words misspelled, I need to determine its ch_no and v_no. For example, if there is a v field in db with text "This is an example verse", a given text like "This an egsample verse" should give me the ch_no and v_no from db. This can be done using Full text search I believe.

1. 可以全文搜索吗？我从我研究过的猜测中可以看出，如 sqlite3第页所示：文本搜索是谷歌，雅虎和Bing对万维网上放置的文档/ 。引用SO，我阅读了这篇文章还有很多其他人，但没有发现任何符合我的要求的 。

1. can full-text search do this? My guess from what I have studied, it can, as said in sqlite3 page: full-text searches is "what Google, Yahoo, and Bing do with documents placed on the World Wide Web". Cited in SO, I read this article too, along with many others, but didn't find anything that closely matches my requirements.

如何使用FJ在django模型中？我读了这个，但没有帮助。似乎太过时了。请阅读此处：...需要直接操纵数据库以添加全文索引。搜索主要提供MySQL相关信息，但我需要在sqlite3中执行。那么如何在sqlite3中执行直接操作？

How to use FTS in django models? I read this but it didn't help. It seems too outdated. Read here that: "...requires direct manipulation of the database to add the full-text index". Searching gives mostly MySQL related info, but I need to do it in sqlite3. So how to do that direct manipulation in sqlite3?

---

编辑：

我选择粘贴sqlite3是否正确？或者我应该使用不同的东西（如haystack +弹性搜索，如 Alex Morozov 所述）？我的数据库不会变得更大，我已经研究了对于小型数据库，sqlite几乎总是更好（我的情况与第四个

---

Is my choice of sticking to sqlite3 correct? Or should I use something different (like haystack+elasticsearch as said by Alex Morozov)? My db will not grow any larger, and I have studied that for small sized db, sqlite is almost always better (my situation matches the fourth in sqlite's when to use checklist).

推荐答案

I认为当sqlite是一个惊人的软件，它的全文搜索功能是非常有限的。相反，您可以使用干草堆 Django应用程序对某些后端像弹性搜索。有了这个设置（并且仍然能够访问你的sqlite数据库）似乎是FTS中最强大和灵活的方式。

I think that while sqlite is an amazing piece of software, its full-text search capabilities are quite limited. Instead you could index your database using Haystack Django app with some backend like Elasticsearch. Having this setup (and still being able to access your sqlite database) seems to me the most robust and flexible way in terms of FTS.

Elasticsearch有一个模糊搜索（简而言之，它将处理你的egsample查询）。所以你需要的是一个正确的查询类型：

Elasticsearch has a fuzzy search based on the Levenshtein distance (in a nutshell, it would handle your "egsample" queries). So all you need is to make a right type of query:

from haystack.forms import SearchForm
from haystack.generic_views import SearchView
from haystack import indexes


class QScriptIndex(indexes.SearchIndex, indexes.Indexable):
    v = indexes.CharField(document=True)

    def get_model(self):
        return QScript


class QScriptSearchForm(SearchForm):
    text_fuzzy = forms.CharField(required=False)    

    def search(self):        
        sqs = super(QScriptSearchForm, self).search()

        if not self.is_valid():
            return self.no_query_found()

        text_fuzzy = self.cleaned_data.get('text_fuzzy')
        if text_fuzzy:
            sqs = sqs.filter(text__fuzzy=text_fuzzy)

        return sqs


class QScriptSearchView(SearchView):        
    form_class = QScriptSearchForm

更新：只要 PostgreSQL 具有Levenshtein距离功能，您还可以利用它作为Haystack后端以及独立的搜索引擎。如果您选择第二种方式，则必须执行自定义查询表达式，如果您使用最新版本的Django，则相对容易。

Update: As long as PostgreSQL has the Levenshtein distance function, you could also leverage it as the Haystack backend as well as a standalone search engine. If you choose the second way, you'll have to implement a custom query expression, which is relatively easy if you're using a recent version of Django.

这篇关于如何在django中使用sqlite3数据库中的全文搜索？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！