Python SQLite & Database Basics

Database kya hota hai?

Database structured data ko organize, query and update karne ka system hai. Relational database data ko tables me store karta hai, jahan rows records represent karti hain aur columns fields/attributes.

Why SQLite?

• Python standard library me sqlite3 module available hai.
• Separate database server install/run karna required nahi.
• Entire database ek file me ho sakta hai.
• Learning, prototypes, desktop tools, local apps and many moderate workloads ke liye useful hai.
• Large multi-server systems ke liye PostgreSQL/MySQL jaise server databases more appropriate ho sakte hain.

Connect to SQLite

import sqlite3

connection = sqlite3.connect("students.db")
print("Connected")
connection.close()

Agar file exist nahi karti, SQLite normally new database file create kar deta hai. Testing or temporary examples ke liye :memory: database RAM me create hota hai and connection close hone par disappear ho jata hai.

Try SQLite connection →

Connection and cursor

Connection database session/transaction state manage karta hai. Cursor SQL execute aur result rows fetch karne ke liye use hota hai.

import sqlite3

connection = sqlite3.connect(":memory:")
cursor = connection.cursor()

print(type(connection).__name__)
print(type(cursor).__name__)
connection.close()

Create a table

import sqlite3

connection = sqlite3.connect(":memory:")
cursor = connection.cursor()

cursor.execute("""
    CREATE TABLE students (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        email TEXT UNIQUE,
        score REAL CHECK(score BETWEEN 0 AND 100)
    )
""")

print("Table created")
connection.close()

PRIMARY KEY unique row identity provide karta hai. NOT NULL, UNIQUE aur CHECK database level par invalid data ko restrict kar sakte hain.

SQLite type basics

Common SQLite storage classes include NULL, INTEGER, REAL, TEXT and BLOB. SQLite dynamic typing use karta hai, but declared column types still type affinity and schema intent express karte hain.

INSERT — add rows

import sqlite3

connection = sqlite3.connect(":memory:")
cursor = connection.cursor()
cursor.execute("CREATE TABLE students (id INTEGER PRIMARY KEY, name TEXT NOT NULL, score REAL)")

cursor.execute(
    "INSERT INTO students (name, score) VALUES (?, ?)",
    ("Aman", 88),
)
connection.commit()

print(cursor.lastrowid)
connection.close()

? placeholders values safely bind karte hain. User values ko SQL string me concatenate ya f-string se inject mat karo.

Try INSERT safely →

Why parameterized queries matter

SQL injection tab ho sakta hai jab untrusted input query syntax ka part ban jaye. Correct approach: SQL structure fixed rakho and data values placeholders ke through bind karo.

name = input("Student name: ")

cursor.execute(
    "SELECT id, name, score FROM students WHERE name = ?",
    (name,),
)

Insert many rows with executemany()

students = [
    ("Aman", 88),
    ("Riya", 94),
    ("Kabir", 72),
]

cursor.executemany(
    "INSERT INTO students (name, score) VALUES (?, ?)",
    students,
)
connection.commit()

SELECT — read rows

cursor.execute("SELECT id, name, score FROM students ORDER BY score DESC")

for row in cursor.fetchall():
    print(row)

Default rows tuples hote hain. Large result sets me always fetchall() required nahi; cursor ko directly iterate kar sakte ho or fetchone()/fetchmany() use kar sakte ho.

WHERE, ORDER BY and LIMIT

minimum = 75
cursor.execute(
    """
    SELECT name, score
    FROM students
    WHERE score >= ?
    ORDER BY score DESC
    LIMIT 5
    """,
    (minimum,),
)

for name, score in cursor:
    print(name, score)

UPDATE — modify rows

cursor.execute(
    "UPDATE students SET score = ? WHERE id = ?",
    (91, 1),
)
connection.commit()
print("Rows changed:", cursor.rowcount)

DELETE — remove rows

cursor.execute("DELETE FROM students WHERE id = ?", (1,))
connection.commit()
print("Rows deleted:", cursor.rowcount)

CRUD recap

• Create: INSERT
• Read: SELECT
• Update: UPDATE
• Delete: DELETE

Transactions

Transaction related changes ko one logical unit ki tarah treat karta hai. Success par commit(); failure par rollback().

import sqlite3

connection = sqlite3.connect(":memory:")
connection.execute("CREATE TABLE wallets (name TEXT PRIMARY KEY, balance INTEGER CHECK(balance >= 0))")
connection.executemany(
    "INSERT INTO wallets VALUES (?, ?)",
    [("Aman", 1000), ("Riya", 500)],
)
connection.commit()

try:
    connection.execute("UPDATE wallets SET balance = balance - ? WHERE name = ?", (200, "Aman"))
    connection.execute("UPDATE wallets SET balance = balance + ? WHERE name = ?", (200, "Riya"))
    connection.commit()
except sqlite3.Error:
    connection.rollback()
    raise
finally:
    connection.close()

Transfer jaisi multi-step operation half-complete state me nahi rehni chahiye; transaction atomicity ka basic goal yahi hai.

Connection as a context manager

import sqlite3

connection = sqlite3.connect("students.db")
try:
    with connection:
        connection.execute(
            "INSERT INTO students (name, score) VALUES (?, ?)",
            ("Riya", 94),
        )
finally:
    connection.close()

with connection: transaction success/failure handling me help karta hai, but connection automatically close hona assume mat karo; explicit close or separate resource-management pattern use karo.

Readable rows with sqlite3.Row

import sqlite3

connection = sqlite3.connect(":memory:")
connection.row_factory = sqlite3.Row
connection.execute("CREATE TABLE students (id INTEGER PRIMARY KEY, name TEXT, score REAL)")
connection.execute("INSERT INTO students (name, score) VALUES (?, ?)", ("Aman", 88))

row = connection.execute("SELECT id, name, score FROM students").fetchone()
print(row["name"], row["score"])
connection.close()

Named column access application code ko tuple indexes se more readable bana sakta hai.

Relationships and foreign keys

Relational design me one table another table ke row ko foreign key se reference kar sakta hai.

connection.execute("PRAGMA foreign_keys = ON")

connection.execute("""
    CREATE TABLE courses (
        id INTEGER PRIMARY KEY,
        title TEXT NOT NULL UNIQUE
    )
""")

connection.execute("""
    CREATE TABLE enrollments (
        student_id INTEGER NOT NULL,
        course_id INTEGER NOT NULL,
        FOREIGN KEY (course_id) REFERENCES courses(id)
    )
""")

SQLite me foreign-key enforcement connection par enable karna important hai. Schema design me referenced keys and delete/update behavior clearly decide karo.

JOIN basics

SELECT students.name, courses.title
FROM enrollments
JOIN students ON students.id = enrollments.student_id
JOIN courses ON courses.id = enrollments.course_id
ORDER BY students.name;

JOIN related tables ka data combine karta hai. Database design ka benefit ye hai ki repeated values ko unnecessarily duplicate kiye bina relationships model kar sakte ho.

Aggregate queries

SELECT
    COUNT(*) AS total_students,
    AVG(score) AS average_score,
    MAX(score) AS highest_score
FROM students;

COUNT, SUM, AVG, MIN, MAX summary reporting me common hain.

GROUP BY

SELECT city, COUNT(*) AS student_count
FROM students
GROUP BY city
ORDER BY student_count DESC;

GROUP BY rows ko category/group ke basis par aggregate karne deta hai.

Indexes — quick introduction

CREATE INDEX idx_students_email ON students(email);

Index certain lookups/sorts ko fast kar sakta hai, but storage and write cost add karta hai. Har column par index banana automatically good optimization nahi. Query patterns measure karke index choose karo.

Schema design basics

• Har entity ke liye clear table responsibility rakho.
• Stable primary key choose karo.
• Required fields ke liye NOT NULL use karo.
• Real uniqueness rule ho to UNIQUE enforce karo.
• Relationships foreign keys se express karo.
• Repeated groups ko separate related tables me normalize karna useful ho sakta hai.
• Dates/timestamps ke storage format ko consistently define karo.

Dates in SQLite

SQLite dedicated datetime storage class force nahi karta. Applications frequently ISO 8601 text, Unix timestamps or another documented representation use karte hain. Format consistently choose karo and Python datetime conversion boundaries clearly handle karo.

Handle database errors

import sqlite3

try:
    connection.execute(
        "INSERT INTO students (email) VALUES (?)",
        ("same@example.com",),
    )
    connection.commit()
except sqlite3.IntegrityError as error:
    connection.rollback()
    print("Constraint failed:", error)
except sqlite3.Error as error:
    connection.rollback()
    print("Database error:", error)

IntegrityError constraint violations ke liye common specialized exception hai. User ko raw database internals expose karne ke bajay application-friendly error message map karo.

Keep SQL organized

Small script me direct SQL fine hai. Project grow hone par database access functions/classes me centralize karna helpful hai.

def add_student(connection, name, score):
    cursor = connection.execute(
        "INSERT INTO students (name, score) VALUES (?, ?)",
        (name, score),
    )
    return cursor.lastrowid


def get_student(connection, student_id):
    return connection.execute(
        "SELECT id, name, score FROM students WHERE id = ?",
        (student_id,),
    ).fetchone()

Business logic aur SQL access ko manageable boundaries me rakhne se tests and future database changes easier ho sakte hain.

Database file hygiene

• Development database and production database mix mat karo.
• Important database ka tested backup strategy rakho.
• Sensitive data plain local database me store karne se pehle threat model and encryption needs samjho.
• Database files ko public Git repository me accidentally commit mat karo when they contain real/user data.
• Schema migrations/version changes plan karo as application evolves.

Common beginner mistakes

• User input ko f-string/concatenation se SQL me inject karna.
• commit() forget karke changes persist hone ki expectation rakhna.
• UPDATE/DELETE me missing WHERE.
• Constraint errors ignore karna.
• Foreign keys define karke SQLite enforcement enable na karna.
• Every query ke liye new schema/table create karna instead of reusing proper design.
• SELECT * ko long-lived application contracts me blindly use karna.
• Huge result ko unnecessarily fetchall() karna.
• Transaction ke multiple related writes me partial state allow karna.
• Database file ko backup/security strategy ke bina production data store samajhna.

Beginner best practices

• Always parameterized values use karo.
• Schema me meaningful constraints rakho.
• Writes ko appropriate transactions me group karo.
• Queries me required columns explicitly select karo.
• Connection lifecycle clearly manage karo.
• Database-specific exceptions handle and log carefully karo.
• Tests me :memory: database useful ho sakta hai.
• Indexes real query needs ke basis par add karo.
• Real data ko source code/repository se separate rakho.

Chapter checklist

• SQLite and relational database basic difference/context samajh aaya?
• sqlite3.connect() and cursor use kar sakte ho?
• Table create with primary key/constraints kar sakte ho?
• Parameterized INSERT, SELECT, UPDATE, DELETE likh sakte ho?
• commit() and rollback() ka role clear hai?
• sqlite3.Row use kar sakte ho?
• Foreign key and basic JOIN idea clear hai?
• Aggregate queries and GROUP BY ka purpose samajh aaya?
• Indexes ke benefit/cost ka basic idea hai?
• SQL injection avoid karne ka correct pattern clear hai?

Open SQLite practice starter →