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.

I hate to ask an obvious question... but are you sure there's a "finland" in your data? I believe sqlalchemy search is case-sensitive... perhaps your data has a "Finland" but no "finland"? — dylrei
– dylrei, Commented Mar 23, 2015 at 15:53
Totally valid question. Yes, I'm certain they are the same. I had actually made a bunch more records, with name and content both being "pie". I edited the original to make finland in all lower case. — Vyndion
– Vyndion, Commented Mar 23, 2015 at 20:18
It seems like you shouldn't get anything back searching for spaces. Do you get back the 5 "finland" records you entered if you do Article.query.search(' ').all()? — dylrei
– dylrei, Commented Mar 23, 2015 at 20:50
I guess I'm slightly off, it's not necessarily searching for spaces, just when the Article.query.search('').all() , I get back the list of all five objects. For clarity (it's in a py file called uploadform): [<uploadform.Article object at 0x7f057c30d8d0>, <uploadform.Article object at 0x7f057a1ed2e8>, <uploadform.Article object at 0x7f057a1edac8>, <uploadform.Article object at 0x7f057a1edb38>, <uploadform.Article object at 0x7f057a1edba8>] — Vyndion
– Vyndion, Commented Mar 23, 2015 at 21:05
Your grasping at straws appears correct. I re-instantiated the records with unicode values, and now the search queries work. I wonder if they all have to be unicode values going in or if Sqlalchemy-searchable can only search unicode values. I was operating under the assumption python3 strings were unicode by default. Guess it will require more testing. Thanks! — Vyndion
– Vyndion, Commented Mar 24, 2015 at 18:43

Collin Allen · Accepted Answer · 2015-11-18 17:52:33Z

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

Sotiris · Accepted Answer · 2015-11-05 15:43:49Z

6

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

answered Nov 5, 2015 at 15:43

Sotiris

692 silver badges5 bronze badges

1 Comment

Collin Allen Over a year ago

Indeed, this works great! I've edited my answer to reflect your note. Thanks!

Collectives™ on Stack Overflow

Flask-Sqlalchemy + Sqlalchemy-searchable returning empty list

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related