Flask passing selected value from autocomplete and do SQL query to print the result

Question

I am new to Flask (and web development) and I am trying to get the selected value from the autocomplete and pass the value to SQL and finally print the table on html.

Here is my javascript in html

<head>
<script type="text/javascript">
$(function() {
    $("#autocomplete").autocomplete({
        source:function(request, response) {
            $.getJSON("/autocomplete",{
            q: request.term,
            }, function(data) {
            response(data.matching_results);
            });
        },
        minLength: 2,
        select: function(event, ui) {
            alert( "You selected: " + ui.item.label );
            console.log(ui.item.value);
        }
    });
})

</script>
</head>

<body>
<div>
<form class="form-horizontal" method="post" action="/showSelection">
    <h3>Genes</h3>
    <input name="autocomplete" name="inputGene" type="text" placeholder="Gene name" id="autocomplete" class="form-control input-lg"/ >
</form>
</div>
</body>

In my app.py (what I have came up with so far, (the autocomplete part is working, but I am not sure how to get the value and use the value to query SQL)

@app.route('/autocomplete', methods=['GET', 'POST'])
def autocomplete():
    czz = mysql.connect()
    cursor=czz.cursor()
    search = request.args.get('q')
    query = ("SELECT Symbol from drugs where Symbol like '%"+search+"%'")
    cursor.execute(query)
    symbols = cursor.fetchall()
    # query = metadata.query(drugs.Symbol).filter(drugs.Symbol.like('%' + str(search) + '%'))
    results = [mv[0] for mv in symbols]

    return jsonify(matching_results=results)
    czz.close()
    cursor.close()

@app.route('/showSelection', methods=['POST'])
def showSelection():
    pd.set_option('display.max_colwidth', -1)
    _Gene = request.form.get('inputGene')
    # _Gene = request.form['inputGene']
    _Gene = str(_Gene)
    print str(_Gene)
    conn = mysql.connect()
    cursor = conn.cursor()
    query=("SELECT * from tbl_wish where gene_name = %s")%(_Gene)
    cursor.execute(query)
    variant=cursor.fetchall()
    print variant

    vas_dict=[]
    for vas in variant:
        vaf_dict = {
          'Gene':vas[1],
          'Literature':vas[2],
          'Variant':vas[3],
          'Description':vas[4]
         }
         vas_dict.append(vaf_dict)
         variants = pd.DataFrame(vas_dict)
         variants = variants[['Gene','Literature','Variant','Description']]
         #print variants

    return render_template('listVariants.html', tables=[variants.to_html(index=False)], titles=["Variant list"])
    cnn.close()
    cursor.close()

Appreciate any help!!

Hi Adam, the autocomplete part is working, but I am not sure how to get the value and use the value to query SQL — Twixdav, Jul 10 '17 at 19:50
Well first off you need to move the closing of the mysql connection and cursor to above the return statement. Nothing after the return statement is actually executed. You should close the cursor before the connection. https://stackoverflow.com/a/17665827/3990806 — Adam, Jul 10 '17 at 19:52
Also you don't need the `'POST'` method on your autocomplete route. — Adam, Jul 10 '17 at 19:55

score 1 · Answer 1 · answered Jul 10 '17 at 20:22

autocomplete

@app.route('/autocomplete', methods=['GET'])
def autocomplete():
    conn = mysql.connect()
    cursor = conn.cursor()
    search = request.args.get('q')
    query = ("SELECT Symbol from drugs where Symbol like '%"+search+"%'")
    cursor.execute(query)
    symbols = cursor.fetchall()
    # query = metadata.query(drugs.Symbol).filter(drugs.Symbol.like('%' + str(search) + '%'))
    results = [mv[0] for mv in symbols]
    cursor.close()
    conn.close()

    return jsonify(matching_results=results)

show route

@app.route('/showSelection', methods=['GET', 'POST'])
def showSelection():
    gene = request.form.get('inputGene')  # Returns none if not found in request
    if gene is None:
        flash('gene not found')
        return redirect(url_for('selection view'))  # redirect on input
    conn = mysql.connect()
    cursor = conn.cursor()
    query = ("SELECT * from tbl_wish where gene_name = %s")%(gene)
    cursor.execute(query)
    variant = cursor.fetchall()

    vas_dict = []
    for vas in variant:
        vaf_dict = {
          'Gene':vas[1],
          'Literature':vas[2],
          'Variant':vas[3],
          'Description':vas[4]
        }
        vas_dict.append(vaf_dict)
    cursor.close()
    conn.close()

    return render_template('listVariants.html', variants=vas_dict)

select.html

<head>
<script type="text/javascript">
$(function() {
    $("#inputGene").autocomplete({
        source:function(request, response) {
            $.getJSON("/autocomplete",{
            q: request.term,
            }, function(data) {
            response(data.matching_results);
            });
        },
        minLength: 2,
        select: function(event, ui) {
            alert( "You selected: " + ui.item.label );
            console.log(ui.item.value);
        }
    });
})

</script>
</head>

<body>
<div>
<form class="form-horizontal" method="post" action="/showSelection">
    <h3>Genes</h3>
    <input name="inputGene" type="text" placeholder="Gene name" id="inputGene" class="form-control input-lg"/ >
</form>
</div>
</body>

1) Your input field has two names and it should only have one

2) You can remove the 'POST' method from your autocomplete route as it is unnecessary

3) You need to close the cursor and connection in the scope of the route preferably cursor then connection (i.e. before the return statement)

4) You need the 'GET' method on your /showSelection route.

5) You shouldn't be relying on pandas to format your tables. That is what Jinja2 is for.

selection.html

{% if variants %}
<table>
    <thead>
    <tr>
        <th>Gene</th>
        <th>Literature</th>
        <th>Variant</th>
        <th>Description</th>
    </tr>
    </thead>
    <tbody>
    {% for variant in variants %}
    <tr>
        <td>variant.Gene</td>
        <td>variant.Literature</td>
        <td>variant.Variant</td>
        <td>variant.Description</td>
    </tr>
    {% endfor %}
</table>
{% else %}
     <p>There are no results</p>
{% endif %}

Thank you Adam, will try it out and post the update. – Twixdav Jul 10 '17 at 21:48 — Twixdav, Jul 10 '17 at 21:48

score 0 · Answer 2 · answered Jul 11 '17 at 15:51

I modified my codes based on your suggestions, for some reason the selected value from the client side (autocomplete event) never get passed back to the server side. I did some googlings and added these two lines to my select: function(

select: function(event, ui) {
            $("#inputGene").val(ui.item.label);
            var getID=ui.item.value;
            return false;
        }

and it works. I am now have a working autocomplete and can get the user selected value to sql and print to table in html.

Now I am trying to figure out how to display a warning message from within the select: fucntion (when the user selected value not in the DB). Currently I can do it in app.py (by return to render_template an erro.html.

Finally, I need to use pandas to format my output before send them to Jinja to print table. Otherwise a list of dictionaries won't work with Jinja. I am sure there are other ways to do it but I am still learning.

Thanks for the pointers

Flask passing selected value from autocomplete and do SQL query to print the result

2 Answers2