Flask-Sqlalchemy + Sqlalchemy-searchable returning empty list

Question

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.

Answer 1

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()

Answer 2

On Collin Allen's answer: actually, the flask-sqlalchemy ''db'' exposes the configure_mappers function.

Replace:

from sqlalchemy.orm.mapper import configure_mappers
...
configure_mappers()

with:

...
db.configure_mappers()