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'))
#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('/', 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)
//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>
