Python Notes
HubHistory of Python - Complete Guide 2026Python Applications - Complete Guide 2026Python Features - Complete Guide 2026What is Python? - Complete Guide 2026Why Learn Python? - Complete Guide 2026Your First Python Program – Hello World & Beyond 2026How to Install Python on Windows, Mac & Linux – Complete Guide 2026Install VS Code for Python – Complete Setup Guide with Extensions 2026Python Environment Setup – Virtual Environments, pip & Project Structure 2026Python IDLE – Complete Guide for Beginners 2026Python CommentsPython Data TypesPython Input and OutputPython OperatorsPython SyntaxPython Strings BasicsPython Type CastingPython VariablesBreak, Continue and Pass in PythonElif Statement in PythonFor Loop in PythonIf-Else Statement in PythonIf Statement in PythonNested If in PythonPattern Programs in PythonWhile Loop in PythonDecorators in PythonFunction Arguments in PythonFunctions Basics in PythonGenerators in PythonLambda Functions in PythonRecursive Functions in PythonReturn Statement in PythonScope of Variables in PythonAbstraction in PythonClasses and Objects in PythonConstructors in PythonEncapsulation in PythonInheritance in PythonMagic Methods (Dunder Methods) in PythonMethod Overriding in PythonPolymorphism in PythonPython Comprehensions – List, Dict, Set & GeneratorPython Dictionaries – Key-Value StorePython Dictionary Methods – Complete ReferencePython List Methods – All Built-in MethodsPython Lists – Complete GuidePython Set Methods – Complete ReferencePython Sets – Unordered Unique CollectionsPython Tuples – Immutable SequencesPython Asynchronous Programming with asyncioPython Context ManagersAdvanced Python DecoratorsAdvanced Python GeneratorsPython Iterators - Advanced GuidePython Memory ManagementPython MultiprocessingPython MultithreadingCRUD Operations in Python DatabasesMySQL with Python (mysql-connector-python)ORM Introduction with SQLAlchemyPostgreSQL with Python (psycopg2)SQLite3 with PythonIntroduction to DjangoDjango Models and ORMDjango REST API with DRFDjango ViewsIntroduction to FlaskFlask RoutingFlask Templates with Jinja2Email Automation with PythonPDF Automation with PythonSelenium Web Automation with PythonTask Scheduler with PythonWeb Scraping with PythonData Analysis with PythonData Cleaning with PythonData Visualization ProjectData Visualization with MatplotlibNumPy IntroductionPandas IntroductionStatistical Visualization with SeabornClassification AlgorithmsClustering AlgorithmsIntroduction to Machine LearningMachine Learning Project: Churn PredictionModel Evaluation and SelectionRegression AlgorithmsSave pipeline (includes preprocessing + model)AI Assistant ProjectCalculator ProjectChatbot ProjectExpense Tracker ProjectFace Detection ProjectPassword Generator ProjectWeather App ProjectPython Coding Interview QuestionsPython OOP Interview Questions and AnswersPython Master CheatsheetPython Interview Questions and AnswersPython Best PracticesCommon Python Errors and SolutionsPython Debugging TechniquesPython Learning Resources
Complete guide to SQLite3 with Python - creating databases, CRUD operations, transactions, error handling, and best practices with detailed Hindi explanations and code examples.
Introduction
SQLite Python ke saath built-in database hai — kisi extra installation ki zaroorat nahi! sqlite3 module Python Standard Library ka hissa hai. Yeh serverless, file-based database hai jo development, testing, aur small-to-medium applications ke liye perfect hai.
2. Creating Tables
Example
import sqlite3
with sqlite3.connect(":memory:") as conn:
cursor = conn.cursor()
# Simple table create
cursor.execute("""
CREATE TABLE IF NOT EXISTS students (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
age INTEGER,
grade TEXT,
marks REAL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
""")
# Multiple tables with relationships
cursor.execute("""
CREATE TABLE IF NOT EXISTS courses (
id INTEGER PRIMARY KEY AUTOINCREMENT,
course_name TEXT NOT NULL UNIQUE,
instructor TEXT,
credits INTEGER
)
""")
cursor.execute("""
CREATE TABLE IF NOT EXISTS enrollments (
id INTEGER PRIMARY KEY AUTOINCREMENT,
student_id INTEGER,
course_id INTEGER,
enrollment_date DATE DEFAULT CURRENT_DATE,
FOREIGN KEY (student_id) REFERENCES students(id),
FOREIGN KEY (course_id) REFERENCES courses(id)
)
""")
conn.commit()
print("Tables created successfully!")
# Check tables
cursor.execute("SELECT name FROM sqlite_master WHERE type='table'")
tables = cursor.fetchall()
print(f"Tables: {[t[0] for t in tables]}")
# Output: Tables: ['students', 'courses', 'enrollments']
3. INSERT — Adding Data
Example
import sqlite3
with sqlite3.connect(":memory:") as conn:
cursor = conn.cursor()
cursor.execute("""
CREATE TABLE students (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT, age INTEGER, grade TEXT, marks REAL
)
""")
# Single insert with placeholders (SAFE — prevents SQL injection)
cursor.execute(
"INSERT INTO students (name, age, grade, marks) VALUES (?, ?, ?, ?)",
("Alice", 20, "A", 95.5)
)
print(f"Inserted row ID: {cursor.lastrowid}") # 1
# Named placeholders (more readable)
cursor.execute(
"INSERT INTO students (name, age, grade, marks) VALUES (:name, :age, :grade, :marks)",
{"name": "Bob", "age": 22, "grade": "B", "marks": 82.0}
)
# Batch insert — executemany()
students_data = [
("Charlie", 21, "A", 91.0),
("Diana", 19, "B", 78.5),
("Eve", 23, "A", 88.0),
("Frank", 20, "C", 65.0),
]
cursor.executemany(
"INSERT INTO students (name, age, grade, marks) VALUES (?, ?, ?, ?)",
students_data
)
print(f"Batch inserted: {cursor.rowcount} rows")
conn.commit()
print("Data committed!")
# Verify count
cursor.execute("SELECT COUNT(*) FROM students")
print(f"Total students: {cursor.fetchone()[0]}") # 6
📝 Hindi Explanation
Parameterized queries (?placeholders) SQL injection prevent karte hain. KABHI BHAI string formatting se query mat banao: ``python # ❌ DANGEROUS — SQL injection possible! cursor.execute(f"INSERT INTO students VALUES ('{name}')") # ✅ SAFE — parameterized query cursor.execute("INSERT INTO students VALUES (?)", (name,))``
4. SELECT — Reading Data
Example
import sqlite3
def setup_db():
conn = sqlite3.connect(":memory:")
c = conn.cursor()
c.execute("CREATE TABLE students (id INTEGER PRIMARY KEY, name TEXT, age INTEGER, grade TEXT, marks REAL)")
c.executemany("INSERT INTO students VALUES (?,?,?,?,?)", [
(1,"Alice",20,"A",95.5), (2,"Bob",22,"B",82.0),
(3,"Charlie",21,"A",91.0), (4,"Diana",19,"B",78.5),
(5,"Eve",23,"A",88.0), (6,"Frank",20,"C",65.0),
])
conn.commit()
return conn
with setup_db() as conn:
cursor = conn.cursor()
# fetchone() — ek row
cursor.execute("SELECT * FROM students WHERE id = ?", (1,))
row = cursor.fetchone()
print(f"fetchone: {row}")
# Output: (1, 'Alice', 20, 'A', 95.5)
# fetchall() — saari rows
cursor.execute("SELECT name, marks FROM students ORDER BY marks DESC")
rows = cursor.fetchall()
for name, marks in rows:
print(f" {name}: {marks}")
# fetchmany(n) — n rows
cursor.execute("SELECT * FROM students")
batch = cursor.fetchmany(3)
print(f"\nFirst 3 rows: {batch}")
# Iteration (memory efficient for large results)
cursor.execute("SELECT * FROM students WHERE grade = ?", ("A",))
print("\nA grade students:")
for row in cursor:
print(f" {row}")
# WHERE, ORDER BY, LIMIT
cursor.execute("""
SELECT name, marks
FROM students
WHERE marks > ? AND age <= ?
ORDER BY marks DESC
LIMIT ?
""", (80, 22, 3))
top3 = cursor.fetchall()
print(f"\nTop 3 students (marks>80, age<=22): {top3}")
5. UPDATE and DELETE
Example
import sqlite3
with sqlite3.connect(":memory:") as conn:
cursor = conn.cursor()
cursor.execute("CREATE TABLE students (id INTEGER PRIMARY KEY, name TEXT, marks REAL)")
cursor.executemany("INSERT INTO students VALUES (?,?,?)", [
(1,"Alice",85.0), (2,"Bob",72.0), (3,"Charlie",91.0)
])
conn.commit()
# UPDATE single record
cursor.execute("UPDATE students SET marks = ? WHERE id = ?", (90.0, 1))
print(f"Updated {cursor.rowcount} row(s)")
# UPDATE multiple records
cursor.execute("UPDATE students SET marks = marks * 1.05 WHERE marks < ?", (80,))
print(f"Updated {cursor.rowcount} row(s) with 5% bonus")
# Verify update
cursor.execute("SELECT * FROM students")
print("\nAfter update:")
for row in cursor.fetchall():
print(f" {row}")
# DELETE specific record
cursor.execute("DELETE FROM students WHERE id = ?", (2,))
print(f"\nDeleted {cursor.rowcount} row(s)")
# DELETE with condition
cursor.execute("DELETE FROM students WHERE marks < ?", (70,))
print(f"Deleted {cursor.rowcount} low-scoring students")
# Delete all (without WHERE)
# cursor.execute("DELETE FROM students") # Careful!
conn.commit()
cursor.execute("SELECT * FROM students")
print("\nFinal data:", cursor.fetchall())
6. Row Factory — Dict-like Access
Example
import sqlite3
# Default: rows are tuples
with sqlite3.connect(":memory:") as conn:
conn.execute("CREATE TABLE users (id INTEGER, name TEXT, email TEXT)")
conn.execute("INSERT INTO users VALUES (1, 'Alice', 'alice@example.com')")
conn.commit()
cursor = conn.cursor()
cursor.execute("SELECT * FROM users")
row = cursor.fetchone()
print(f"Tuple access: {row[1]}") # Position-based
# sqlite3.Row — dict-like access
conn.row_factory = sqlite3.Row
cursor2 = conn.cursor()
cursor2.execute("SELECT * FROM users")
row2 = cursor2.fetchone()
print(f"Row access: {row2['name']}") # Name-based — much cleaner!
print(f"Columns: {row2.keys()}") # ['id', 'name', 'email']
print(f"Dict: {dict(row2)}")
# Output: {'id': 1, 'name': 'Alice', 'email': 'alice@example.com'}
# Custom row factory — returns dict
def dict_factory(cursor, row):
fields = [description[0] for description in cursor.description]
return dict(zip(fields, row))
conn.row_factory = dict_factory
cursor3 = conn.cursor()
cursor3.execute("SELECT * FROM users")
row3 = cursor3.fetchone()
print(f"Dict row: {row3}")
📝 Hindi Explanation
Row Factory se aap control kar sakte ho ki rows kaise return hogi.sqlite3.Rowdictionary-style access deta hai (row['column_name']), jo code ko bahut readable banata hai. Customdict_factorypure Python dict return karta hai.
7. Transactions
Example
import sqlite3
with sqlite3.connect(":memory:") as conn:
cursor = conn.cursor()
cursor.execute("CREATE TABLE accounts (id INTEGER PRIMARY KEY, name TEXT, balance REAL)")
cursor.execute("INSERT INTO accounts VALUES (1, 'Alice', 1000.0)")
cursor.execute("INSERT INTO accounts VALUES (2, 'Bob', 500.0)")
conn.commit()
def transfer_money(conn, from_id, to_id, amount):
"""Atomic transfer — ya sab hoga ya kuch nahi"""
cursor = conn.cursor()
try:
# Debit
cursor.execute("UPDATE accounts SET balance = balance - ? WHERE id = ?",
(amount, from_id))
if cursor.rowcount == 0:
raise ValueError(f"Account {from_id} not found")
# Verify sufficient balance
cursor.execute("SELECT balance FROM accounts WHERE id = ?", (from_id,))
balance = cursor.fetchone()[0]
if balance < 0:
raise ValueError("Insufficient balance!")
# Credit
cursor.execute("UPDATE accounts SET balance = balance + ? WHERE id = ?",
(amount, to_id))
if cursor.rowcount == 0:
raise ValueError(f"Account {to_id} not found")
conn.commit()
print(f"Transfer of {amount} successful!")
except Exception as e:
conn.rollback() # Sab kuch undo kar do
print(f"Transfer failed: {e}")
# Successful transfer
transfer_money(conn, 1, 2, 300)
cursor.execute("SELECT * FROM accounts")
for row in cursor.fetchall():
print(f" Account {row[0]} ({row[1]}): ${row[2]:.2f}")
# Failed transfer (insufficient balance)
transfer_money(conn, 2, 1, 10000) # Bob only has 800
8. Error Handling
python example
import sqlite3
def safe_db_operation():
try:
with sqlite3.connect(":memory:") as conn:
cursor = conn.cursor()
cursor.execute("CREATE TABLE items (id INTEGER PRIMARY KEY, name TEXT UNIQUE)")
conn.commit()
# IntegrityError — unique constraint violation
try:
cursor.execute("INSERT INTO items VALUES (1, 'Apple')")
cursor.execute("INSERT INTO items VALUES (1, 'Apple')") # Duplicate PK!
conn.commit()
except sqlite3.IntegrityError as e:
print(f"IntegrityError: {e}")
conn.rollback()
# OperationalError — syntax error
try:
cursor.execute("SELEKT * FROM items") # Typo
except sqlite3.OperationalError as e:
print(f"OperationalError: {e}")
# ProgrammingError — incorrect number of bindings
try:
cursor.execute("INSERT INTO items VALUES (?, ?)", (2,)) # Missing value
except sqlite3.ProgrammingError as e:
print(f"ProgrammingError: {e}")
except sqlite3.DatabaseError as e:
print(f"Database error: {e}")
except Exception as e:
print(f"Unexpected error: {e}")
safe_db_operation()9. Advanced Queries
Example
import sqlite3
with sqlite3.connect(":memory:") as conn:
conn.row_factory = sqlite3.Row
cursor = conn.cursor()
# Setup sample data
cursor.executescript("""
CREATE TABLE departments (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE employees (
id INTEGER PRIMARY KEY,
name TEXT,
salary REAL,
dept_id INTEGER,
FOREIGN KEY(dept_id) REFERENCES departments(id)
);
INSERT INTO departments VALUES (1,'Engineering'),(2,'Marketing'),(3,'HR');
INSERT INTO employees VALUES
(1,'Alice',90000,1),(2,'Bob',75000,1),(3,'Carol',60000,2),
(4,'Dave',55000,2),(5,'Eve',70000,3),(6,'Frank',80000,1);
""")
conn.commit()
# JOIN query
cursor.execute("""
SELECT e.name, e.salary, d.name AS department
FROM employees e
JOIN departments d ON e.dept_id = d.id
ORDER BY e.salary DESC
""")
print("Employees with departments:")
for row in cursor.fetchall():
print(f" {row['name']} - ${row['salary']:,.0f} - {row['department']}")
# Aggregate queries
cursor.execute("""
SELECT d.name, COUNT(*) AS count, AVG(e.salary) AS avg_salary, MAX(e.salary) AS max_salary
FROM employees e
JOIN departments d ON e.dept_id = d.id
GROUP BY d.id
HAVING COUNT(*) > 1
ORDER BY avg_salary DESC
""")
print("\nDepartment statistics:")
for row in cursor.fetchall():
print(f" {row['name']}: {row['count']} employees, avg=${row['avg_salary']:,.0f}")
# Subquery
cursor.execute("""
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
ORDER BY salary DESC
""")
print("\nAbove average salary:")
for row in cursor.fetchall():
print(f" {row['name']}: ${row['salary']:,.0f}")
10. Best Practices
python example
import sqlite3
from contextlib import contextmanager
# ✅ Use context manager
@contextmanager
def get_db(db_path=":memory:"):
conn = sqlite3.connect(db_path)
conn.row_factory = sqlite3.Row
conn.execute("PRAGMA foreign_keys = ON") # Enable FK constraints
try:
yield conn
conn.commit()
except Exception:
conn.rollback()
raise
finally:
conn.close()
with get_db() as db:
db.execute("CREATE TABLE test (id INTEGER PRIMARY KEY, value TEXT)")
db.execute("INSERT INTO test VALUES (1, 'hello')")
row = db.execute("SELECT * FROM test WHERE id = 1").fetchone()
print(dict(row))
# ✅ Enable WAL mode for better concurrent read performance
with sqlite3.connect("mydb.db") as conn:
conn.execute("PRAGMA journal_mode = WAL")
conn.execute("PRAGMA synchronous = NORMAL")
# ✅ Use indexes for frequently queried columns
with sqlite3.connect(":memory:") as conn:
conn.execute("CREATE TABLE logs (id INTEGER PRIMARY KEY, user_id INTEGER, action TEXT, timestamp TEXT)")
conn.execute("CREATE INDEX idx_logs_user ON logs(user_id)") # Fast user queries
conn.execute("CREATE INDEX idx_logs_timestamp ON logs(timestamp)")
# ❌ Never do this (SQL injection):
# name = input("Name: ")
# cursor.execute(f"SELECT * FROM users WHERE name = '{name}'")
# ✅ Always use parameterized queries
name = "Alice'; DROP TABLE users; --" # Malicious input
# cursor.execute("SELECT * FROM users WHERE name = ?", (name,)) # Safe!Summary
| Operation | SQL | Python |
|---|---|---|
| Connect | — | sqlite3.connect("db.db") |
| Create table | CREATE TABLE | cursor.execute() |
| Insert | INSERT INTO | cursor.execute() / executemany() |
| Select | SELECT | fetchone() / fetchall() |
| Update | UPDATE | cursor.execute() |
| Delete | DELETE | cursor.execute() |
| Commit | — | conn.commit() |
| Rollback | — | conn.rollback() |
SQLite is great for: Development/testing, mobile apps, embedded systems, small datasets (up to a few GB). For production web apps, consider PostgreSQL or MySQL.
📤 Output Examples
Section 2 - Creating Tables Output:
Output
Tables created successfully! Tables: ['students', 'courses', 'enrollments']
Section 3 - INSERT Output:
Output
Inserted row ID: 1 Batch inserted: 4 rows Data committed! Total students: 6
Section 4 - SELECT Output:
Output
fetchone: (1, 'Alice', 20, 'A', 95.5)
Alice: 95.5
Charlie: 91.0
Eve: 88.0
Bob: 82.0
Diana: 78.5
Frank: 65.0
First 3 rows: [(1, 'Alice', 20, 'A', 95.5), (2, 'Bob', 22, 'B', 82.0), (3, 'Charlie', 21, 'A', 91.0)]
A grade students:
(1, 'Alice', 20, 'A', 95.5)
(3, 'Charlie', 21, 'A', 91.0)
(5, 'Eve', 23, 'A', 88.0)
Top 3 students (marks>80, age<=22): [('Alice', 95.5), ('Charlie', 91.0), ('Bob', 82.0)]Section 5 - UPDATE and DELETE Output:
Output
Updated 1 row(s) Updated 1 row(s) with 5% bonus After update: (1, 'Alice', 90.0) (2, 'Bob', 75.6) (3, 'Charlie', 91.0) Deleted 1 row(s) Deleted 0 low-scoring students Final data: [(1, 'Alice', 90.0), (3, 'Charlie', 91.0)]
Section 6 - Row Factory Output:
Output
Tuple access: Alice
Row access: Alice
Columns: ['id', 'name', 'email']
Dict: {'id': 1, 'name': 'Alice', 'email': 'alice@example.com'}
Dict row: {'id': 1, 'name': 'Alice', 'email': 'alice@example.com'}Section 7 - Transactions Output:
Output
Transfer of 300 successful! Account 1 (Alice): $700.00 Account 2 (Bob): $800.00 Transfer failed: Insufficient balance!
Section 8 - Error Handling Output:
Output
IntegrityError: UNIQUE constraint failed: items.id OperationalError: near "SELEKT": syntax error ProgrammingError: Incorrect number of bindings supplied. The current statement uses 2, and there are 1 supplied.
Section 9 - Advanced Queries Output:
Output
Employees with departments: Alice - $90,000 - Engineering Frank - $80,000 - Engineering Bob - $75,000 - Engineering Eve - $70,000 - HR Carol - $60,000 - Marketing Dave - $55,000 - Marketing Department statistics: Engineering: 3 employees, avg=$81,667 Marketing: 2 employees, avg=$57,500 Above average salary: Alice: $90,000 Frank: $80,000 Bob: $75,000 Eve: $70,000
Section 10 - Best Practices Output:
Output
{'id': 1, 'value': 'hello'}⚠️ Common Mistakes
❌ Mistake 1: Connection close karna bhool jaana
python example
# ❌ Wrong — connection leak hoti hai
conn = sqlite3.connect("data.db")
cursor = conn.cursor()
cursor.execute("SELECT * FROM users")
# conn.close() bhool gaye!
# ✅ Right — always use context manager
with sqlite3.connect("data.db") as conn:
cursor = conn.cursor()
cursor.execute("SELECT * FROM users")💡 with statement use karo taaki connection hamesha close ho, chahe error aaye ya na aaye.❌ Mistake 2: String formatting se SQL queries banana (SQL Injection)
python example
# ❌ DANGEROUS — SQL injection attack possible!
name = input("Enter name: ")
cursor.execute(f"SELECT * FROM users WHERE name = '{name}'")
# ✅ SAFE — parameterized queries
cursor.execute("SELECT * FROM users WHERE name = ?", (name,))💡 User input kabhi bhi directly SQL string mein mat dalo — hamesha ? placeholder use karo!❌ Mistake 3: commit() karna bhool jaana
python example
# ❌ Data save nahi hoga
cursor.execute("INSERT INTO users VALUES (1, 'Alice')")
# bhool gaye: conn.commit()
# ✅ Always commit after writes
cursor.execute("INSERT INTO users VALUES (1, 'Alice')")
conn.commit()💡 INSERT, UPDATE, DELETE ke baad conn.commit() zaroori hai warna changes disk pe save nahi honge!❌ Mistake 4: fetchone() ka result None check na karna
Example
# ❌ NoneType error aayega agar row exist nahi karti
cursor.execute("SELECT * FROM users WHERE id = ?", (999,))
row = cursor.fetchone()
print(row[0]) # TypeError: 'NoneType' object is not subscriptable
# ✅ Pehle None check karo
row = cursor.fetchone()
if row:
print(row[0])
else:
print("User not found!")
❌ Mistake 5: executemany() mein single tuple ki trailing comma bhoolna
python example
# ❌ Wrong — single value tuple needs trailing comma
cursor.execute("INSERT INTO items (name) VALUES (?)", ("Apple")) # Error!
# ✅ Right — tuple ke liye trailing comma zaroori
cursor.execute("INSERT INTO items (name) VALUES (?)", ("Apple",))💡 Single value tuple banane ke liye ("value",) likhna hota hai — comma ke bina yeh sirf string hai!❌ Mistake 6: Foreign key constraints enable na karna
python example
# ❌ By default SQLite foreign keys enforce nahi karta
conn = sqlite3.connect("data.db")
# ✅ Explicitly enable karo
conn.execute("PRAGMA foreign_keys = ON")💡 SQLite mein foreign keys by default OFF hoti hain — har connection pe PRAGMA se ON karo.
❌ Mistake 7: Large datasets ke liye fetchall() use karna
python example
# ❌ Memory waste — 1 million rows ek saath RAM mein
cursor.execute("SELECT * FROM huge_table")
all_rows = cursor.fetchall() # Out of memory possible!
# ✅ Iterate karo — memory efficient
cursor.execute("SELECT * FROM huge_table")
for row in cursor:
process(row)💡 Bahut badi tables ke liye fetchall() avoid karo — direct cursor iteration memory-efficient hai.✅ Key Takeaways
- 🔹 SQLite3 Python mein built-in hai — koi extra installation nahi chahiye,
import sqlite3se shuru karo - 🔹 Context manager (
with) hamesha use karo — connection leak prevent hota hai aur auto-close milta hai - 🔹 Parameterized queries (
?) mandatory hain — SQL injection se bachne ka yahi sabse safe tarika hai - 🔹
conn.commit()bhoolna = data loss — write operations ke baad commit zaroori hai - 🔹
sqlite3.Rowfactory use karo — column name se access karna (row['name']) position-based access se zyada readable hai - 🔹 Transactions (try/except + rollback) — money transfer jaise critical operations ke liye atomic transactions use karo
- 🔹
executemany()batch inserts ke liye — loop mein single insert se kahin zyada fast hai - 🔹 Error handling structured rakho —
IntegrityError,OperationalErroralag-alag handle karo - 🔹 Indexes create karo frequently queried columns pe — SELECT queries dramatically fast ho jaati hain
- 🔹 Production ke liye PostgreSQL/MySQL consider karo — SQLite small-medium apps ke liye best hai, high-concurrency ke liye nahi
❓ FAQ
Q1: SQLite aur MySQL/PostgreSQL mein kya difference hai?
Answer: SQLite ek serverless, file-based database hai — koi server process nahi chalti. MySQL/PostgreSQL server-based hain jo multiple clients handle kar sakte hain simultaneously. SQLite single-user apps, mobile apps, testing ke liye perfect hai. Production web apps ke liye PostgreSQL recommended hai kyunki woh concurrent writes better handle karta hai.
Q2: :memory: database ka use kab karna chahiye?
Answer: Jab aapko temporary data store karna ho jo program band hone pe delete ho jaaye — testing, caching, ya temporary calculations ke liye. In-memory database bahut fast hota hai kyunki disk I/O nahi hota, lekin program end hone pe sab data lost ho jaata hai.
Q3: cursor.execute() aur conn.execute() mein kya fark hai?
Answer: Dono kaam karte hain! conn.execute() internally ek temporary cursor create karke query run karta hai. Simple queries ke liye conn.execute() use kar sakte ho. Lekin agar aapko lastrowid, rowcount ya fetchone() chahiye toh explicit cursor better hai kyunki uska reference aapke paas rehta hai.
Q4: Kya SQLite multiple threads se safe hai?
Answer: Haan, lekin check_same_thread=False pass karna padta hai:
python example
conn = sqlite3.connect("data.db", check_same_thread=False)Default mein SQLite sirf usi thread se access allow karta hai jisne connection banaya. WAL mode enable karne se concurrent reads better perform karte hain.
Q5: SQL injection kya hai aur kaise prevent karein?
Answer: SQL injection mein attacker malicious SQL code input field mein dalta hai. For example: ' OR 1=1; DROP TABLE users; -- input karne se poori table delete ho sakti hai. Prevention: Hamesha parameterized queries use karo (? placeholders). Kabhi bhi f-strings ya string concatenation se SQL query mat banao.
Q6: commit() aur rollback() ka difference kya hai?
Answer: commit() changes ko permanently disk pe save karta hai. rollback() last commit ke baad ke saare changes undo kar deta hai — jaise kuch hua hi nahi. Transaction mein error aaye toh rollback() se database consistent state mein wapas aa jaata hai.
Q7: SQLite mein maximum kitna data store kar sakte hain?
Answer: Theoretically 281 TB tak, lekin practically 1-2 GB tak best performance milti hai. Iske upar PostgreSQL ya MySQL better choice hai. SQLite single-writer hai toh heavy write operations mein slow ho sakta hai.
Q8: executemany() aur loop mein execute() mein kya difference hai?
Answer: executemany() internally optimized hai — ek hi transaction mein saari rows insert karta hai jo 10x-100x faster hai. Loop mein individual execute() calls har baar overhead add karte hain. Bulk inserts ke liye hamesha executemany() use karo.
🎯 Interview Questions
Q1: SQLite3 module Python mein kaise use karte hain? Basic workflow explain karo.
Answer: SQLite3 Python ki standard library mein built-in hai. Basic workflow:
import sqlite3— module import karoconn = sqlite3.connect("db.db")— connection establish karocursor = conn.cursor()— cursor object banaocursor.execute("SQL QUERY")— queries execute karoconn.commit()— changes save karoconn.close()— connection band karo
Best practice: with sqlite3.connect("db.db") as conn: context manager use karo for automatic cleanup.
Q2: Parameterized queries kya hain aur kyun zaroori hain?
Answer: Parameterized queries mein SQL string mein directly values concatenate karne ki jagah placeholders (? ya :name) use karte hain:
python example
# Parameterized (Safe)
cursor.execute("SELECT * FROM users WHERE name = ?", (name,))Yeh SQL injection prevent karta hai — agar user malicious input de bhi toh database engine usse data treat karta hai, executable SQL nahi. Security ke liye yeh mandatory practice hai.
Q3: fetchone(), fetchall(), aur fetchmany() mein kya difference hai?
Answer:
fetchone()— Ek row return karta hai (yaNoneagar koi row nahi). Memory efficient for single results.fetchall()— Saari rows ek list mein return karta hai. Chhote datasets ke liye theek, large data pe memory issue.fetchmany(n)—nrows return karta hai. Batch processing ke liye useful — memory aur performance ka balance.- Cursor iteration (
for row in cursor) — Sabse memory-efficient, rows lazily fetch hoti hain.
Q4: Transaction kya hota hai? ACID properties explain karo SQLite ke context mein.
Answer: Transaction ek group of operations hai jo all-or-nothing execute hota hai:
- Atomicity — Ya toh saare changes apply honge, ya koi nahi (rollback)
- Consistency — Database hamesha valid state mein rahega
- Isolation — Concurrent transactions ek dusre ko affect nahi karenge
- Durability — Commit ke baad data permanently saved hai
SQLite mein conn.commit() transaction complete karta hai, conn.rollback() undo karta hai. Context manager (with conn:) automatic commit/rollback handle karta hai.
Q5: SQLite mein indexing kya hai aur kab use karni chahiye?
Answer: Index ek lookup structure hai jo SELECT queries fast karta hai (jaise book ka index):
python example
cursor.execute("CREATE INDEX idx_name ON users(name)")Kab use karein: Frequently searched columns pe (WHERE clause, JOIN conditions, ORDER BY). Kab na use karein: Small tables pe, ya jahan writes bahut zyada hain (kyunki har INSERT/UPDATE pe index bhi update hota hai).
Q6: sqlite3.Row factory kaise kaam karta hai aur iske advantages kya hain?
Answer: Default mein rows tuples return hoti hain (row[0], row[1]). sqlite3.Row set karne se dictionary-style access milta hai:
Example
conn.row_factory = sqlite3.Row
row = cursor.fetchone()
print(row['name']) # Column name se access
print(row.keys()) # All column names
Advantages: Code readable hota hai, column order change hone pe code nahi tootta, dict(row) se easily JSON-compatible dict milta hai.
Q7: SQLite ke limitations kya hain? Kab PostgreSQL/MySQL prefer karenge?
Answer: SQLite limitations:
- Single writer — ek time pe sirf ek write operation (concurrent writes slow)
- No user management — authentication/authorization nahi hai
- Limited data types — sirf NULL, INTEGER, REAL, TEXT, BLOB
- No network access — sirf local file access
PostgreSQL prefer karein jab: Multiple users, high concurrency, large datasets (>2GB), complex queries, production web applications, ya network access chahiye.
Q8: Context manager (with statement) database operations mein kyun important hai?
Answer: Context manager ensures:
- Auto-commit — agar koi error nahi aaya toh changes automatically commit ho jaate hain
- Auto-rollback — exception pe automatically rollback hota hai
- Resource cleanup — connection properly close hoti hai
- No leaks — chahe exception aaye ya na aaye, resources free ho jaate hain
python example
with sqlite3.connect("db.db") as conn:
conn.execute("INSERT INTO users VALUES (1, 'Alice')")
# Auto-committed if no error, auto-rolled-back if errorQ9: executemany() aur executescript() mein kya difference hai?
Answer:
executemany(sql, data_list)— Same SQL statement ko multiple data sets ke saath execute karta hai (batch inserts). Parameterized hai, safe hai.executescript(sql_string)— Multiple different SQL statements ek saath execute karta hai (semicolon-separated). Yeh parameterized nahi hai, aur implicit commit karta hai pehle. Schema setup ke liye useful hai.
Example
# executemany — same query, different data
cursor.executemany("INSERT INTO users VALUES (?,?)", [(1,"A"),(2,"B")])
# executescript — different queries
cursor.executescript("CREATE TABLE x(id); CREATE TABLE y(id);")
Q10: Database migration kya hota hai aur SQLite mein kaise handle karein?
Answer: Migration matlab database schema ko safely update karna (columns add/remove, tables modify) bina existing data lose kiye. SQLite mein approach:
Example
# Version tracking with user_version pragma
cursor.execute("PRAGMA user_version")
version = cursor.fetchone()[0]
if version == 0:
cursor.execute("ALTER TABLE users ADD COLUMN email TEXT")
cursor.execute("PRAGMA user_version = 1")
conn.commit()
Production mein Alembic (SQLAlchemy ke saath) ya manual migration scripts use karte hain. Hamesha backup lo migration se pehle!
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for SQLite3 with Python.
Interview Use
Prepare one clear explanation, one practical example, and one common mistake for this Python Master Course topic.
Search Terms
python-master-course, python master course, python, master, course, databases, sqlite3, sqlite3 with python
Related Python Master Course Topics
Continue learning this concept
DatabasesCRUD Operations in Python DatabasesMaster CRUD (Create, Read, Update, Delete) operations across SQLite, MySQL, and PostgreSQL with Python, including best practices, error handling, and real-world examples.DatabasesMySQL with Python (mysql-connector-python)Complete guide to connecting MySQL with Python using mysql-connector-python, performing CRUD operations, connection pooling, stored procedures, and best practices.DatabasesORM Introduction with SQLAlchemyIntroduction to ORM (Object-Relational Mapping) with SQLAlchemy - models, sessions, queries, relationships, migrations with Alembic, and best practices with Hindi explanations.DatabasesPostgreSQL with Python (psycopg2)Master PostgreSQL with Python using psycopg2, including connection setup, CRUD operations, transactions, connection pools, JSON support, and advanced features with Hindi explanations.Advanced PythonPython Asynchronous Programming with asyncioDeep dive into Python async/await, asyncio event loop, coroutines, tasks, async generators, aiohttp, and real-world asynchronous patterns with Hindi explanations.Advanced PythonPython Context ManagersComprehensive guide to Python context managers using with statement, __enter__ and __exit__ methods, contextlib module, nested context managers and custom resource management.