CRUD Operations Using Python and SQLite
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]
How Can SAP ERP Be Beneficial
What Are the Most Popular Backend Development Languages in 2025?
Frequently Asked Questions
What is CRUD and how does it relate to Python and SQLite?
CRUD stands for Create, Read, Update, and Delete, which are the basic operations used to manage data in a database. In the context of Python and SQLite, CRUD operations can be performed using Python’s sqlite3 module, which provides a SQL database engine. This allows developers to interact with SQLite databases using Python scripts.
How do I create a new SQLite database using Python?
To create a new SQLite database using Python, you can use the sqlite3 module and the connect() function, which returns a connection object representing the database. If the database does not exist, SQLite will create a new database with the specified name. The connection object is then used to execute SQL commands and perform CRUD operations.
What is the difference between the UPDATE and REPLACE statements in SQLite?
The UPDATE statement is used to modify existing data in a table, while the REPLACE statement is used to replace existing data with new data. The main difference between the two is that the REPLACE statement will delete the existing data if a conflict occurs, whereas the UPDATE statement will not. In Python, you can use the execute() method of the cursor object to execute UPDATE and REPLACE statements.
How do I handle errors that occur during CRUD operations in Python and SQLite?
To handle errors that occur during CRUD operations, you can use try-except blocks in Python to catch exceptions raised by the sqlite3 module. The module raises exceptions for errors such as SQL syntax errors, database connection errors, and data type mismatches. By handling these exceptions, you can provide meaningful error messages and prevent your program from crashing.
Can I use Python and SQLite to perform CRUD operations on a large dataset?
Yes, Python and SQLite can be used to perform CRUD operations on a large dataset, but the performance may degrade as the dataset size increases. SQLite is a lightweight database engine that is designed for small to medium-sized databases, and it may not be suitable for very large datasets. However, Python’s sqlite3 module provides an efficient way to interact with SQLite databases, and you can use techniques such as indexing and caching to improve performance.
Leave a Reply