Tutorial101

Showing posts with label PostgreSQL. Show all posts

Sunday, July 25, 2021

Dynamic Select Box and Sign Up using Python Flask PostgreSQL jsonify and javascript

Dynamic Select Box and Sign Up using Python Flask PostgreSQL jsonify and javascript

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

#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>

Saturday, July 24, 2021

Add Remove Input Fields Dynamically with Python Flask jQuery and PostgreSQL Database

Add Remove Input Fields Dynamically with Python Flask jQuery and PostgreSQL Database

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 skills (

id serial PRIMARY KEY,

skillname VARCHAR ( 150 ) NOT NULL

);

app.py

#app.py
from flask import Flask, render_template, 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('/', methods=['GET', 'POST'])
def index():
    cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
    message = ''    
    if request.method == 'POST':
        skills = request.form.getlist('field[]')
        for value in skills:  
            cur.execute("INSERT INTO skills (skillname) VALUES (%s)",[value])
            conn.commit()       
        cur.close()
        message = "Succesfully Register"
    return render_template('index.html', message=message)
 
if __name__ == "__main__":
    app.run(debug=True)

templates/index.html

//templates/index.html
<html>  
<head>  
<title>Add Remove Input Fields Dynamically with Python Flask jQuery and PostgreSQL Database</title>  
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.3/jquery.min.js"></script>
<style>
.input-wrapper div {
    margin-bottom: 10px;
}
.remove-input {
    margin-top: 10px;
    margin-left: 15px;
    vertical-align: text-bottom;
}
.add-input {
    margin-top: 10px;
    margin-left: 10px;
    vertical-align: text-bottom;
}
</style>
</head>  
<body>  
<div style="width:85%;padding:50px;">  
    <h2>Add Remove Input Fields Dynamically with Python Flask jQuery and PostgreSQL Database</h2>  
    {% if message %}
          <div class="alert alert-success">{{message}}</div>
    {% endif %}
    <form method="POST">
        <div class="input-wrapper">
            <div>Programming Language : <br/>
            <input type="text" name="field[]" value=""/>
            <a href="javascript:void(0);" class="add-input" title="Add input"><img src="/static/img/add.png"/></a>
            </div>
        </div>
        <input type="submit" name="cmdsubmit">
    </form>
</div>  
<script>
$(document).ready(function(){
    var max_input_fields = 10;
    var add_input = $('.add-input');
    var input_wrapper = $('.input-wrapper');
    var new_input = '<div><input type="text" name="field[]" value=""/><a href="javascript:void(0);" class="remove-input" title="Remove input"><img src="/static/img/remove.png"/></a></div>';
    var add_input_count = 1; 
    $(add_input).click(function(){
        if(add_input_count < max_input_fields){
            add_input_count++; 
            $(input_wrapper).append(new_input); 
        }
    });
    $(input_wrapper).on('click', '.remove-input', function(e){
        e.preventDefault();
        $(this).parent('div').remove();
        add_input_count--;
    });
});
</script>
</body>  
</html>  

Drag and Drop Reorder with Jquery Ajax Jquery-UI Python Flask and PostgreSQL Database

Drag and Drop Reorder with Jquery Ajax Jquery-UI Python Flask and PostgreSQL Database

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 gallery (

id serial PRIMARY KEY,

photoname VARCHAR ( 150 ) NOT NULL,

display_order INT NOT NULL,

created TIMESTAMP,

modified TIMESTAMP

);

INSERT INTO

gallery(photoname, display_order, created, modified)

VALUES

('01.jpg', '1', '2021-06-29', '2021-06-29'),

('02.jpg', '2', '2021-06-29', '2021-06-29'),

('03.jpg', '3', '2021-06-29', '2021-06-29'),

('04.jpg', '4', '2021-06-29', '2021-06-29');

https://jqueryui.com/

Sortable https://jqueryui.com/sortable/

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 gallery ORDER BY display_order")
    gallery = cur.fetchall()
    return render_template('index.html', gallery=gallery)
       
@app.route("/orderupdate",methods=["POST","GET"])
def orderupdate():
    cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)    
    if request.method == 'POST':
        cur.execute("SELECT * FROM gallery")    
        number_of_rows = cur.rowcount   
        print(number_of_rows)    
        getorder = request.form['order']    
        order = getorder.split(",", number_of_rows)
        count=0   
        for value in order:
            count +=1
            #print(count)                       
            cur.execute("UPDATE gallery SET display_order = %s WHERE id = %s ", [count, value])
            conn.commit()       
        cur.close()
    return jsonify(order)
 
if __name__ == "__main__":
    app.run(debug=True)

templates/index.html

//templates/index.html
<html>  
<head>  
<title>Drag and Drop Reorder with Jquery Ajax Jquery-UI Python Flask and PostgreSQL Database</title>  
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.4/jquery.min.js"></script>
<script src="https://ajax.googleapis.com/ajax/libs/jqueryui/1.12.1/jquery-ui.min.js"></script>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css">
<style>
.gallery{ width:100%; float:left;}
.gallery ul{ margin:0; padding:0; list-style-type:none;}
.gallery ul li{ padding:7px; border:2px solid #ccc; float:left; margin:10px 7px; background:none; width:auto; height:auto;}
.gallery img{ width:250px;}
</style>
</head>  
<body> 
<div class="container">
    <h2>Drag and Drop Reorder with Jquery Ajax Jquery-UI Python Flask and PostgreSQL Database</h2>
    <div>     
        <div class="gallery">
            <ul class="reorder-gallery">
            {% for row in gallery %}
                <li id="{{row.id}}" class="ui-sortable-handle"><a href="javascript:void(0);"><img src="/static/images/{{row.photoname}}" alt=""></a></li>
            {% endfor %}
            </ul>
        </div>
    </div><div id="test"></div>
</div>
<script>
$(document).ready(function(){   
    $("ul.reorder-gallery").sortable({      
        update: function( event, ui ) {
            updateOrder();
        }
    });  
});
function updateOrder() {    
    var item_order = new Array();
    $('ul.reorder-gallery li').each(function() {
        item_order.push($(this).attr("id"));
    }); 
    var order_string = 'order='+item_order;
    $.ajax({
        method: "POST",
        url: "/orderupdate",
        data: order_string,
        cache: false,
        success: function(data){    
            $("#test").html(data);
        }
    });
}
</script> 
</body>  
</html> 

Friday, July 23, 2021

Drag and Drop File Upload using DropzoneJS Python Flask PostgreSQL

Drag and Drop File Upload using DropzoneJS Python Flask PostgreSQL

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 uploads (

id serial PRIMARY KEY,

file_name VARCHAR ( 150 ) NOT NULL,

upload_time TIMESTAMP NOT NULL

);

DropzoneJS is an open source library that provides drag’n’drop file uploads with image previews.

https://www.dropzonejs.com/

app.py

#app.py
from flask import Flask, render_template, jsonify, request
from werkzeug.utils import secure_filename
import os
#import magic
import urllib.request
from datetime import datetime
 
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)
 
UPLOAD_FOLDER = 'static/uploads'
app.config['UPLOAD_FOLDER'] = UPLOAD_FOLDER
app.config['MAX_CONTENT_LENGTH'] = 16 * 1024 * 1024
   
ALLOWED_EXTENSIONS = set(['txt', 'pdf', 'png', 'jpg', 'jpeg', 'gif'])
   
def allowed_file(filename):
 return '.' in filename and filename.rsplit('.', 1)[1].lower() in ALLOWED_EXTENSIONS
 
@app.route('/')
def main():
    return render_template('index.html')
       
@app.route("/upload",methods=["POST","GET"])
def upload():
    cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)   
    if request.method == 'POST':
        file = request.files['file']
        filename = secure_filename(file.filename)
        now = datetime.now()
          
        if file and allowed_file(file.filename):
           file.save(os.path.join(app.config['UPLOAD_FOLDER'], filename))
  
           cur.execute("INSERT INTO uploads (file_name, upload_time) VALUES (%s, %s)",[file.filename, now])
           conn.commit()
           cur.close()
           print('File successfully uploaded ' + file.filename + ' to the database!')
        else:
           print('Invalid Uplaod only txt, pdf, png, jpg, jpeg, gif') 
        msg = 'Success Uplaod'    
    return jsonify(msg)
 
if __name__ == "__main__":
    app.run(debug=True)

templates/index.html

//templates/index.html
<html>  
 <head>  
  <title>Drag and Drop File Upload using DropzoneJS Python Flask PostgreSQL</title>  
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.3/jquery.min.js"></script>
<link rel="stylesheet" type="text/css" href="/static/dropzone/dropzone.css" />
<script type="text/javascript" src="/static/dropzone/dropzone.js"></script>
 </head>  
 <body> 
<div class="container"> 
    <h2>Drag and Drop File Upload using DropzoneJS Python Flask PostgreSQL</h2>
    <div class="dropzone">
        <div class="dz-message needsclick">
            <h1>Drop files here or click to upload.</h1>
        </div>
    </div>
</div>
<script>
$(document).ready(function(){
    $(".dropzone").dropzone({
      url: '/upload',
      width: 300,
      height: 300, 
      progressBarWidth: '100%',
      maxFileSize: '5MB'
    })
});
</script> 
 </body>  
</html> 

Thursday, July 22, 2021

Insert Checkbox values using Python Flask Jquery Ajax and PostgreSQL Database

Insert Checkbox values using Python Flask Jquery Ajax and PostgreSQL Database

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 checkbox (

id serial PRIMARY KEY,

name VARCHAR ( 150 ) NOT NULL

);

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():
    return render_template('index.html')
  
@app.route("/insert",methods=["POST","GET"])
def insert():  
    cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
    if request.method == 'POST':
        insert = request.form['checkboxvalue'] 
        cur.execute("INSERT INTO checkbox (name) VALUES (%s)",[insert])
        conn.commit()
        cur.close() 
        msg = 'Data Inserted Successfully!'
    else:
        msg = 'Invalid'
    return jsonify(msg)
 
if __name__ == "__main__":
    app.run(debug=True)

templates/index.html

//templates/index.html
<!doctype html>
<html>
<head>
    <meta charset="UTF-8">
    <title>Insert Checkbox values using Python Flask Jquery Ajax and PostgreSQL Database</title>
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.0/jquery.min.js"></script>
</head>
<body>
    <h3>Insert Checkbox values using Python Flask Jquery Ajax and PostgreSQL Database</h3>
    <label><input type="checkbox" class="get_value" value="Python"> Python</label>
    <br/>
    <label> <input type="checkbox" class="get_value" value="JavaScript"> JavaScript</label>
    <br/>
    <label><input type="checkbox" class="get_value" value="Java"> Java</label>
    <br/>
    <label><input type="checkbox" class="get_value" value="PHP"> PHP</label>
    <br/>
    <label><input type="checkbox" class="get_value" value="Swift"> Swift</label>
    <br/>
    <br/>
    <button type="button" name="submit" id="submit">Save</button>
    <br/>
    <h4 id="result"></h4>
    <script>
        $(document).ready(function() {
            $('#submit').click(function() {
                var insert = [];
                $('.get_value').each(function() {
                    if ($(this).is(":checked")) {
                        insert.push($(this).val());
                    }
                });
                insert = insert.toString();         
                var insert_string = 'checkboxvalue='+insert;            
                $.ajax({
                    method: "POST",
                    url: "/insert",
                    data: insert_string,
                    cache: false,
                    success: function(data){    
                        $("#result").html(data);
                    }
                });
            });
        });
    </script>
</body>
</html>

Dependent Dropdown with Search Box using Python Flask jQuery Ajax and PostgreSQL Database

Dependent Dropdown with Search Box using Python Flask jQuery Ajax and PostgreSQL Database

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>

Tuesday, July 13, 2021

Delete multiple records by selecting checkboxes using Python Flask with PostgreSQL

Delete multiple records by selecting checkboxes using Python Flask with PostgreSQL

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 contacts (

id serial PRIMARY KEY,

fullname VARCHAR ( 150 ) NOT NULL,

position VARCHAR ( 150 ) NOT NULL,

office VARCHAR ( 150 ) NOT NULL

);

INSERT INTO

contacts(fullname, position, office)

VALUES

('Tiger Wood', 'Accountant', 'Tokyo'),

('Mark Oto Ednalan', 'Chief Executive Officer (CEO)', 'London'),

('Jacob thompson', 'Junior Technical Author', 'San Francisco'),

('cylde Ednalan', 'Software Engineer', 'Olongapo'),

('Rhona Davidson', 'Software Engineer', 'San Francisco'),

('Quinn Flynn', 'Integration Specialist', 'New York'),

('Tiger Nixon', 'Software Engineer', 'London'),

('Airi Satou', 'Pre-Sales Support', 'New York'),

('Angelica Ramos', 'Sales Assistant', 'New York'),

('Ashton updated', 'Senior Javascript Developer', 'Olongapo'),

('Bradley Greer', 'Regional Director', 'San Francisco'),

('Brenden Wagner', 'Javascript Developer', 'San Francisco'),

('Brielle Williamson', 'Personnel Lead', 'Olongapo'),

('Bruno Nash', 'Customer Support', 'New York'),

('cairocoders', 'Sales Assistant', 'Sydney'),

('Zorita Serrano', 'Support Engineer', 'San Francisco'),

('Zenaida Frank', 'Chief Operating Officer (COO)', 'San Francisco'),

('Sakura Yamamoto', 'Support Engineer', 'Tokyo'),

('Serge Baldwin', 'Data Coordinator', 'Singapore'),

('Shad Decker', 'Regional Director', 'Tokyo');

app.py

#app.py
from flask import Flask, render_template, request, redirect, flash
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)
    result = cur.execute("SELECT * FROM contacts ORDER BY id")
    contacts = cur.fetchall()   
    return render_template('index.html', contacts=contacts)
      
@app.route('/delete', methods=['GET', 'POST'])
def delete():   
    cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
    if request.method == 'POST': 
        for getid in request.form.getlist('mycheckbox'):
            print(getid)
            cur.execute('DELETE FROM contacts WHERE id = {0}'.format(getid))
            conn.commit()
        flash('Successfully Deleted!')
    return redirect('/')
     
if __name__ == "__main__":
    app.run(debug=True)

templates/index.html

//templates/index.html
<!DOCTYPE html>
<html>
 <head>
  <title>Delete multiple records by selecting checkboxes using Python Flask with PostgreSQL</title>
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.3/jquery.min.js"></script>
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>
 </head>
 <body>
  <div class="container">
   <br />
   <h3 align="center">Delete multiple records by selecting checkboxes using Python Flask with PostgreSQL</h3><br />
   <form method="POST" action="/delete">
   <div class="table-responsive">
    <div align="right" style="padding:10px;">
        {% with messages = get_flashed_messages() %}
          {% if messages %}
         {% for message in messages %}
         <div class="alert alert-danger" role="alert">{{ message }}</div>
         {% endfor %}
         {% endif %}
        {% endwith %}
       <input type="submit" value="Delete All Selected" class="btn btn-primary">  
      </div>
    <table class="table table-bordered">
     <tr>
      <th>Name</th>
      <th>Position</th>
      <th>Office</th>
      <th>Delete</th>
     </tr>
     {% for row in contacts %}
     <tr id="{{row.id}}" >
      <td>{{row.fullname}}</td>
      <td>{{row.position}}</td>
      <td>{{row.office}}</td>
      <td><input type="checkbox" name="mycheckbox" value="{{row.id}}" /></td>
     </tr>
     {% endfor %}
    </table>
   </div>
   </form>  
 </body>
</html>

Monday, July 12, 2021

Progress Bar Data Insert using Python Flask Jquery Ajax Bootstrap and PostgreSQL database

Progress Bar Data Insert using Python Flask Jquery Ajax Bootstrap and PostgreSQL database

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 tbl_user (

id serial PRIMARY KEY,

username VARCHAR ( 150 ) NOT NULL,

useremail VARCHAR ( 150 ) NOT NULL

);

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 index():
    return render_template('index.html')
   
@app.route("/ajaxprogressbar",methods=["POST","GET"])
def ajaxprogressbar():
    if request.method == 'POST':
        username = request.form['username']
        useremail = request.form['useremail']
        print(username)
        cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
        cur.execute("INSERT INTO tbl_user (username, useremail) VALUES (%s, %s)",[username, useremail])
        conn.commit()
        cur.close()
        msg = 'New record created successfully'   
    return jsonify(msg)
     
if __name__ == "__main__":
    app.run(debug=True)

templates/index.html

100

101

102

103

104

105

106

107

108

//templates/index.html
<!DOCTYPE html>
<html>
<head>
<title>Progress Bar Data Insert using Python Flask Jquery Ajax Bootstrap and PostgreSQL database</title>  
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.0/jquery.min.js"></script>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
<script>
$(document).ready(function(){
    $('#sample_form').on('submit', function(event){
        event.preventDefault();
        var count_error = 0;
        if($('#username').val() == '') {
            $('#first_name_error').text('User Name is required');
            count_error++;
        }else
        {
            $('#first_name_error').text('');
        }
   
        if($('#useremail').val() == '') {
            $('#last_name_error').text('Email is required');
            count_error++;
        }else {
            $('#last_name_error').text('');
        }
   
        if(count_error == 0)
        {
            $.ajax({
                url:"/ajaxprogressbar",
                method:"POST",
                data:$(this).serialize(),
                beforeSend:function()
                {
                    $('#save').attr('disabled', 'disabled');
                    $('#process').css('display', 'block');
                },
                success:function(data)
                { 
                    var percentage = 0;
                    var timer = setInterval(function(){
                    percentage = percentage + 20;
                    progress_bar_process(percentage, timer,data);
                    }, 1000);
                }
            })
        }else{
            return false;
        }
  });
     
  function progress_bar_process(percentage, timer,data)
  {
    $('.progress-bar').css('width', percentage + '%');
    if(percentage > 100)
    {
        clearInterval(timer);
        $('#sample_form')[0].reset();
        $('#process').css('display', 'none');
        $('.progress-bar').css('width', '0%');
        $('#save').attr('disabled', false);
        $('#success_message').html(data);
        setTimeout(function(){
        $('#success_message').html('');
        }, 5000);
    }
  }
     
 });
</script>
</head>
<body><br /><br />
<div class="container">
   <h1 align="center">Progress Bar Data Insert using Python Flask Jquery Ajax Bootstrap and PostgreSQL database</h1>
   <br />
   <div class="panel panel-default">
        <div class="panel-heading">
        <h3 class="panel-title">Registration</h3>
        </div>
        <div class="panel-body">
            <span id="success_message"></span>
            <form method="post" id="sample_form">
                <div class="form-group">
                    <label>User Name</label>
                    <input type="text" name="username" id="username" class="form-control" />
                    <span id="first_name_error" class="text-danger"></span>
                </div>
                <div class="form-group">
                    <label>Email</label>
                    <input type="text" name="useremail" id="useremail" class="form-control" />
                    <span id="last_name_error" class="text-danger"></span>
                </div>
                <div class="form-group" align="center">
                    <input type="submit" name="save" id="save" class="btn btn-info" value="Save" />
                </div>
            </form>
            <div class="form-group" id="process" style="display:none;">
                <div class="progress">
                    <div class="progress-bar progress-bar-striped active bg-success" role="progressbar" aria-valuemin="0" aria-valuemax="100" style=""></div>
                </div>
            </div>
        </div>
    </div>
</div>
</body>
</html>

Add Remove Input Fields Dynamically with JQuery Ajax Python Flask and PostgreSQL

Add Remove Input Fields Dynamically with JQuery Ajax Python Flask and PostgreSQL

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 skills (

id serial PRIMARY KEY,

skillname VARCHAR ( 150 ) NOT NULL

);

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 index():
    return render_template('index.html')
  
@app.route("/postskill",methods=["POST","GET"])
def postskill():
    cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
    if request.method == 'POST':
        skills = request.form.getlist('skill[]')
        for value in skills:  
            cur.execute("INSERT INTO skills (skillname) VALUES (%s)",[value])
            conn.commit()       
        cur.close()
        msg = 'New record created successfully'   
    return jsonify(msg)
     
if __name__ == "__main__":
    app.run(debug=True)

templates/index.html

//templates/index.html
<!DOCTYPE html>
<html>
<head> 
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Add Remove Input Fields Dynamically with JQuery Ajax Python Flask and PostgreSQL</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />  
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>  
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>    
</head>
<body>
 <script>
$(document).ready(function() {
   
var MaxInputs       = 8; //maximum input boxes allowed
var InputsWrapper   = $("#InputsWrapper"); //Input boxes wrapper ID
var AddButton       = $("#AddMoreFileBox"); //Add button ID
   
var x = InputsWrapper.length; //initlal text box count
var FieldCount=1; //to keep track of text box added
   
$(AddButton).click(function (e)  //on add input button click
{
    if(x <= MaxInputs) //max input box allowed
    {
        FieldCount++; //text box added increment
        //add input box
        $(InputsWrapper).append('<div class="row"><p class="col-xs-6"><input type="text" placeholder="Enter your skill" class="form-control skill_list" name="skill[]" id="field_'+ FieldCount +'" value="Enter your skill '+ FieldCount +'"/></p><a href="#" class="btn btn-danger removeclass">×</a></div>');
        x++; //text box increment
    }
    return false;
});
   
$("body").on("click",".removeclass", function(e){ //user click on remove text
    if( x > 1 ) {
        $(this).parent('div').remove(); //remove text box
         x--; //decrement textbox
    }
    return false;
})
 
 $('#submit').click(function(){            
    $.ajax({  
        url:"/postskill",  
        method:"POST",  
        data:$('#add_skills').serialize(),  
        success:function(data)  
        {  alert(data)
            $('#resultbox').html(data);  
            $('#add_skills')[0].reset();  
        }  
    });  
}); 
 
});
</script>
<style>
.row {padding:10px;}
</style>
<div class="container"><br /> <br />  
    <h2 align="center">Add Remove Input Fields Dynamically with JQuery Ajax Python Flask and PostgreSQL</h2><div id="resultbox"></div>  
    <div class="form-group">  
        <form name="add_skills" id="add_skills">  
        <div id="InputsWrapper">
            <div class="row">
                <div class="col-xs-6"><input type="text" name="skill[]" placeholder="Enter your skill" class="form-control name_list" /></div>
                <div class="col-xs-6"><button type="button" name="add" id="AddMoreFileBox" class="btn btn-success">Add More</button></div>
            </div>
        </div>
        <br/>
        <input type="button" name="submit" id="submit" class="btn btn-info" value="Submit" />  
        </form>  
    </div>  
</div>  
</body>
</html>

Saturday, July 10, 2021

Upload Multiple Images using Python Flask PostgreSQL Database

Upload Multiple Images using Python Flask PostgreSQL Database

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 images (

id serial PRIMARY KEY,

file_name VARCHAR ( 100 ) NOT NULL,

uploaded_on TIMESTAMP

);

app.py

#app.py
from flask import Flask, render_template, redirect, request, flash
from werkzeug.utils import secure_filename
import os
#import magic
import urllib.request
from datetime import datetime
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)
  
UPLOAD_FOLDER = 'static/uploads'
app.config['UPLOAD_FOLDER'] = UPLOAD_FOLDER
   
ALLOWED_EXTENSIONS = set(['png', 'jpg', 'jpeg', 'gif'])
   
def allowed_file(filename):
 return '.' in filename and filename.rsplit('.', 1)[1].lower() in ALLOWED_EXTENSIONS
  
@app.route('/')
def index():
    return render_template('index.html')
  
@app.route("/upload",methods=["POST","GET"])
def upload():
    cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
    now = datetime.now()
    print(now)
    if request.method == 'POST':
        files = request.files.getlist('files[]')
        #print(files)
        for file in files:
            if file and allowed_file(file.filename):
                filename = secure_filename(file.filename)
                file.save(os.path.join(app.config['UPLOAD_FOLDER'], filename))
                cur.execute("INSERT INTO images (file_name, uploaded_on) VALUES (%s, %s)",[filename, now])
                conn.commit()
            print(file)
        cur.close()   
        flash('File(s) successfully uploaded')    
    return redirect('/')
     
if __name__ == "__main__":
    app.run(debug=True)

templates/index.html

//templates/index.html
<!DOCTYPE html>
<html>
<head>
 <title>Upload Multiple Images using Python Flask PostgreSQL Database</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />  
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>  
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>
</head>
<body>
<div class="container">
<h2>Upload Multiple Images using Python Flask PostgreSQL Database</h2>
    <div class="row">
        <div class="col-lg-12">
            <p>
                {% with messages = get_flashed_messages() %}
                  {% if messages %}
                 {% for message in messages %}
                   <div class="alert alert-success">
                    <strong>{{ message }}</strong>
                  </div>
                 {% endfor %}
                  {% endif %}
                {% endwith %}
               </p>
  <div>
   <!-- File upload form -->
   <form method="post" action="/upload" enctype="multipart/form-data" class="form-inline">
    <div class="form-group">
                      <label>Choose Images: </label>
                      <input type="file" name="files[]" id="fileInput" class="form-control" multiple >
                 </div>
                 <input type="submit" name="submit" class="btn btn-success" value="UPLOAD"/>
             </form>
  </div>
  </div>
 </div>
</div>
<script>
$(document).ready(function(){
 // File type validation
    $("#fileInput").change(function(){
        var fileLength = this.files.length;
        var match= ["image/jpeg","image/png","image/jpg","image/gif"];
        var i;
        for(i = 0; i < fileLength; i++){ 
            var file = this.files[i];
            var imagefile = file.type;
            if(!((imagefile==match[0]) || (imagefile==match[1]) || (imagefile==match[2]) || (imagefile==match[3]))){
                alert('Please select a valid image file (JPEG/JPG/PNG/GIF).');
                $("#fileInput").val('');
                return false;
            }
        }
    });
});
</script>
</body>
</html>

Wednesday, July 7, 2021

Add Edit Delete Datatable Row Using Jquery Ajax Python Flask and PostgreSQL database

Add Edit Delete Datatable Row Using Jquery Ajax Python Flask and PostgreSQL database

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 employee (

id serial PRIMARY KEY,

name VARCHAR ( 100 ) NOT NULL,

position VARCHAR ( 100 ) NOT NULL,

office VARCHAR ( 100 ) NOT NULL

);

INSERT INTO

employee(name, position, office)

VALUES

('Tiger Wood', 'Accountant', 'Tokyo'),

('Mark Oto Ednalan', 'Chief Executive Officer (CEO)', 'London'),

('Jacob thompson', 'Junior Technical Author', 'San Francisco'),

('cylde Ednalan', 'Software Engineer', 'Olongapo');

app.py

#app.py
from flask import Flask, render_template, redirect, request, flash, 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('/')
def index():
    cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
    cur.execute("SELECT * FROM employee ORDER BY id")
    employee = cur.fetchall()
    return render_template('index.html', employee=employee)
  
@app.route("/ajax_add",methods=["POST","GET"])
def ajax_add():
    cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
    if request.method == 'POST':
        txtname = request.form['txtname']
        txtposition = request.form['txtposition']
        txtoffice = request.form['txtoffice']
        print(txtname)
        if txtname == '':
            msg = 'Please Input name' 
        elif txtposition == '':
           msg = 'Please Input Position' 
        elif txtoffice == '':
           msg = 'Please Input Office' 
        else:        
            cur.execute("INSERT INTO employee (name,position,office) VALUES (%s,%s,%s)",[txtname,txtposition,txtoffice])
            conn.commit()       
            cur.close()
            msg = 'New record created successfully'  
    return jsonify(msg)
  
@app.route("/ajax_update",methods=["POST","GET"])
def ajax_update():
    cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
    if request.method == 'POST':
        string = request.form['string']
        txtname = request.form['txtname']
        txtposition = request.form['txtposition']
        txtoffice = request.form['txtoffice']
        print(string)
        cur.execute("UPDATE employee SET name = %s, position = %s, office = %s WHERE id = %s ", [txtname, txtposition, txtoffice, string])
        conn.commit()       
        cur.close()
        msg = 'Record successfully Updated'  
    return jsonify(msg)    
  
@app.route("/ajax_delete",methods=["POST","GET"])
def ajax_delete():
    cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
    if request.method == 'POST':
        getid = request.form['string']
        print(getid)
        cur.execute('DELETE FROM employee WHERE id = {0}'.format(getid))
        conn.commit()       
        cur.close()
        msg = 'Record deleted successfully'  
    return jsonify(msg) 
 
if __name__ == "__main__":
    app.run(debug=True)

templates/index.html

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

//templates/index.html
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>Add Edit Delete Datatable Row Using Jquery Ajax Python Flask and PostgreSQL database</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
<script type="text/javascript">
$(document).ready(function(){
    $('[data-toggle="tooltip"]').tooltip();
    var actions = $("table td:last-child").html();
    // Append table with add row form on add new button click
    $(".add-new").click(function(){
        $(this).attr("disabled", "disabled");
        var index = $("table tbody tr:last-child").index();
        var row = '<tr>' +
            '<td><input type="text" class="form-control" name="name" id="txtname"></td>' +
            '<td><input type="text" class="form-control" name="position" id="txtposition"></td>' +
            '<td><input type="text" class="form-control" name="office" id="txtoffice"></td>' +
        '<td>' + actions + '</td>' +
        '</tr>';
        $("table").append(row);  
        $("table tbody tr").eq(index + 1).find(".add, .edit, .delete").toggle();
        $('[data-toggle="tooltip"]').tooltip();
  
    });
    
    // Add row on add button click
    $(document).on("click", ".add", function(){
        var empty = false;
        var input = $(this).parents("tr").find('input[type="text"]');
        input.each(function(){
            if(!$(this).val()){
                $(this).addClass("error");
                empty = true;
            } else{
                $(this).removeClass("error");
            }
        });
        var txtname = $("#txtname").val();
        var txtposition = $("#txtposition").val();
        var txtoffice = $("#txtoffice").val();
        $.post("/ajax_add", { txtname: txtname, txtposition: txtposition, txtoffice: txtoffice}, function(data) {
            $("#displaymessage").html(data);
            $("#displaymessage").show();
        });
        $(this).parents("tr").find(".error").first().focus();
        if(!empty){
            input.each(function(){
                $(this).parent("td").html($(this).val());
            });   
            $(this).parents("tr").find(".add, .edit, .delete").toggle();
            $(".add-new").removeAttr("disabled");
        } 
    });
    // Delete row on delete button click
    $(document).on("click", ".delete", function(){
        $(this).parents("tr").remove();
        $(".add-new").removeAttr("disabled");
        var id = $(this).attr("id");
        var string = id;
        $.post("/ajax_delete", { string: string}, function(data) {
            $("#displaymessage").html(data);
            $("#displaymessage").show();
        });
    });
    // update rec row on edit button click
    $(document).on("click", ".update", function(){
        var id = $(this).attr("id");
        var string = id;
        var txtname = $("#txtname").val();
        var txtposition = $("#txtposition").val();
        var txtoffice = $("#txtoffice").val();
        $.post("/ajax_update", { string: string,txtname: txtname, txtposition: txtposition, txtoffice: txtoffice}, function(data) {
            $("#displaymessage").html(data);
            $("#displaymessage").show();
        });
          
          
    });
    // Edit row on edit button click
    $(document).on("click", ".edit", function(){  
        $(this).parents("tr").find("td:not(:last-child)").each(function(i){
            if (i=='0'){
                var idname = 'txtname';
            }else if (i=='1'){
                var idname = 'txtposition';
            }else if (i=='2'){
                var idname = 'txtoffice';
            }else{} 
            $(this).html('<input type="text" name="updaterec" id="' + idname + '" class="form-control" value="' + $(this).text() + '">');
        });  
        $(this).parents("tr").find(".add, .edit").toggle();
        $(".add-new").attr("disabled", "disabled");
        $(this).parents("tr").find(".add").removeClass("add").addClass("update"); 
    });
});
</script> 
</head>
<body>
    <div class="container"><p><h1 align="center">Add Edit Delete Datatable Row Using Jquery Ajax Python Flask and PostgreSQL database</h1></p>
        <div class="table-wrapper">
            <div class="table-title">
                <div class="row">
                    <div class="col-sm-8"><h2>Employee <b>Details</b></h2></div>
                    <div class="col-sm-4">
                        <button type="button" class="btn btn-info add-new"><i class="fa fa-plus"></i> Add New</button>
                    </div>
                    <div class='btn btn-info' id="displaymessage" style="display:none;width:100%;margin-top:10px;"></div>
                </div>
            </div>
   <table class="table table-bordered">
                <thead>
                    <tr>
                        <th>Name</th>
                        <th>Position</th>
                        <th>Office</th>
                        <th>Actions</th>
                    </tr>
                </thead>
                <tbody>
                    {% for row in employee %}    
                    <tr>
                        <td>{{row.name}}</td>
                        <td>{{row.position}}</td>
                        <td>{{row.office}}</td>
                        <td>
                            <a class="add" title="Add" data-toggle="tooltip" id="{{row.id}}"><i class="fa fa-user-plus"></i></a>
                            <a class="edit" title="Edit" data-toggle="tooltip" id="{{row.id}}"><i class="fa fa-pencil"></i></a>
                            <a class="delete" title="Delete" data-toggle="tooltip" id="{{row.id}}"><i class="fa fa-trash-o"></i></a>
                        </td>
                    </tr>   
                    {% endfor %}    
                </tbody>
            </table>
        </div>
    </div>     
<style type="text/css">
    body {
        color: #404E67;
        background: #F5F7FA;
  font-family: 'Open Sans', sans-serif;
 }
 .table-wrapper {
  width: 700px;
  margin: 30px auto;
        background: #fff;
        padding: 20px; 
        box-shadow: 0 1px 1px rgba(0,0,0,.05);
    }
    .table-title {
        padding-bottom: 10px;
        margin: 0 0 10px;
    }
    .table-title h2 {
        margin: 6px 0 0;
        font-size: 22px;
    }
    .table-title .add-new {
        float: right;
  height: 30px;
  font-weight: bold;
  font-size: 12px;
  text-shadow: none;
  min-width: 100px;
  border-radius: 50px;
  line-height: 13px;
    }
 .table-title .add-new i {
  margin-right: 4px;
 }
    table.table {
        table-layout: fixed;
    }
    table.table tr th, table.table tr td {
        border-color: #e9e9e9;
    }
    table.table th i {
        font-size: 13px;
        margin: 0 5px;
        cursor: pointer;
    }
    table.table th:last-child {
        width: 100px;
    }
    table.table td a {
  cursor: pointer;
        display: inline-block;
        margin: 0 5px;
  min-width: 24px;
    }   
 table.table td a.add {
        color: #27C46B;
    }
    table.table td a.edit {
        color: #FFC107;
    }
    table.table td a.delete {
        color: #E34724;
    }
    table.table td i {
        font-size: 19px;
    }
 table.table td a.add i {
        font-size: 24px;
     margin-right: -1px;
        position: relative;
        top: 3px;
    }    
    table.table .form-control {
        height: 32px;
        line-height: 32px;
        box-shadow: none;
        border-radius: 2px;
    }
 table.table .form-control.error {
  border-color: #f50000;
 }
 table.table td .add {
  display: none;
 }
</style>    
</body>
</html>

Search Box SQL LIKE operator using Jquery Ajax Python Flask and PostgreSQL database

Search Box SQL LIKE operator using Jquery Ajax Python Flask and PostgreSQL database

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 employee (

id serial PRIMARY KEY,

name VARCHAR ( 100 ) NOT NULL,

position VARCHAR ( 100 ) NOT NULL,

office VARCHAR ( 100 ) NOT NULL,

age INT NOT NULL,

salary INT NOT NULL,

photo VARCHAR ( 150 ) NOT NULL

);

INSERT INTO

employee(name, position, office, age, salary, photo)

VALUES

('Tiger Wood', 'Accountant', 'Tokyo', 36, 5689, '01.jpg'),

('Mark Oto Ednalan', 'Chief Executive Officer (CEO)', 'London', 56, 5648, '02.jpg'),

('Jacob thompson', 'Junior Technical Author', 'San Francisco', 23, 5689, '03.jpg'),

('cylde Ednalan', 'Software Engineer', 'Olongapo', 23, 54654, '04.jpg'),

('Rhona Davidson', 'Software Engineer', 'San Francisco', 26, 5465, '05.jpg'),

('Quinn Flynn', 'Integration Specialist', 'New York', 53, 56465, '06.jpg'),

('Tiger Nixon', 'Software Engineer', 'London', 45, 456, '07.jpg'),

('Airi Satou', 'Pre-Sales Support', 'New York', 25, 4568, '08.jpg'),

('Angelica Ramos', 'Sales Assistant', 'New York', 45, 456, '09.jpg'),

('Ashton updated', 'Senior Javascript Developer', 'Olongapo', 45, 54565, '01.jpg'),

('Bradley Greer', 'Regional Director', 'San Francisco', 27, 5485, '02.jpg'),

('Brenden Wagner', 'Javascript Developer', 'San Francisco', 38, 65468, '03.jpg'),

('Brielle Williamson', 'Personnel Lead', 'Olongapo', 56, 354685, '04.jpg'),

('Bruno Nash', 'Customer Support', 'New York', 36, 65465, '05.jpg'),

('cairocoders', 'Sales Assistant', 'Sydney', 45, 56465, '06.jpg'),

('Zorita Serrano', 'Support Engineer', 'San Francisco', 38, 6548, '07.jpg'),

('Zenaida Frank', 'Chief Operating Officer (COO)', 'San Francisco', 39, 545, '08.jpg'),

('Sakura Yamamoto', 'Support Engineer', 'Tokyo', 48, 5468, '05.jpg'),

('Serge Baldwin', 'Data Coordinator', 'Singapore', 85, 5646, '05.jpg'),

('Shad Decker', 'Regional Director', 'Tokyo', 45, 4545, '05.jpg');

app.py

#app.py
from flask import Flask, render_template, request, 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('/')
def index():
    return render_template('index.html')
  
@app.route("/searchdata",methods=["POST","GET"])
def searchdata():
    if request.method == 'POST':
        search_word = request.form['search_word']
        print(search_word)
        cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
        cur.execute('SELECT * FROM employee WHERE name LIKE %(name)s', { 'name': '%{}%'.format(search_word)})
        employee = cur.fetchall()
    return jsonify({'data': render_template('response.html', employee=employee)})
  
if __name__ == "__main__":
    app.run(debug=True)

templates/index.html

//templates/index.html
<!DOCTYPE html>
<html>
<head> 
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Search Box SQL LIKE operator using Jquery Ajax Python Flask and PostgreSQL database</title>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>
<script type="text/javascript">
$(function() {
  $(".search_button").click(function() {
      var search_word = $("#search_box").val();
      var dataString = 'search_word='+ search_word;
      if(search_word==''){
      }else{
        $.ajax({
          type: "POST",
          url: "/searchdata",
          data: dataString,
          cache: false,
          beforeSend: function(html) {
              document.getElementById("insert_search").innerHTML = ''; 
              $("#flash").show();
              $("#searchword").show();
              $(".searchword").html(search_word);
              $("#flash").html('<img src="/static/img/loader.gif" align="absmiddle"> Loading Results...');
            },
          success: function(html){
              $("#insert_search").show();
              $("#insert_search").append(html.data);
              $("#flash").hide();
          }
        });
      }
    return false;
  });
});
</script>
</head>
<body>
<div align="center">
  <div style="width:700px">
  <div style="margin-top:20px; text-align:left">
    <p align="center"><h1>Search Box SQL LIKE operator using Jquery Ajax Python Flask and PostgreSQL database </h1></p>
    <form method="get" action="">
    <input type="text" name="search" id="search_box" class='search_box'/>
    <input type="submit" value="Search" class="search_button" /><br />
    <span style="color:#666666; font-size:14px; font-family:Arial, Helvetica, sans-serif;"><b>Ex :</b> Cairocoders</span>
    </form>
  </div>   
  <div>
    <div id="searchword">Search results for <span class="searchword"></span></div>
    <div id="flash"></div>
    <ol id="insert_search" class="update"></ol>
  </div>
  </div>
</div>
<style>
body{
font-family:Arial, Helvetica, sans-serif;
}
a
{
color:#DF3D82;
text-decoration:none
}
a:hover
{
color:#DF3D82;
text-decoration:underline;
}
#search_box{
 padding:3px; border:solid 1px #666666; width:400px; height:45px; font-size:18px;-moz-border-radius: 6px;-webkit-border-radius: 6px;
}
.search_button{
 height:50px;border:#fe6f41 solid 1px; padding-left:9px;padding-right:9px;padding-top:9px;padding-bottom:9px; color:#000; font-weight:bold; font-size:16px;-moz-border-radius: 6px;-webkit-border-radius: 6px;
}
ol.update{
 list-style:none;font-size:1.1em; margin-top:20px;padding-left:0; 
}
#flash{
 margin-top:20px;
 text-align:left;
}
#searchword{
 text-align:left; margin-top:20px; display:none;
 font-family:Arial, Helvetica, sans-serif;
 font-size:16px;
 color:#000;
}
.searchword{
 font-weight:bold;
 color:#fe6f41;
}
ol.update li{ border-bottom:#dedede dashed 1px; text-align:left;padding-top:10px;padding-bottom:10px;}
ol.update li:first-child{ border-top:#dedede dashed 1px; text-align:left}
</style>
</body>
</html>

templates/response.html

//templates/response.html
{% for row in employee %}  
  <li>{{row.name}} <br/><span style='font-size:12px;'>{{row.position}}</span></li>
{% endfor %}

Tuesday, July 6, 2021

Live Data Search using Jquery Ajax Python Flask and PostgreSQL Database

Live Data Search using Jquery Ajax Python Flask and PostgreSQL Database