Code forHow to Build a CRUD App with PyQt5 and SQLite3 in Python Tutorial

db.py

import sqlite3import datetimedef 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_lstdef 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, QDatefrom datetime import datetimefrom 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()

Join 50,000+ Python Programmers & Enthusiasts like you!

Subscribe

Tags

New Tutorials

Popular Tutorials