Python CRUD Operation Project
Project : in this project you will learn complete manual crud operation on browser or localhost.
In this project you will learn about insert single/many reecords, fetch single/all records, update, delete by manualy on localhost . Python script will execute on browser or localhost.
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("")
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
Open on localhost :http://localhost/crudapp/dbcon.py
Insert single data in database
DB Connection File: importing from dbcon.py
File: insert.py
#!C:\Programs\Python\Python37\python.exe
from dbcon import conn, dbcon
#How to inseret data in batabase using python in command line
try:
sql ="INSERT INTO users(username,email,mobile,city, country) VALUES('Kaman Kumar','kaman123@demo.com',444444444,'Lunknow','India')"
run = dbcon.execute(sql) #insert new data in database
if dbcon:
print(f"<br><br><center><div class='alert alert-success w-50'> {run} record inserted successfully in database</div></center>")
conn.commit()
except:
print("<br><br><center><div class='alert alert-danger w-50'>No data has been inserted ???</div></center>")
conn.rollback()
dbcon.close()
Open on localhost :http://localhost/crudapp/insert.py
Insert many records in database
DB Connection File: importing from dbcon.py
File: insertall.py
#!C:\Programs\Python\Python37\python.exe
from dbcon import conn, dbcon
#How to inseret many data in on time in batabase using python in command line
try:
sql ="INSERT INTO users(username, email, mobile, city, country) VALUES(%s, %s, %s, %s, %s)"
data=[
("Haman Kumar","haman@demo.com",111111111,"Chandigarh","India"),
("Eaman Kumar","eaman@demo.com",222222222,"Delhi","India"),
("Laman Kumar","laman@demo.com",333333333,"Lucknow","India"),
("jaman Kumar","jaman@demo.com",444444444,"Hyderabad","India")
]
run = dbcon.executemany(sql,data) #insert multiples records in database
if dbcon:
print(f"<br><br><center><div class='alert alert-success w-50'>The {dbcon.rowcount} records have been inserted successfully in database</div></center>")
conn.commit()
except:
print("<br><br><center><div class='alert alert-danger w-50'>No data has been inserted ???</div></center>")
conn.rollback()
dbcon.close()
Open on localhost :http://localhost/crudapp/insertall.py
Fetch single record
DB Connection File: importing from dbcon.py
File: fetch.py
#!C:\Programs\Python\Python37\python.exe
from dbcon import conn, dbcon
#How to fetch first row user's data from database using python in command line
try:
dbcon.execute("SELECT * FROM users WHERE id=3")
data = dbcon.fetchone() #fetch first row user's data from database
print(f"<br><br><center><div class='alert alert-success w-50'>Found {dbcon.rowcount} record in database</div></center>")
print(f"<br><br><center><div class='w-50'>")
print(data, end='<br>')
print("</div></center>")
conn.commit()
except:
print("<br><br><center><div class='alert alert-danger w-50'>No data found in database ???</div></center>")
conn.rollback()
dbcon.close()
Open on localhost :http://localhost/crudapp/fetch.py
Fetch all records
DB Connection File: importing from dbcon.py
File: fetchall.py
#!C:\Programs\Python\Python37\python.exe
from dbcon import conn, dbcon
#How to fetch all the data from database using python in command line
try:
dbcon.execute("SELECT * FROM users")
rows = dbcon.fetchall()
print(f"<br><br><center><div class='alert alert-success w-50'>Total number of records {dbcon.rowcount} found in database</div></center>")
print(f"<br><br><center><div class='w-50'>")
for data in rows:
print(data, end='<br>')
print("</div></center>")
conn.commit()
except:
print("<br><br><center><div class='alert alert-danger w-50'>No found in database ???</div></center>")
conn.rollback()
dbcon.close()
Open on localhost :http://localhost/crudapp/fetchall.py
Update record in database
DB Connection File: importing from dbcon.py
File: update.py
#!C:\Programs\Python\Python37\python.exe
from dbcon import conn, dbcon
try:
uid= 4
sql ="UPDATE users SET username='Naman Kumar', email='naman@demo.com', mobile=0000000001, city='Pune',country='India' WHERE id=%s"
run = dbcon.execute(sql,uid)
if run==True:
print(f"<br><br><center><div class='alert alert-success w-50'>The record id {uid} has been updated in database</div></center>")
conn.commit()
except:
print("<br><br><center><div class='alert alert-danger w-50'>No found updated database ???</div></center>")
conn.rollback()
dbcon.close()
Open on localhost :http://localhost/crudapp/update.py
Delete record from database
DB Connection File: importing from dbcon.py
File: delete.py
#!C:\Programs\Python\Python37\python.exe
from dbcon import conn, dbcon
#How to delete user based on user id in database using python in command line
try:
uid = 2
sql ="DELETE FROM users WHERE id=%s"
run = dbcon.execute(sql,uid) #user will be deleted from database who's id=4 in database table
if run==True:
print(f"<br><br><center><div class='alert alert-success w-50'>The record id {uid} has been deleted from database</div></center>")
conn.commit()
except:
conn.rollback()
dbcon.close()
Open on localhost :http://localhost/crudapp/delete.py