Implementing CRUD in Python

1. Creating a Table

import sqlite3

conn = sqlite3.connect('my_database.db') # Creates file if not exists
cursor = conn.cursor()

# Create Table
cursor.execute('''
    CREATE TABLE IF NOT EXISTS students (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        grade REAL
    )
''')
conn.commit()

Column Constraints:

2. Insert (Create)

Security Warning: SQL Injection! Never insert user input directly into SQL strings. Attackers can manipulate your query.

Study Deep: Parameterized Queries (Safety First)

When you use cursor.execute("INSERT ... VALUES (?)", (name,)), you are using a parameterized query.

• How it works: You send the SQL command and the data to the database separately. The database "pre-compiles" the SQL first, then treats your data strictly as values, not as parts of the code.
• Why it matters: This is the #1 defense against SQL Injection. An attacker might try to input a "malicious name" like 'John'; DROP TABLE students; --. If you concatenated strings, the database would run the DROP TABLE command. With parameterization, it just tries to save a student whose name is that entire long string, keeping your table safe.

# Single Insert (Parameterized — SAFE)
cursor.execute("INSERT INTO students (name, grade) VALUES (?, ?)", ("Rahul", 85.5))

# Bulk Insert (Many records at once)
data = [("Amit", 90), ("Sneha", 92), ("Priya", 88)]
cursor.executemany("INSERT INTO students (name, grade) VALUES (?, ?)", data)

conn.commit()

3. Select (Read)

No commit needed after SELECT (no changes made). Use fetch methods to retrieve results.

cursor.execute("SELECT * FROM students WHERE grade > 80")

# Fetch one row at a time
first_student = cursor.fetchone()    # Returns tuple: (1, 'Rahul', 85.5)

# Fetch all remaining rows
all_toppers = cursor.fetchall()       # Returns list of tuples

# Iterate through results
for row in all_toppers:
    print(f"ID: {row[0]}, Name: {row[1]}, Grade: {row[2]}")

Fetch Methods:

4. Update and Delete

# Update a record
cursor.execute("UPDATE students SET grade = ? WHERE name = ?", (95, "Rahul"))

# Delete a record
cursor.execute("DELETE FROM students WHERE name = ?", ("Amit",))

conn.commit()
conn.close()  # Always close at the end

5. Error Handling

Always wrap database operations in try/except to handle errors gracefully:

import sqlite3

try:
    conn = sqlite3.connect('my_database.db')
    cursor = conn.cursor()
    cursor.execute("INSERT INTO students (name, grade) VALUES (?, ?)", ("Test", 80))
    conn.commit()
    print("Success!")
except sqlite3.IntegrityError as e:
    print(f"Integrity Error: {e}")  # e.g., duplicate primary key
except sqlite3.OperationalError as e:
    print(f"Operational Error: {e}")  # e.g., table doesn't exist
finally:
    conn.close()  # Always runs, even after an error

6. CRUD Summary