Performing CRUD Operations in Python: Step-by-Step Guide

CRUD stands for Create, Read, Update, and Delete — the four basic operations used when working with databases. In Python, performing CRUD operations is simple thanks to its built-in libraries and connectors for databases like SQLite, MySQL, and PostgreSQL.

In this article, we’ll walk through the steps required to perform CRUD operations in Python, with examples using SQLite and MySQL.

What are CRUD Operations?

1. Create – Insert new data into a database.

2. Read – Retrieve existing data.

3. Update – Modify existing records.

4. Delete – Remove unwanted data.

These operations are the foundation of all data-driven applications.

Steps to Perform CRUD Operations in Python

Step 1: Install Required Library

For SQLite (comes pre-installed):

# No installation required

For MySQL:

pip install mysql-connector-python

Step 2: Connect to the Database

SQLite Example:

import sqlite3

connection = sqlite3.connect("mydatabase.db")
cursor = connection.cursor()

MySQL Example:

import mysql.connector

connection = mysql.connector.connect(
    host="localhost",
    user="root",
    password="yourpassword",
    database="testdb"
)
cursor = connection.cursor()

Step 3: Create a Table

cursor.execute("""CREATE TABLE IF NOT EXISTS employees (
                    id INTEGER PRIMARY KEY AUTO_INCREMENT,
                    name VARCHAR(50),
                    salary FLOAT)""")

Step 4: Perform CRUD Operations

1. Create (Insert Data)

cursor.execute("INSERT INTO employees (name, salary) VALUES (%s, %s)", ("Alice", 60000))
connection.commit()

2. Read (Fetch Data)

cursor.execute("SELECT * FROM employees")
for row in cursor.fetchall():
    print(row)

3. Update (Modify Data)

cursor.execute("UPDATE employees SET salary = %s WHERE name = %s", (70000, "Alice"))
connection.commit()

4. Delete (Remove Data)

cursor.execute("DELETE FROM employees WHERE name = %s", ("Alice",))
connection.commit()

Step 5: Close the Connection

connection.close()

Always close the connection to free up resources.

Best Practices for CRUD Operations in Python

1. Use parameterized queries to prevent SQL injection.

2. Handle exceptions with try-except blocks.

3. Always close database connections after use.

4. Use connection pooling for performance in large-scale applications.

5. Leverage ORMs like SQLAlchemy for complex projects.

Real-World Applications of CRUD Operations

• User management systems – Adding, updating, and removing users.

• E-commerce websites – Managing products, orders, and customers.

• Banking applications – Handling accounts and transactions.

• Machine learning projects – Storing datasets and predictions.

Conclusion

CRUD operations are the building blocks of any database-driven Python application. By following the steps outlined above, you can easily perform create, read, update, and delete operations in both SQLite and MySQL. Mastering CRUD operations is essential for backend development, data science, and enterprise-level applications.