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 carbrands (
brand_id serial PRIMARY KEY,
brand_name VARCHAR ( 150 ) NOT NULL
);
INSERT INTO
carbrands(brand_name)
VALUES
('Toyota'),
('Honda'),
('Suzuki'),
('Mitsubishi'),
('Hyundai');
CREATE TABLE carmodels (
model_id serial PRIMARY KEY,
brand_id INT NOT NULL,
car_models VARCHAR ( 150 ) NOT NULL
);
INSERT INTO
carmodels(brand_id,car_models)
VALUES
(1, 'Toyota Corolla'),
(2, 'Toyota Camry'),
(1, 'Toyota Yaris'),
(1, 'Toyota Sienna'),
(1, 'Toyota RAV4'),
(1, 'Toyota Highlander'),
(2, 'Honda HR-V'),
(2, 'Honda Odyssey'),
(3, 'Swift'),
(3, 'Celerio'),
(3, 'Ertiga'),
(3, 'Vitara'),
(4, 'Mirage'),
(4, 'Mirage G4'),
(4, 'Xpander Cross'),
(4, 'Montero Sport'),
(4, 'Strada Athlete'),
(5, 'Reina '),
(5, 'Accent'),
(5, 'Elantra'),
(5, 'Tucson');
bootstrap-select plugin https://developer.snapappointments.com/bootstrap-select/
app.py
#app.py
from flask import Flask, render_template, jsonify, request
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('/')
def main():
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
cur.execute("SELECT * FROM carbrands ORDER BY brand_id")
carbrands = cur.fetchall()
return render_template('index.html', carbrands=carbrands)
@app.route("/carbrand",methods=["POST","GET"])
def carbrand():
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
if request.method == 'POST':
category_id = request.form['category_id']
print(category_id)
cur.execute("SELECT * FROM carmodels WHERE brand_id = %s ORDER BY car_models ASC", [category_id] )
carmodel = cur.fetchall()
OutputArray = []
for row in carmodel:
outputObj = {
'id': row['brand_id'],
'name': row['car_models']}
OutputArray.append(outputObj)
return jsonify(OutputArray)
if __name__ == "__main__":
app.run(debug=True)
templates/index.html
//templates/index.html
<!DOCTYPE html>
<html>
<head>
<title>Dependent Dropdown with Search Box using Python Flask jQuery Ajax and PostgreSQL Database</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css" />
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/bootstrap-select/1.12.2/css/bootstrap-select.min.css" />
</head>
<body>
<div class="container">
<h1 align="center">Dependent Dropdown with Search Box using Python Flask jQuery Ajax and PostgreSQL Database</h1>
<div class="row">
<div class="col-md-6">
<label>Select Car</label>
<select name="car_brand" data-live-search="true" id="car_brand" class="form-control" title="Select Car Brand">
{% for row in carbrands %}
<option value="{{row.brand_id}}">{{row.brand_name}}</option>
{% endfor %}
</select>
</div>
<div class="col-md-6">
<label>Select Brand</label>
<select name="car_models" data-live-search="true" id="car_models" class="form-control" title="Select Car Model"> </select>
</div>
</div>
</div>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/js/bootstrap.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/bootstrap-select/1.12.2/js/bootstrap-select.min.js"></script>
<script>
$(document).ready(function () {
$("#car_brand").selectpicker();
$("#car_models").selectpicker();
function load_data(type, category_id) {
$.ajax({
url: "/carbrand",
method: "POST",
data: { type: type, category_id: category_id },
dataType: "json",
success: function (data) { //alert(category_id)
var html = "";
for (var count = 0; count < data.length; count++) {
html += '<option value="' + data[count].id + '">' + data[count].name + "</option>";
}
if (type == "carData") {
$("#car_brand").html(html);
$("#car_brand").selectpicker("refresh");
} else {
$("#car_models").html(html);
$("#car_models").selectpicker("refresh");
}
},
});
}
$(document).on("change", "#car_brand", function () {
var category_id = $("#car_brand").val();
load_data("carModeldata", category_id);
});
});
</script>
</body>
</html>
