FLASK HTML field for Mysql query

Question

Hello Stackoverflow community,

I am new to FLASK however while the learning curve has been very steep, there is one item that I have not been able to get my head around.

I am using a very simple HTML seach form, into which users type the name of a city, this input gets passed to a Mysql query and returns output into a Table.

Everything works except that I can't get the variable to pass into Mysql... if I fix the query it works.

I tried to work with FLASK WTForms, POST and GET requets, but I don't know where I am going wrong. The variable data that I am passing is not confidencial, so I have no concern if it shows up in the URL.

Here just the simple FORM (I guess not correct)

  <form>
<div class="form-group">
    <div class="col-sm-3">
        <input type="text" placeholder="City Name" name="City_Name" action=/search class="form-control">
    </div>

<div class="form-group">
    <div class="col-sm-2">
        <input type="submit" value="SEARCH" class="btn btn-primary btn-block">
    </div>
    </div>
</form>

Here the table output (working perfectly)

<table class="table table-striped">


            <tr>
              <th>PO_Number</th>
              <th>Plant Name</th>
              <th>GMID</th>
              <th>Material</th>
              <th>INCOTERM</th>
              <th>Vendor</th>
              <th>Vendor Count</th>
            </tr>


           {% for row in tabledata %}
           <tr>

             <td>{{ row['PO_Number'] }}</td>
             <td>{{ row['PN'] }}</td>
             <td>{{ row['GD'] }}</td>
             <td>{{ row['MN'] }}</td>
             <td>{{ row['INCO'] }}</td>
             <td>{{ row['VNGS'] }}</td>
             <td>{{ row['CVNGS'] }}</td>
           </tr>
           {% endfor %}

   </table>

Here the Python code

from flask import Flask, render_template, request, url_for
from dbhelper_single_search import DBHelper


app = Flask(__name__)
DB = DBHelper()


@app.route('/table')
def table():
    try:
        tabledata = DB.table_inputs()
    except Exception as e:
        print(e)
        tabledata = None
    return render_template("table.html", tabledata=tabledata)


if __name__ == '__main__':
    app.run(port=5000, debug=True)

Data Base Helper Mysql (the valye for PLN should change based on the input in the Form.

import pymysql
class DBHelper:

def table_inputs(self):
        connection = self.connect()
        PLN="**City_Name**"
        try:
            query = "SELECT Plant_Geo, Plant_Code, Plant_Name, GMID, Material_Name, GROUP_CONCAT(DISTINCT Vendor_Name_GS ORDER BY Vendor_Name_GS) as VNGS, sum(2014_USD), sum(2015_USD), sum(2016_USD) FROM invoice_report WHERE plant_code like '%s' GROUP BY GMID ORDER BY sum(2015_USD) DESC" %(PLN);
            with connection.cursor(pymysql.cursors.DictCursor) as cursor:
                cursor.execute(query)
            return cursor.fetchall()
        finally:
            connection.close()

Thank you in advance for any help.

abigperson · Accepted Answer · 2017-10-26 14:23:49Z

2

I think you need to set the action on the <form> element, rather than <input> and you want to direct it to the same Flask endpoint (I assume?):

<form method="GET" action>
    <div class="form-group">
        <div class="col-sm-3">
            <input type="text" placeholder="City Name" name="City_Name" class="form-control">
        </div>
    </div>
    <div class="form-group">
        <div class="col-sm-2">
            <input type="submit" value="SEARCH" class="btn btn-primary btn-block">
        </div>
    </div>
</form>

Update your helper class a little to accept a city variable from your view function (you could tighten this up a bit more):

import pymysql


class DBHelper:

    def table_inputs(self, city):
        connection = self.connect()
        PLN = "**%s**" % city
        try:
            query = "SELECT Plant_Geo, Plant_Code, Plant_Name, GMID, Material_Name, GROUP_CONCAT(DISTINCT Vendor_Name_GS ORDER BY Vendor_Name_GS) as VNGS, sum(2014_USD), sum(2015_USD), sum(2016_USD) FROM invoice_report WHERE plant_code like '%s' GROUP BY GMID ORDER BY sum(2015_USD) DESC";
            with connection.cursor(pymysql.cursors.DictCursor) as cursor:
                # actually better to pass parameters like this:
                cursor.execute(query, (PLN,))
            return cursor.fetchall()

        except Exception as err:
            # this may also help find errors generated above...
            print(err)

        finally:
            connection.close()

Then, update your view function to test if city is submitted and submit it to your helper class:

@app.route('/table')
def table():
    // the second argument is the default if "City_Name" is not submitted
    city = request.args.get('City_Name', 'New York')
    try:
        tabledata = DB.table_inputs(city)
    except Exception as e:
        print(e)
        tabledata = None
    return render_template("table.html", tabledata=tabledata)

edited Oct 26, 2017 at 14:23

answered Dec 16, 2016 at 15:44

abigperson

5,3823 gold badges25 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

S. Pynewb22 Over a year ago

PJ Santoro, thank you so much! This works 100%. Great help and really appreciated.

abigperson Over a year ago

Great, glad I could help! If you could accept the answer that would be great.

S. Pynewb22 Over a year ago

PJ Santoro it has been a while and I have used this code for the last year. However recently I up-graded to macOS Sierra and MariaDB 10.2.9 and the code (exactly the same one) now throws a "TypeError: 'NoneType' object is not iterable". I cannot figure out why, but if you have any idea I would greatly appreciate.

abigperson Over a year ago

maybe try changing the %s to ?... could be a minor change like that in the DBAPI... not familiar with MariaDB

abigperson Over a year ago

PS: just made a couple edits above... 1) better practice to pass parameters into the execute function directly so everything is properly escaped 2) added an except clause as I assume there is an error where tabledata is ending up as None... hence the error you are experiencing

|

Collectives™ on Stack Overflow

FLASK HTML field for Mysql query

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related