🗄️ Python Tutorial — SQLite Database Connectivity

Introduction 🌟

SQLite is a lightweight, serverless, file-based database included with Python by default. It’s perfect for small to medium applications, prototyping, local storage, and embedded systems.

Note

💡 No server required — database stored in a single .db file
💡 Fast, reliable, widely used
💡 Python provides sqlite3 module built-in

1. Importing sqlite3 Module 🧱

import_sqlite.py

import sqlite3

2. Connecting to a Database 🔌

connect.py

conn = sqlite3.connect("mydatabase.db")  # Creates file if not exists
print("Connected!")

✔ connect() returns a Connection object
✔ File is automatically created if missing

3. Creating a Cursor 🖱️

cursor.py

conn = sqlite3.connect("mydatabase.db")
cursor = conn.cursor()  # Needed to execute SQL

✔ Cursor executes SQL commands

4. Creating a Table 🏗️

create_table.py

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

✔ Always call commit() after modifying data

5. Inserting Data into SQLite ➕

insert_data.py

cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ("Sathish", 25))
conn.commit()

Note

✔ Never use string concatenation for SQL queries — always use ? placeholders
✔ Prevents SQL injection

6. Inserting Multiple Rows 📦

multiple_insert.py

data = [("Arun", 22), ("Kumar", 30), ("Priya", 27)]

cursor.executemany("INSERT INTO users (name, age) VALUES (?, ?)", data)
conn.commit()

7. Reading Data (SELECT) 🔍

Fetching All Rows

fetch_all.py

cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()
for row in rows:
    print(row)

Fetching One Row

fetch_one.py

cursor.execute("SELECT * FROM users WHERE id = 1")
row = cursor.fetchone()
print(row)

Fetching Many

fetch_many.py

cursor.execute("SELECT * FROM users")
rows = cursor.fetchmany(2)
print(rows)

8. Updating Data ✏️

update.py

cursor.execute("UPDATE users SET age = ? WHERE name = ?", (26, "Sathish"))
conn.commit()

9. Deleting Data ❌

delete.py

cursor.execute("DELETE FROM users WHERE id = ?", (1,))
conn.commit()

10. Query with ORDER BY, LIMIT, LIKE 🔎

query_examples.py

cursor.execute("SELECT * FROM users ORDER BY age DESC LIMIT 5")
print(cursor.fetchall())

cursor.execute("SELECT * FROM users WHERE name LIKE 'S%'")
print(cursor.fetchall())

11. Using Row Factory for Dictionary Output 📘

row_factory.py

conn.row_factory = sqlite3.Row
cursor = conn.cursor()

cursor.execute("SELECT * FROM users")
for row in cursor.fetchall():
    print(dict(row))

✔ Makes results easier to use in applications

12. Using Context Manager (with statement) 🤝

context_manager.py

with sqlite3.connect("mydatabase.db") as conn:
    cursor = conn.cursor()
    cursor.execute("SELECT COUNT(*) FROM users")
    print(cursor.fetchone())

✔ Automatically commits or rolls back

13. Transactions — Commit & Rollback 🔄

transaction_example.py

try:
    cursor.execute("UPDATE users SET age = age + 1")
    conn.commit()
except:
    conn.rollback()

✔ Ensures data safety

14. Handling SQLite Exceptions ⚠️

exception_handling.py

try:
    cursor.execute("INSERT INTO unknown VALUES (1)")
except sqlite3.Error as e:
    print("Error:", e)

15. Closing the Connection 🔚

close.py

cursor.close()
conn.close()

16. Real-World Example — Simple CRUD App 📝

crud_example.py

import sqlite3

def connect():
    return sqlite3.connect("crud.db")

def create_table():
    with connect() as conn:
        conn.execute("CREATE TABLE IF NOT EXISTS tasks(id INTEGER PRIMARY KEY, title TEXT)")

def add_task(title):
    with connect() as conn:
        conn.execute("INSERT INTO tasks(title) VALUES (?)", (title,))

def get_tasks():
    with connect() as conn:
        return conn.execute("SELECT * FROM tasks").fetchall()

def delete_task(id):
    with connect() as conn:
        conn.execute("DELETE FROM tasks WHERE id = ?", (id,))

# Usage
create_table()
add_task("Learn Python")
print(get_tasks())

SQLite Cheat Sheet 📘

OperationCommand
Create tableCREATE TABLE ...
InsertINSERT INTO table VALUES ...
SelectSELECT * FROM table
UpdateUPDATE table SET ...
DeleteDELETE FROM table

Best Practices 💡

  • ✔ Always use parameterized queries (avoid SQL injection)
  • ✔ Use with blocks for automatic cleanup
  • ✔ Enable row_factory when working with APIs
  • ✔ Keep transactions short to avoid locking
  • ✔ Use indexes for faster SELECT queries

Conclusion 🎉

>>“SQLite makes database usage simple, lightweight, and blazing fast — perfect for local apps, prototypes, and small systems.” ✨

You now understand SQLite Database Connectivity in Python! Want the next topic? Try SQLAlchemy ORM, PostgreSQL Connectivity, MongoDB, or REST API with Database. Just tell me! 😊