9

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.

Vyndion
  • 151
  • 8
  • 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 Mar 23 '15 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 Mar 23 '15 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 Mar 23 '15 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): [, , , , ] – Vyndion Mar 23 '15 at 21:05
  • So that makes more sense. If the search string is empty string, you should just get back your full query... in this case, all records. If you inspect `[a.name for a in Article.query.search('').all()]` do you get a list of 5 `'finland'`s? – dylrei Mar 23 '15 at 21:21
  • Absolutely. ['finland', 'finland', 'finland', 'finland', 'finland']. – Vyndion Mar 23 '15 at 22:10
  • This might be grasping at straws, but what if you explicitly use Unicode values to instantiate your records ex: `a= Article(name=u'finland', content=u'finland')` – dylrei Mar 24 '15 at 15:43
  • 1
    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 Mar 24 '15 at 18:43
  • Actually, I guess this leads to a bigger problem, if my variables are strings that should be utf-8, since python3, if I do stringname='finland' followed by Article(name=stringname, content=stringname), my queries, once again, turn blank. Specifically, Article.query.search(u'finland').all() and Article.query.search('finland').all() – Vyndion Mar 24 '15 at 19:05
  • This sounds like it might be worth a bug report. I wouldn't have guessed that it would be necessary to specify Unicode, I just noticed that this was really the only obvious difference between how the code was documented and what you were doing. Hope it helped a little, anyway. :) – dylrei Mar 24 '15 at 23:45

2 Answers2

9

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()
Collin Allen
  • 4,449
  • 3
  • 37
  • 52
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()
Sotiris
  • 69
  • 2
  • 5