Code for How to Build a CRUD App with PyQt5 and SQLite3 in Python Tutorial
View on Github
db.py
import sqlite3
import datetime
def create_table():
db = sqlite3.connect('database.db')
query = """
CREATE TABLE if not exists BOOKS
(ID INTEGER PRIMARY KEY AUTOINCREMENT,
NAME TEXT NOT NULL,
PRICE INTERGER NOT NULL,
CREATED_AT DATETIME default current_timestamp,
COMPLETED_AT DATATIME
)
"""
cur = db.cursor()
cur.execute(query)
db.close()
create_table()
def insert_book(name, completed_at, price):
db = sqlite3.connect('database.db')
query = """
INSERT INTO BOOKS(NAME, COMPLETED_AT, PRICE)
VALUES (?,?,?)
"""
cur = db.cursor()
cur.execute(query, (name, completed_at, price))
db.commit()
db.close()
print('completed')
def get_all_books():
db = sqlite3.connect('database.db')
statement = 'SELECT id, name, completed_at, price FROM BOOKS'
cur = db.cursor()
items_io = cur.execute(statement)
item_lst = [i for i in items_io]
return item_lst
def delete_book(book_id):
# Connect to the SQLite database
db = sqlite3.connect('database.db')
# Define the SQL query to delete a book with a specific ID
query = "DELETE FROM books WHERE id = ?"
# Execute the query with the provided book ID as a parameter
db.execute(query, (book_id,))
# Commit the changes to the database
db.commit()
# Close the database connection
db.close()
def update_book(book_id, updated_name, updated_completed_date, updated_price):
db = sqlite3.connect('database.db')
query = "UPDATE BOOKS SET NAME=?, COMPLETED_AT=? PRICE=? WHERE ID=?"
cur = db.cursor()
cur.execute(query, (updated_name, updated_completed_date,
updated_price, book_id))
db.commit()
db.close()
main.py
from PyQt5.QtWidgets import (QApplication, QMainWindow, QVBoxLayout, QScrollArea,
QLineEdit, QFormLayout, QHBoxLayout, QFrame, QDateEdit,
QPushButton, QLabel, QListWidget, QDialog, QAction, QToolBar)
from PyQt5.QtCore import Qt, QDate
from datetime import datetime
from db import (get_all_books, create_table,
insert_book, delete_book, update_book)
class UpdateBookDialog(QDialog):
def __init__(self, main_window, book_id, name, completed_date, price):
super().__init__(main_window)
self.main_window = main_window
self.book_id = book_id
self.setWindowTitle('Update Book')
self.setFixedSize(300, 150)
self.main_layout = QVBoxLayout()
self.book_name_edit = QLineEdit()
self.book_name_edit.setText(name)
self.price_edit = QLineEdit()
self.price_edit.setText(price)
self.date_edit = QDateEdit()
# set the date edit
self.set_date(completed_date)
self.action_button_layout = QHBoxLayout()
self.save_button = QPushButton(text="Save", clicked=self.save_update)
self.cancel_button = QPushButton(text="Cancel", clicked=self.accept)
self.main_layout.addWidget(self.book_name_edit)
self.main_layout.addWidget(self.price_edit)
self.main_layout.addWidget(self.date_edit)
self.action_button_layout.addWidget(self.save_button)
self.action_button_layout.addWidget(self.cancel_button)
self.main_layout.addLayout(self.action_button_layout)
self.setLayout(self.main_layout)
def set_date(self, date_string):
# Convert the date string to a QDate object
date = QDate.fromString(date_string, "yyyy-MM-dd")
# Set the date in the QDateEdit widget
self.date_edit.setDate(date)
def save_update(self):
updated_name, updated_price = self.book_name_edit.text(), self.price_edit.text()
updated_completed_date = self.date_edit.date().toString("yyyy-MM-dd")
update_book(self.book_id, updated_name,
updated_completed_date, updated_price)
self.accept() # Close the dialog after updating
self.main_window.load_collection()
class CreateRecord(QFrame):
def __init__(self, main_window):
super().__init__()
self.main_window = main_window # Pass a reference to the main window
self.date_entry = QDateEdit()
self.book_name = QLineEdit()
self.book_name.setPlaceholderText('Book name')
self.price = QLineEdit()
self.price.setPlaceholderText('$')
self.add_button = QPushButton(text="Add Book")
# Connect the button to add_book function
self.add_button.clicked.connect(self.add_book)
layout = QVBoxLayout(self)
layout.addWidget(QLabel('Book Name:'))
layout.addWidget(self.book_name)
layout.addWidget(QLabel('Price:'))
layout.addWidget(self.price)
layout.addWidget(QLabel('Completed Date:'))
layout.addWidget(self.date_entry)
layout.addWidget(self.add_button)
def add_book(self):
book_name = self.book_name.text()
completed_date = self.date_entry.date().toString("yyyy-MM-dd")
price = self.price.text()
if book_name and price:
insert_book(book_name, completed_date, price)
# Reload the book collection after adding a book
self.main_window.load_collection()
self.book_name.clear() # Clear the input field
self.price.clear()
class BookCard(QFrame):
def __init__(self, book_id, bookname, completed_date, price, main_window):
super().__init__()
self.setStyleSheet(
'background:white; border-radius:4px; color:black;'
)
self.setFixedHeight(140)
self.book_id, self.bookname, self.completed_date, self.price = book_id, bookname, completed_date, price
layout = QVBoxLayout()
label = QLabel(f'<strong>{bookname}</strong>')
price_label = QLabel(f'<em>${price}</em>')
# Update the format string here
parsed_datetime = datetime.strptime(completed_date, "%Y-%m-%d")
formatted_datetime = parsed_datetime.strftime("%Y-%m-%d")
date_completed = QLabel(f"Completed {formatted_datetime}")
delete_button = QPushButton(
text='Delete', clicked=self.delete_book_click)
delete_button.setFixedWidth(60)
delete_button.setStyleSheet('background:red; padding:4px;')
layout.addWidget(label)
layout.addWidget(price_label)
layout.addWidget(date_completed)
edit_button = QPushButton(
text='Edit', clicked=lambda: self.edit_book_click(main_window))
edit_button.setFixedWidth(60)
edit_button.setStyleSheet('background:lightblue; padding:3px;')
layout.addWidget(edit_button)
layout.addWidget(delete_button)
layout.addStretch()
self.setLayout(layout)
def delete_book_click(self):
delete_book(self.book_id)
self.close()
def edit_book_click(self, main_window):
main_window.update_book(
self.book_id, self.bookname, self.completed_date, self.price)
class Main(QMainWindow):
def __init__(self):
super().__init__()
self.initUI()
self.load_collection()
def initUI(self):
self.main_frame = QFrame()
self.main_layout = QVBoxLayout(self.main_frame)
# add register widget
# Pass a reference to the main window
self.register_widget = CreateRecord(self)
self.main_layout.addWidget(self.register_widget)
books_label = QLabel('Completed Books')
books_label.setStyleSheet('font-size:18px;')
self.main_layout.addWidget(books_label)
self.book_collection_area()
self.setCentralWidget(self.main_frame)
def book_collection_area(self):
scroll_frame = QFrame()
self.book_collection_layout = QVBoxLayout(scroll_frame)
scroll = QScrollArea()
scroll.setWidgetResizable(True)
scroll.setWidget(scroll_frame)
scroll.setStyleSheet('QScrollArea{border:0px}')
self.book_collection_layout.addStretch()
self.main_layout.addWidget(scroll)
def load_collection(self):
# Clear existing book cards before reloading
for i in reversed(range(self.book_collection_layout.count())):
widget = self.book_collection_layout.itemAt(i).widget()
if widget is not None:
widget.deleteLater()
collections = get_all_books()
for collection in collections:
frame = BookCard(*collection, self)
self.book_collection_layout.insertWidget(0, frame)
def update_book(self, book_id, name, date, price):
dialog = UpdateBookDialog(self, book_id, name, date, str(price))
dialog.exec_()
def main():
app = QApplication([])
app.setStyle('fusion')
win = Main()
win.show()
app.exec_()
if __name__ == '__main__':
main()
