install flask-mysqldb
pip install flask-mysqldb https://github.com/alexferl/flask-mysqldb
Database Table
CREATE TABLE countries (
id INTEGER PRIMARY KEY,
name STRING (60)
);
INSERT INTO countries (id, name) VALUES (171, 'Philippines');
INSERT INTO countries (id, name) VALUES (227, 'United States of America');
CREATE TABLE state (
id INTEGER PRIMARY KEY,
name STRING (60),
country_id INTEGER
);
INSERT INTO state (id, name, country_id) VALUES (1, 'ARMM', 171);
INSERT INTO state (id, name, country_id) VALUES (2, 'Bicol', 171);
INSERT INTO state (id, name, country_id) VALUES (3, 'Central Luzon', 171);
INSERT INTO state (id, name, country_id) VALUES (4, 'Central Mindanao', 171);
INSERT INTO state (id, name, country_id) VALUES (5, 'Alabama', 227);
INSERT INTO state (id, name, country_id) VALUES (6, 'Alaska', 227);
INSERT INTO state (id, name, country_id) VALUES (7, 'Arizona', 227);
INSERT INTO state (id, name, country_id) VALUES (8, 'California', 227);
INSERT INTO state (id, name, country_id) VALUES (9, 'Florida', 227);
CREATE TABLE city (
id INTEGER PRIMARY KEY,
state STRING (60),
name STRING (60),
stateid INTEGER
);
INSERT INTO city (id, state, name, stateid) VALUES (1, 'CA', 'Anaheim', 8);
INSERT INTO city (id, state, name, stateid) VALUES (2, 'NV', 'Arden-Arcade', 8);
INSERT INTO city (id, state, name, stateid) VALUES (3, 'CA', 'Bakersfield', 8);
INSERT INTO city (id, state, name, stateid) VALUES (4, 'CA', 'Carson', 8);
INSERT INTO city (id, state, name, stateid) VALUES (5, 'NV', 'Daly City', 8);
INSERT INTO city (id, state, name, stateid) VALUES (6, NULL, 'Angeles City', 3);
INSERT INTO city (id, state, name, stateid) VALUES (7, NULL, 'Olongapo', 3);
INSERT INTO city (id, state, name, stateid) VALUES (8, NULL, 'San Fernando', 3);
INSERT INTO city (id, state, name, stateid) VALUES (9, NULL, 'Tarlac', 3);
#app.py
from flask import Flask, render_template, redirect, request, json, jsonify
from flask_mysqldb import MySQL,MySQLdb #pip install flask-mysqldb https://github.com/alexferl/flask-mysqldb
app = Flask(__name__)
app.secret_key = "caircocoders-ednalan-2020"
app.config['MYSQL_HOST'] = 'localhost'
app.config['MYSQL_USER'] = 'root'
app.config['MYSQL_PASSWORD'] = ''
app.config['MYSQL_DB'] = 'testingdb'
app.config['MYSQL_CURSORCLASS'] = 'DictCursor'
mysql = MySQL(app)
@app.route('/', methods=['GET', 'POST'])
def index():
cursor = mysql.connection.cursor()
query = "select * from country"
cursor.execute(query)
country = cursor.fetchall()
message = ''
if request.method == 'POST':
fullname = request.form['fullname']
email = request.form['email']
country = request.form['country']
state = request.form['state']
city = request.form['city']
cur = mysql.connection.cursor(MySQLdb.cursors.DictCursor)
cur.execute("INSERT INTO userflask (fullname, email, countryid, stateid, cityid) VALUES (%s,%s,%s,%s,%s)",(fullname,email,country,state,city))
mysql.connection.commit()
cur.close()
message = "Succesfully Register"
return render_template('index.html', country=country, message=message)
@app.route('/state/')
def statebycountry(get_state):
cur = mysql.connection.cursor(MySQLdb.cursors.DictCursor)
result = cur.execute("SELECT * FROM state WHERE country_id = %s", [get_state])
state = cur.fetchall()
stateArray = []
for row in state:
stateObj = {
'id': row['id'],
'name': row['name']}
stateArray.append(stateObj)
return jsonify({'statecountry' : stateArray})
@app.route('/city/')
def city(get_city):
cur = mysql.connection.cursor(MySQLdb.cursors.DictCursor)
result = cur.execute("SELECT * FROM city WHERE stateid = %s", [get_city])
state = cur.fetchall()
cityArray = []
for row in state:
cityObj = {
'id': row['id'],
'name': row['cityname']}
cityArray.append(cityObj)
return jsonify({'citylist' : cityArray})
if __name__ == '__main__':
app.run(debug=True)
templates/index.html //templates/index.html
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta http-equiv="X-UA-Compatible" content="ie=edge">
<title>Python Flask Dynamic Select Box and Sign Up using Mysql jsonify and javascript</title>
<link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.0/css/bootstrap.min.css" rel="stylesheet" id="bootstrap-css">
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.0/js/bootstrap.min.js"></script>
<script src="https://code.jquery.com/jquery-1.11.1.min.js"></script>
</head>
<body>
<div class="container">
<div class="row">
<div class="col-md-12">
<p><h2>Python Flask Dynamic Select Box and Sign Up using Mysql jsonify and javascript</h2></p>
{% if message %}
<div class="alert alert-success">{{message}}</div>
{% endif %}
<form method="POST">
<div class="form-group">
<label for="email">Name:</label>
<input type="text" class="form-control" name="fullname" id="fullname" placeholder="Your Name">
</div>
<div class="form-group">
<label for="email">Email:</label>
<input type="email" class="form-control" name="email" id="email" placeholder="Your Email">
</div>
<div class="form-group">
<label for="email">Country:</label>
<select class="form-control" id="country" name="country">
{% for row in country %}
<option value="{{row.id}}">{{row.country}}</option>
{% endfor %}
</select>
</div>
<div class="form-group">
<label for="email">State:</label>
<select class="form-control" id="state" name="state"></select>
</div>
<div class="form-group">
<label for="email">City:</label>
<select class="form-control" id="city" name="city"></select>
</div>
<input type="submit" class="btn btn-success btn-lg">
</form>
</div>
</div>
</div>
<script>
country_select = document.getElementById('country');
state_select = document.getElementById('state');
city_select = document.getElementById('city');
country_select.onchange = function(){
country = country_select.value;
alert(country);
fetch('state/' + country).then(function(response){
response.json().then(function(data) {
optionHTML = '';
for (state of data.statecountry) {
optionHTML += '<option value="' + state.id +'">' + state.name + '</option>'
}
state_select.innerHTML = optionHTML;
});
});
}
state_select.onchange = function(){
city = state_select.value;
fetch('city/' + city).then(function(response){
response.json().then(function(data) {
optionHTML = '';
for (city_rs of data.citylist) {
optionHTML += '<option value="' + city_rs.id +'">' + city_rs.name + '</option>'
}
city_select.innerHTML = optionHTML;
});
});
}
</script>
</body>
</html>
