Python Notes
Master CRUD (Create, Read, Update, Delete) operations across SQLite, MySQL, and PostgreSQL with Python, including best practices, error handling, and real-world examples.
Introduction
CRUD = Create, Read, Update, Delete — yeh char operations database programming ka foundation hain. Is guide mein hum SQLite ke saath CRUD seekhenge (concepts MySQL aur PostgreSQL pe bhi apply hote hain).
2. CREATE — Insert Operations
3. READ — Select Operations
📝 Hindi Explanation
Dynamic WHERE clause banane ka safe tarika:WHERE 1=1se shuru karo, phir conditionsANDse add karo. Parameters list mein append karo — SQL injection se safe, readable code.LIKE '%keyword%'se partial search hoti hai (case insensitive SQLite mein).
4. UPDATE — Modify Operations
5. DELETE — Remove Operations
6. Complete CRUD Example
7. Error Handling Patterns
import sqlite3
from enum import Enum
class CRUDError(Exception):
pass
class NotFoundError(CRUDError):
pass
class DuplicateError(CRUDError):
pass
def safe_crud(operation, conn, *args, **kwargs):
"""CRUD operation ko safely execute karo"""
try:
return operation(conn, *args, **kwargs)
except sqlite3.IntegrityError as e:
if "UNIQUE" in str(e):
raise DuplicateError(f"Duplicate entry: {e}")
raise CRUDError(f"Integrity error: {e}")
except sqlite3.OperationalError as e:
raise CRUDError(f"SQL error: {e}")
except Exception as e:
raise CRUDError(f"Unexpected error: {e}")
def get_or_404(conn, table, record_id):
"""Record dhundho ya NotFoundError raise karo"""
row = conn.execute(f"SELECT * FROM {table} WHERE id = ?", (record_id,)).fetchone()
if not row:
raise NotFoundError(f"{table} with id={record_id} not found")
return dict(row)Summary — CRUD Quick Reference
# SQLite CRUD Template
import sqlite3
with sqlite3.connect("mydb.db") as conn:
conn.row_factory = sqlite3.Row
# CREATE
cursor = conn.execute("INSERT INTO table (col1, col2) VALUES (?, ?)", (val1, val2))
new_id = cursor.lastrowid
# READ ONE
row = conn.execute("SELECT * FROM table WHERE id = ?", (id,)).fetchone()
# READ MANY
rows = conn.execute("SELECT * FROM table WHERE col = ?", (val,)).fetchall()
# READ with pagination
rows = conn.execute("SELECT * FROM table LIMIT ? OFFSET ?", (limit, offset)).fetchall()
# UPDATE
conn.execute("UPDATE table SET col1 = ? WHERE id = ?", (new_val, id))
# DELETE
conn.execute("DELETE FROM table WHERE id = ?", (id,))
conn.commit() # Always commit!| Operation | SQL | Python Method |
|---|---|---|
| Create | INSERT INTO | execute() / executemany() |
| Read All | SELECT * | fetchall() |
| Read One | SELECT WHERE id= | fetchone() |
| Update | UPDATE SET | execute() |
| Delete | DELETE WHERE | execute() |
| Count | SELECT COUNT(*) | fetchone()[0] |
| Aggregate | GROUP BY + AVG/SUM | fetchall() |
Remember: Always use parameterized queries (?in SQLite,%sin MySQL/PostgreSQL) to prevent SQL injection. Never format user input directly into SQL strings!
Output Examples 📋
Setup Output
DatabaseManager(db=':memory:')
CREATE Output
Created product ID: 1
Bulk created: 4 products
Upsert result: {'id': 1, 'action': 'updated'}READ Output
--- READ OPERATIONS ---
Product 1: Laptop — ₹75000
First 3 products:
[1] Laptop: ₹75000
[2] Phone: ₹25000
[3] Book: ₹500
Electronics (3 items):
Headphones: ₹3000
Phone: ₹25000
Laptop: ₹75000
Search 'phone' under ₹30000: ['Phone']
Stats: {'total_products': 5, 'categories': 3, 'avg_price': 22300.0, 'min_price': 500.0, 'max_price': 75000.0, 'total_stock': 375, 'inventory_value': 2287500.0}UPDATE Output
--- UPDATE OPERATIONS --- Updated product 1: True Product 1 new price: ₹70000 Updated 3 Electronics products (10% increase) Batch updated 2 products
DELETE Output
--- DELETE OPERATIONS --- Total products before: 5 Soft deleted product 4: True Hard deleted product 5: True Bulk deleted: 0 Total products after: 3
Complete CRUD Demo Output
=== TODO LIST CRUD DEMO === 1. CREATE todos: Created 5 todos 2. READ todos (by priority): [○] 1. Learn Python (P3) [○] 2. Build a project (P2) [○] 4. Write tests (P2) [○] 3. Read documentation (P1) [○] 5. Deploy application (P1) 3. READ high priority todos: - Learn Python - Build a project - Write tests 4. UPDATE — Mark 'Learn Python' as completed: Updated: Learn Python — completed: True 5. DELETE — Remove completed todos: Remaining todos: 4 6. STATISTICS: Total: 4, Done: 0, Avg Priority: 1.5
Error Handling Output
# DuplicateError example: DuplicateError: Duplicate entry: UNIQUE constraint failed: products.name # NotFoundError example: NotFoundError: products with id=999 not found
⚠️ Common Mistakes
❌ Mistake 1: SQL Injection — String Formatting Use Karna
# ❌ WRONG — SQL injection vulnerable!
name = input("Enter name: ")
cursor.execute(f"SELECT * FROM users WHERE name = '{name}'")
# ✅ RIGHT — Parameterized query
cursor.execute("SELECT * FROM users WHERE name = ?", (name,))Explanation: User input directly SQL string mein dalna bohot dangerous hai. Hacker'; DROP TABLE users; --type kar sakta hai! Always use?placeholders.
❌ Mistake 2: conn.commit() Bhool Jaana
# ❌ WRONG — Data save nahi hoga!
conn.execute("INSERT INTO products (name, price) VALUES (?, ?)", ("Pen", 10))
# commit karna bhool gaye...
# ✅ RIGHT — Always commit after write operations
conn.execute("INSERT INTO products (name, price) VALUES (?, ?)", ("Pen", 10))
conn.commit()Explanation: SQLite mein bina commit() ke data disk pe save nahi hota. Connection close hone pe changes lost ho jaate hain.❌ Mistake 3: Connection Close Na Karna
# ❌ WRONG — Resource leak!
conn = sqlite3.connect("mydb.db")
cursor = conn.execute("SELECT * FROM products")
results = cursor.fetchall()
# conn.close() bhool gaye...
# ✅ RIGHT — Context manager use karo
with sqlite3.connect("mydb.db") as conn:
results = conn.execute("SELECT * FROM products").fetchall()Explanation: Connection open chhod dena memory leak aur database lock issues create karta hai. with statement automatically handle karta hai.❌ Mistake 4: fetchone() pe None Check Na Karna
Explanation:fetchone()jab koi record nahi milta tohNonereturn karta hai. Directly access karna crash karega.
❌ Mistake 5: executemany() mein Wrong Tuple Format
Explanation: Python mein single element tuple ke liye trailing comma zaroori hai:("Alice",)≠("Alice"). Bina comma ke ye sirf string hai parentheses mein.
❌ Mistake 6: DELETE bina WHERE Clause ke
# ❌ DANGEROUS — Saara data delete ho jaayega!
conn.execute("DELETE FROM products")
conn.commit()
# ✅ SAFE — Always specify WHERE condition
conn.execute("DELETE FROM products WHERE id = ?", (product_id,))
conn.commit()Explanation:DELETEbinaWHEREke table ka saara data permanently delete kar deta hai. Production mein yeh disaster hai! Always WHERE clause lagao.
❌ Mistake 7: Transaction mein Error Handling Na Karna
# ❌ WRONG — Partial data save ho sakta hai!
conn.execute("INSERT INTO orders (...) VALUES (...)")
conn.execute("UPDATE inventory SET stock = stock - 1 WHERE ...") # Ye fail hua
conn.commit() # First INSERT committed, inconsistent state!
# ✅ RIGHT — try/except with rollback
try:
conn.execute("INSERT INTO orders (...) VALUES (...)")
conn.execute("UPDATE inventory SET stock = stock - 1 WHERE ...")
conn.commit()
except Exception:
conn.rollback() # Saare changes undo!
raiseExplanation: Multiple related operations mein agar ek fail ho toh saare rollback hone chahiye. Partial commit se data inconsistency hoti hai.
✅ Key Takeaways
- 🔑 CRUD = Create, Read, Update, Delete — yeh 4 operations har database application ka foundation hain, chahe SQLite ho ya PostgreSQL.
- 🛡️ Always use parameterized queries (
?for SQLite,%sfor MySQL/PostgreSQL) — SQL injection se protection ke liye string formatting KABHI use mat karo. - 🔄 Transaction management zaroori hai —
commit()successful operations ke baad,rollback()failures pe. Context managers (with) best practice hain. - 📦
executemany()bulk operations ke liye — Loop mein ek-ek insert karne se kahin zyada fast hai. Batch inserts performance 10x improve karte hain. - 🔍
fetchone()vsfetchall()vsfetchmany()— Single record ke liyefetchone(), saare records ke liyefetchall(), controlled batches ke liyefetchmany(size). - 🏗️
row_factory = sqlite3.Row— Dictionary-style access milta hai (row['name']), tuple indexing (row[0]) se kahin readable hai. - ⚡ Dynamic queries safely banao —
WHERE 1=1pattern use karo aur conditions dynamically add karo with proper parameterization. - 🗑️ Soft delete vs Hard delete — Production mein soft delete prefer karo (flag set karo), hard delete sirf cleanup scripts mein use karo.
- 📊 Aggregation queries (
COUNT,SUM,AVG,GROUP BY) — Statistics aur reporting ke liye Python mein loop chalane ki zaroorat nahi, SQL mein hi karo. - 🧹 Always close connections —
withstatement ya explicitconn.close()use karo, warna resource leaks aur database locks honge.
❓ FAQ
Q1: execute() aur executemany() mein kya difference hai?
Answer: execute() single SQL statement run karta hai ek set of parameters ke saath. executemany() same SQL statement ko multiple parameter sets ke saath run karta hai — essentially ek loop hai but C-level optimization ke saath. Jab 100+ records insert karne ho toh executemany() significantly faster hai kyunki SQL parsing sirf ek baar hoti hai.
Q2: fetchone(), fetchall(), aur fetchmany() kab use karein?
Answer: fetchone() — jab single record chahiye (login check, ID se lookup). fetchall() — jab saare results ek saath chahiye (small datasets). fetchmany(size) — jab large dataset hai aur memory bachani hai, batches mein process karna hai. Rule of thumb: 1000+ rows expected ho toh fetchmany() ya pagination use karo.
Q3: Soft delete aur hard delete mein kya farak hai? Kab kya use karein?
Answer: Hard delete = row permanently remove (DELETE FROM). Soft delete = ek flag set karo (is_deleted = TRUE ya deleted_at = timestamp). Production applications mein soft delete prefer karo kyunki: (1) Data recovery possible hai, (2) Audit trail maintain hota hai, (3) Foreign key references break nahi hote. Hard delete sirf archival/cleanup scripts mein use karo.
Q4: SQL injection kya hai aur parameterized queries kaise protect karti hain?
Answer: SQL injection ek attack hai jahan malicious user input SQL command change kar deta hai. Example: input "'; DROP TABLE users; --" dene se poora table delete ho sakta hai. Parameterized queries (? placeholders) mein database engine input ko sirf DATA treat karta hai, SQL command nahi — toh koi bhi malicious input safely escape ho jaata hai.
Q5: conn.commit() aur conn.rollback() kab use karein?
Answer: commit() — jab saare operations successfully complete ho jayein tab changes permanently save karo. rollback() — jab koi error aaye toh saare changes undo karo (transaction ke andar). Best practice: try/except block use karo — success pe commit, failure pe rollback. with sqlite3.connect() as context manager auto-commit karta hai agar koi exception nahi aaya.
Q6: row_factory = sqlite3.Row set karna kyun zaroori hai?
Answer: By default SQLite rows tuples return karta hai: (1, "Laptop", 75000). sqlite3.Row set karne se dictionary-like access milta hai: row['name'], row['price']. Yeh code readable aur maintainable banata hai. Column order change hone pe bhi code break nahi hota. Production code mein ALWAYS row_factory set karo.
Q7: SQLite mein PRAGMA foreign_keys = ON kyun zaroori hai?
Answer: SQLite by default foreign key constraints enforce NAHI karta — yeh backward compatibility ke liye hai. PRAGMA foreign_keys = ON explicitly enable karna padta hai HAR connection pe. Bina iske aap parent record delete kar sakte ho jabki child records exist karte hain — data integrity break ho jaati hai.
Q8: :memory: database kab use karein aur kab file-based?
Answer: :memory: — testing, prototyping, temporary data processing ke liye. RAM mein hota hai, fast hai, program end hone pe data lost. File-based ("myapp.db") — actual applications ke liye jahan data persist karna hai. Rule: development/testing mein :memory:, production mein file path ya proper database server (PostgreSQL/MySQL).
🎯 Interview Questions
Q1: CRUD operations kya hain? Real-world example ke saath explain karein.
Answer: CRUD = Create, Read, Update, Delete — yeh 4 fundamental operations hain jo kisi bhi data storage system pe apply hote hain. E-commerce example: Create = new product listing add karna, Read = product details dekhna/search karna, Update = price ya stock change karna, Delete = discontinued product remove karna. REST APIs mein CRUD directly HTTP methods se map hota hai: POST (Create), GET (Read), PUT/PATCH (Update), DELETE (Delete).
Q2: SQL injection kya hai? Python mein isse kaise prevent karein? Code example dein.
Answer: SQL injection ek security vulnerability hai jahan attacker malicious SQL code user input ke through inject karta hai. Prevention: parameterized queries use karo. SQLite mein ? placeholder, MySQL/PostgreSQL mein %s. Example: cursor.execute("SELECT * FROM users WHERE email = ?", (user_email,)). KABHI bhi f-strings ya .format() se SQL queries mat banao. ORMs (SQLAlchemy, Django ORM) bhi automatically parameterize karte hain.
Q3: executemany() vs loop mein execute() — performance difference explain karein.
Answer: executemany() internally optimized hai: SQL statement ek baar parse hota hai, phir multiple parameter sets ke saath execute hota hai. Loop mein execute() har iteration pe SQL parsing, planning, aur execution separately hoti hai. 10,000 rows insert karne pe executemany() typically 5-10x faster hota hai. Additionally, single transaction mein batch operation disk I/O reduce karta hai compared to implicit auto-commit per statement.
Q4: Database transactions kya hain? ACID properties explain karein.
Answer: Transaction ek ya multiple operations ka atomic unit hai — ya toh saare succeed karein ya saare fail. ACID: (1) Atomicity — all or nothing, partial execution nahi, (2) Consistency — transaction ke baad database valid state mein hona chahiye, (3) Isolation — concurrent transactions ek dusre ko interfere nahi karein, (4) Durability — committed data permanently saved hai (even after crash). Python mein: conn.commit() transaction complete karta hai, conn.rollback() undo karta hai.
Q5: fetchone(), fetchall(), fetchmany() mein kya difference hai? Memory implications?
Answer: fetchone() — ek row return karta hai, minimal memory. fetchall() — saari rows ek list mein load karta hai, large datasets pe memory heavy. fetchmany(size) — specified number of rows return karta hai, batch processing ke liye ideal. Memory consideration: 1 million rows fetchall() se load karna GB's memory le sakta hai. Solution: fetchmany(1000) ke saath loop chalao ya server-side pagination (LIMIT/OFFSET) use karo.
Q6: Soft delete vs Hard delete — production mein kaun sa approach better hai aur kyun?
Answer: Production mein soft delete better hai kyunki: (1) Accidental deletion recoverable hai, (2) Audit trail maintain hota hai (compliance requirements), (3) Foreign key references intact rehte hain, (4) Historical data analytics possible hai. Implementation: is_deleted BOOLEAN DEFAULT FALSE column add karo, queries mein WHERE is_deleted = FALSE filter lagao. Hard delete sirf use karo jab: GDPR compliance require kare, storage optimization needed ho, ya scheduled cleanup jobs mein.
Q7: Context manager (with statement) database connections ke saath kyun use karein?
Answer: Context manager ensure karta hai ki connection ALWAYS properly close ho — chahe exception aaye ya na aaye. Benefits: (1) Resource leak prevention — open connections database pool exhaust kar sakte hain, (2) Automatic cleanup — __exit__ method mein commit/rollback handle hota hai, (3) Exception safety — finally block manually likhne ki zaroorat nahi. sqlite3.connect() as context manager auto-commit karta hai success pe, but connection close NAHI karta — conn.close() separately chahiye ya nested with use karo.
Q8: Database indexing kya hai? CRUD operations pe iska kya impact hota hai?
Answer: Index ek data structure hai (usually B-tree) jo specific columns pe fast lookup enable karta hai — jaise book ka index page numbers quickly find karne deta hai. Impact on CRUD: READ — dramatically faster (O(log n) vs O(n)), especially WHERE, ORDER BY, JOIN pe. CREATE/UPDATE/DELETE — slightly slower kyunki har modification pe index bhi update hona chahiye. Best practice: frequently searched columns pe index banao, but over-indexing avoid karo (write-heavy tables pe).
Q9: Database connection pooling kya hai? Kab zaroorat hoti hai?
Answer: Connection pooling ek technique hai jahan pre-created connections ka pool maintain kiya jaata hai — har request pe new connection create karne ki jagah pool se reuse hota hai. Zaroorat: web applications jahan concurrent requests aati hain. Benefits: (1) Connection creation overhead avoid (TCP handshake, authentication), (2) Resource limit management, (3) Better performance under load. Python mein: sqlalchemy.create_engine(pool_size=10), ya psycopg2.pool.ThreadedConnectionPool. SQLite single-file hai toh pooling kam relevant hai, but PostgreSQL/MySQL mein critical hai.
Q10: ORM (SQLAlchemy/Django ORM) vs Raw SQL — kab kya use karein?
Answer: ORM use karein jab: (1) Standard CRUD operations hain, (2) Team mein SQL expertise varied hai, (3) Database switching possible hai, (4) Rapid development chahiye. Raw SQL use karein jab: (1) Complex queries (CTEs, window functions, custom joins), (2) Performance critical operations, (3) Database-specific features chahiye, (4) Bulk data processing. Best practice: 80% operations ORM se, 20% complex queries raw SQL se. SQLAlchemy dono support karta hai — ORM layer + raw text() queries.
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for CRUD Operations in Python Databases.
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, crud, operations
Related Python Master Course Topics