Search Autocomplete using SQLite & Flask

Question

I have looked around and I can't seem to find a good solution for this. I have a simple SQLite DB that has electronic products within it, and I have a search box where you can type in the name of the product to search for it. But I want to use a AutoComplete so that the user is able to see all the products that a related to what they are typing.

(i.e. If they type "EOS", it will have a little dropdown that shows them all the products with EOS lettering and they can choose which one they want)

I have seen that Ajaxx has a AutoComplete feature, but I can not find good instructions on how to properly implement it into my app (I am still fairly new to programming, only about 4 months).

A quick google turned this up using jqueryui.... https://jqueryui.com/autocomplete/ — Neo, Nov 28 '16 at 18:27
And this https://www.devbridge.com/sourcery/components/jquery-autocomplete/ What did you search on, LOL. — Neo, Nov 28 '16 at 18:28
@MisterPositive I have seen this as well. But for the example it shows that the `var availableTags` is static and prepoplutated with available names. I am not sure if there is a way to have it pull information from my DB, since my DB can change at anytime. — kstullich, Nov 28 '16 at 18:30
Autocomplete requires a frontend component (like [jQuery UI's Autocomplete](https://jqueryui.com/autocomplete/)), and then a backend route to handle an AJAX request (countless examples on Github, [here's one I did a few months ago](https://github.com/YellowSharkMT/flask-ajax-example/blob/master/flask-ajax.py)). In order to help you, we need to know if you have those components in place already, and/or if you are having problems with them. — YellowShark, Nov 28 '16 at 18:33

score 3 · Accepted Answer · answered Nov 28 '16 at 18:34

3

jQueryUI will be the quickest way to implement this. This is really a Javascript question, but I'll give you some tips for using Flask to implement this.

Within your view function create a list of the products based on the database, e.g.:

def index():
    products = [row.product for row in Products.query.all()]
    return render_template('index.html', products=products)

Then, within your HTML/Javascript use the following Jinja2 syntax & filters to translate the list from Python into something Javascript can use (from jQueryUI Docs):

<script>
$( function() {
    var availableTags = {{ products|tojson|safe }};
    $( "#tags" ).autocomplete({
        source: availableTags
    });
} );
</script>

You'll obviously need to build and include the appropriate jQuery UI source javascript file in addition to this code as well as jQuery UI styling CSS.

answered Nov 28 '16 at 18:34

abigperson

5,252
3
22
25

Thanks for an answer. While reading the jQuery UI it states "Enables users to quickly find and select from a _pre-populated_ list of values as they type, leveraging searching and filtering." I don't really know anything about Javascript, so I'll have to take a look into it, but would I be able to have it read from my DB instead of a static list? – kstullich Nov 28 '16 at 18:47
That's the sort of thing that would require an AJAX endpoint, but the answer is definitely yes. – YellowShark Nov 28 '16 at 18:53
@YellowShark okay thank you. I will have to read up more on the Ajaxx and jQuery. – kstullich Nov 28 '16 at 18:55
So, with the method I'm suggesting the list would re-populate on every page load (every time Flask renders the page). Is the information in your database changing so frequently that this won't work? If this is the case you **can** pass a function to the source parameter, instead of a list, and this function could use XHR to call an endpoint within Flask to populate the list in real time from the database. It really depends how often the information is changing in your database. – abigperson Nov 28 '16 at 18:57
@PJSantoro No my information in my DB most _likely_ will not change frequently (if it all). But in the case that it _does_ change, then I would prefer the list to automatically change according to whatever the data changed to inside of the database. I actually have not thought about how much my DB will be changing, so I guess I could actually create a static pre-populated list. – kstullich Nov 28 '16 at 19:06
Sure, that makes total sense. If Flask is populating the list I don't really consider it "static" (e.g. static would be typing the list out in HTML). It's just a question or server side (Flask) versus client side (AJAX). Definitely worth considering in your application design. It's a lot of overhead to make it completely "real-time" (e.g. every character typed/deleted in the input box would trigger an XHR call and a database query). – abigperson Nov 28 '16 at 19:13
see the accepted answer here for a good example of "live" autocomplete: http://stackoverflow.com/questions/34704997/jquery-autocomplete-in-flask – abigperson Nov 28 '16 at 19:17

score 0 · Answer 2 · answered Oct 15 '19 at 02:20

0

Consider checking out Datasette which provides a mechanism to have search autocomplete and REST support from a sqlite database.

answered Oct 15 '19 at 02:20

zelusp

3,500
3
31
65

Search Autocomplete using SQLite & Flask

2 Answers2