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
-
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
customerIDand returnsjsonify(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
1 Answer
Reset to default 6SQL 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>