install psycopg2 https://pypi.org/project/psycopg2/
Psycopg is the most popular PostgreSQL database adapter for the Python programming language.
(venv) PS C:\flaskmyproject> pip install psycopg2
CREATE TABLE countries (
id serial PRIMARY KEY,
name VARCHAR ( 60 ) NOT NULL
);
INSERT INTO
countries(id,name)
VALUES
(1, 'Afghanistan'),
(2, 'Aringland Islands'),
(3, 'Albania'),
(4, 'Algeria'),
(5, 'American Samoa'),
(6, 'Andorra');
CREATE TABLE state (
id serial PRIMARY KEY,
name VARCHAR ( 60 ) NOT NULL,
country_id INT NOT NULL
);
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 serial PRIMARY KEY,
state VARCHAR ( 60 ) NOT NULL,
name VARCHAR ( 60 ) NOT NULL,
stateid INT NOT NULL
);
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, 'AC', 'Angeles City', 3);
INSERT INTO city (id, state, name, stateid) VALUES (7, 'OC', 'Olongapo', 3);
INSERT INTO city (id, state, name, stateid) VALUES (8, 'SF', 'San Fernando', 3);
INSERT INTO city (id, state, name, stateid) VALUES (9, 'TA', 'Tarlac', 3);
#app.py
from flask import Flask, render_template, request, jsonify
from flask_sqlalchemy import SQLAlchemy
from wtforms import SelectField
from flask_wtf import FlaskForm #https://flask-wtf.readthedocs.io/en/stable/
app = Flask(__name__)
app.config['SECRET_KEY'] = 'cairocoders-ednalan'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True
#password:admin
app.config['SQLALCHEMY_DATABASE_URI']='postgresql://postgres:admin@localhost/sampledb' #install psycopg2 https://pypi.org/project/psycopg2/
db = SQLAlchemy(app)
class Country(db.Model):
__tablename__ = 'countries'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(60))
class State(db.Model):
__tablename__ = 'state'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(60))
country_id = db.Column(db.Integer)
class City(db.Model):
__tablename__ = 'city'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(60))
stateid = db.Column(db.Integer)
class Form(FlaskForm):
country = SelectField('country', choices=[])
state = SelectField('state', choices=[])
city = SelectField('city', choices=[])
@app.route('/', methods=['GET', 'POST'])
def index():
form = Form()
form.country.choices = [(country.id, country.name) for country in Country.query.all()]
if request.method == 'POST':
city = City.query.filter_by(id=form.city.data).first()
country = Country.query.filter_by(id=form.country.data).first()
state = State.query.filter_by(id=form.state.data).first()
return 'Country : {}, State: {}, City: {}
'.format(country.name, state.name, city.name)
return render_template('index.html', form=form)
@app.route('/state/')
def statebycountry(get_state):
state = State.query.filter_by(country_id=get_state).all()
stateArray = []
for city in state:
stateObj = {}
stateObj['id'] = city.id
stateObj['name'] = city.name
stateArray.append(stateObj)
return jsonify({'statecountry' : stateArray})
@app.route('/city/')
def city(get_city):
state_data = City.query.filter_by(stateid=get_city).all()
cityArray = []
for city in state_data:
cityObj = {}
cityObj['id'] = city.id
cityObj['name'] = city.name
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>Dynamic Select Box using Python Flask PostgreSQL Flask-WTF, javascript and SQLAlchemy</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>Dynamic Select Box using Python Flask PostgreSQL Flask-WTF, javascript and SQLAlchemy</h2></p>
<form method="POST">
{{ form.csrf_token}}
<div class="form-group">
<label for="email">Country:</label>
{{ form.country(class="form-control") }}
</div>
<div class="form-group">
<label for="email">State:</label>
{{ form.state(class="form-control")}}
</div>
<div class="form-group">
<label for="email">City:</label>
{{ form.city(class="form-control")}}
</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;
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>
