article

Monday, March 23, 2020

Python Flask SQLAlchemy Search LIKE, Equals, IN, AND and OR with Pagination and Datatable bootstap


Python Flask SQLAlchemy Pagination with Search LIKE, Equals, IN, AND and OR - Datatable

 LIKE: query.filter(User.name.like('%ednalan%'))
equals: query.filter(User.name == 'ednalan')
IN: query.filter(User.name.in_(['rai', 'kenshin', 'Ednalan']))
AND: query.filter(User.name == 'ednalan', User.fullname == 'clyde ednalan')
OR: from sqlalchemy import or_ 
filter(or_(User.name == 'ednalan', User.name == 'caite'))
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
#app.py
from flask import Flask, render_template, url_for, request
from flask_sqlalchemy import SQLAlchemy 
from sqlalchemy import or_
  
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///blog.db'
app.config['SECRET_KEY'] = 'cairocoders-ednalan'
  
db = SQLAlchemy(app)
  
class Employees(db.Model):
 id = db.Column(db.Integer, primary_key=True)
 fullname = db.Column(db.String(150))
 position = db.Column(db.String(150))
 office = db.Column(db.String(150))
 age = db.Column(db.Integer)
 startdate = db.Column(db.String(150))
 salary = db.Column(db.String(150))
  
@app.route('/', methods=['GET', 'POST'], defaults={"page": 1})
@app.route('/<int:page>', methods=['GET', 'POST'])
def index(page):
    page = page
    pages = 5
    #employees = Employees.query.filter().all()
    #employees = Employees.query.paginate(page,pages,error_out=False)
    employees = Employees.query.order_by(Employees.id.asc()).paginate(page,pages,error_out=False)  #desc()
    if request.method == 'POST' and 'tag' in request.form:
       tag = request.form["tag"]
       search = "%{}%".format(tag)
       #employees = Employees.query.filter(Employees.fullname.like(search)).paginate(per_page=pages, error_out=False) # LIKE: query.filter(User.name.like('%ednalan%'))
       #employees = Employees.query.filter(Employees.fullname == 'Tiger Nixon').paginate(per_page=pages, error_out=True) # equals: query.filter(User.name == 'ednalan')   
       #employees = Employees.query.filter(Employees.fullname.in_(['rai', 'kenshin', 'Ednalan'])).paginate(per_page=pages, error_out=True) # IN: query.filter(User.name.in_(['rai', 'kenshin', 'Ednalan'])) 
       #employees = Employees.query.filter(Employees.fullname == 'Tiger Nixon', Employees.position == 'System Architect').paginate(per_page=pages, error_out=True) # AND: query.filter(User.name == 'ednalan', User.fullname == 'clyde ednalan')   
       employees = Employees.query.filter(or_(Employees.fullname == 'Tiger Nixon', Employees.fullname == 'Ednalan')).paginate(per_page=pages, error_out=True) # OR: from sqlalchemy import or_  filter(or_(User.name == 'ednalan', User.name == 'caite'))
       return render_template('index.html', employees=employees, tag=tag)
    return render_template('index.html', employees=employees)
   
if __name__ == '__main__':
    app.run(debug=True)
</int:page>
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
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
//templates/index.html
<html>
<head>
<title>Python Flask SQLAlchemy Pagination with Search LIKE, Equals, IN, AND and OR - Datatable</title> 
<link href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css" rel="stylesheet" id="bootstrap-css">
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/js/bootstrap.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>  
</head>
<body>
<div class="container">
 <p><h2>Python Flask SQLAlchemy Pagination with Search LIKE, Equals, IN, AND and OR - Datatable</h2>  </p>
 <div class="row">
  <div class="col-8">
  <strong>Search For : {{ tag}}</strong>
  </div>
  <div class="col-4">
   <form action="" method="post" autocomplete="off">
   <div class="row">
    <div class="col-6">
    <input type="text" class="form-control" name="tag" id="tag"  placeholder="Enter keyword"/>
    </div>
    <div class="col-6" align="left">
    <input type="submit" value="Search" class="form-control btn btn-primary " name="">
    </div>
   </form>
   </div>
  </div>
 </div>
<div class="row">
<div class="col-12">
  <table id="example" class="table table-striped table-bordered" style="width:100%">
        <thead>
            <tr>
                <th><input type="checkbox" onclick="checkAll(this)"></th>
                <th>Name</th>
                <th>Position</th>
                <th>Office</th>
                <th>Age</th>
                <th>Start date</th>
                <th>Salary</th>
            </tr>
        </thead>
        <tbody>
  {% for employee in employees.items %}
  <tr>
                <td><input type="checkbox" name="">{{ employee.id}}</td>
                <td>{{ employee.fullname}}</td>
                <td>{{ employee.position}}</td>
                <td>{{ employee.office}}</td>
                <td>{{ employee.age}}</td>
                <td>{{ employee.startdate}}</td>
                <td>{{ employee.salary}}</td>
        </tr>
        {% endfor %} 
        </tbody>
        <tfoot>
            <tr>
                <th></th>
                <th>Name</th>
                <th>Position</th>
                <th>Office</th>
                <th>Age</th>
                <th>Start date</th>
                <th>Salary</th>
            </tr>
        </tfoot>
  </table>
   <nav aria-label="Page navigation example">
                <ul class="pagination">
                    {% if employees.has_prev %}
                      <li class="page-item"> <a class="page-link" href="{{ url_for('index', page=employees.prev_num) }}">Previous</a></li>
                    {% else %}
                      <li class="page-item"><a class="page-link btn disabled" href="#">Previous</a></li>
                    {% endif %}
      
     {% for page in employees.iter_pages(left_edge=3, right_edge=3) %}
     {% if page %}
      
      {% if page==employees.page %}
       <li class="page-item active"><a class="page-link" href="{{ url_for('index', page=page) }}">{{ page }}</a></li>
      {% else %}
       <li class="page-item"><a class="page-link" href="{{ url_for('index', page=page) }}">{{ page }}</a></li>
      {% endif %}
       
     {% else %}
      <li class="page-item disabled" id="example_ellipsis"><a href="#" class="page-link">…</a></li>
     {% endif %}
     {% endfor %}
 
                    {% if employees.has_next %}
                      <li class="page-item"> <a class="page-link" href="{{ url_for('index', page=employees.next_num) }}">Next</a></li>
                    {% else %}
                      <li class="page-item"><a class="page-link btn disabled" href="#">Next</a></li>
                    {% endif %}
 
                </ul>
    </nav>
 </div>
 </div>
 
</div>
<style>
table{
    width:100%;
}
#example_filter{
    float:right;
}
#example_paginate{
    float:right;
}
label {
    display: inline-flex;
    margin-bottom: .5rem;
    margin-top: .5rem;
     
}
.page-item.disabled .page-link {
    color: #6c757d;
    pointer-events: none;
    cursor: auto;
    background-color: #fff;
    border-color: #dee2e6;
}
</style>
<script>
function checkAll(bx) {
  var cbs = document.getElementsByTagName('input');
  for(var i=0; i < cbs.length; i++) {
    if(cbs[i].type == 'checkbox') {
      cbs[i].checked = bx.checked;
    }
  }
}
</script>
</body>
</html>
Posted by ednalan at 10:47 PM . Under .

Related Post