最新消息:雨落星辰是一个专注网站SEO优化、网站SEO诊断、搜索引擎研究、网络营销推广、网站策划运营及站长类的自媒体原创博客

javascript - Flask: Dropdown values based on prior selection - Stack Overflow

programmeradmin1浏览0评论

I'm trying to limit selections in a dropdown based on a prior selection the user has made. This is how my flask looks:

init.py

@app.route('/create/', methods=['GET','POST'])
def create():
    mySQL2 = SelectCustomer(session['ID']) #displayed invoicereceiver
    global sessioncur
    try:
        form = CreateinvoiceForm(request.form)
        if request.method == 'POST' and form.validate():
            #HEADER
            #This fetches from HTML
            customer = request.form.get('customer')
            goodsrec = request.form.get('goodsrec')
    return render_template("createinvoice.html", form=form,  mySQL2 = mySQL2)

customer is populated from an html form using mySQL2 as possible variables to select from:

html select form

<select required name="customer" class="selectpicker form-control" , 
placeholder="Select">
<option selected="selected"></option>
{% for o in mySQL2 %}      
<option value="{{ o[2] }}">{{ o[2] }}</option>  
{% endfor %}
</select>

The selection for goodsrec has to dependend on which customer was selected. My idea was to obtain the customer ID doing as follows:

c, conn = connection()
customerID = c.execute("SELECT Cm_Id FROM customer WHERE Cm_name ='" +
str(customer) +"' limit 1")
customerID = c.fetchone()[0]

This value I could then use in a function I have to obtain the goodsreceivers with that ID:

def SelectGoodsrecSEE(customerID):
    c,conn = connection()
    c.execute("SELECT * FROM goodsrec WHERE Gr_Cm_id=" +str(id))
    mySQL8 = c.fetchall()
    c.close()
    conn.close()
    gc.collect()
    return mySQL8

So far I am quite sure that would work. What I don't know is how to structure the flask to make it load the first selection and take it into account for the second one. Similiar to the html I would have to loop through mySQL8. But how does the strucutre look in flask to get that done? At the moment what I have looks like

@app.route('/create/', methods=['GET','POST'])
def create():
    mySQL2 = SelectCustomer(session['ID']) #displayed invoicereceiver
    global sessioncur
    try:
    form = CreateinvoiceForm(request.form)
    if request.method == 'POST' and form.validate():
        #HEADER
        #This fetches from HTML
        customer = request.form.get('customer')
        c, conn = connection()
        customerID = c.execute("SELECT Cm_Id FROM customer WHERE Cm_name ='" +
        str(customer) +"' limit 1")
        customerID = c.fetchone()[0]
        mySQL8 = SelectGoodsrecSEE(customerID)
        goodsrec = request.form.get('goodsrec')
    return render_template("create.html", form=form,  mySQL2 = mySQL2)

I need to be able to pass mySQL8 to the create.html, so that I can make the selection from it in html. Any ideas? Hope its more or less clear what I'm looking for..

EDIT

SELECT * FROM goodsrec WHERE Gr_Cm_id=18; mySQL8

I'm trying to limit selections in a dropdown based on a prior selection the user has made. This is how my flask looks:

init.py

@app.route('/create/', methods=['GET','POST'])
def create():
    mySQL2 = SelectCustomer(session['ID']) #displayed invoicereceiver
    global sessioncur
    try:
        form = CreateinvoiceForm(request.form)
        if request.method == 'POST' and form.validate():
            #HEADER
            #This fetches from HTML
            customer = request.form.get('customer')
            goodsrec = request.form.get('goodsrec')
    return render_template("createinvoice.html", form=form,  mySQL2 = mySQL2)

customer is populated from an html form using mySQL2 as possible variables to select from:

html select form

<select required name="customer" class="selectpicker form-control" , 
placeholder="Select">
<option selected="selected"></option>
{% for o in mySQL2 %}      
<option value="{{ o[2] }}">{{ o[2] }}</option>  
{% endfor %}
</select>

The selection for goodsrec has to dependend on which customer was selected. My idea was to obtain the customer ID doing as follows:

c, conn = connection()
customerID = c.execute("SELECT Cm_Id FROM customer WHERE Cm_name ='" +
str(customer) +"' limit 1")
customerID = c.fetchone()[0]

This value I could then use in a function I have to obtain the goodsreceivers with that ID:

def SelectGoodsrecSEE(customerID):
    c,conn = connection()
    c.execute("SELECT * FROM goodsrec WHERE Gr_Cm_id=" +str(id))
    mySQL8 = c.fetchall()
    c.close()
    conn.close()
    gc.collect()
    return mySQL8

So far I am quite sure that would work. What I don't know is how to structure the flask to make it load the first selection and take it into account for the second one. Similiar to the html I would have to loop through mySQL8. But how does the strucutre look in flask to get that done? At the moment what I have looks like

@app.route('/create/', methods=['GET','POST'])
def create():
    mySQL2 = SelectCustomer(session['ID']) #displayed invoicereceiver
    global sessioncur
    try:
    form = CreateinvoiceForm(request.form)
    if request.method == 'POST' and form.validate():
        #HEADER
        #This fetches from HTML
        customer = request.form.get('customer')
        c, conn = connection()
        customerID = c.execute("SELECT Cm_Id FROM customer WHERE Cm_name ='" +
        str(customer) +"' limit 1")
        customerID = c.fetchone()[0]
        mySQL8 = SelectGoodsrecSEE(customerID)
        goodsrec = request.form.get('goodsrec')
    return render_template("create.html", form=form,  mySQL2 = mySQL2)

I need to be able to pass mySQL8 to the create.html, so that I can make the selection from it in html. Any ideas? Hope its more or less clear what I'm looking for..

EDIT

SELECT * FROM goodsrec WHERE Gr_Cm_id=18; mySQL8

Share Improve this question edited Jul 10, 2018 at 9:44 Ignacio Vergara Kausel 6,0264 gold badges34 silver badges42 bronze badges asked Mar 19, 2017 at 13:10 Mark WellingsMark Wellings 811 silver badge9 bronze badges 4
  • I wrote a post on this subject not too long ago that describes the logic to implement cascading selects with AJAX/XHR using the jQuery $.getJSON() method. You add a second endpoint in your Flask application that takes in customerID and returns jsonify(mySQL8) and then call it whenever the user makes a selection on customer. – abigperson Commented Mar 19, 2017 at 14:58
  • So you suggest I do the selection fields in flask? At the moment I've got them in html.. thanks for the ment!! – Mark Wellings Commented Mar 19, 2017 at 23:11
  • Definitely depends. If it's a CRUD style app with lots of forms then using WTForms will make your life much easier. If it's a couple of simple selects that control some other features of your site it might be overkill. Either way the overarching logic around using AJAX to keep a second select box pegged to the value of the first is the same. – abigperson Commented Mar 19, 2017 at 23:22
  • I've tried to use your solution, but can't get it to work. Is there a more simple way of storing the variable select in the first dropdown and then use that to select the respective select options? I thought maybe I could use a flask definition inside the jinja template.. – Mark Wellings Commented Mar 20, 2017 at 11:37
Add a ment  | 

1 Answer 1

Reset to default 6

SQL Injection Risk

First and foremost you should improve your SQL code because as you have it now you are vulnerable to SQL Injection attacks. So, instead of:

c.execute("SELECT Cm_Id FROM customer WHERE Cm_name ='" + str(customer) + "' limit 1")

the remended usage is:

sql = 'SELECT Cm_Id FROM customer WHERE Cm_name = %s LIMIT 1'
parameters = [str(customer)]
c.execute(sql, parameters)

a couple additional SO posts that discuss this issue:

  • Python best practice and securest to connect to MySQL and execute queries
  • Python MySQL Parameterized Queries

Implementing Cascading Selects

Python:

@app.route('/create/', methods=['GET','POST'])
def create():
    mySQL2 = SelectCustomer(session['ID'])
    global sessioncur
    try:
        form = CreateinvoiceForm(request.form)
        if request.method == 'POST' and form.validate():
            customer = request.form.get('customer')
            goodsrec = request.form.get('goodsrec')
            # do stuff with submitted form...
    return render_template("createinvoice.html", form=form,  mySQL2 = mySQL2)


@app.route('/get_goods_receivers/')
def get_goods_receivers():
    customer = request.args.get('customer')
    print(customer)
    if customer:
        c = connection()
        customerID = c.execute("SELECT Cm_Id FROM customer WHERE Cm_name = %s LIMIT 1", [customer])
        customerID = c.fetchone()[0]
        print customerID
        c.execute("SELECT * FROM goodsrec WHERE Gr_Cm_id = %s", [customerID])
        mySQL8 = c.fetchall()
        c.close()
        # x[0] here is Gr_id (for application use)
        # x[3] here is the Gr_name field (for user display)
        data = [{"id": x[0], "name": x[3]} for x in mySQL8]
        print(data)
    return jsonify(data)

HTML/Javascript:

<select name="customer" id="select_customer" class="selectpicker form-control">
    <option selected="selected"></option>
    {% for o in mySQL2 %}
    <option value="{{ o[2] }}">{{ o[2] }}</option>
    {% endfor %}
</select>

<select name="goodsrec" id="select_goodsrec" class="selectpicker form-control" disabled>
    <option>Select a Customer...</option>
</select>

<script src="https://ajax.googleapis./ajax/libs/jquery/2.1.1/jquery.min.js"></script>
<script charset="utf-8" type="text/javascript">
    $(function() {
        var dropdown = {
            customer: $('#select_customer'),
            goodsrec: $('#select_goodsrec')
        };

        // function to call XHR and update goodsrec dropdown
        function updateGoodsrec() {
            var customer = dropdown.customer.val();
            dropdown.goodsrec.attr('disabled', 'disabled');
            console.log(customer);

            if (customer.length) {
                dropdown.goodsrec.empty();
                $.getJSON("{{ url_for('get_goods_receivers') }}", {customer: customer}, function(data) {
                    console.log(data);
                    data.forEach(function(item) {
                        dropdown.goodsrec.append(
                            $('<option>', {
                                value: item.id,
                                text: item.name
                            })
                        );
                    });
                    dropdown.goodsrec.removeAttr('disabled');
                });
            }
        }

        // event listener to customer dropdown change
        dropdown.customer.on('change', function() {
            updateGoodsrec();
        });

    });
</script>
发布评论

评论列表(0)

  1. 暂无评论