🗄️ SQLite3 in Python

If you’re new to databases and want a lightweight, easy-to-use solution, SQLite3 is perfect!

This guide explains all essential concepts in simple language with examples you can copy and run.

---

✅ What is SQLite3?

• SQLite is a lightweight, file-based database.
• No server required – your data is stored in a single .db file.
• Perfect for small projects, prototypes, and learning SQL.

---

📦 Install SQLite3

SQLite comes built-in with Python, so no extra installation needed!

Just import:

import sqlite3

---

✅ How SQLite Works

Explanation of Diagram:
Let’s break down what “Python Application → interacts with SQLite Methods via the Python DB API” means in the diagram:

---

✅ Python Application

• This is your Python program (the code you write) that needs to store, retrieve, or manipulate data.
• Example: A script for managing users, invoices, or any data-driven app.

---

✅ Python DB API

• A standard interface defined in Python (PEP 249) for interacting with relational databases.
• It ensures consistency across different database modules (SQLite, MySQL, PostgreSQL).
• Provides methods like:
  • connect() → Connect to the database
  • cursor() → Create a cursor for executing queries
  • execute() → Run SQL commands
  • fetchall() → Retrieve query results

---

✅ SQLite Methods

• These are the actual functions implemented by the sqlite3 module that follow the DB API standard.
• Examples:
  • sqlite3.connect('my_database.db')
  • cursor.execute("SELECT * FROM users")

---

✅ How It Works

1. Your Python Application calls sqlite3.connect() → creates a connection to the .db file.
2. Through the DB API, you use methods like execute() to send SQL queries.
3. SQLite engine processes the query and returns results.
4. Your Python code handles the results (e.g., prints them, stores them in variables).

---

✅ Visual Flow:

Python Code → sqlite3 (DB API methods) → SQLite Database → Results back to Python

In case if your a JAVA developer below table is for quick comparision of modules:

Feature	Python (SQLite3 & DB API)	Java (H2, JDBC, JPA)
Database Engine	SQLite3 (file-based, embedded)	H2 (file-based, embedded)
Low-Level API	Python DB API (PEP 249)	JDBC (Java Database Connectivity)
High-Level ORM	SQLAlchemy, Django ORM	JPA (Java Persistence API), Hibernate
Storage	Single .db file	.mv.db file or in-memory
Setup	Built-in with Python	Requires JDBC driver
Use Case	Scripts, small apps, prototyping	Java apps, unit testing, Spring Boot
Transactions	Supported	Supported
Indexes & Joins	Supported	Supported
Foreign Keys	Supported (PRAGMA enable)	Supported
Backup	conn.backup()	Manual or via tools
Performance Tuning	PRAGMA settings	MVCC, caching options

---

✅ What is 'my_database.db'?

• It’s the name of the SQLite database file.
• When you run:

  conn = sqlite3.connect('my_database.db')

• If the file exists, Python connects to it.
• If it doesn’t exist, Python creates a new database file in your current directory.
  • You can use any name (e.g., data.sqlite, students.db).

---

✅ Basic Operations (CRUD OPERATIONS)

Connect to Database

conn = sqlite3.connect('my_database.db')
cursor = conn.cursor()

Create Table

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

Insert Data

cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ('Alice', 25))
cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ('Bob', 30))
conn.commit()

Fetch Data

cursor.execute("SELECT * FROM users")
print(cursor.fetchall())

Delete Data

import sqlite3

# Connect to the database
conn = sqlite3.connect('my_database.db')
cursor = conn.cursor()

# Delete a specific record
cursor.execute("DELETE FROM users WHERE name = ?", ('Alice',))

# Delete All Rows (Keep Table):
# This clears the table but does not remove its schema.
cursor.execute("DELETE FROM users")

# Commit changes and close connection
conn.commit()
conn.close()

✅ Key Points:

• Always use a WHERE clause to avoid deleting all rows unintentionally.
• Use parameterized queries (?) to prevent SQL injection.
• After deletion, call conn.commit() to save changes.

---

✅ Advanced Topics (Expanded)

1. Transactions

• Transactions ensure atomicity (all-or-nothing execution).
• Use BEGIN, COMMIT, and ROLLBACK:

conn.execute('BEGIN')
cursor.execute("UPDATE users SET age = 26 WHERE name = 'Alice'")
cursor.execute("DELETE FROM users WHERE name = 'Bob'")
conn.commit()  # or conn.rollback() if something fails

Why? Prevents partial updates when errors occur.

---

2. Indexes for Speed

• Indexes make queries faster by reducing search time.

cursor.execute("CREATE INDEX idx_name ON users(name)")

Tip: Use indexes on columns frequently used in WHERE clauses.

---

3. JOIN Queries

• Combine data from multiple tables:

cursor.execute('''
SELECT orders.id, users.name
FROM orders
JOIN users ON orders.user_id = users.id
''')

Types of JOIN:

• INNER JOIN → Matches in both tables
• LEFT JOIN → All from left + matches from right
• CROSS JOIN → Cartesian product

---

4. Foreign Keys

• Enable relationships between tables:

cursor.execute("PRAGMA foreign_keys = ON")

Example:

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    FOREIGN KEY(user_id) REFERENCES users(id)
)

---

5. Backup Database

• Create a backup easily:

with sqlite3.connect('backup.db') as backup_conn:
    conn.backup(backup_conn)

---

6. Row Factory for Dict Output

• Get results as dictionaries instead of tuples:

conn.row_factory = sqlite3.Row
cursor = conn.cursor()
cursor.execute("SELECT * FROM users")
for row in cursor.fetchall():
    print(dict(row))

---

7. Error Handling

• Use try-except for safe operations:

try:
    cursor.execute("SELECT * FROM users")
except sqlite3.Error as e:
    print("Error:", e)

---

8. Prepared Statements

• Prevent SQL injection:

cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ('Charlie', 35))

---

9. Performance Tips

• Use executemany() for bulk inserts:

data = [('Tom', 22), ('Jerry', 24)]
cursor.executemany("INSERT INTO users (name, age) VALUES (?, ?)", data)

• Use PRAGMA cache_size and PRAGMA synchronous for tuning.

---

✅ Summary Table

Concept	Command Example
Connect DB	sqlite3.connect('file.db')
Create Table	CREATE TABLE ...
Insert Data	INSERT INTO table VALUES (?, ?)
Fetch Data	SELECT * FROM table
Update Data	UPDATE table SET column=? WHERE condition
Delete Data	DELETE FROM table WHERE condition
Index	CREATE INDEX idx_name ON table(column)
Join	SELECT ... JOIN ...
Backup	conn.backup(backup_conn)

---

🔥 Pro Tip: Use SQLite for small apps, testing, or learning SQL. For large-scale apps, consider PostgreSQL or MySQL.

---