**Course Title:** PyQt6 Application Development **Section Title:** Integrating Databases with PyQt6 **Topic:** Performing CRUD operations in SQL databases ### Introduction to CRUD Operations CRUD (Create, Read, Update, Delete) operations are fundamental to interacting with SQL databases. In this topic, we'll explore how to perform these operations using PyQt6 and QSqlDatabase. By the end of this topic, you'll understand how to execute CRUD operations and apply them in real-world scenarios. ### Creating a Database Connection Before we dive into CRUD operations, let's review how to establish a connection to a database using QSqlDatabase. In the previous topic, we covered the basics of QSqlDatabase and QSqlQuery. We'll build upon that knowledge here. ```python import sys from PyQt6.QtSql import QSqlDatabase, QSqlQuery from PyQt6.QtCore import QSqlDatabase, QSqlQuery # Create a database connection db = QSqlDatabase.addDatabase("QSQLITE") db.setDatabaseName("example.db") if not db.open(): print("Failed to open database:", db.lastError().text()) sys.exit(1) ``` ### Creating Data (Inserting Records) To create data in a SQL database, we'll use the QSqlQuery's `prepare()` and `exec()` methods. The `prepare()` method prepares a SQL query, and the `exec()` method executes the query. ```python # Create a QSqlQuery object query = QSqlQuery(db) # Prepare and execute the INSERT query query.prepare("INSERT INTO users (name, email) VALUES (?, ?)") query.bindValue(0, "John Doe") query.bindValue(1, "john@example.com") if not query.exec(): print("Failed to create record:", query.lastError().text()) ``` ### Reading Data (Fetching Records) To read data from a SQL database, we'll use the QSqlQuery's `prepare()` and `exec()` methods. We'll also use the `next()` method to iterate over the result set. ```python # Prepare and execute the SELECT query query.prepare("SELECT * FROM users") if not query.exec(): print("Failed to read records:", query.lastError().text()) # Iterate over the result set while query.next(): name = query.value(0) email = query.value(1) print(f"Name: {name}, Email: {email}") ``` ### Updating Data (Modifying Records) To update data in a SQL database, we'll use the QSqlQuery's `prepare()` and `exec()` methods. We'll also use the `bindValue()` method to bind new values to the query. ```python # Prepare and execute the UPDATE query query.prepare("UPDATE users SET name = ?, email = ? WHERE id = ?") query.bindValue(0, "Jane Doe") query.bindValue(1, "jane@example.com") query.bindValue(2, 1) # Update the record with id = 1 if not query.exec(): print("Failed to update record:", query.lastError().text()) ``` ### Deleting Data (Removing Records) To delete data from a SQL database, we'll use the QSqlQuery's `prepare()` and `exec()` methods. We'll also use the `bindValue()` method to bind the id of the record to delete. ```python # Prepare and execute the DELETE query query.prepare("DELETE FROM users WHERE id = ?") query.bindValue(0, 1) # Delete the record with id = 1 if not query.exec(): print("Failed to delete record:", query.lastError().text()) ``` ### Practical Takeaways * Use the QSqlQuery's `prepare()` and `exec()` methods to execute SQL queries. * Use the `bindValue()` method to bind values to the query. * Use the `next()` method to iterate over the result set. * Always check the return value of QSqlQuery methods to handle potential errors. ### Example Use Cases * Creating a user registration system where you need to store user data in a database. * Building a blog application where you need to store and retrieve blog posts. * Developing an e-commerce application where you need to store and manage products, orders, and customers. ### Additional Resources * [Qt Documentation: QSqlDatabase](https://doc.qt.io/qt-6/qsqldatabase.html) * [Qt Documentation: QSqlQuery](https://doc.qt.io/qt-6/qsqlquery.html) ### Comments and Feedback We'd love to hear your thoughts and feedback on this topic. If you have any questions or need further clarification, feel free to leave a comment below. In the next topic, we'll cover **Displaying database data in views like QTableView**.