Experiment No.

12
Program to demonstrate CRUD (create, read, update and
delete) operations on database (SQLite/ MySQL) using
python
Date of Performance:28/3/25
Date of Submission:
 Experiment No. 12

Title: Program to demonstrate CRUD (create, read, update and delete) operations on database
(SQLite/ MySQL) using python

Aim: To study and implement CRUD (create, read, update and delete) operations on database
(SQLite/ MySQL) using python

Objective: To introduce database connectivity with python

Theory:

In general CRUD means performing Create, Retrieve, Update and Delete operations on a table in
a database. Let’s discuss what actually CRUD means,​

Create – create or add new entries in a table in the database. ​

Retrieve – read, retrieve, search, or view existing entries as a list(List View) or retrieve a
particular entry in detail (Detail View) ​
Update – update or edit existing entries in a table in the database ​
Delete – delete, deactivate, or remove existing entries in a table in the database
Code:

PythonProject.py

import tkinter as tk
from tkinter import ttk, messagebox
import re
from database import PasswordDatabase

class PasswordManager:
def __init__(self):
self.db = PasswordDatabase()
self.setup_gui()

def setup_gui(self):
self.root = tk.Tk()
self.root.title("Password Manager")
self.root.geometry("600x400")

# Create notebook for tabs

self.notebook = ttk.Notebook(self.root)
self.notebook.pack(pady=10, expand=True)

# Create tabs
self.add_tab = ttk.Frame(self.notebook)
self.view_tab = ttk.Frame(self.notebook)
self.notebook.add(self.add_tab, text="Add Password")
self.notebook.add(self.view_tab, text="View Passwords")

# Setup Add Password tab

self.setup_add_tab()

# Setup View Passwords tab

self.setup_view_tab()

def setup_add_tab(self):
# Website entry
ttk.Label(self.add_tab, text="Website:").pack(pady=5)
self.website_entry = ttk.Entry(self.add_tab)
self.website_entry.pack(pady=5)

# Username entry
ttk.Label(self.add_tab, text="Username:").pack(pady=5)
 self.username_entry = ttk.Entry(self.add_tab)
self.username_entry.pack(pady=5)

# Password entry
ttk.Label(self.add_tab, text="Password:").pack(pady=5)
self.password_entry = ttk.Entry(self.add_tab, show="*")
self.password_entry.pack(pady=5)

# Show Password checkbox

self.show_password_var = tk.BooleanVar()
ttk.Checkbutton(self.add_tab, text="Show Password",
variable=self.show_password_var,
command=self.toggle_password).pack()

# Add button
ttk.Button(self.add_tab, text="Add Password",
command=self.add_password).pack(pady=10)

# Status label
self.status_label = ttk.Label(self.add_tab, text="")
self.status_label.pack()

def setup_view_tab(self):
# Create Treeview
self.tree = ttk.Treeview(self.view_tab, columns=("Website", "Username", "Password"),
show="headings")
self.tree.heading("Website", text="Website")
self.tree.heading("Username", text="Username")
self.tree.heading("Password", text="Password")
self.tree.pack(pady=10, padx=10, fill="both", expand=True)

# Add scrollbar
scrollbar = ttk.Scrollbar(self.view_tab, orient="vertical", command=self.tree.yview)
scrollbar.pack(side="right", fill="y")
self.tree.configure(yscrollcommand=scrollbar.set)

# Buttons frame
button_frame = ttk.Frame(self.view_tab)
button_frame.pack(pady=10)

# Add show/hide password checkbox

self.show_stored_passwords = tk.BooleanVar()
ttk.Checkbutton(button_frame, text="Show Passwords",
variable=self.show_stored_passwords,
 command=self.refresh_passwords).pack(side="left", padx=5)

# Add buttons
ttk.Button(button_frame, text="Refresh",
command=self.refresh_passwords).pack(side="left", padx=5)
ttk.Button(button_frame, text="Delete Selected",
command=self.delete_selected).pack(side="left", padx=5)

# Initial load of passwords

self.refresh_passwords()

def toggle_password(self):
if self.show_password_var.get():
self.password_entry.config(show="")
else:
self.password_entry.config(show="*")

def validate_password(self, password):

if len(password) < 8:
return False, "Password must be at least 8 characters long"
if not re.search(r"[A-Z]", password):
return False, "Password must contain at least one uppercase letter"
if not re.search(r"[a-z]", password):
return False, "Password must contain at least one lowercase letter"
if not re.search(r"\d", password):
return False, "Password must contain at least one number"
return True, "Strong password!"

def add_password(self):
website = self.website_entry.get()
username = self.username_entry.get()
password = self.password_entry.get()

if not website or not username or not password:

messagebox.showerror("Error", "Please fill in all fields")
return

is_valid, message = self.validate_password(password)

if not is_valid:
messagebox.showerror("Error", message)
return
if self.db.add_password(website, username, password):
self.status_label.config(text="Password added successfully!", foreground="green")
self.website_entry.delete(0, tk.END)
 self.username_entry.delete(0, tk.END)
self.password_entry.delete(0, tk.END)
self.refresh_passwords()
else:
self.status_label.config(text="Error adding password!", foreground="red")

def refresh_passwords(self):
# Clear existing items
for item in self.tree.get_children():
self.tree.delete(item)
# Get passwords from database
passwords = self.db.get_all_passwords()
for password in passwords:
# If show_stored_passwords is True, show actual password, else show asterisks
display_password = password[3] if self.show_stored_passwords.get() else "********"
self.tree.insert("", "end", values=(password[1], password[2], display_password))

def delete_selected(self):
selected_item = self.tree.selection()
if not selected_item:
messagebox.showwarning("Warning", "Please select a password to delete")
return

if messagebox.askyesno("Confirm", "Are you sure you want to delete this password?"):

website = self.tree.item(selected_item)['values'][0]
if self.db.delete_password(website):
self.refresh_passwords()
messagebox.showinfo("Success", "Password deleted successfully!")
else:
messagebox.showerror("Error", "Failed to delete password")

def run(self):
self.root.mainloop()

if __name__ == "__main__":
app = PasswordManager()
app.run()

database.py:

import mysql.connector
from mysql.connector import Error

class PasswordDatabase:
def __init__(self):
try:
# First connect without database
self.connection = mysql.connector.connect(
host='localhost',
user='root',
password='Lakshya@4321'
)
self.create_database()
# Then connect to the created database
self.connection = mysql.connector.connect(
host='localhost',
user='root',
password='Lakshya@4321',
database='password_manager'
)
self.create_table()
except Error as e:
print(f"Error connecting to MySQL: {e}")
self.connection = None

def create_database(self):
try:
cursor = self.connection.cursor()
cursor.execute("CREATE DATABASE IF NOT EXISTS password_manager")
cursor.execute("USE password_manager")
self.connection.commit()
except Error as e:
print(f"Error creating database: {e}")

def create_table(self):
try:
cursor = self.connection.cursor()
cursor.execute("""
CREATE TABLE IF NOT EXISTS passwords (
id INT AUTO_INCREMENT PRIMARY KEY,
website VARCHAR(255) NOT NULL,
username VARCHAR(255) NOT NULL,
password VARCHAR(255) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
""")
self.connection.commit()
except Error as e:
 print(f"Error creating table: {e}")

def add_password(self, website, username, password):

try:
cursor = self.connection.cursor()
query = "INSERT INTO passwords (website, username, password) VALUES (%s, %s, %s)"
cursor.execute(query, (website, username, password))
self.connection.commit()
return True
except Error as e:
print(f"Error adding password: {e}")
return False

def get_all_passwords(self):
try:
cursor = self.connection.cursor()
cursor.execute("SELECT * FROM passwords")
return cursor.fetchall()
except Error as e:
print(f"Error getting passwords: {e}")
return []

def update_password(self, id, website, username, password):

try:
cursor = self.connection.cursor()
query = "UPDATE passwords SET website = %s, username = %s, password = %s WHERE id =
%s"
cursor.execute(query, (website, username, password, id))
self.connection.commit()
return True
except Error as e:
print(f"Error updating password: {e}")
return False

def delete_password(self, id):

try:
cursor = self.connection.cursor()
query = "DELETE FROM passwords WHERE id = %s"
cursor.execute(query, (id,))
self.connection.commit()
return True
except Error as e:
print(f"Error deleting password: {e}")
return False
 def __del__(self):
if hasattr(self, 'connection') and self.connection.is_connected():
self.connection.close()

Output:
Conclusion:

Databases are crucial for modern applications as they provide a structured, efficient, and secure
way to handle data. Unlike static file storage, databases offer:
Data Persistence: Information remains safe and organized even after the application closes
Concurrent Access: Multiple users can access and modify data simultaneously
Data Integrity: Built-in rules ensure data remains accurate and consistent
Scalability: Can handle growing amounts of data efficiently
Security: Controlled access with user permissions and encryption
CRUD Operations: Easy to Create, Read, Update, and Delete data
Data Relationships: Connect related information (like users and their passwords)
Query Capabilities: Quickly search and filter large amounts of data
Backup & Recovery: Protect against da