Python with SQLite – Complete Guide

SQLite is a lightweight, serverless, self-contained SQL database engine built into Python via the sqlite3 module. It is perfect for applications that require a simple database without the overhead of client-server interaction.

Why Use SQLite with Python?

No server required (embedded)
Comes pre-installed with Python
Great for desktop, mobile, and testing
Uses a single .db file for storage

Setting Up

Import SQLite Module

import sqlite3

Connect to a Database

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

Step 1: Create a Table

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

Step 2: Insert Data

Single Insert

cursor.execute("INSERT INTO users (name, email) VALUES (?, ?)", ("Alice", "alice@example.com"))
conn.commit()

Multiple Inserts

users = [
    ("Bob", "bob@example.com"),
    ("Charlie", "charlie@example.com")
]
cursor.executemany("INSERT INTO users (name, email) VALUES (?, ?)", users)
conn.commit()

Step 3: Read/Retrieve Data

Fetch All Records

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

for row in rows:
    print(row)

Fetch One Record

cursor.execute("SELECT * FROM users WHERE name = ?", ("Alice",))
row = cursor.fetchone()
print(row)

Step 4: Update Records

cursor.execute("UPDATE users SET email = ? WHERE name = ?", ("alice@newmail.com", "Alice"))
conn.commit()

Step 5: Delete Records

cursor.execute("DELETE FROM users WHERE name = ?", ("Bob",))
conn.commit()

Step 6: Close the Connection

cursor.close()
conn.close()

Full Working Example

import sqlite3

# Connect
conn = sqlite3.connect('example.db')
cursor = conn.cursor()

# Create table
cursor.execute('''CREATE TABLE IF NOT EXISTS products (
    id INTEGER PRIMARY KEY,
    name TEXT,
    price REAL
)''')

# Insert
cursor.execute("INSERT INTO products (name, price) VALUES (?, ?)", ("Pen", 1.5))
cursor.execute("INSERT INTO products (name, price) VALUES (?, ?)", ("Notebook", 3.0))

# Read
cursor.execute("SELECT * FROM products")
print(cursor.fetchall())

# Update
cursor.execute("UPDATE products SET price = ? WHERE name = ?", (2.0, "Pen"))

# Delete
cursor.execute("DELETE FROM products WHERE name = ?", ("Notebook",))

conn.commit()
conn.close()

Bonus: Using with Statement (Auto Close)

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

Benefits of SQLite in Python Projects

Ideal for prototyping
No configuration needed
Great for mobile apps and local desktop tools
Easy to migrate to PostgreSQL/MySQL later

Final Tips

Always commit() after changes.
Use ? placeholders to avoid SQL injection.
For large apps, consider using an ORM like SQLAlchemy or Django ORM.