Flask - SQLAlchemy full text search

Question

I am trying to build a small online plant database, and would like to incorporate a search engine. I am building my application with Flask and I have the application connected to a MySQL database using SQLAlchemy and pymysql. I have been fiddling with Flask-WhooshAlchemy and so far am having no luck. What I would like have is a search where if users search for a specific plant that it will either go to the plant info page or return results of similar species. For example, if they were to search for 'Carex utriculata' it would bring them directly to the species info page for 'Carex utriculata'. But if they searched for 'Carex' they would get a results page listing all similar species so there might be a list something like 'Carex aquatilis' 'Carex keloggii''Carex utriculata'.

In my attempts so far it appears that a search is attempted, but the results display all the plants in my database, not just what was entered into the search. And I have not even tried to play with getting the search to go straight to an individual plant's page if it is an exact match with the search because I have been stuck on getting the search to work. I am working with Python 3.5. Here is my code.

init.py

import pymysql.cursors
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
import flask_whooshalchemy as whooshalchemy


app = Flask(__name__)


app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://root:passwrd@localhost/restorationplantdb'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True
app.config['WHOOSE_BASE'] = 'results'


db = SQLAlchemy(app)

class SPInfo(db.Model):
    __tablename__ = 'speciesinfo'
    __searchable__ = ['NRCS_Species', 'NRCS_CommonName']
    speciesinfo_id = db.Column(db.Integer,primary_key=True)
    ID = db.Column(db.Integer)
    MTNHP_Species = db.Column(db.String(45))
    NRCS_Species = db.Column(db.String(45))
    NRCS_CommonName = db.Column(db.String(45))
    Synonyms = db.Column(db.Text)
    Sp_Stratum = db.Column(db.String(12))
    Origin = db.Column(db.String(25))
    Duration = db.Column(db.String(25))
    Habitat = db.Column(db.Text)
    Elevation = db.Column(db.Text)


whooshalchemy.whoosh_index(app, SPInfo)

import RestorationPlantDB.views

views.py

from flask import render_template, url_for, redirect, session, request
from datetime import datetime
from RestorationPlantDB import app
from RestorationPlantDB import db
from RestorationPlantDB import SPInfo



@app.route('/search', methods = ['GET','POST'])
def search():
    results = SPInfo.query.all()
    return render_template('search.html', results=results)

@app.route('/results')
def results():
    results = SPInfo.query.whoosh_search(request.args.get('query')).all()
    return render_template('search.html', results=results)

search.html

{% extends "layout.html" %}

{% block content %}


{% for result in results %}
<p>{{ result.NRCS_Species }} - {{ result.NRCS_CommonName }}</p>
{% endfor %}



<div class="col-sm-3 col-sm-offset-1 blog sidebar">
    <div class="sidebar-module sidebar-module-insert">
        <h4>Search</h4>
        <form class="form-inline" method="GET" action="search">
            <div class="form-group">
                <label for="query">Name</label>
                <input type="text" class="form-control" name="query" id="query" />
            </div>
            <button type="submit" class="btn btn-primary">Search</button>
        </form>
    </div>
</div>


{% endblock %}

Answer 1

All you're doing is querying everything, which is why you get everything. You're filtering the query under /results but the form doesn't go there. Rather than separating them out like that, you could easily just do this:

@app.route('/search', methods = ['GET','POST'])
def search(results=None):
    if request.method == 'POST':
        results = SPInfo.query.whoosh_search(request.form.get('query')).all()
    return render_template('search.html', results=results)

and remove /results entirely. Next, you would want to implement redirecting to an exact match. I would add this into the above code for that:

from sqlalchemy import func

@app.route('/search', methods = ['GET','POST'])
def search(results=None):
    if request.method == 'POST':
        keywords = request.form.get('query')
        unique_result = SPInfo.query.filter(func.lower(SPInfo.NRCS_Species) == func.lower(keywords)).first() or SPInfo.query.filter(func.lower(SPInfo.NRCS_CommonName) == func.lower(keywords)).first()
        if unique_result:
            #insert return redirect() to specific page using unique_result here
        results = SPInfo.query.whoosh_search(keywords).all()
    return render_template('search.html', results=results)

Normally I would've just done:

unique_result = SPInfo.query.filter_by(NRCS_Species=keywords).first() or SPInfo.query.filter_by(NRCS_CommonName=keywords).first()

but I assume that you want it to be case insensitive. If you don't, replace the queries with this one.

I also left the redirect blank because I don't know what your route looks like for the species page, but it shouldn't be hard to figure out.

EDIT

Changed the function definitions, and a minor change needs to be added to the template, like so:

{% if results %}
    {% for result in results %}
        <p>{{ result.NRCS_Species }} - {{ result.NRCS_CommonName }}</p>
    {% endfor %}
{% endif %}