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 (id, fname, lname, email)
VALUES('1','Mark','Oto', 'Oto@gmail.com'),
Insert multiple records
INSERT INTO
students(id,fname,lname,email)
VALUES
('2','Quinn','Flynn'', 'Flynn'@gmail.com'),
('3','Tiger','nizon', 'nizon@gmail.com'),
('4','Airi','sato', 'sato@gmail.com');
How to Alter Sequence in PostgreSQL
To alter the sequence so that IDs start a different number, you can't just do an update, you have to use the alter sequence command.
alter sequence students_id_seq restart with 9;
#app.py
from flask import Flask, render_template, request, redirect, url_for, flash
import psycopg2 #pip install psycopg2
import psycopg2.extras
app = Flask(__name__)
app.secret_key = "cairocoders-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)
s = "SELECT * FROM students"
cur.execute(s) # Execute the SQL
list_users = cur.fetchall()
return render_template('index.html', list_users = list_users)
@app.route('/add_student', methods=['POST'])
def add_student():
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
if request.method == 'POST':
fname = request.form['fname']
lname = request.form['lname']
email = request.form['email']
cur.execute("INSERT INTO students (fname, lname, email) VALUES (%s,%s,%s)", (fname, lname, email))
conn.commit()
flash('Student Added successfully')
return redirect(url_for('Index'))
@app.route('/edit/', methods = ['POST', 'GET'])
def get_employee(id):
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
cur.execute('SELECT * FROM students WHERE id = %s', (id))
data = cur.fetchall()
cur.close()
print(data[0])
return render_template('edit.html', student = data[0])
@app.route('/update/', methods=['POST'])
def update_student(id):
if request.method == 'POST':
fname = request.form['fname']
lname = request.form['lname']
email = request.form['email']
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
cur.execute("""
UPDATE students
SET fname = %s,
lname = %s,
email = %s
WHERE id = %s
""", (fname, lname, email, id))
flash('Student Updated Successfully')
conn.commit()
return redirect(url_for('Index'))
@app.route('/delete/', methods = ['POST','GET'])
def delete_student(id):
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
cur.execute('DELETE FROM students WHERE id = {0}'.format(id))
conn.commit()
flash('Student Removed Successfully')
return redirect(url_for('Index'))
if __name__ == "__main__":
app.run(debug=True)
templates/index.html
//templates/index.html
{% extends "layout.html" %}
{% block body %}
<div class="row"><h3>Students</h3></div>
<div class="row">
<div class="col-md-4">
{% with messages = get_flashed_messages() %}
{% if messages %}
{% for message in messages %}
<div class="alert alert-success alert-dismissible fade show" role="alert">
{{ message }}
<button type="button" class="close" data-dismiss="alert" aria-label="Close">
<span aria-hidden="true">×</span>
</button>
</div>
{% endfor %}
{% endif %}
{% endwith %}
<div class="card card-body">
<form action="{{url_for('add_student')}}" method="POST">
<div class="form-group">
<input type="text" class="form-control" name="fname" placeholder="First Name">
</div>
<div class="form-group">
<input type="text" class="form-control" name="lname" placeholder="Last Name">
</div>
<div class="form-group">
<input type="email" class="form-control" name="email" placeholder="Email">
</div>
<button class="btn btn-primary btn-block">
Save
</button>
</form>
</div>
</div>
<div class="col-md-8">
<table id="example" class="table table-striped table-bordered" style="width:100%">
<thead>
<tr>
<td>ID</td>
<td>First Name</td>
<td>Last Name</td>
<td>Email</td>
<td>Action</td>
</tr>
</thead>
<tbody>
{% for row in list_users %}
<tr>
<td>{{row[0]}}</td>
<td>{{row[1]}}</td>
<td>{{row[2]}}</td>
<td>{{row[3]}}</td>
<td width="130">
<a href="/edit/{{row[0]}}" class="btn btn-secondary btn-sm">edit</a>
<a href="/delete/{{row[0]}}" class="btn btn-danger btn-delete btn-sm">delete</a>
</td>
</tr>
{% endfor %}
</tbody>
</table>
</div>
</div>
</div>
{% endblock %}
templates/layout.html
//templates/layout.html
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Python Flask Student Create, read, update and delete (CRUD) using PostgreSQL psycopg2 and dataTables bootstrap</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>
<script src="https://cdn.datatables.net/1.10.16/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/1.10.16/js/dataTables.bootstrap4.min.js"></script>
</head>
<body>
<nav class="navbar navbar-dark bg-dark">
<a class="navbar-brand" href="/">Python Flask Student Create, read, update and delete (CRUD) using PostgreSQL psycopg2 and dataTables bootstrap</a>
</nav>
<div class="container pt-4">
{% block body %}
{% endblock %}
</div>
<script>
const btnDelete= document.querySelectorAll('.btn-delete');
if(btnDelete) {
const btnArray = Array.from(btnDelete);
btnArray.forEach((btn) => {
btn.addEventListener('click', (e) => {
if(!confirm('Are you sure you want to delete it?')){
e.preventDefault();
}
});
})
}
$(document).ready(function() {
$('#example').DataTable({
"aLengthMenu": [[3, 5, 10, 25, -1], [3, 5, 10, 25, "All"]],
"iDisplayLength": 3
}
);
} );
</script>
</body>
</html>
templates/edit.html
//templates/edit.html
{% extends "layout.html" %}
{% block body %}
<div class="row">
<div class="col-md-4 offset-md-4">
<div class="card card-body">
<form action="/update/{{student.id}}" method="POST">
<div class="form-group">
<input type="text" name="fname" value="{{student.fname}}" class="form-control">
</div>
<div class="form-group">
<input type="text" name="lname" value="{{student.lname}}" class="form-control">
</div>
<div class="form-group">
<input type="text" name="email" value="{{student.email}}" class="form-control">
</div>
<div class="form-group">
<button type="submit" class="btn btn-primary btn-block">
Update
</button>
</div>
</form>
</div>
</div>
</div>
{% endblock %}
