SQLAlchemy中MySQL全文搜索的正确用法

9 投票

1 回答

9702 浏览

提问于 2025-04-17 16:27

我想在我的一个SQLAlchemy映射对象的多个文本字段中进行全文搜索。同时，我希望我的映射对象能够支持外键和事务。

我打算使用MySQL来进行全文搜索。不过，我了解到MySQL只能在MyISAM表上进行全文搜索，而MyISAM表不支持事务和外键。

为了实现我的目标，我计划创建两个表。我的代码大概是这样的：

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    description = Column(Text)

users_myisam = Table('users_myisam', Base.metadata,
                     Column('id', Integer),
                     Column('name', String(50)),
                     Column('description', Text),
                     mysql_engine='MyISAM')

conn = Base.metadata.bind.connect()
conn.execute("CREATE FULLTEXT INDEX idx_users_ftxt \
              on users_myisam (name, description)")

然后，为了进行搜索，我会运行这个：

q = 'monkey'
ft_search = users_myisam.select("MATCH (name,description) AGAINST ('%s')" % q)
result = ft_search.execute()
for row in result: print row

这似乎可以工作，但我有几个问题：

我创建两个表来解决我的问题，这个方法合理吗？有没有更标准、更好的方法呢？
有没有SQLAlchemy的方法可以创建全文索引，还是说我直接执行“CREATE FULLTEXT INDEX ...”这样做比较好？
看起来我在搜索/匹配查询时有SQL注入的问题。我该如何用“SQLAlchemy的方式”来选择，以解决这个问题？
有没有简单的方法可以将users_myisam的选择/匹配结果再连接回我的用户表，并返回实际的用户实例，因为这才是我真正想要的？
为了保持我的users_myisam表与映射对象的用户表同步，使用MapperExtension在我的User类上设置before_insert、before_update和before_delete方法来适当地更新users_myisam表，这样做合理吗？还是有更好的方法可以实现这个？

谢谢，
迈克尔

mysql sqlalchemy foreign key sql injection myisam full-text search transactions mapper extension

1 个回答

我用两个表来解决我的问题，这样做合理吗？有没有更标准、更好的方法呢？

我之前没见过这种用法，因为那些重视事务和约束的开发者通常会选择使用Postgresql。我理解在你的特定情况下可能不适用。

有没有SQLAlchemy的方法来创建全文索引，还是直接执行“CREATE FULLTEXT INDEX ...”更好呢？

使用conn.execute()是可以的，不过如果你想要更集成一点的方式，可以使用DDL()这个构造，具体可以查看这个链接了解详细信息。

看起来我在搜索/匹配查询时遇到了SQL注入问题。我该如何用“SQLAlchemy的方式”来选择以解决这个问题呢？

注意：这个方法仅适用于MATCH多个列同时匹配——如果只有一列，使用match()操作会更简单。

最基本的，你可以使用text()构造：

from sqlalchemy import text, bindparam

users_myisam.select(
  text("MATCH (name,description) AGAINST (:value)", 
       bindparams=[bindparam('value', q)])
)

更全面的，你可以定义一个自定义构造：

from sqlalchemy.ext.compiler import compiles
from sqlalchemy.sql.expression import ClauseElement
from sqlalchemy import literal

class Match(ClauseElement):
    def __init__(self, columns, value):
        self.columns = columns
        self.value = literal(value)

@compiles(Match)
def _match(element, compiler, **kw):
    return "MATCH (%s) AGAINST (%s)" % (
               ", ".join(compiler.process(c, **kw) for c in element.columns),
               compiler.process(element.value)
             )

my_table.select(Match([my_table.c.a, my_table.c.b], "some value"))

文档：

http://docs.sqlalchemy.org/en/rel_0_8/core/compiler.html

有没有简单的方法把users_myisam的选择/匹配结果再连接回我的用户表，并返回实际的用户实例，因为这才是我真正想要的？

你可能需要创建一个UserMyISAM类，像User那样进行映射，然后使用relationship()将这两个类连接起来，这样就可以进行简单的操作了：

query(User).join(User.search_table).\
           filter(Match([UserSearch.x, UserSearch.y], "some value"))

为了保持我的users_myisam表与映射的用户表同步，使用MapperExtension在我的User类上设置before_insert、before_update和before_delete方法来适当地更新users_myisam表，这样做合理吗？还是有更好的方法？

MapperExtensions已经不推荐使用了，所以你至少应该使用事件API。在大多数情况下，我们希望在flush过程之外进行对象的变更。在这种情况下，我会使用User的构造函数，或者使用初始化事件，还可以使用基本的@validates装饰器，它会接收User目标属性的值，并将这些值复制到User.search_table中。

总的来说，如果你是从其他来源（比如O'Reilly的书）学习SQLAlchemy的，那些资料已经过时很多年了，我建议你关注当前的在线文档。

回答于 2025-04-17 由 Python大师

分享举报

SQLAlchemy中MySQL全文搜索的正确用法

1 个回答

撰写回答