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'),
(171, 'Philippines'),
(227, 'United States of America');
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, 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);
CREATE TABLE userflask (
id serial PRIMARY KEY,
fullname VARCHAR ( 150 ) NOT NULL,
email VARCHAR ( 150 ) NOT NULL,
countryid INT NOT NULL,
stateid INT NOT NULL,
cityid INT NOT NULL
);
#app.py
from flask import Flask, render_template, redirect, request, json, jsonify
import psycopg2 #pip install psycopg2
import psycopg2.extras
app = Flask(__name__)
app.secret_key = "caircocoders-ednalan"
DB_HOST = "localhost"
DB_NAME = "sampledb"
DB_USER = "postgres"
DB_PASS = "admin"
conn = psycopg2.connect(dbname=DB_NAME, user=DB_USER, password=DB_PASS, host=DB_HOST)
@app.route('/', methods=['GET', 'POST'])
def index():
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
query = "select * from countries"
cur.execute(query)
country = cur.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.execute("INSERT INTO userflask (fullname, email, countryid, stateid, cityid) VALUES (%s,%s,%s,%s,%s)",(fullname,email,country,state,city))
conn.commit()
cur.close()
message = "Succesfully Register"
return render_template('index.html', country=country, message=message)
@app.route('/state/<get_state>')
def statebycountry(get_state):
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
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/<get_city>')
def city(get_city):
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
cur.execute("SELECT * FROM city WHERE stateid = %s", [get_city])
state = cur.fetchall()
cityArray = []
for row in state:
cityObj = {
'id': row['id'],
'name': row['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 and Sign Up using Python Flask PostgreSQL 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>Dynamic Select Box and Sign Up using Python Flask PostgreSQL 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.name}}</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>
