CRUD Operations in Python

Learn how to create, read, update and delete database records using Python and SQLite.

What are CRUD Operations?

CRUD is a common term used for the four basic operations performed on data in a database.

CRUD stands for Create, Read, Update and Delete.

CRUD Flow

1. Create

The Create operation is used to add new records to the database.

In SQL, the INSERT statement is used for creating new records.

import sqlite3

connection = sqlite3.connect("students.db")

cursor = connection.cursor()

cursor.execute(
    "INSERT INTO students (name, course) VALUES (?, ?)",
    ("Rahul", "Python")
)

connection.commit()

connection.close()

print("Student created")

Output:

Student created

2. Read

The Read operation is used to retrieve records from the database.

The SQL SELECT statement is used for reading data.

import sqlite3

connection = sqlite3.connect("students.db")

cursor = connection.cursor()

cursor.execute("SELECT * FROM students")

students = cursor.fetchall()

for student in students:

    print(student)

connection.close()

Example Output:

(1, 'Rahul', 'Python')
(2, 'Priya', '.NET')

Reading One Record

The fetchone() method can be used to retrieve one record.

cursor.execute(
    "SELECT * FROM students WHERE id = ?",
    (1,)
)

student = cursor.fetchone()

print(student)

Example Output:

(1, 'Rahul', 'Python')

3. Update

The Update operation is used to modify existing records in the database.

The SQL UPDATE statement is used for changing data.

import sqlite3

connection = sqlite3.connect("students.db")

cursor = connection.cursor()

cursor.execute(
    "UPDATE students SET course = ? WHERE id = ?",
    ("Python Full Stack", 1)
)

connection.commit()

connection.close()

print("Student updated")

Output:

Student updated

4. Delete

The Delete operation is used to remove a record from the database.

The SQL DELETE statement is used for deleting data.

import sqlite3

connection = sqlite3.connect("students.db")

cursor = connection.cursor()

cursor.execute(
    "DELETE FROM students WHERE id = ?",
    (1,)
)

connection.commit()

connection.close()

print("Student deleted")

Output:

Student deleted

Creating a Table for CRUD

Before performing CRUD operations, a table must exist.

import sqlite3

connection = sqlite3.connect("students.db")

cursor = connection.cursor()

cursor.execute("""
CREATE TABLE IF NOT EXISTS students
(
    id INTEGER PRIMARY KEY,
    name TEXT,
    course TEXT
)
""")

connection.commit()

connection.close()

print("Table ready")

Output:

Table ready

Parameterized Queries in CRUD

Parameterized queries allow values to be passed separately from the SQL statement.

name = "Amit"
course = "Java"

cursor.execute(
    "INSERT INTO students (name, course) VALUES (?, ?)",
    (name, course)
)

connection.commit()

Parameterized queries are preferred when working with user-provided values.

CRUD with User Input

import sqlite3

connection = sqlite3.connect("students.db")

cursor = connection.cursor()

name = input("Enter student name: ")
course = input("Enter course: ")

cursor.execute(
    "INSERT INTO students (name, course) VALUES (?, ?)",
    (name, course)
)

connection.commit()

print("Student added successfully")

connection.close()

Example Output:

Enter student name: Pridnya
Enter course: Python
Student added successfully

CRUD Example 👀📩

The following example performs Create, Read, Update and Delete operations on a students table.

import sqlite3

connection = sqlite3.connect("college.db")

cursor = connection.cursor()

cursor.execute("""
CREATE TABLE IF NOT EXISTS students
(
    id INTEGER PRIMARY KEY,
    name TEXT,
    course TEXT
)
""")

cursor.execute(
    "INSERT INTO students (name, course) VALUES (?, ?)",
    ("Samiksha", "Python")
)

connection.commit()

cursor.execute("SELECT * FROM students")

students = cursor.fetchall()

for student in students:

    print(student)

cursor.execute(
    "UPDATE students SET course = ? WHERE name = ?",
    ("Python Full Stack", "Samiksha")
)

connection.commit()

cursor.execute(
    "DELETE FROM students WHERE name = ?",
    ("Samiksha",)
)

connection.commit()

connection.close()

print("CRUD operations completed")

Example Output:

(1, 'Samiksha', 'Python')
CRUD operations completed

CRUD and SQL Statements

Advantages of CRUD Operations

Best Practices

Common Mistakes

• Forgetting to call commit().
• Using the wrong SQL statement.
• Forgetting to close the database connection.
• Not using parameterized queries.
• Updating or deleting records without a proper condition.

Practice Programs

1. Create a students table.
2. Insert five student records.
3. Display all student records.
4. Update the course of one student.
5. Delete one student record.
6. Create a simple CRUD program using user input.