SQLite with Python: Perform CRUD Operations (Complete Guide)

Table of Contents

• Introduction
• Why Use SQLite with Python?
• Setting Up SQLite in Python
• Creating a Database and Table
• Inserting Data (Create Operation)
• Retrieving Data (Read Operation)
• Updating Data (Update Operation)
• Deleting Data (Delete Operation)
• Best Practices for SQLite in Python
• Conclusion

Introduction

Databases are a critical part of modern application development, and SQLite offers an easy, lightweight, and efficient way to manage data locally.
In this module, you will learn how to use SQLite with Python to perform essential CRUD operations — Create, Read, Update, and Delete.
This knowledge is fundamental whether you are building small desktop applications, prototypes, or even testing database-backed systems.

SQLite is a self-contained, serverless, and zero-configuration database engine, making it ideal for many lightweight use cases.

Why Use SQLite with Python?

SQLite is built into Python’s standard library, which means:

• No external database server setup is needed.
• It is perfect for rapid development and testing.
• It offers excellent performance for small to medium-sized projects.
• Database is stored in a single .db file, simplifying management.

Applications like browsers (Chrome, Firefox) and mobile apps (WhatsApp) often use SQLite behind the scenes.

Setting Up SQLite in Python

Python’s sqlite3 module allows you to interact with SQLite databases.

You can import it directly without installing any external package:

import sqlite3

Connecting to a Database

conn = sqlite3.connect('example.db')  # Creates or opens 'example.db'
cursor = conn.cursor()

• If the file example.db does not exist, SQLite will create it automatically.

Creating a Database and Table

Once connected, you can create a table:

cursor.execute('''
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        email TEXT UNIQUE NOT NULL,
        age INTEGER
    )
''')

conn.commit()

• CREATE TABLE IF NOT EXISTS: Ensures the table is created only if it doesn’t exist.
• Fields: id, name, email, age.

Always call conn.commit() after changes to save them.

Inserting Data (Create Operation)

You can insert data using INSERT INTO:

cursor.execute('''
    INSERT INTO users (name, email, age)
    VALUES (?, ?, ?)
''', ('Alice', '[email protected]', 30))

conn.commit()

Key Points:

• Use placeholders (?) to prevent SQL Injection.
• Always parameterize your queries.

Inserting Multiple Records

users = [
    ('Bob', '[email protected]', 25),
    ('Charlie', '[email protected]', 35)
]

cursor.executemany('''
    INSERT INTO users (name, email, age)
    VALUES (?, ?, ?)
''', users)

conn.commit()

Retrieving Data (Read Operation)

Fetching records from the table:

cursor.execute('SELECT * FROM users')
rows = cursor.fetchall()

for row in rows:
    print(row)

• fetchall(): Retrieves all matching records.
• fetchone(): Retrieves the next record.

Fetching with Conditions

cursor.execute('SELECT * FROM users WHERE age > ?', (30,))
for row in cursor.fetchall():
    print(row)

You can also use ORDER BY, LIMIT, and other SQL clauses.

Updating Data (Update Operation)

Modifying existing records:

cursor.execute('''
    UPDATE users
    SET age = ?
    WHERE email = ?
''', (32, '[email protected]'))

conn.commit()

Verify the Update

cursor.execute('SELECT * FROM users WHERE email = ?', ('[email protected]',))
print(cursor.fetchone())

Deleting Data (Delete Operation)

Removing records:

cursor.execute('''
    DELETE FROM users
    WHERE name = ?
''', ('Bob',))

conn.commit()

Deleting All Data

To delete all rows from a table:

cursor.execute('DELETE FROM users')
conn.commit()

Warning: This deletes all records but keeps the table structure.

Best Practices for SQLite in Python

1. Always Close Connections:
   After your database operations are complete:

conn.close()

2. Use Context Managers:
   Python’s with block can handle closing automatically.

with sqlite3.connect('example.db') as conn:
    cursor = conn.cursor()
    cursor.execute('SELECT * FROM users')
    print(cursor.fetchall())

3. Parameterized Queries:
   Never insert data directly using string formatting to prevent SQL Injection.
4. Use Transactions Thoughtfully:
   Group multiple related operations into a single transaction when needed.
5. Handle Exceptions:
   Wrap database code in try-except blocks to manage errors gracefully.

try:
    conn = sqlite3.connect('example.db')
    cursor = conn.cursor()
    # Operations
except sqlite3.Error as e:
    print(f"Database error: {e}")
finally:
    if conn:
        conn.close()

Conclusion

SQLite is an incredibly powerful tool when you need a reliable, simple database without the complexity of setting up a server.
By mastering CRUD operations in SQLite with Python, you can build real-world applications ranging from small utilities to larger-scale desktop software.

This knowledge forms the basis for more advanced database topics, including ORM (Object Relational Mapping) with libraries like SQLAlchemy and Django ORM.