Python for Data Science & Automation · Module 7: Python SQL & Database Integration · Lesson 30 of 34

7.3 Python Database CRUD Operations - Complete Tutorial

7.3 Python Database CRUD Operations

CRUD is one of the most important concepts in database programming. CRUD stands for Create, Read, Update, and Delete — the four fundamental operations performed on persistent data.

In Python applications, CRUD operations allow programs to interact with databases programmatically rather than requiring users to manually edit database records.

CRUD = Create + Read + Update + Delete

These four operations form the foundation of database-driven applications, APIs, ERP systems, inventory systems, reporting tools, and automation workflows.

1. Learning Objectives

By the end of this lesson, you will be able to:

  • Explain the CRUD model.
  • Map CRUD operations to SQL commands.
  • Create records using Python.
  • Read records from a database.
  • Update existing records.
  • Delete records safely.
  • Use parameterized SQL queries.
  • Validate user input.
  • Handle database exceptions.
  • Use transactions and rollback.
  • Build reusable CRUD functions.
  • Implement search and filtering.
  • Work with multiple database records.
  • Use CRUD operations with pandas.
  • Build a complete Python CRUD application.

2. What Is CRUD?

CRUD is an acronym representing the four fundamental operations performed on stored data.

CRUD Operation SQL Command Purpose
Create INSERT Add new records
Read SELECT Retrieve records
Update UPDATE Modify existing records
Delete DELETE Remove records

3. CRUD Architecture

Python Application
        |
        v
CRUD Functions
        |
        v
Database Driver
        |
        v
SQL Statements
        |
        v
Relational Database

A well-designed application normally separates database operations from presentation and business logic.

4. CRUD and REST APIs

CRUD concepts are also closely related to REST API operations.

CRUD Typical HTTP Method Typical Purpose
Create POST Create a resource
Read GET Retrieve a resource
Update PUT / PATCH Modify a resource
Delete DELETE Remove a resource

Understanding database CRUD therefore provides useful background for Python web development and API development.

5. Database Used in This Lesson

The examples use MySQL with Python's mysql-connector-python driver. 

pip install mysql-connector-python

The same CRUD concepts can also be implemented with SQLite, PostgreSQL, and other relational databases using their respective Python drivers.

6. Sample Database Design

We will use a simple products table throughout the examples. 

products
--------------------------------
id
name
category
price
quantity
created_at

7. Create the Database Table

import mysql.connector


connection = mysql.connector.connect(
    host="localhost",
    user="app_user",
    password="your_password",
    database="inventory_db"
)

cursor = connection.cursor()


cursor.execute(
    """
    CREATE TABLE IF NOT EXISTS products (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(150) NOT NULL,
        category VARCHAR(100),
        price DECIMAL(10, 2),
        quantity INT DEFAULT 0,
        created_at DATETIME
            DEFAULT CURRENT_TIMESTAMP
    )
    """
)

connection.commit()

8. CREATE — Insert a Single Record

The Create operation adds a new record to the database. 

sql = """
INSERT INTO products
(name, category, price, quantity)
VALUES (%s, %s, %s, %s)
"""

values = (
    "Laptop",
    "Electronics",
    850.00,
    10
)

cursor.execute(
    sql,
    values
)

connection.commit()

The record is inserted only after the transaction is committed.

9. CREATE — Insert Multiple Records

products = [
    (
        "Laptop",
        "Electronics",
        850.00,
        10
    ),
    (
        "Keyboard",
        "Accessories",
        45.50,
        25
    ),
    (
        "Monitor",
        "Electronics",
        220.00,
        15
    )
]


sql = """
INSERT INTO products
(name, category, price, quantity)
VALUES (%s, %s, %s, %s)
"""


cursor.executemany(
    sql,
    products
)

connection.commit()

executemany() is useful for inserting multiple records using one SQL statement pattern.

10. CREATE — Retrieve the Generated ID

cursor.execute(
    """
    INSERT INTO products
    (name, category, price, quantity)
    VALUES (%s, %s, %s, %s)
    """,
    (
        "Mouse",
        "Accessories",
        25.00,
        50
    )
)


product_id = cursor.lastrowid

connection.commit()

print(
    "New product ID:",
    product_id
)

11. READ — Retrieve All Records

The Read operation retrieves information stored in the database. 

cursor.execute(
    """
    SELECT *
    FROM products
    """
)

rows = cursor.fetchall()


for row in rows:

    print(row)

12. READ — Retrieve Selected Columns

cursor.execute(
    """
    SELECT
        id,
        name,
        price,
        quantity
    FROM products
    """
)

rows = cursor.fetchall()


for row in rows:

    print(row)

Selecting only required columns can reduce unnecessary data retrieval.

13. READ — Retrieve One Record

cursor.execute(
    """
    SELECT *
    FROM products
    WHERE id = %s
    """,
    (1,)
)

product = cursor.fetchone()

print(product)

fetchone() returns one row from the current result set.

14. READ — Search by Name

search_term = "Laptop"


cursor.execute(
    """
    SELECT *
    FROM products
    WHERE name LIKE %s
    """,
    (
        f"%{search_term}%",
    )
)


rows = cursor.fetchall()


for row in rows:

    print(row)

The % wildcard allows the search term to occur anywhere within the product name.

15. READ — Filter by Category

category = "Electronics"


cursor.execute(
    """
    SELECT *
    FROM products
    WHERE category = %s
    """,
    (category,)
)


rows = cursor.fetchall()

16. READ — Filter by Price

minimum_price = 100


cursor.execute(
    """
    SELECT *
    FROM products
    WHERE price > %s
    ORDER BY price DESC
    """,
    (minimum_price,)
)


rows = cursor.fetchall()

17. READ — Sort Records

cursor.execute(
    """
    SELECT *
    FROM products
    ORDER BY price DESC
    """
)

rows = cursor.fetchall()

Use ASC for ascending order and DESC for descending order.

18. READ — Limit Results

cursor.execute(
    """
    SELECT *
    FROM products
    ORDER BY price DESC
    LIMIT 5
    """
)

rows = cursor.fetchall()

19. READ — Count Records

cursor.execute(
    """
    SELECT COUNT(*)
    FROM products
    """
)

total_products = (
    cursor.fetchone()[0]
)

print(
    "Total products:",
    total_products
)

20. READ — Aggregate Data

cursor.execute(
    """
    SELECT
        COUNT(*) AS total_products,
        AVG(price) AS average_price,
        MIN(price) AS minimum_price,
        MAX(price) AS maximum_price
    FROM products
    """
)

result = cursor.fetchone()

print(result)

21. READ — Calculate Inventory Value

SQL can perform calculations directly in the database. 

cursor.execute(
    """
    SELECT
        SUM(price * quantity)
    FROM products
    """
)

inventory_value = (
    cursor.fetchone()[0]
)

print(
    "Inventory value:",
    inventory_value
)

22. UPDATE — Modify a Record

The Update operation changes values in existing records. 

product_id = 1

new_price = 899.99


cursor.execute(
    """
    UPDATE products
    SET price = %s
    WHERE id = %s
    """,
    (
        new_price,
        product_id
    )
)


connection.commit()

23. UPDATE — Modify Multiple Columns

cursor.execute(
    """
    UPDATE products
    SET
        price = %s,
        quantity = %s
    WHERE id = %s
    """,
    (
        920.00,
        20,
        1
    )
)

connection.commit()

24. UPDATE — Increase Stock

additional_quantity = 10
product_id = 1


cursor.execute(
    """
    UPDATE products
    SET quantity = quantity + %s
    WHERE id = %s
    """,
    (
        additional_quantity,
        product_id
    )
)

connection.commit()

SQL expressions can update a value relative to its current value.

25. UPDATE — Decrease Stock

sold_quantity = 2
product_id = 1


cursor.execute(
    """
    UPDATE products
    SET quantity = quantity - %s
    WHERE id = %s
    """,
    (
        sold_quantity,
        product_id
    )
)

connection.commit()
Important:

Production inventory systems should validate that stock does not become negative and should use appropriate transaction logic when multiple operations depend on the same inventory value.

26. UPDATE — Conditional Update

cursor.execute(
    """
    UPDATE products
    SET price = price * 0.90
    WHERE category = %s
    """,
    ("Accessories",)
)

connection.commit()

This demonstrates how an SQL condition can target a group of records.

27. Verify an UPDATE

cursor.execute(
    """
    SELECT *
    FROM products
    WHERE id = %s
    """,
    (1,)
)

product = cursor.fetchone()

print(product)

28. Understanding rowcount

cursor.execute(
    """
    UPDATE products
    SET price = %s
    WHERE id = %s
    """,
    (
        950.00,
        1
    )
)

print(
    "Rows affected:",
    cursor.rowcount
)

connection.commit()

rowcount can provide useful information about the number of rows affected by certain database operations.

29. DELETE — Remove One Record

The Delete operation removes records from a database table. 

product_id = 4


cursor.execute(
    """
    DELETE FROM products
    WHERE id = %s
    """,
    (product_id,)
)

connection.commit()

30. DELETE — Remove Records by Condition

cursor.execute(
    """
    DELETE FROM products
    WHERE quantity = 0
    """
)

connection.commit()

This removes products that currently have no stock.

31. DELETE — Verify Before Deleting

A safer application can first retrieve the record before deleting it. 

product_id = 4


cursor.execute(
    """
    SELECT *
    FROM products
    WHERE id = %s
    """,
    (product_id,)
)

product = cursor.fetchone()


if product is not None:

    print(
        "Product found:",
        product
    )

    cursor.execute(
        """
        DELETE FROM products
        WHERE id = %s
        """,
        (product_id,)
    )

    connection.commit()

else:

    print(
        "Product not found"
    )

32. The Most Dangerous CRUD Mistake

Consider this statement: 

DELETE FROM products;

Without a WHERE condition, it can delete every row in the table.

Similarly: 

UPDATE products
SET price = 0;

can modify every product.

Always review UPDATE and DELETE statements before execution.

33. Parameterized CRUD Queries

Parameterized queries should be used for values supplied by users, external files, APIs, forms, or other untrusted sources. 

name = input(
    "Enter product name: "
)

price = float(
    input(
        "Enter price: "
    )
)


cursor.execute(
    """
    INSERT INTO products
    (name, price)
    VALUES (%s, %s)
    """,
    (
        name,
        price
    )
)

connection.commit()

34. Why String Concatenation Is Unsafe

Do not construct SQL like this:

name = input(
    "Enter product name: "
)

sql = (
    "SELECT * FROM products "
    + "WHERE name = '"
    + name
    + "'"
)

cursor.execute(sql)

Directly embedding untrusted values into SQL can introduce SQL injection vulnerabilities.

Use parameter binding instead: 

cursor.execute(
    """
    SELECT *
    FROM products
    WHERE name = %s
    """,
    (name,)
)

35. Input Validation Before CREATE

name = input(
    "Product name: "
).strip()

price_text = input(
    "Product price: "
).strip()

quantity_text = input(
    "Quantity: "
).strip()


if not name:

    print(
        "Product name is required"
    )

elif not price_text:

    print(
        "Price is required"
    )

elif not quantity_text:

    print(
        "Quantity is required"
    )

Validation should occur before attempting database operations.

36. Numeric Validation

try:

    price = float(
        price_text
    )

    quantity = int(
        quantity_text
    )

    if price < 0:

        raise ValueError(
            "Price cannot be negative"
        )

    if quantity < 0:

        raise ValueError(
            "Quantity cannot be negative"
        )

except ValueError as error:

    print(
        "Invalid input:",
        error
    )

37. CRUD with Functions

Reusable functions make database applications easier to maintain and test. 

def create_product(
    cursor,
    connection,
    name,
    category,
    price,
    quantity
):

    cursor.execute(
        """
        INSERT INTO products
        (name, category, price, quantity)
        VALUES (%s, %s, %s, %s)
        """,
        (
            name,
            category,
            price,
            quantity
        )
    )

    connection.commit()

38. Read Function

def get_products(cursor):

    cursor.execute(
        """
        SELECT
            id,
            name,
            category,
            price,
            quantity,
            created_at
        FROM products
        ORDER BY name
        """
    )

    return cursor.fetchall()

39. Read-One Function

def get_product(
    cursor,
    product_id
):

    cursor.execute(
        """
        SELECT
            id,
            name,
            category,
            price,
            quantity,
            created_at
        FROM products
        WHERE id = %s
        """,
        (product_id,)
    )

    return cursor.fetchone()

40. Update Function

def update_product(
    cursor,
    connection,
    product_id,
    name,
    category,
    price,
    quantity
):

    cursor.execute(
        """
        UPDATE products
        SET
            name = %s,
            category = %s,
            price = %s,
            quantity = %s
        WHERE id = %s
        """,
        (
            name,
            category,
            price,
            quantity,
            product_id
        )
    )

    connection.commit()

    return cursor.rowcount

41. Delete Function

def delete_product(
    cursor,
    connection,
    product_id
):

    cursor.execute(
        """
        DELETE FROM products
        WHERE id = %s
        """,
        (product_id,)
    )

    connection.commit()

    return cursor.rowcount

42. Complete CRUD Function Set

def create_product(
    cursor,
    connection,
    name,
    category,
    price,
    quantity
):

    cursor.execute(
        """
        INSERT INTO products
        (name, category, price, quantity)
        VALUES (%s, %s, %s, %s)
        """,
        (
            name,
            category,
            price,
            quantity
        )
    )

    connection.commit()

    return cursor.lastrowid


def get_products(cursor):

    cursor.execute(
        """
        SELECT *
        FROM products
        ORDER BY name
        """
    )

    return cursor.fetchall()


def get_product(
    cursor,
    product_id
):

    cursor.execute(
        """
        SELECT *
        FROM products
        WHERE id = %s
        """,
        (product_id,)
    )

    return cursor.fetchone()


def update_product(
    cursor,
    connection,
    product_id,
    name,
    category,
    price,
    quantity
):

    cursor.execute(
        """
        UPDATE products
        SET
            name = %s,
            category = %s,
            price = %s,
            quantity = %s
        WHERE id = %s
        """,
        (
            name,
            category,
            price,
            quantity,
            product_id
        )
    )

    connection.commit()

    return cursor.rowcount


def delete_product(
    cursor,
    connection,
    product_id
):

    cursor.execute(
        """
        DELETE FROM products
        WHERE id = %s
        """,
        (product_id,)
    )

    connection.commit()

    return cursor.rowcount

43. CRUD Menu Application

A simple command-line interface can use the CRUD functions to provide a basic database application. 

while True:

    print()
    print("1. Add Product")
    print("2. View Products")
    print("3. Search Product")
    print("4. Update Product")
    print("5. Delete Product")
    print("6. Exit")

    choice = input(
        "Enter your choice: "
    )

    if choice == "1":

        print(
            "Create operation"
        )

    elif choice == "2":

        print(
            "Read operation"
        )

    elif choice == "3":

        print(
            "Search operation"
        )

    elif choice == "4":

        print(
            "Update operation"
        )

    elif choice == "5":

        print(
            "Delete operation"
        )

    elif choice == "6":

        break

    else:

        print(
            "Invalid choice"
        )

44. Transaction-Based CRUD

Related operations should often be executed within one transaction so that the database does not remain in an inconsistent state if one operation fails. 

try:

    cursor.execute(
        """
        UPDATE products
        SET quantity = quantity - %s
        WHERE id = %s
        AND quantity >= %s
        """,
        (
            2,
            1,
            2
        )
    )

    if cursor.rowcount != 1:

        raise ValueError(
            "Insufficient stock"
        )

    cursor.execute(
        """
        INSERT INTO sales
        (product_id, quantity)
        VALUES (%s, %s)
        """,
        (
            1,
            2
        )
    )

    connection.commit()

except Exception:

    connection.rollback()

    raise

This pattern demonstrates why transaction boundaries matter when multiple database changes belong to one logical operation.

45. CRUD with Database Constraints

Database constraints provide an additional layer of data integrity. 

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(100) NOT NULL UNIQUE,
    email VARCHAR(150) NOT NULL UNIQUE,
    age INT CHECK (age >= 0)
)

Application validation and database constraints complement each other; validation improves user experience, while database constraints protect data integrity at the storage layer.

46. Handling Duplicate Records

A UNIQUE constraint can prevent duplicate values. 

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(150) UNIQUE
)

If an application attempts to insert an existing email address, MySQL can reject the operation with a database error.

47. Exception Handling During CREATE

try:

    cursor.execute(
        """
        INSERT INTO users
        (email)
        VALUES (%s)
        """,
        ("user@example.com",)
    )

    connection.commit()

except mysql.connector.Error as error:

    connection.rollback()

    print(
        "Insert failed:",
        error
    )

48. READ with Pagination

Large datasets should generally not be loaded into memory all at once. 

page = 2
page_size = 10

offset = (
    page - 1
) * page_size


cursor.execute(
    """
    SELECT *
    FROM products
    ORDER BY id
    LIMIT %s OFFSET %s
    """,
    (
        page_size,
        offset
    )
)

rows = cursor.fetchall()

Pagination allows applications to retrieve manageable subsets of large result sets.

49. READ with Multiple Conditions

cursor.execute(
    """
    SELECT *
    FROM products
    WHERE category = %s
    AND price BETWEEN %s AND %s
    AND quantity > %s
    """,
    (
        "Electronics",
        100,
        1000,
        0
    )
)

rows = cursor.fetchall()

50. READ with Search and Sorting

search_term = "lap"


cursor.execute(
    """
    SELECT *
    FROM products
    WHERE name LIKE %s
    ORDER BY price DESC
    """,
    (
        f"%{search_term}%"
    )
)

rows = cursor.fetchall()

51. CRUD and pandas

pandas can be used for analytical reads from a relational database. 

import pandas as pd


query = """
SELECT
    name,
    category,
    price,
    quantity
FROM products
"""


df = pd.read_sql(
    query,
    connection
)

print(df)

52. Create Records from a DataFrame

df = pd.DataFrame(
    {
        "name": [
            "Laptop",
            "Keyboard",
            "Monitor"
        ],
        "category": [
            "Electronics",
            "Accessories",
            "Electronics"
        ],
        "price": [
            850.00,
            45.50,
            220.00
        ],
        "quantity": [
            10,
            25,
            15
        ]
    }
)


df.to_sql(
    "products",
    connection,
    if_exists="append",
    index=False
)

The exact pandas database-writing approach depends on the database driver and SQLAlchemy configuration used by the project. For production applications, use the database integration pattern appropriate for the selected stack.

53. CRUD Layer vs Business Logic

A maintainable application should avoid putting all database operations directly inside the user-interface code. 

User Interface
      |
      v
Business Logic
      |
      v
CRUD / Data Access Layer
      |
      v
MySQL Database

This separation makes applications easier to test, maintain, and extend.

54. Data Access Layer

A data-access layer contains database-specific operations such as creating, retrieving, updating, and deleting records. 

class ProductRepository:

    def __init__(
        self,
        connection
    ):

        self.connection = (
            connection
        )


    def create(
        self,
        name,
        category,
        price,
        quantity
    ):

        cursor = (
            self.connection.cursor()
        )

        cursor.execute(
            """
            INSERT INTO products
            (name, category, price, quantity)
            VALUES (%s, %s, %s, %s)
            """,
            (
                name,
                category,
                price,
                quantity
            )
        )

        self.connection.commit()

        product_id = (
            cursor.lastrowid
        )

        cursor.close()

        return product_id

55. Repository Read Method

def get_all(self):

    cursor = (
        self.connection.cursor()
    )

    cursor.execute(
        """
        SELECT *
        FROM products
        ORDER BY name
        """
    )

    rows = cursor.fetchall()

    cursor.close()

    return rows

56. Repository Update Method

def update(
    self,
    product_id,
    name,
    category,
    price,
    quantity
):

    cursor = (
        self.connection.cursor()
    )

    cursor.execute(
        """
        UPDATE products
        SET
            name = %s,
            category = %s,
            price = %s,
            quantity = %s
        WHERE id = %s
        """,
        (
            name,
            category,
            price,
            quantity,
            product_id
        )
    )

    self.connection.commit()

    affected = (
        cursor.rowcount
    )

    cursor.close()

    return affected

57. Repository Delete Method

def delete(
    self,
    product_id
):

    cursor = (
        self.connection.cursor()
    )

    cursor.execute(
        """
        DELETE FROM products
        WHERE id = %s
        """,
        (product_id,)
    )

    self.connection.commit()

    affected = (
        cursor.rowcount
    )

    cursor.close()

    return affected

58. CRUD Lifecycle

CREATE
  ↓
Validate input
  ↓
Execute INSERT
  ↓
Commit
  ↓
Return ID


READ
  ↓
Build SELECT
  ↓
Execute query
  ↓
Fetch results
  ↓
Return data


UPDATE
  ↓
Validate ID
  ↓
Execute UPDATE
  ↓
Commit
  ↓
Verify affected rows


DELETE
  ↓
Validate ID
  ↓
Confirm record
  ↓
Execute DELETE
  ↓
Commit

59. CRUD Security Checklist

  • Use parameterized SQL.
  • Validate all external input.
  • Use least-privilege database accounts.
  • Keep database credentials outside source code.
  • Avoid exposing raw database errors to end users.
  • Use transactions for related changes.
  • Verify record ownership or authorization where applicable.
  • Carefully review destructive operations.

60. Common CRUD Mistakes

Mistake Risk Better Approach
SQL string concatenation SQL injection Parameterized queries
Missing WHERE in UPDATE Unintended mass update Verify conditions
Missing WHERE in DELETE Unintended mass deletion Verify conditions
No validation Invalid data Validate before database operations
No transaction handling Partial changes Use commit and rollback
Hard-coded passwords Credential exposure Use secure configuration
Loading huge result sets Memory/performance issues Use filtering and pagination
Mixing UI and SQL everywhere Difficult maintenance Use a data-access layer

61. Real-World CRUD Example

Consider an inventory application. 

New Product
     ↓
CREATE
     ↓
Database


Product Search
     ↓
READ
     ↓
Database


Stock Adjustment
     ↓
UPDATE
     ↓
Database


Discontinued Product
     ↓
DELETE
     ↓
Database

This same pattern can be adapted to employee records, orders, library systems, customer records, tickets, assets, or other structured datasets.

62. Mini Project — Inventory CRUD Application

Build a command-line inventory application with the following features:

  1. Add a product.
  2. Display all products.
  3. Search by product name.
  4. Filter by category.
  5. Update product details.
  6. Increase or decrease stock.
  7. Delete a product.
  8. Calculate total inventory value.
  9. Display low-stock products.
  10. Export the inventory to Excel.

63. Low-Stock Query

threshold = 5


cursor.execute(
    """
    SELECT
        id,
        name,
        quantity
    FROM products
    WHERE quantity <= %s
    ORDER BY quantity
    """,
    (threshold,)
)


rows = cursor.fetchall()


for row in rows:

    print(row)

64. Inventory Summary Query

cursor.execute(
    """
    SELECT
        COUNT(*) AS products,
        SUM(quantity) AS units,
        SUM(price * quantity)
            AS inventory_value
    FROM products
    """
)


summary = cursor.fetchone()

print(
    "Summary:",
    summary
)

65. Practical Examination Tasks

Task 1 — CREATE

Write a Python function that inserts a new employee into a MySQL table using a parameterized query.

Task 2 — READ

Write a function that retrieves all employees and displays their names and departments.

Task 3 — UPDATE

Write a function that updates an employee's department using their primary key.

Task 4 — DELETE

Write a function that deletes an employee only when the specified employee ID exists.

Task 5 — SEARCH

Implement a parameterized search by employee name.

Task 6 — ANALYSIS

Load the database records into pandas and calculate summary statistics.

66. Interview Questions

Q1. What does CRUD stand for?

View Answer

CRUD stands for Create, Read, Update, and Delete.

Q2. Which SQL command is associated with Create?

View Answer

INSERT is normally used to create new records.

Q3. Which SQL command retrieves data?

View Answer

SELECT.

Q4. Why are parameterized queries important?

View Answer

They separate SQL syntax from data values and help protect applications against SQL injection.

Q5. What can happen if UPDATE is executed without WHERE?

View Answer

The statement can modify every row in the table.

Q6. What can happen if DELETE is executed without WHERE?

View Answer

It can delete every row in the target table.

Q7. What is a transaction?

View Answer

A transaction is a logical unit of database operations that can be committed as a whole or rolled back when necessary.

Q8. Why is input validation important?

View Answer

Validation prevents invalid values from reaching the database and improves data quality and application reliability.

Q9. What is the purpose of a data-access layer?

View Answer

It isolates database operations from application or user interface logic, improving maintainability and testing.

Q10. How can large database results be handled efficiently?

View Answer

Use filtering, pagination, appropriate indexes, and controlled result retrieval instead of loading unnecessary data into memory.

67. Examination MCQs

Q1. What does the C in CRUD represent?

  1. Connect
  2. Create
  3. Calculate
  4. Control

Answer: B

Q2. Which SQL command is used to retrieve data?

  1. INSERT
  2. UPDATE
  3. SELECT
  4. DELETE

Answer: C

Q3. Which SQL command modifies existing records?

  1. UPDATE
  2. ALTER
  3. INSERT
  4. CREATE

Answer: A

Q4. Which SQL command removes records?

  1. REMOVE
  2. DELETE
  3. DROP ROW
  4. ERASE

Answer: B

Q5. Which technique helps prevent SQL injection?

  1. String concatenation
  2. Parameterized queries
  3. Removing spaces
  4. Changing SQL keywords to uppercase

Answer: B

Q6. Which method commits MySQL changes?

  1. save()
  2. commit()
  3. store()
  4. write()

Answer: B

Q7. Which method retrieves all rows from a result?

  1. fetchall()
  2. getall()
  3. readall()
  4. rows()

Answer: A

Q8. What does cursor.lastrowid provide?

  1. Number of deleted rows
  2. Last generated row identifier
  3. Total rows in the table
  4. Last SQL statement

Answer: B

Q9. Which clause is normally used to restrict which rows are updated?

  1. WHERE
  2. GROUP BY
  3. ORDER BY
  4. HAVING

Answer: A

Q10. What is the main purpose of a transaction?

  1. Format SQL code
  2. Group related database operations reliably
  3. Create Python variables
  4. Generate HTML

Answer: B

68. Coding Challenge

Build a Customer CRUD Application

Create a Python application connected to MySQL with a customers table. 

customers
--------------------------------
id
name
email
phone
city
created_at

Requirements

  1. Create the database table.
  2. Add new customers.
  3. Display all customers.
  4. Search customers by name.
  5. Search customers by city.
  6. Update customer information.
  7. Delete a customer.
  8. Prevent duplicate email addresses.
  9. Use parameterized queries throughout.
  10. Handle database exceptions.
  11. Load customers into a pandas DataFrame.
  12. Generate a summary report.

69. CRUD Quick Reference

Operation SQL Python Method
Create INSERT cursor.execute()
Read SELECT execute() + fetch*
Update UPDATE cursor.execute()
Delete DELETE cursor.execute()
Save Transaction connection.commit()
Undo Transaction connection.rollback()

70. Self-Assessment Checklist

You should now be able to:

  • ☐ Explain CRUD.
  • ☐ Map CRUD operations to SQL commands.
  • ☐ Insert records using Python.
  • ☐ Retrieve records.
  • ☐ Search and filter records.
  • ☐ Sort and paginate results.
  • ☐ Update records safely.
  • ☐ Delete records safely.
  • ☐ Use parameterized queries.
  • ☐ Validate input.
  • ☐ Handle database errors.
  • ☐ Use transactions.
  • ☐ Use reusable CRUD functions.
  • ☐ Separate database logic from application logic.
  • ☐ Connect CRUD workflows with pandas.
  • ☐ Build a complete database application.

71. Key Takeaway

CRUD is the foundation of practical database programming. Python provides the application and automation layer, while SQL provides the mechanism for creating, retrieving, modifying, and deleting persistent data.

Once you can implement secure and reusable CRUD operations, you have the core skills required to build database-backed applications, REST APIs, automation systems, reporting tools, and data-processing pipelines.

Start This Course From the Beginning →