Skip to main content
 首页 » 编程设计

python之如何使用SQLAlchemy在SQLite上创建全文检索索引并进行查询

2025年01月19日9落叶无声

我正在创建一个可以执行基本操作的简单应用程序。 SQLite用作数据库。我想执行通配符搜索,但我知道它的性能很差。我想尝试全文搜索,但我无法在 怎么做。我确认 SQLite 有 full text search support .这是我的示例代码。

from flask_sqlalchemy import SQLAlchemy 
 
db = SQLAlchemy() 
 
class Person(db.Model): 
    id = db.Column(db.Integer, primary_key=True) 
    name = db.Column(db.Text, unique=True, nullable=False) 
    thumb = db.Column(db.Text, nullable=False, default="") 
 
    role = db.relationship("Role", backref="person", cascade="delete") 
 
 
class Role(db.Model): 
    id = db.Column(db.Integer, primary_key=True) 
    person_id = db.Column(db.Integer, db.ForeignKey(Person.id, ondelete="CASCADE"), nullable=False) 
    role = db.Column(db.Text, nullable=False) 

如何创建 FTS 索引并使用 SQLAlchemy 查询它。例如,在 Person 中搜索姓名。

请您参考如下方法:

FTS5 提供了支持全文搜索的虚拟表。换句话说,您不能在现有表中的列上创建全文索引。相反,您可以创建一个 FTS5 虚拟表并从原始表中复制相关数据以进行索引。为了避免两次存储相同的数据,您可以将其设为 external content table。 ,尽管您仍然必须确保 FTS5 表保持同步,无论是手动还是使用触发器。

您可以创建一个通用的自定义 DDL 结构来处理创建一个镜像另一个表的 FTS5 虚拟表:

class CreateFtsTable(DDLElement): 
    """Represents a CREATE VIRTUAL TABLE ... USING fts5 statement, for indexing 
    a given table. 
 
    """ 
 
    def __init__(self, table, version=5): 
        self.table = table 
        self.version = version 
 
 
@compiles(CreateFtsTable) 
def compile_create_fts_table(element, compiler, **kw): 
    """ 
    """ 
    tbl = element.table 
    version = element.version 
    preparer = compiler.preparer 
    sql_compiler = compiler.sql_compiler 
 
    tbl_name = preparer.format_table(tbl) 
    vtbl_name = preparer.quote(tbl.name + "_idx") 
 
    text = "\nCREATE VIRTUAL TABLE " 
    text += vtbl_name + " " 
    text += "USING fts" + str(version) + "(" 
 
    separator = "\n" 
 
    pk_column, = tbl.primary_key 
    columns = [col for col in tbl.columns if col is not pk_column] 
 
    for column in columns: 
        text += separator 
        separator = ", \n" 
        text += "\t" + preparer.format_column(column) 
 
        if not isinstance(column.type, String): 
            text += " UNINDEXED" 
 
    text += separator 
    text += "\tcontent=" + sql_compiler.render_literal_value( 
            tbl.name, String()) 
 
    text += separator 
    text += "\tcontent_rowid=" + sql_compiler.render_literal_value( 
            pk_column.name, String()) 
 
    text += "\n)\n\n" 
    return text 

给定的实现有点天真,默认情况下索引所有文本列。创建的虚拟表通过在原表名后添加_idx隐式命名。

但仅此还不够,如果您想自动保持表与触发器同步,并且由于您只为一个表添加索引,您可以选择在迁移脚本中使用文本 DDL 结构:

def upgrade(): 
    ddl = [ 
        """ 
        CREATE VIRTUAL TABLE person_idx USING fts5( 
            name, 
            thumb UNINDEXED, 
            content='person', 
            content_rowid='id' 
        ) 
        """, 
        """ 
        CREATE TRIGGER person_ai AFTER INSERT ON person BEGIN 
            INSERT INTO person_idx (rowid, name, thumb) 
            VALUES (new.id, new.name, new.thumb); 
        END 
        """, 
        """ 
        CREATE TRIGGER person_ad AFTER DELETE ON person BEGIN 
            INSERT INTO person_idx (person_idx, rowid, name, thumb) 
            VALUES ('delete', old.id, old.name, old.thumb); 
        END 
        """, 
        """ 
        CREATE TRIGGER person_au AFTER UPDATE ON person BEGIN 
            INSERT INTO person_idx (person_idx, rowid, name, thumb) 
            VALUES ('delete', old.id, old.name, old.thumb); 
            INSERT INTO person_idx (rowid, name, thumb) 
            VALUES (new.id, new.name, new.thumb); 
        END 
        """ 
    ] 
 
    for stmt in ddl: 
        op.execute(sa.DDL(stmt)) 

如果您的个人表包含现有数据,请记住将这些数据也插入到创建的虚拟表中以进行索引。

为了实际使用创建的虚拟表,您可以创建一个 non-primary mapper对于人:

person_idx = db.Table('person_idx', db.metadata, 
                      db.Column('rowid', db.Integer(), primary_key=True), 
                      db.Column('name', db.Text()), 
                      db.Column('thumb', db.Text())) 
 
PersonIdx = db.mapper( 
    Person, person_idx, non_primary=True, 
    properties={ 
        'id': person_idx.c.rowid 
    } 
) 

并使用例如 MATCH 进行全文查询:

db.session.query(PersonIdx).\ 
    filter(PersonIdx.c.name.op("MATCH")("john")).\ 
    all() 

请注意,结果是 Person 对象的列表。 PersonIdx 只是一个 Mapper .


正如 Victor K 所指出的那样,不推荐使用非主映射器,新的替代方法是使用 aliased() .设置基本相同,但是 rowid 到 id 映射需要在创建 person_idx Table 时使用列的key参数:

person_idx = db.Table('person_idx', db.metadata, 
                      db.Column('rowid', db.Integer(), key='id', primary_key=True), 
                      db.Column('name', db.Text()), 
                      db.Column('thumb', db.Text())) 

而不是新的映射器创建别名:

PersonIdx = db.aliased(Person, person_idx, adapt_on_names=True) 

别名更像映射类,因为您不通过 .c 访问映射属性,而是直接访问:

db.session.query(PersonIdx).\ 
    filter(PersonIdx.name.op("MATCH")("john")).\ 
    all()