Python CRUD Operation Project without framework
Project: in this project you will learn complete dynamic crud operation on browser or localhost with html, css, bootstrap, js
In this project you will learn about insert reecords , fetch all records, update, delete on localhost
Python script will execute on browser or localhost
Features
Introduction
You must have basic knowledge in following fields
- You mush have basic knowledge of python
- You mush have basic knowledge of MySql Database
- Understanding of HMTL,CSS,Bootstrap,Javascript
Configuration in Apache in Xampp Server
Follow the instructions to run python on localhost
Before getting start with python on localhost please configure your apache, check following steps.
Go to your folder and open file. C:\xampp\apache\conf\httpd.conf (httpd.conf)
find line or search for : AddHandler cgi-script in this line just first give space then add .py
Example: AddHandler cgi-script .cgi .pl .asp .py
Then restart xampp
1) Important step: First add python script on the top of every page. #!C:\Programs\Python\Python37\python.exe (as it is, do not remove #tag)
Its should according to your python folder where you installed python.
2) Important step: After that add python print statement print("Content-Type: text/html")
3) Important step: After that add python print statement print("")
4) Important step: After that need to import cgi library import cgi
After these three python statement we can write python script or html, css, bootstap,js, or jquery but before these three script we can not write any script otherwise it will give server error.
Database connection with pymysql
In this section we will learn how to connect with Database using pymysql library
DB connection file: dbcon.py
#!C:\Programs\Python\Python37\python.exe
print("Content-Type: text/html")
print("")
#--------------------------------------------------------------
#How to create connection with database using pymysql library
import pymysql
print("<link rel='stylesheet' type='text/css' href='css/bootstrap.min.css'>")
try:
conn = pymysql.connect(host='localhost',user='root', password='', database='mydata')
dbcon=conn.cursor()
#print(f"<br><br><center><div class='alert alert-success w-50'>Database connected successfully........</div></center>")
except:
print("<br><br><center><div class='alert alert-danger w-50'>Database connection failed........???</div></center>")
# dbcon.close() #// This must be commented if we are importing this on another file
Database and table
CREATE TABLE `users` (
`id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
`username` varchar(100) NOT NULL,
`email` varchar(100) NOT NULL,
`mobile` varchar(100) NOT NULL,
`city` varchar(100) NOT NULL,
`country` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Open on localhost :http://localhost/crudapp/dbcon.py
Insert data using form in database
DB Connection File: importing from dbcon.py
File: insert.py
#!C:\Programs\Python\Python37\python.exe
print("Content-type: text/html")
print("")
import cgi
from dbcon import conn, dbcon
form = cgi.FieldStorage()
uname = form.getvalue("username")
email = form.getvalue("email")
mobile = form.getvalue("mobile")
city = form.getvalue("city")
country = form.getvalue("country")
# ============HTML FORM============================
print ("""
<!DOCTYPE html>
<head>
<title>Python Project by Saten Chauhan</title>
<script src="js/jquery.min.js"></script>
<script src="js/bootstrap.min.js"></script>
<link rel='stylesheet' type='text/css' href='css/bootstrap.min.css'>
<link rel='stylesheet' href='https://use.fontawesome.com/releases/v5.0.13/css/all.css'>
</head>
<body>
<nav class="navbar navbar-expand-lg navbar-light bg-info">
<a class="navbar-brand text-white" href="#">CRUD OPERATION PYTHON</a>
<button class="navbar-toggler text-white" type="button" data-toggle="collapse" data-target="#navbarNav" aria-controls="navbarNav" aria-expanded="false" aria-label="Toggle navigation">
<span class="navbar-toggler-icon"></span>
</button>
<div class="collapse navbar-collapse" id="navbarNav">
<ul class="navbar-nav ml-auto">
<li class="nav-item">
<a class="nav-link text-white" href="fomr.py">+ Add </a>
</li>
<li class="nav-item">
<a class="nav-link text-white" href="table.py">Users</a>
</li>
</ul>
</div>
</nav><br>
<div class="container border w-50" style="background:#f5f6fa;">
<form method="POST" action="form.py" class="p-2">
<h5 class="rounded bg-primary w-100 p-2 text-white text-center">HTML Form Submit in Python</h5>
<form action="addform.html" method="POST">
<div class="form-group">
<label for="username">Username:</label>
<input type="text" class="form-control" name="username" id="username" placeholder="Enter username">
</div>
<div class="form-group">
<label for="email">Email:</label>
<input type="text" class="form-control" name="email" id="email" placeholder="Enter email">
</div>
<div class="form-group">
<label f="mobile">Mobile:</label>
<input type="text" class="form-control" name="mobile" id="mobile" placeholder="Enter mobile">
</div>
<div class="form-group">
<label for="city">City:</label>
<input type="text" class="form-control" name="city" id="city" placeholder="Enter city">
</div>
<div class="form-group">
<label for="country">Country:</label>
<input type="text" class="form-control" name="country" id="country" placeholder="Enter country">
</div>
<div class="form-group">
<input type="submit" name="submit" id="submit" value="Submit" class="btn btn-success btn-sm">
<a href='table.py' class='btn btn-info btn-sm float-right'>Go to User Table</a>
</div>
</form>
""")
sql = "INSERT INTO users (username,email,mobile,city,country) VALUES(%s, %s, %s, %s, %s)"
run = dbcon.execute(sql,(uname,email,mobile,city,country))
if run==1:
print(f"<center><div class='alert alert-success'>{uname} has been submitted in database table successfully</div></center>")
else:
print(f"<center><div class='alert alert-danger'>Data has not been submitted in database</div></center>")
("""
</div>
</body>
</html>
""")
#commiting the connection then closing it.
conn.commit()
conn.close()
Open on localhost :http://localhost/crudapp/insert.py
Fetch all records from database
Fecthing all datas from database display on browser
DB Connection File: importing from dbcon.py
File: fetch.py
#!C:\Programs\Python\Python37\python.exe
print("Content-type: text/html")
print("")
import cgi
from dbcon import conn, dbcon
#Fetch all records-------------------
sql = "SELECT * FROM users"
dbcon.execute(sql)
result = dbcon.fetchall()
#Delete record part section------------------------------
form = cgi.FieldStorage()
del_id = form.getvalue('del_id') #id coming from http://localhost/pyhtml/edit.py?del_id=23
del_sql ="DELETE FROM users WHERE id='%s'" % del_id
delete =dbcon.execute(del_sql)
#Delete record part----------------section closed
# ==============HTML FORM=======================
print("""
<!DOCTYPE html>
<head>
<title>Python Project by Saten Chauhan</title>
<script src="js/jquery.min.js"></script>
<script src="js/bootstrap.min.js"></script>
<link rel='stylesheet' type='text/css' href='css/bootstrap.min.css'>
<link rel='stylesheet' href='https://use.fontawesome.com/releases/v5.0.13/css/all.css'>
</head>
<body>
<nav class="navbar navbar-expand-lg navbar-light bg-info">
<a class="navbar-brand text-white" href="#">CRUD OPERATION PYTHON</a>
<button class="navbar-toggler text-white" type="button" data-toggle="collapse" data-target="#navbarNav" aria-controls="navbarNav" aria-expanded="false" aria-label="Toggle navigation">
<span class="navbar-toggler-icon"></span>
</button>
<div class="collapse navbar-collapse" id="navbarNav">
<ul class="navbar-nav ml-auto">
<li class="nav-item">
<a class="nav-link text-white" href="form.py">+ Add </a>
</li>
<li class="nav-item">
<a class="nav-link text-white" href="table.py">Users</a>
</li>
</ul>
</div>
</nav><br><br>
<div class='container'>
<a href='index.html' class='btn btn-primary float-left'>Go to Home</a>
<a href='form.py' class='btn btn-success float-right'>+ Add User</a>
<h3 class="bg-primary w-100 rounded text-center text-white pb-2">Users List</h3>
<table class="table table- table-bordered">
<thead>
<tr>
<th>Sr No.</th>
<th>Username</th>
<th>Email</th>
<th>Mobile</th>
<th>City</th>
<th>Country</th>
<th colspan="2" width="16%" class="text-center">Action</th>
</tr>
</thead>
<tbody id="products-table">
""")
for row in result:
print(f"""<tr>
<td>{row[0]}</td>
<td>{row[1].title()}</td>
<td>{row[2]}</td>
<td align='center'>{row[3]}</td>
<td>{row[4].title()}</td>
<td align='center'>{row[5].title()}</td>
<td align='center'>
<a href='edit.py?edit_id={row[0]}' class='btn btn-success btn-sm'>Edit</a>
</td>
<td align="center">
<a href='table.py?del_id={row[0]}' class='btn btn-danger btn-sm'>Delete</a>
</td>
</tr>""")
("""
</tbody>
</table>
</div>
""")
try:
if delete==1:
print(f'<center><div class="alert alert-success alert-dismissible fade show" role="alert">The user {del_id} has been deleted from database table successfully<button type="button" class="close" data-dismiss="alert" aria-label="Close"><span aria-hidden="true">×</span></button></div></center>')
except:
print(f'<center><div class="alert alert-danger">The user has not been deleted from database table successfully</div></center>')
("""
</body>
</html>
""")
#commiting the connection then closing it.
conn.commit()
conn.close()
Open on localhost :http://localhost/crudapp/fetchall.py
Update record using form in database
DB Connection File: importing from dbcon.py
File: update.py
#!C:\Programs\Python\Python37\python.exe
print("Content-type: text/html")
print("")
import cgi
from dbcon import conn, dbcon
form = cgi.FieldStorage()
url_id = form.getvalue('edit_id') #id coming from http://localhost/pyhtml/edit.py?edit_id=23
#============all theses valuse are comming from updateform================
uid = form.getvalue("uid")
uname = form.getvalue("edit-username")
email = form.getvalue("edit-email")
mobile = form.getvalue("edit-mobile")
city = form.getvalue("edit-city")
country = form.getvalue("edit-country")
update_sql="UPDATE users SET username=%s, email=%s, mobile=%s, city=%s, country=%s WHERE id=%s"
data = (uname, email, mobile, city, country, uid)
run = dbcon.execute(update_sql, data)
#To fetch all details of user who id=url_id
sql = "SELECT * FROM users WHERE id='%s'" % url_id
dbcon.execute(sql)
result = dbcon.fetchone()
edit_id = result[0]
edit_name = result[1]
edit_email = result[2]
edit_mobile = result[3]
edit_city = result[4]
edit_country= result[5]
print (f"""
<!DOCTYPE html>
<head>
<title>Python Project</title>
<script src="js/jquery.min.js"></script>
<script src="js/bootstrap.min.js"></script>
<link rel='stylesheet' type='text/css' href='css/bootstrap.min.css'>
<link rel='stylesheet' href='https://use.fontawesome.com/releases/v5.0.13/css/all.css'>
</head>
<body>
<nav class="navbar navbar-expand-lg navbar-light bg-info navbar-fixed-top">
<a class="navbar-brand text-white" href="#">CRUD OPERATION PYTHON</a>
<button class="navbar-toggler text-white" type="button" data-toggle="collapse" data-target="#navbarNav" aria-controls="navbarNav" aria-expanded="false" aria-label="Toggle navigation">
<span class="navbar-toggler-icon"></span>
</button>
<div class="collapse navbar-collapse" id="navbarNav">
<ul class="navbar-nav ml-auto">
<li class="nav-item">
<a class="nav-link text-white" href="form.py">+ Add</a>
</li>
<li class="nav-item">
<a class="nav-link text-white" href="table.py">Users</a>
</li>
</ul>
</div>
</nav><br>
<div class="container border w-50" style="background:#f5f6fa;">
<form method="POST" action="edit.py?edit_id={edit_id}" class="p-2">
<h5 class="rounded bg-primary w-100 p-2 text-white text-center">Update Form in Python</h5>
<input type="hidden" class="form-control" name="uid" value="{edit_id}">
<div class="form-group">
<label for="username">Username:</label>
<input type="text" class="form-control" value="{edit_name}" name="edit-username" id="username" placeholder="Enter username">
</div>
<div class="form-group">
<label for="email">Email:</label>
<input type="text" class="form-control" value="{edit_email}" name="edit-email" id="email" placeholder="Enter email">
</div>
<div class="form-group">
<label f="mobile">Mobile:</label>
<input type="text" class="form-control" value="{edit_mobile}" name="edit-mobile" id="mobile" placeholder="Enter mobile">
</div>
<div class="form-group">
<label for="city">City:</label>
<input type="text" class="form-control" value="{edit_city}" name="edit-city" id="city" placeholder="Enter city">
</div>
<div class="form-group">
<label for="country">Country:</label>
<input type="text" class="form-control" value="{edit_country}" name="edit-country" id="country" placeholder="Enter country">
</div>
<div class="form-group">
<input type="submit" name="submit" id="update" value="Update" class="btn btn-success btn-sm">
<a href='table.py' class='btn btn-info btn-sm float-right'>Go to User Table</a>
</div>
</form>
""")
if run==1:
print(f"<center><div class='alert alert-success'>{uname} has been updated in database table successfully</div></center>")
else:
print(f" ")
("""
</div>
</body>
</html>
""")
#commiting the connection then closing it.
conn.commit()
conn.close()
Open on localhost :http://localhost/crudapp/delete.py
Delete record
Delete record will be on on same page (fetch.py) we can create new page but I am doing delete operation same page
DB Connection File: importing from dbcon.py
File: fetch.py
#!C:\Programs\Python\Python37\python.exe
print("Content-type: text/html")
print("")
import cgi
from dbcon import conn, dbcon
#Fetch all records-------------------
sql = "SELECT * FROM users"
dbcon.execute(sql)
result = dbcon.fetchall()
#Delete record part section------------------------------
form = cgi.FieldStorage()
del_id = form.getvalue('del_id') #id coming from http://localhost/pyhtml/edit.py?del_id=23
#del_sql ="DELETE FROM users WHERE id='%s'" % del_id # we can write this query too
del_sql ="DELETE FROM users WHERE id='%s'" % del_id
delete =dbcon.execute(del_sql)
#Delete record part----------------//section closed
# ==============HTML FORM=======================
print("""
<!DOCTYPE html>
<head>
<title>Python Project by Saten Chauhan</title>
<script src="js/jquery.min.js"></script>
<script src="js/bootstrap.min.js"></script>
<link rel='stylesheet' type='text/css' href='css/bootstrap.min.css'>
<link rel='stylesheet' href='https://use.fontawesome.com/releases/v5.0.13/css/all.css'>
</head>
<body>
<nav class="navbar navbar-expand-lg navbar-light bg-info">
<a class="navbar-brand text-white" href="#">CRUD OPERATION PYTHON</a>
<button class="navbar-toggler text-white" type="button" data-toggle="collapse" data-target="#navbarNav" aria-controls="navbarNav" aria-expanded="false" aria-label="Toggle navigation">
<span class="navbar-toggler-icon"></span>
</button>
<div class="collapse navbar-collapse" id="navbarNav">
<ul class="navbar-nav ml-auto">
<li class="nav-item">
<a class="nav-link text-white" href="form.py">+ Add </a>
</li>
<li class="nav-item">
<a class="nav-link text-white" href="table.py">Users</a>
</li>
</ul>
</div>
</nav><br><br>
<div class='container'>
<a href='index.html' class='btn btn-primary float-left'>Go to Home</a>
<a href='form.py' class='btn btn-success float-right'>+ Add User</a>
<h3 class="bg-primary w-100 rounded text-center text-white pb-2">Users List</h3>
<table class="table table- table-bordered">
<thead>
<tr>
<th>Sr No.</th>
<th>Username</th>
<th>Email</th>
<th>Mobile</th>
<th>City</th>
<th>Country</th>
<th colspan="2" width="16%" class="text-center">Action</th>
</tr>
</thead>
<tbody id="products-table">
""")
for row in result:
print(f"""<tr>
<td>{row[0]}</td>
<td>{row[1].title()}</td>
<td>{row[2]}</td>
<td align='center'>{row[3]}</td>
<td>{row[4].title()}</td>
<td align='center'>{row[5].title()}</td>
<td align='center'>
<a href='edit.py?edit_id={row[0]}' class='btn btn-success btn-sm'>Edit</a>
</td>
<td align="center">
<a href='table.py?del_id={row[0]}' class='btn btn-danger btn-sm'>Delete</a>
</td>
</tr>""")
("""
</tbody>
</table>
</div>
""")
try:
if delete==1:
print(f'<center><div class="alert alert-success alert-dismissible fade show" role="alert">The user {del_id} has been deleted from database table successfully<button type="button" class="close" data-dismiss="alert" aria-label="Close"><span aria-hidden="true">×</span></button></div></center>')
except:
print(f'<center><div class="alert alert-danger">The user has not been deleted from database table successfully</div></center>')
("""
</body>
</html>
""")
#commiting the connection then closing it.
conn.commit()
conn.close()
Open on localhost :http://localhost/crudapp/fetchall.py