In this article, we’ll create a CRUD (Create, Read, Update, Delete) application in Python using Object-Oriented Programming (OOP) principles. We’ll also incorporate a graphical user interface (GUI) using the tkinter library. This application will manage a simple database of items, such as a list of books.
Also will be added user management and database management
This will involve several additional steps:
- Setting Up the Database: Creating the database and tables in MySQL.
- Updating the Project Structure: Adding a file to manage database connections and queries.
- Creating User Management: Adding user registration and login features.
- Modifying the CRUD Operations: Updating CRUD operations to work with the MySQL database.
- Detailed Comments and Explanations: Ensuring each step is well-documented.
1. Setting Up the Database
First, create a MySQL database and tables for users and books. Use the following SQL commands to set up your database:
CREATE DATABASE book_manager;
USE book_manager;
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE,
password VARCHAR(255) NOT NULL
);
CREATE TABLE books (
id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(100) NOT NULL,
author VARCHAR(100) NOT NULL,
year INT NOT NULL,
isbn VARCHAR(50) NOT NULL,
user_id INT,
FOREIGN KEY (user_id) REFERENCES users(id)
);
2. Updating the Project Structure
Add a new file database.py to manage database connections and queries. The updated project structure will look like this:
crud_app/
├── main.py
├── book.py
├── gui.py
├── user.py
├── database.py
3. Creating User Management
Add user registration and login features. Let’s start by updating database.py to handle database interactions.
database.py
# database.py
import mysql.connector
from mysql.connector import Error
class Database:
def __init__(self):
# Initialize the connection to the MySQL database
self.connection = self.create_connection()
def create_connection(self):
# Create a connection to the MySQL database
try:
connection = mysql.connector.connect(
host='localhost',
database='book_manager',
user='your_username', # Replace with your MySQL username
password='your_password' # Replace with your MySQL password
)
if connection.is_connected():
print("Connected to MySQL database")
return connection
except Error as e:
print(f"Error connecting to MySQL database: {e}")
return None
def execute_query(self, query, data):
# Execute a query to modify the database (INSERT, UPDATE, DELETE)
cursor = self.connection.cursor()
try:
cursor.execute(query, data)
self.connection.commit()
return cursor
except Error as e:
print(f"Error executing query: {e}")
return None
def fetch_query(self, query, data):
# Execute a query to fetch data from the database (SELECT)
cursor = self.connection.cursor(dictionary=True)
try:
cursor.execute(query, data)
return cursor.fetchall()
except Error as e:
print(f"Error fetching query: {e}")
return None
def close_connection(self):
# Close the database connection
if self.connection.is_connected():
self.connection.close()
Database Interaction:
- The
Databaseclass indatabase.pymanages database connections and query execution. - The
Bookclass inbook.pyis updated to interact with the MySQL database for CRUD operations.
- The
user.py
Create a user.py file to manage user operations.
# user.py
from database import Database
import bcrypt
class User:
db = Database()
@staticmethod
def create_user(username, password):
# Create a new user with a hashed password
hashed_password = bcrypt.hashpw(password.encode('utf-8'), bcrypt.gensalt())
query = "INSERT INTO users (username, password) VALUES (%s, %s)"
User.db.execute_query(query, (username, hashed_password))
@staticmethod
def verify_user(username, password):
# Verify a user's credentials
query = "SELECT * FROM users WHERE username = %s"
user = User.db.fetch_query(query, (username,))
if user:
return bcrypt.checkpw(password.encode('utf-8'), user[0]['password'].encode('utf-8'))
return False
@staticmethod
def get_user_id(username):
# Retrieve the user ID for a given username
query = "SELECT id FROM users WHERE username = %s"
user = User.db.fetch_query(query, (username,))
if user:
return user[0]['id']
return None
User Management:
- The
Userclass inuser.pyhandles user creation and verification. - Passwords are hashed using bcrypt for security.
- The
book.py
Update book.py to interact with the MySQL database.
python
# book.py
from database import Database
class Book:
db = Database()
@staticmethod
def add_book(title, author, year, isbn, user_id):
# Add a new book to the database
query = "INSERT INTO books (title, author, year, isbn, user_id) VALUES (%s, %s, %s, %s, %s)"
Book.db.execute_query(query, (title, author, year, isbn, user_id))
@staticmethod
def get_books(user_id):
# Retrieve all books for a specific user
query = "SELECT * FROM books WHERE user_id = %s"
return Book.db.fetch_query(query, (user_id,))
@staticmethod
def update_book(book_id, title, author, year, isbn):
# Update a book in the database
query = "UPDATE books SET title = %s, author = %s, year = %s, isbn = %s WHERE id = %s"
Book.db.execute_query(query, (title, author, year, isbn, book_id))
@staticmethod
def delete_book(book_id):
# Delete a book from the database
query = "DELETE FROM books WHERE id = %s"
Book.db.execute_query(query, (book_id,))
gui.py
Update gui.py to include user registration and login features, and to handle CRUD operations with the database.
# gui.py
import tkinter as tk
from tkinter import messagebox
from book import Book
from user import User
class BookApp:
def __init__(self):
# Initialize the main window
self.root = tk.Tk()
self.root.title("Book Manager")
self.user_id = None # To store the logged-in user's ID
self.create_login_widgets() # Start with the login widgets
def create_login_widgets(self):
# Create the login and registration widgets
self.clear_widgets()
self.username_label = tk.Label(self.root, text="Username")
self.username_label.grid(row=0, column=0)
self.username_entry = tk.Entry(self.root)
self.username_entry.grid(row=0, column=1)
self.password_label = tk.Label(self.root, text="Password")
self.password_label.grid(row=1, column=0)
self.password_entry = tk.Entry(self.root, show="*")
self.password_entry.grid(row=1, column=1)
self.login_button = tk.Button(self.root, text="Login", command=self.login)
self.login_button.grid(row=2, column=0)
self.register_button = tk.Button(self.root, text="Register", command=self.register)
self.register_button.grid(row=2, column=1)
def login(self):
# Handle user login
username = self.username_entry.get()
password = self.password_entry.get()
if User.verify_user(username, password):
self.user_id = User.get_user_id(username)
self.create_main_widgets()
else:
messagebox.showerror("Login Error", "Invalid username or password")
def register(self):
# Handle user registration
username = self.username_entry.get()
password = self.password_entry.get()
User.create_user(username, password)
messagebox.showinfo("Success", "User registered successfully")
def create_main_widgets(self):
# Create the main widgets for the book management interface
self.clear_widgets()
self.title_label = tk.Label(self.root, text="Title")
self.title_label.grid(row=0, column=0)
self.title_entry = tk.Entry(self.root)
self.title_entry.grid(row=0, column=1)
self.author_label = tk.Label(self.root, text="Author")
self.author_label.grid(row=1, column=0)
self.author_entry = tk.Entry(self.root)
self.author_entry.grid(row=1, column=1)
self.year_label = tk.Label(self.root, text="Year")
self.year_label.grid(row=2, column=0)
self.year_entry = tk.Entry(self.root)
self.year_entry.grid(row=2, column=1)
self.isbn_label = tk.Label(self.root, text="ISBN")
self.isbn_label.grid(row=3, column=0)
self.isbn_entry = tk.Entry(self.root)
self.isbn_entry.grid(row=3, column=1)
self.add_button = tk.Button(self.root, text="Add Book", command=self.add_book)
self.add_button.grid(row=4, column=0)
self.view_button = tk.Button(self.root, text="View Books", command=self.view_books)
self.view_button.grid(row=4, column=1)
self.update_button = tk.Button(self.root, text="Update Book", command=self.update_book)
self.update_button.grid(row=5, column=0)
self.delete_button = tk.Button(self.root, text="Delete Book", command=self.delete_book)
self.delete_button.grid(row=5, column=1)
self.book_list = tk.Listbox(self.root, height=10, width=50)
self.book_list.grid(row=6, column=0, columnspan=2)
self.book_list.bind('<<ListboxSelect>>', self.on_select)
self.selected_book_id = None
def add_book(self):
# Add a new book to the database
title = self.title_entry.get()
author = self.author_entry.get()
year = self.year_entry.get()
isbn = self.isbn_entry.get()
if title and author and year and isbn:
Book.add_book(title, author, year, isbn, self.user_id)
messagebox.showinfo("Success", "Book added successfully!")
self.clear_entries()
else:
messagebox.showwarning("Input Error", "Please fill all fields")
def view_books(self):
# Display all books for the logged-in user
self.book_list.delete(0, tk.END)
books = Book.get_books(self.user_id)
for book in books:
self.book_list.insert(tk.END, f"{book['title']} by {book['author']} ({book['year']}) [ISBN: {book['isbn']}]")
def update_book(self):
# Update the selected book in the database
if self.selected_book_id is not None:
title = self.title_entry.get()
author = self.author_entry.get()
year = self.year_entry.get()
isbn = self.isbn_entry.get()
if title and author and year and isbn:
Book.update_book(self.selected_book_id, title, author, year, isbn)
messagebox.showinfo("Success", "Book updated successfully!")
self.view_books()
self.clear_entries()
else:
messagebox.showwarning("Input Error", "Please fill all fields")
def delete_book(self):
# Delete the selected book from the database
if self.selected_book_id is not None:
Book.delete_book(self.selected_book_id)
messagebox.showinfo("Success", "Book deleted successfully!")
self.view_books()
self.clear_entries()
def on_select(self, event):
# Handle selection from the book listbox
try:
index = self.book_list.curselection()[0]
self.selected_book_id = Book.get_books(self.user_id)[index]['id']
selected_book = Book.get_books(self.user_id)[index]
self.title_entry.delete(0, tk.END)
self.title_entry.insert(tk.END, selected_book['title'])
self.author_entry.delete(0, tk.END)
self.author_entry.insert(tk.END, selected_book['author'])
self.year_entry.delete(0, tk.END)
self.year_entry.insert(tk.END, selected_book['year'])
self.isbn_entry.delete(0, tk.END)
self.isbn_entry.insert(tk.END, selected_book['isbn'])
except IndexError:
self.selected_book_id = None
def clear_entries(self):
# Clear all input fields
self.title_entry.delete(0, tk.END)
self.author_entry.delete(0, tk.END)
self.year_entry.delete(0, tk.END)
self.isbn_entry.delete(0, tk.END)
def clear_widgets(self):
# Remove all widgets from the root window
for widget in self.root.winfo_children():
widget.grid_forget()
def run(self):
# Start the Tkinter main loop
self.root.mainloop()
- The
BookAppclass ingui.pynow includes user registration and login. - Upon successful login, the main book management interface is displayed.
- The
add_book,view_books,update_book, anddelete_bookmethods are updated to interact with the database.
Running the Application
Ensure MySQL server is running and you have the necessary Python packages installed:
pip install mysql-connector-python bcrypt
Then, run the application:
python main.py
This will open the Book Manager application window. First, you need to register or log in. After logging in, you can add, view, update, and delete books associated with your user account.
With these changes, the application now includes user management and interacts with a MySQL database, providing a more robust and secure solution. The detailed comments and explanations should help in understanding each step and component of the application.
