Flask-Sqlalchemy + Sqlalchemy搜索返回空列表 [英] Flask-Sqlalchemy + Sqlalchemy-searchable returning empty list
问题描述
from flask导入Flask
from flask.ext.sqlalchemy从sqlalchemy_searchable导入SQLAlchemy,BaseQuery
从sqlalchemy_utils.types导入SearchQueryMixin
从sqlalchemy_searchable导入TSVectorType
import make_searchable
$ b app = Flask(__ name__)
app.config ['SQLALCHEMY_DATABASE_URI'] ='postgresql + psycopg2:// usr:admin @ localhost / dev'
app.config [文章搜索(BaseQuery,SearchQueryMixin):
pass(''SECRET_KEY'] ='notreallyasecret'
db = SQLAlchemy(app)
make_searchable()
$ b class ArticleQuery
$ b class Article(db.Model):
query_class = ArticleQuery
__tablename__ ='article'
id = db.Column( db.Integer,primary_key = True)
name = db.Column(db.Unicode(255))
content = db.Column(db.UnicodeText)
search_vector = db.Co lumn(TSVectorType('name','content'))
我的搜索查询无法正常工作。我打开了一个python shell并创建了数据库,并且插入了五个相同的文章
a = Article(name ='finland',content ='finland')
db.session.add(a)
db.session.commit()#ae
与芬兰作为名称和内容。根据示例:
$ b $ pre $ Article.query.search(u'finland')。limit(5).all()
应该返回芬兰文章。就我而言,我得到一个空的列表。如果我将示例查询修改为:
Article.query.search('').first()
但是对于空白空间的搜索是无用的。任何想法?
增加一点:在文章表中,我注意到'search_vector tsvector'列是完全空的,尽管数据在内容和名称列;我不确定这是否与此有关。
使用 Flask-Script 添加 code> search_vector manage.py <
列是空的,尽管你有添加相应的
$ b TSVectorType
参数意味着SQLAlchemy-Searchable触发器不存在于postgres数据库中。您可以通过在 psql
命令行工具中执行 \df +
来验证缺失情况 - 您将看不到触发器名为 article_search_vector_update
。 SQLAlchemy-Searchable在 TSVectorType(...)$ c $中指定的列设置此触发器以更新
search_vector
列的内容c> change。
在 manage.py
的情况下,我必须先打电话:
$ b $ pre code $ db.configure_mappers
code $ $ $ $ $ $ $ $ $ $ b
基本上,您必须在调用 create_all()
之前配置SQLAlchemy的映射器。如果不这样做,SQLAlchemy-Searchable将不会有机会添加它的 search_vector
触发器来填充 TSVectorType
列 SQLAlchemy-Searchable文档有更多的内容。
总的来说,根据需要,可以正确配置SQLAlchemy-Searchable的最小 manage.py
可能如下所示: / p>
#!/ usr / bin / env python
from flask.ext.script import Manager
从应用程序导入应用程序,db
经理=经理(应用程序)
经理和
def init_db():
删除并重新创建SQL模式
db.drop_all()
db.configure_mappers()
db.create_all()
db.session.commit()
First time on the site, so hi to all and thanks in advance. Longtime lurker and newb.
I'm working on a web app in flask, using Flask-SqlAlchemy and SqlAlchemy-Searchable (docs-> https://sqlalchemy-searchable.readthedocs.org/en/latest/index.html). For a reason I can't figure out, when I try a similar example to the code shown on the docs page:
from flask import Flask
from flask.ext.sqlalchemy import SQLAlchemy, BaseQuery
from sqlalchemy_searchable import SearchQueryMixin
from sqlalchemy_utils.types import TSVectorType
from sqlalchemy_searchable import make_searchable
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql+psycopg2://usr:admin@localhost/dev'
app.config['SECRET_KEY'] = 'notreallyasecret'
db = SQLAlchemy(app)
make_searchable()
class ArticleQuery(BaseQuery, SearchQueryMixin):
pass
class Article(db.Model):
query_class = ArticleQuery
__tablename__ = 'article'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.Unicode(255))
content = db.Column(db.UnicodeText)
search_vector = db.Column(TSVectorType('name', 'content'))
My search queries don't work properly. I opened a python shell and created the db, and inserted five identical articles
a= Article(name='finland',content='finland')
db.session.add(a)
db.session.commit() #a-e
with 'finland' both as name and content. According to the example:
Article.query.search(u'finland').limit(5).all()
There should be articles returned that have finland somewhere in them. In my case, I get an empty list. I get an object back if I modify the example query to:
Article.query.search(' ').first()
But it's rather useless searching for empty spaces. Any ideas?
Adding a bit more to it: I noticed in the article table, the 'search_vector tsvector' column is completely empty despite data being in the content and name columns; I'm not sure if that has anything to do with it.
I ran into this exact issue once, too, when using Flask-Script to add a manage.py
management tool to my application.
The fact that the search_vector
column is empty despite you having added the appropriate TSVectorType
parameters means that the SQLAlchemy-Searchable trigger isn't present in the postgres DB. You can verify its absence by doing a \df+
in psql
command line tool -- you will not see a trigger named article_search_vector_update
. SQLAlchemy-Searchable sets up this trigger to update the content of the search_vector
column when the columns named in TSVectorType(...)
change.
In the case of manage.py
, I had to first call:
db.configure_mappers()
Essentially, you have to configure SQLAlchemy's mappers before calling create_all()
. Without doing this, SQLAlchemy-Searchable will not be given the opportunity to add its search_vector
trigger to populate the TSVectorType
column in the model.The SQLAlchemy-Searchable docs have more on this.
In total, a minimal manage.py
that properly configures SQLAlchemy-Searchable as you require might look like:
#!/usr/bin/env python
from flask.ext.script import Manager
from app import app, db
manager = Manager(app)
@manager.command
def init_db():
"""
Drops and re-creates the SQL schema
"""
db.drop_all()
db.configure_mappers()
db.create_all()
db.session.commit()
这篇关于Flask-Sqlalchemy + Sqlalchemy搜索返回空列表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!