I am writing a web application in Python based on Flask, SQLalchemy, postgresql. I have created a database and am trying to implement a search tool. I first referred to https://sqlalchemy-searchable.readthedocs.org/en/latest/installation.html#quickstart and tried the example given there. The call to search() always returned None. When I searched for a similar issue on stackoverflow, I found the exact same question at Flask-Sqlalchemy + Sqlalchemy-searchable returning empty list. So I copied and pasted that code, word-to-word (with some relevant changes), but I still seem to have problem with search query returning None. Here is the code:
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['SQLALCHEMY_DATABASE_URI'] = 'postgres:///dbuser_db1'
#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'))
def test_db():
article1 = Article(name=u'finland', content=u'finland')
db.session.add(article1)
db.session.commit()
result1 = Article.query.search(u'finland').limit(5).all()
result_blank = Article.query.search(u'').limit(5).all()
result = Article.query.search(u'finland').first()
print "RESULT: ", result1, result_blank, result
if __name__ == "__main__":
test_db()
As per the conversation in the above stackoverflow link (Flask-Sqlalchemy + Sqlalchemy-searchable returning empty list), the person originating the question says the search queries started working when he/she re-instantiated the records with unicode values. In my case I always used unicode for the records, as given in the code above, but it never seems to work for me. The print statement above prints the following: RESULT: [] [<main.Article object at 0x7f9d4351bf90>, <main.Article object at 0x7f9d4342d090>, <main.Article object at 0x7f9d434ae550>] None
Also as per the observation of the originator of the referred question, I too see that in the article table, the 'search_vector tsvector' column is completely empty even though there is data in the content and name columns. Not sure if this is the root cause. I wonder if he/she is able to see the search_vector column populated after the search query started working.
dbuser_db1=> SELECT article.id AS article_id, article.name AS article_name, article.content AS article_content, article.search_vector AS article_search_vector FROM article ;
article_id | article_name | article_content | article_search_vector
------------+--------------+-----------------+-----------------------
127 | finland | finland |
128 | finland | finland |
129 | finland | finland |
(3 rows)
I appreciate any help here.
