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
Crate database table
CREATE TABLE students (
id serial PRIMARY KEY,
fname VARCHAR ( 40 ) NOT NULL,
lname VARCHAR ( 40 ) NOT NULL,
email VARCHAR ( 40 ) NOT NULL
);
SELECT * FROM students
INSERT INTO students (fname, lname, email)
VALUES('cairocoders','ednalan', 'cairocoders@gmail.com');
Multiple insert
INSERT INTO
students(id,fname,lname,email)
VALUES
('Quinn','Flynn'', 'Flynn'@gmail.com'),
('Tiger','nizon', 'nizon@gmail.com'),
('Airi','sato', 'sato@gmail.com');
#app.py
from flask import Flask, render_template
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True
#password:admin
app.config['SQLALCHEMY_DATABASE_URI']='postgresql://postgres:admin@localhost/sampledb'
db=SQLAlchemy(app)
class Student(db.Model):
__tablename__='students'
id=db.Column(db.Integer,primary_key=True)
fname=db.Column(db.String(40))
lname=db.Column(db.String(40))
email=db.Column(db.String(40))
def __init__(self,fname,lname,email):
self.fname=fname
self.lname=lname
self.email=email
@app.route('/student/')
def student(page_num):
student = Student.query.paginate(per_page=5, page=page_num, error_out=True)
return render_template('index.html', student=student)
if __name__ == '__main__':
app.run(debug=True)
templates/index.html
//templates/index.html
<html>
<head>
<title>Python Flask SQLAlchemy Pagination</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">
<div class="row" style="padding: 20px;">
<p><h2>Pagination using Python Flask PostgreSQL and SQLAlchemy</h2> </p>
<table id="example" class="table table-striped table-bordered" style="width:100%">
<thead>
<tr>
<th><input type="checkbox" onclick="checkAll(this)"></th>
<th>First Name</th>
<th>Last Name</th>
<th>Email</th>
</tr>
</thead>
<tbody>
{% for rs in student.items %}
<tr>
<td><input type="checkbox" name=""></td>
<td>{{ rs.fname}}</td>
<td>{{ rs.lname}}</td>
<td>{{ rs.email}}</td>
{% endfor %}
</tr>
</tbody>
<tfoot>
<tr>
<th></th>
<th>First Name</th>
<th>Last Name</th>
<th>Email</th>
</tr>
</tfoot>
</table>
</div>
<ul class="pagination">
{% if student.has_prev %}
<li class="page-item"><a class="page-link" href="{{ url_for('student', page_num=student.prev_num) }}">Previous</a></li>
{% else %}
<li class="page-item disabled"><span class="page-link">Previous</span>
{% endif %}
</li>
{% for page in student.iter_pages(left_edge=3, right_edge=3) %}
{% if page %}
<li class="page-item"><a class="page-link" href="{{ url_for('student', page_num=page) }}">{{ page }}</a></li>
{% else %}
<li class="page-item disabled" id="example_ellipsis"><a href="#" class="page-link">…</a></li>
{% endif %}
{% endfor %}
{% if student.has_next %}
<li class="page-item"><a class="page-link" href="{{ url_for('student', page_num=student.next_num) }}">Next</a></li>
{% else %}
<li class="page-item disabled"><span class="page-link">Next</span>
{% endif %}
</ul>
</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>
