Learning how to perform CRUD (Create, Read, Update, Delete) operations is a foundational step in building applications that store data. Python, paired with SQLite, offers a lightweight and easy way to manage local databases — perfect for learning and small-scale projects.
This beginner-friendly guide walks you through how to build and manipulate an SQLite database using Python. By the end, you’ll be comfortable creating a database, inserting records, retrieving data, updating entries, and deleting rows.
What is SQLite?
SQLite is a self-contained, file-based database engine. It’s included with Python by default, meaning there’s no setup required — just import and start coding.
Step 1: Connect to a Database
Start by connecting to an SQLite database file (it will be created if it doesn’t exist).
python
Copy code
import sqlite3
conn = sqlite3.connect(‘example.db’) # creates or opens the file
cursor = conn.cursor()
Step 2: Create a Table
Use SQL commands to define your database schema.
python
Copy code
cursor.execute(”’
CREATE TABLE IF NOT EXISTS employees (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
department TEXT,
salary REAL
)
”’)
conn.commit()
Step 3: Insert Data (Create)
python
Copy code
cursor.execute(”’
INSERT INTO employees (name, department, salary)
VALUES (?, ?, ?)
”’, (“Alice”, “HR”, 75000))
conn.commit()
Use parameterized queries (?) to avoid SQL injection and improve code safety.
Step 4: Read Data
python
Copy code
cursor.execute(“SELECT * FROM employees”)
rows = cursor.fetchall()
for row in rows:
print(row)
This retrieves all records and prints them as tuples.
Step 5: Update Data
python
Copy code
cursor.execute(”’
UPDATE employees
SET salary = ?
WHERE name = ?
”’, (80000, “Alice”))
conn.commit()
This modifies the salary of a specific employee.
Step 6: Delete Data
python
Copy code
cursor.execute(”’
DELETE FROM employees
WHERE name = ?
”’, (“Alice”,))
conn.commit()
This removes the employee record from the table.
Step 7: Close the Connection
Always close your connection after completing operations.
python
Copy code
conn.close()
Practice Challenge
Try this:
Add a new employee and then update their department using two separate SQL statements. This practice will reinforce how to work with inserts and updates in SQLite.
To level up your Python database skills and work on hands-on projects with expert mentorship, visit:
👉 https://www.thefullstack.co.in/courses/
We offer step-by-step backend tracks, beginner to advanced SQL lessons, and real-world use cases.
You also like this:
What is Backend Development? A Complete Guide for Beginners [2025]