The Flask Mega-Tutorial, Part X: Full Text Search

Posted by
on under

(Great news! There is a new version of this tutorial!)

This is the tenth article in the series in which I document my experience writing web applications in Python using the Flask microframework.

The goal of the tutorial series is to develop a decently featured microblogging application that demonstrating total lack of originality I have decided to call microblog.

NOTE: This article was revised in September 2014 to be in sync with current versions of Python and Flask.

Here is an index of all the articles in the series that have been published to date:


In the previous article in the series we've enhanced our database queries so that we can get results on pages.

Today, we are going to continue working on our database, but in a different area. All applications that store content must provide a search capability.

For many types of web sites it is possible to just let Google, Bing, etc. index all the content and provide the search results. This works well for sites that have mostly static pages, like a forum. In our little microblog application the basic unit of content is just a short user post, not a whole page. The type of search results that we want are dynamic. For example, if we search for the word "dog" we want to see blog posts from any users that include that word. It is obvious that until someone searches for that word there is no page that the big search engines could have indexed with these results, so clearly we have no choice other than rolling our own search.

Introduction to full text search engines

Unfortunately support for full text search in relational databases is not well standardized. Each database implements full text search in its own way, and SQLAlchemy at this time does not have a full text search abstration.

We are currently using SQLite for our database, so we could just create a full text index using the facilities provided by SQLite, bypassing SQLAlchemy. But that isn't a good idea, because if one day we decide to switch to another database we would need to rewrite our full text search capability for another database.

So instead, we are going to let our database deal with the regular data, and we are going to create a specialized database that will be dedicated to text searches.

There are a few open source full text search engines. The only one that to my knowledge has a Flask extension is Whoosh, an engine also written in Python. The advantage of using a pure Python engine is that it will install and run anywhere a Python interpreter is available. The disadvantage is that search performance will not be up to par with other engines that are written in C or C++. In my opinion the ideal solution would be to have a Flask extension that can connect to several engines and abstract us from dealing with a particular one in the same way Flask-SQLAlchemy gives us the freedom to use several database engines, but nothing of that kind seems to be available for full text searching at this time. Django developers do have a very nice extension that supports several full text search engines called django-haystack. Maybe one day someone will create a similar extension for Flask.

But for now, we'll implement our text searching with Whoosh. The extension that we are going to use is Flask-WhooshAlchemy, which integrates a Whoosh database with Flask-SQLAlchemy models.

Python 3 Compatibility

Unfortunately, we have a problem with Python 3 and these packages. The Flask-WhooshAlchemy extension was never made compatible with Python 3. I have forked this extension and made a few changes to make it work, so if you are on Python 3 you will need to uninstall the official version and install my fork:

$ flask/bin/pip uninstall flask-whooshalchemy
$ flask/bin/pip install git+git://

Sadly this isn't the only problem. Whoosh also has issues with Python 3, it seems. In my testing I have encontered this bug, and to my knowledge there isn't a solution available, which means that at this time the full text search capability does not work well on Python 3. I will update this section once the issues are resolved.


Configuration for Flask-WhooshAlchemy is pretty simple. We just need to tell the extension what is the name of the full text search database (file

WHOOSH_BASE = os.path.join(basedir, 'search.db')

Model changes

Since Flask-WhooshAlchemy integrates with Flask-SQLAlchemy, we indicate what data is to be indexed for searching in the proper model class (file app/

from app import app

import sys
if sys.version_info >= (3, 0):
    enable_search = False
    enable_search = True
    import flask_whooshalchemy as whooshalchemy

class Post(db.Model):
    __searchable__ = ['body']

    id = db.Column(db.Integer, primary_key=True)
    body = db.Column(db.String(140))
    timestamp = db.Column(db.DateTime)
    user_id = db.Column(db.Integer, db.ForeignKey(''))

    def __repr__(self):
        return '<Post %r>' % (self.body)

if enable_search:
    whooshalchemy.whoosh_index(app, Post)

The model has a new __searchable__ field, which is an array with all the database fields that will be in the searchable index. In our case we only want to index the body field of our posts.

We also have to initialize the full text index for this model by calling the whoosh_index function. Note that since we know that the search capability currently does not work on Python 3 we have to skip its initialization. Once the problems in Whoosh are fixed the logic around enable_search can be removed.

Since this isn't a change that affects the format of our relational database we do not need to record a new migration.

Unfortunately any posts that were in the database before the full text engine was added will not be indexed. To make sure the database and the full text engine are synchronized we are going to delete all posts from the database and start over. First we start the Python interpreter. For Windows users:


And for everyone else:


Then in the Python prompt we delete all the posts:

>>> from app.models import Post
>>> from app import db
>>> for post in Post.query.all():
...    db.session.delete(post)
>>> db.session.commit()


And now we are ready to start searching. First let's add a few new posts to the database. We have two options to do this. We can just start the application and enter posts via the web browser, as regular users would do, or we can also do it in the Python prompt.

From the Python prompt we can do it as follows:

>>> from app.models import User, Post
>>> from app import db
>>> import datetime
>>> u = User.query.get(1)
>>> p = Post(body='my first post', timestamp=datetime.datetime.utcnow(), author=u)
>>> db.session.add(p)
>>> p = Post(body='my second post', timestamp=datetime.datetime.utcnow(), author=u)
>>> db.session.add(p)
>>> p = Post(body='my third and last post', timestamp=datetime.datetime.utcnow(), author=u)
>>> db.session.add(p)
>>> db.session.commit()

The Flask-WhooshAlchemy extension is nice, because it hooks up into Flask-SQLAlchemy commits automatically. We do not need to maintain the full text index, it is all done for us transparently.

Now that we have a few posts in our full text index we can issue searches:

>>> Post.query.whoosh_search('post').all()
[<Post u'my second post'>, <Post u'my first post'>, <Post u'my third and last post'>]
>>> Post.query.whoosh_search('second').all()
[<Post u'my second post'>]
>>> Post.query.whoosh_search('second OR last').all()
[<Post u'my second post'>, <Post u'my third and last post'>]

As you can see in the examples above, the queries do not need to be limited to single words. In fact, Whoosh supports a pretty powerful search query language.

Integrating full text searches into the application

To make the searching capability available to our application's users we have to add just a few small changes.


As far as configuration, we'll just indicate how many search results should be returned as a maximum (file


Search form

We are going to add a search form to the navigation bar at the top of the page. Putting the search box at the top is nice, because then the search will be accessible from all pages.

First we add a search form class (file app/

class SearchForm(Form):
    search = StringField('search', validators=[DataRequired()])

Then we need to create a search form object and make it available to all templates, since we will be putting the search form in the navigation bar that is common to all pages. The easiest way to achieve this is to create the form in the before_request handler, and then stick it in Flask's global g (file app/

from forms import SearchForm

def before_request():
    g.user = current_user
    if g.user.is_authenticated:
        g.user.last_seen = datetime.utcnow()
        g.search_form = SearchForm()

Then we add the form to our template (file app/templates/base.html):

    <a href="{{ url_for('index') }}">Home</a>
    {% if g.user.is_authenticated %}
    | <a href="{{ url_for('user', nickname=g.user.nickname) }}">Your Profile</a>
    | <form style="display: inline;" action="{{ url_for('search') }}" method="post" name="search">{{ g.search_form.hidden_tag() }}{{ }}<input type="submit" value="Search"></form>
    | <a href="{{ url_for('logout') }}">Logout</a>
    {% endif %}

Note that we only display the form when we have a logged in user. Likewise, the before_request handler will only create a form when a user is logged in, since our application does not show any content to guests that are not authenticated.

Search view function

The action field of our form was set above to send all search requests the the search view function. This is where we will be issuing our full text queries (file app/

@app.route('/search', methods=['POST'])
def search():
    if not g.search_form.validate_on_submit():
        return redirect(url_for('index'))
    return redirect(url_for('search_results',

This function doesn't really do much, it just collects the search query from the form and then redirects to another page passing this query as an argument. The reason the search work isn't done directly here is that if a user then hits the refresh button the browser will put up a warning indicating that form data will be resubmitted. This is avoided when the response to a POST request is a redirect, because after the redirect the browser's refresh button will reload the redirected page.

Search results page

Once a query string has been received the form POST handler sends it via page redirection to the search_results handler (file app/

from config import MAX_SEARCH_RESULTS

def search_results(query):
    results = Post.query.whoosh_search(query, MAX_SEARCH_RESULTS).all()
    return render_template('search_results.html',

The search results view function sends the query into Whoosh, passing a maximum number of search results, since we don't want to be presenting a potentially large number of hits, we are happy showing just the first fifty.

The final piece is the search results template (file app/templates/search_results.html):

<!-- extend base layout -->
{% extends "base.html" %}

{% block content %}
  <h1>Search results for "{{ query }}":</h1>
  {% for post in results %}
      {% include 'post.html' %}
  {% endfor %}
{% endblock %}

And here, once again, we can reuse our post.html sub-template, so we don't need to worry about rendering avatars or other formatting elements, since all of that is done in a generic way in the sub-template.

Final words

We now have completed yet another important, though often overlooked piece that any decent web application must have.

The source code for the updated microblog application is available below:


As always, the above download does not include a database or a flask virtual environment. See previous articles in the series to learn how to create these.

I hope you enjoyed this tutorial. If you have any questions feel free to write in the comments below. Thank you for reading, and I will be seeing you again in the next installment!


Become a Patron!

Hello, and thank you for visiting my blog! If you enjoyed this article, please consider supporting my work on this blog on Patreon!

  • #26 Miguel Grinberg said

    @Cory: Did you install my fork of the Flask-WhooshAlchemy extension? The official one has a bug that causes the error that you are getting.

  • #27 Miguel Grinberg said

    @Cory: I should add that I used this bug as an opportunity to cover debugging in a future article, so if you can wait a bit you'll get to the solution.

  • #28 Cory Gough said

    @Miguel: I installed flask-whooshalchemy==0.54a, as mentioned in part 1. I'll wait until I read the debugging atricle unless you know of a quick solution. Thank you!

  • #29 jda2000 said

    Hey Miquel,

    Awsome Lesson. Very useful and educational.

    I did a diff between your and the other to see if I could spot how you fixed the "no such thing as paginate()" error. Sadly, I was not clever enough to identify the fix. I don't think I'd be the only one who would like to hear how you did fix it if you had the time and inclination to say.

  • #30 Miguel Grinberg said

    @jda2000: Did you read part XVI yet? I give a good detail of the problem and how I fixed it. Note that since then the author of Flask-WhooshAlchemy used a slightly different fix than mine, though similar in concept.

  • #31 Victor Reichert said


    I'm getting the error below form whooshalchemy when I try to run the tests:

    OperationalError: (OperationalError) no such table: post u'SELECT AS post_id, post.body AS post_body, post.timestamp AS post_timestamp, post.user_id AS post_user_id \nFROM post JOIN followers ON followers.followed_id = post.user_id \nWHERE followers.follower_id = ? ORDER BY post.timestamp DESC' (1,)

    When I comment out the whooshalchemy.whoosh_index(app, Post) line, the tests pass. The search.db folder and post subfolder are being created.

    I've tried googling this but it didn't turn up anything. Do you have any idea what the problem is or know of other resources?

    Thank you for your help! The Tutorial is awesome!


  • #32 Miguel Grinberg said

    @Victor: same error with the code I have on github? Please compare your version against mine and see if there are any differences.

  • #33 Bogdan said

    Hi Miguel,

    I have a problem when using Flask-WhooshAlchemy when trying to index a column in a table that has a composite primary key (3 distinct ID's make up the primary key for this table). The problem is that whoosh indexes the text inside a forth column in the same table and when it delivers the search results it shows all the entries that have the last primary key = with the search result primary key.

    The problem is that id is not unique so SQLAlchemy brings back all the entries that have that ID.

    Is there a way to tell whoosh to index while using composite primary keys?

    Here is my table class:

    class Comment(db.Model):
    searchable = ['message']
    __table_name = 'comment'
    id = db.Column(db.BigInteger, primary_key=True, index=True)
    post_id = db.Column(db.BigInteger, primary_key=True)
    page_id = db.Column(db.BigInteger, primary_key=True)
    fb_id = db.Column(db.BigInteger, index=True)
    message = db.Column(db.Text)
    can_remove = db.Column(db.SmallInteger)
    created_time = db.Column(db.DateTime)

    def __repr__(self):
        return '<Comment %r>' % self.message

    So in my case if a search has a result it will bring in all the entries that have the same page_id = with the entry matched with that search result instead of doing a composite primary key for all 3.

    What do you think I can do in this case to fix the indexing or the searching?

  • #34 Miguel Grinberg said

    @Bogdan: Reading the Flask-WhooshAlchemy code it is clear it automatically finds the primary key by looping over the columns, so it appears it makes the assumption you have a single column as key. The easiest would be to adapt your table to use a single primary key.

  • #35 Bogdan said

    @Miguel: this is the problem... the dataset that I am working with relies on a composite primary key and it was generated before hand. I am inserting it in the database and am trying to use whoosh to index the full text so I can make it searchable...

    You're saying that I will have to find a way to change the way Flask-WhooshAlchemy looks for the primary key and also the way it stores/generates the index in its own data store?

    Isn't anyone else out there that has run into the same issue with this? Or is there any other full text search library that can work with SQLAlchemy?

    I'm stuck...

  • #36 Miguel Grinberg said

    @Bogdan: the problem is with the Flask-WhooshAlchemy extension. You can use plain Whoosh if you want to avoid it.

  • #37 Sam Alan said

    For e.g, post is "hello everyone...!!!"
    And I search "everyone" then it shows but if I search "every" then don't...So can you tell how we can get that posts also which contains search string...

  • #38 Miguel Grinberg said

    @Sam: I'm using Whoosh for full text search. Here is the docs for the query language it uses:

  • #39 Kane Blueriver said

    Hello, Miguel. I'm using flask-whooshalchemy to deal with search of a table called torrents. For some reason, I dumped all the data out to a sql file and finally dumped it back all manually. And I found all the indexes are lost, and I can got nothing using whoosh_search('some_word')

  • #40 Miguel Grinberg said

    @Kane: the full text database is not stored in the relational database, it is stored in a Whoosh database in the "search.db" directory. You need to back that up as well when you take a db backup.

  • #41 Travis Heath said

    Hi Miguel,

    I have another question for you. I followed this tutorial to the point where we introduce the error '_QueryProxy' object has no attribute 'paginate'.

    I then moved ahead to Part 16, Debugging, Testing and Profiling because you mentioned in the comments here that was going to be the article that explores fixing the bug. I implemented the fix in

    This fixed the test case (where the error was caused by an extra session). However, I am back to getting the '_QueryProxy' object has no attribute 'paginate' error when the application opens. Is there anything else I need to do besides just editing the file?

  • #42 Miguel Grinberg said

  • #43 Travis Heath said

    Sure enough, as expected, user error on my part. I had all of the changes in, but I did not notice that the object passed to the object _QueryProxy had changed to the flask.ext.sqlalchemy BaseQuery; thus, it had no paginate function.

    Thanks for the quick reply!

  • #44 Alex Leonhardt said

    re django haystack, i think this is something similar (tho only for solr):

  • #45 Alex Leonhardt said

    Did I miss an update to the unit tests ? as my test_follow_posts is broken getting the wrong number of posts :

    Traceback (most recent call last):
    File "/full/path/to/python/flask-tutorial/", line 107, in test_follow_posts
    assert len(f1) == 3

  • #46 Miguel Grinberg said

    @Alex: Flask-solr hasn't been touched in 3 years, so use with caution. For starters, it uses the old style of extension structure, from the Flask 0.7 and older days.

  • #47 Miguel Grinberg said

    @Alex: if you look at the code for that test, you can see that user "u1" is following three people, and each user has one post in the database:

        u1.follow(u1) # john follows himself
        u1.follow(u2) # john follows susan
        u1.follow(u4) # john follows david

    Add a print statement to that test to see which of these posts are you getting, or compare the test code with my version on github.

  • #48 Alex Leonhardt said

    @Miguel - oh wow, such a shame about flask-solr :( .. am going to be looking for something along the ElasticSearch lines, if you know of any implementation/s ?

    re the unittest failing, it seems the setup is not being executed - it appears to use the actual app db :( ... i'll check out the tests in github and compare whats going on ..

  • #49 Igor Mosyagin said

    Hi! Great tutorial :)

    I've been told that search forms are supposed to be GET-forms and not POST-forms. Even though I like your approach with redirecting and everything, shouldn't it be more convenient to have a GET-form for the search request?

  • #50 Miguel Grinberg said

    @Igor: the benefit of using GET for a search form is that users can bookmark the search. Depending on your use case this may be good or bad. It would be fairly simple to modify the app to use GET for searches.

Leave a Comment