Python Notes
Master PostgreSQL with Python using psycopg2, including connection setup, CRUD operations, transactions, connection pools, JSON support, and advanced features with Hindi explanations.
Introduction
PostgreSQL duniya ka most advanced open-source relational database hai. Python ke saath use karne ke liye psycopg2 (most popular) ya asyncpg (async applications ke liye) use karte hain.
pip install psycopg2-binary # Binary distribution — no C compilation needed
# OR for production:
pip install psycopg2 # Requires PostgreSQL dev libraries2. Creating Tables
import psycopg2
def create_tables(conn):
"""Schema create karo"""
cursor = conn.cursor()
try:
# Students table with PostgreSQL-specific types
cursor.execute("""
CREATE TABLE IF NOT EXISTS students (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
age INTEGER CHECK (age BETWEEN 10 AND 100),
grade CHAR(2),
marks NUMERIC(5,2),
metadata JSONB,
tags TEXT[],
enrolled_at TIMESTAMPTZ DEFAULT NOW(),
is_active BOOLEAN DEFAULT TRUE
)
""")
cursor.execute("""
CREATE TABLE IF NOT EXISTS courses (
id SERIAL PRIMARY KEY,
course_name VARCHAR(100) NOT NULL,
instructor VARCHAR(100),
credits INTEGER DEFAULT 3,
description TEXT,
schedule JSONB
)
""")
# Create index
cursor.execute("""
CREATE INDEX IF NOT EXISTS idx_students_grade ON students(grade);
CREATE INDEX IF NOT EXISTS idx_students_marks ON students(marks DESC);
CREATE INDEX IF NOT EXISTS idx_students_metadata ON students USING gin(metadata);
""")
conn.commit()
print("Tables and indexes created!")
except psycopg2.Error as e:
conn.rollback()
print(f"Error: {e}")
finally:
cursor.close()3. INSERT Operations
4. SELECT with RealDictCursor
📝 Hindi Explanation
RealDictCursor rows ko Python dictionaries ke roop mein return karta hai. Column name ko key ki tarah use kar sakte ho.metadata->>'phone'JSONB field se value extract karta hai.@>JSON containment operator hai. PostgreSQL ka JSONB type indexed JSON storage provide karta hai — bahut powerful!
5. Connection Pooling with psycopg2.pool
import psycopg2
from psycopg2 import pool
from psycopg2.extras import RealDictCursor
from contextlib import contextmanager
# Connection pool create karo
connection_pool = psycopg2.pool.ThreadedConnectionPool(
minconn=2, # Minimum connections
maxconn=10, # Maximum connections
host="localhost",
database="school_db",
user="postgres",
password="mypassword",
)
@contextmanager
def get_connection():
"""Pool se connection lo, use karo, wapas karo"""
conn = connection_pool.getconn()
try:
yield conn
conn.commit()
except Exception:
conn.rollback()
raise
finally:
connection_pool.putconn(conn) # Always return to pool!
def execute_query(query, params=None, fetch=True):
"""Pool-based query execution"""
with get_connection() as conn:
with conn.cursor(cursor_factory=RealDictCursor) as cursor:
cursor.execute(query, params or ())
if fetch:
return cursor.fetchall()
return cursor.rowcount
# Usage
# students = execute_query("SELECT * FROM students WHERE grade = %s", ("A",))
# count = execute_query("UPDATE students SET is_active = FALSE WHERE id = %s", (5,), fetch=False)
# Async pool (for async applications)
# pip install asyncpg
# import asyncpg
# pool = await asyncpg.create_pool(dsn="postgresql://user:pass@localhost/db", min_size=2, max_size=10)6. Transactions and Savepoints
import psycopg2
def complex_transaction(conn):
"""Nested transactions with savepoints"""
cursor = conn.cursor()
try:
# Outer transaction begins automatically
cursor.execute("INSERT INTO students (name, email, age) VALUES (%s, %s, %s)",
("Student1", "s1@test.com", 20))
# Savepoint create karo
cursor.execute("SAVEPOINT sp1")
try:
cursor.execute("INSERT INTO students (name, email, age) VALUES (%s, %s, %s)",
("Student2", "s1@test.com", 21)) # Duplicate email!
except psycopg2.IntegrityError:
# Savepoint tak rollback karo (outer transaction safe hai)
cursor.execute("ROLLBACK TO SAVEPOINT sp1")
print("Inner insert failed, rolled back to savepoint")
# Savepoint release karo
cursor.execute("RELEASE SAVEPOINT sp1")
# Outer transaction commit karo
conn.commit()
print("Outer transaction committed!")
except psycopg2.Error as e:
conn.rollback()
print(f"Transaction failed: {e}")
finally:
cursor.close()7. Advanced PostgreSQL Features
8. UPSERT — INSERT or UPDATE
📝 Hindi Explanation
UPSERT (INSERT ... ON CONFLICT DO UPDATE) PostgreSQL ka powerful feature hai. Agar record exist kare toh update, nahi toh insert — single atomic operation mein.EXCLUDEDprefix se nayi values refer karo.(xmax = 0)se pata chalta hai ki insert hua ya update.
9. Error Handling
10. Best Practices
import psycopg2
from psycopg2.extras import RealDictCursor
from contextlib import contextmanager
import os
DB_CONFIG = {
"host": os.getenv("PG_HOST", "localhost"),
"database": os.getenv("PG_DB", "school_db"),
"user": os.getenv("PG_USER", "postgres"),
"password": os.getenv("PG_PASSWORD", ""),
"port": int(os.getenv("PG_PORT", 5432)),
}
@contextmanager
def db_cursor(dict_cursor=True):
"""Safe cursor context manager"""
conn = psycopg2.connect(**DB_CONFIG)
factory = RealDictCursor if dict_cursor else None
try:
with conn.cursor(cursor_factory=factory) as cursor:
yield cursor
conn.commit()
except Exception:
conn.rollback()
raise
finally:
conn.close()
# Clean usage
# with db_cursor() as cursor:
# cursor.execute("SELECT * FROM students")
# return cursor.fetchall()| Feature | SQLite | MySQL | PostgreSQL |
|---|---|---|---|
| JSON support | Basic | JSON | JSONB (indexed) |
| Arrays | No | No | Yes |
| Full-text search | Limited | Yes | Excellent |
| Window functions | Basic | Yes | Full |
| UPSERT | Yes | Yes | Yes |
| Concurrency | Limited | Good | Excellent |
PostgreSQL is the best choice for: Data-intensive applications, complex queries, JSON document storage, full-text search, advanced analytics, and production-scale systems.
📤 Expected Outputs
Connection Setup Output:
PostgreSQL 2.9.9 connected!
Create Tables Output:
Tables and indexes created!
Insert Student Output:
Inserted student ID: 1, at: 2026-06-12 10:30:45+05:30
Bulk Insert Output:
Bulk inserted 5 students
SELECT with RealDictCursor Output:
[
{'id': 1, 'name': 'Alice', 'email': 'alice@school.com', 'grade': 'A', 'marks': Decimal('95.50'), 'tags': ['python', 'data-science'], 'metadata': {'phone': '9876543210', 'city': 'Delhi'}},
{'id': 2, 'name': 'Bob', 'email': 'bob@school.com', 'grade': 'A', 'marks': Decimal('92.00'), 'tags': ['java', 'spring'], 'metadata': {'phone': '9988776655', 'city': 'Mumbai'}}
]Paginated Query Output:
{
'data': [{'id': 1, 'name': 'Alice', ...}, {'id': 2, 'name': 'Bob', ...}],
'total': 15,
'page': 1,
'per_page': 10,
'pages': 2
}JSON Data Query Output:
{'name': 'Alice', 'phone': '9876543210', 'city': 'Delhi', 'is_delhi': True, 'tags': ['python', 'data-science']}Connection Pool Query Output:
[{'id': 1, 'name': 'Alice', 'grade': 'A', ...}, {'id': 3, 'name': 'Charlie', 'grade': 'A', ...}]Transaction with Savepoints Output:
Inner insert failed, rolled back to savepoint Outer transaction committed!
Array & JSON Operations Output:
Alice: ['python', 'data-science'] (python: True) Bob: ['java', 'spring'] (python: False) Alice from Delhi
Window Functions Output:
[
{'name': 'Alice', 'grade': 'A', 'marks': Decimal('95.50'), 'overall_rank': 1, 'grade_rank': 1, 'grade_avg': Decimal('88.75'), 'above_avg_by': Decimal('12.30')},
{'name': 'Bob', 'grade': 'A', 'marks': Decimal('92.00'), 'overall_rank': 2, 'grade_rank': 2, 'grade_avg': Decimal('88.75'), 'above_avg_by': Decimal('8.80')}
]UPSERT Output:
Inserted new student with ID: 6 # OR if email already exists: Updated existing student with ID: 1
Batch Upsert Output:
Upserted 5 records
Error Handling Outputs:
# UniqueViolation: Duplicate entry: ERROR: duplicate key value violates unique constraint "students_email_key" # CheckViolation: Check constraint failed: ERROR: new row for relation "students" violates check constraint "students_age_check" # NotNullViolation: Not null constraint: ERROR: null value in column "email" violates not-null constraint
⚠️ Common Mistakes
❌ Mistake 1: Connection Close Na Karna
# ❌ WRONG — connection leak hogi!
conn = psycopg2.connect(host="localhost", database="db", user="user", password="pass")
cursor = conn.cursor()
cursor.execute("SELECT * FROM students")
# Connection kabhi close nahi hui... resource leak!
# ✅ RIGHT — always close or use context manager
with psycopg2.connect(**DB_CONFIG) as conn:
with conn.cursor() as cursor:
cursor.execute("SELECT * FROM students")
results = cursor.fetchall()
# Connection auto-close hoga🔑 Tip: with statement use karo taaki connection automatically close ho. Production mein connection pooling use karo.❌ Mistake 2: String Formatting se SQL Injection
# ❌ WRONG — SQL Injection vulnerable!
name = "'; DROP TABLE students; --"
cursor.execute(f"SELECT * FROM students WHERE name = '{name}'")
# ✅ RIGHT — parameterized queries use karo
cursor.execute("SELECT * FROM students WHERE name = %s", (name,))🔑 Tip: KABHI bhi f-string ya.format()se SQL query mein user input mat daalo. Hamesha%splaceholders use karo.
❌ Mistake 3: Commit Bhool Jaana
# ❌ WRONG — data save nahi hoga!
cursor.execute("INSERT INTO students (name, email) VALUES (%s, %s)", ("Alice", "alice@test.com"))
# conn.commit() missing... INSERT lost!
# ✅ RIGHT — always commit after write operations
cursor.execute("INSERT INTO students (name, email) VALUES (%s, %s)", ("Alice", "alice@test.com"))
conn.commit()🔑 Tip: psycopg2 mein autocommit off hota hai by default. INSERT/UPDATE/DELETE ke baad conn.commit() zaroori hai.❌ Mistake 4: fetchall() Large Results Par
# ❌ WRONG — 10 million rows memory mein load!
cursor.execute("SELECT * FROM huge_table")
all_rows = cursor.fetchall() # Out of Memory!
# ✅ RIGHT — server-side cursor use karo
with conn.cursor(name="large_query_cursor") as cursor:
cursor.execute("SELECT * FROM huge_table")
while True:
rows = cursor.fetchmany(1000) # 1000 rows at a time
if not rows:
break
process(rows)🔑 Tip: Large datasets ke liye named (server-side) cursor use karo — memory efficient rehta hai.
❌ Mistake 5: Pool Connection Return Na Karna
# ❌ WRONG — pool exhaust ho jayega!
conn = connection_pool.getconn()
cursor = conn.cursor()
cursor.execute("SELECT * FROM students")
# connection pool mein wapas nahi ki... pool dead!
# ✅ RIGHT — always putconn() karo
conn = connection_pool.getconn()
try:
cursor = conn.cursor()
cursor.execute("SELECT * FROM students")
results = cursor.fetchall()
finally:
connection_pool.putconn(conn) # Always return!🔑 Tip: Connection pool se li hui connection ko putconn() se wapas karo. Best practice: context manager use karo (Section 5 example).❌ Mistake 6: Rollback Bhool Jaana Error Ke Baad
# ❌ WRONG — connection unusable state mein reh jayegi!
try:
cursor.execute("INSERT INTO students ...")
except psycopg2.Error:
print("Error occurred")
# rollback nahi kiya... next query bhi fail hogi!
# ✅ RIGHT — error ke baad rollback karo
try:
cursor.execute("INSERT INTO students ...")
conn.commit()
except psycopg2.Error as e:
conn.rollback() # Transaction clean karo
print(f"Error: {e}")🔑 Tip: PostgreSQL mein failed transaction ke baad sab queries fail hongi jab tak rollback() na karo. Always rollback in except block!✅ Key Takeaways
- 🐘 psycopg2 PostgreSQL ka most popular aur battle-tested Python adapter hai — production-ready applications ke liye best choice
- 🔐 Parameterized queries (
%splaceholders) HAMESHA use karo — SQL injection se bachne ka eklauta reliable tarika hai - 📦 Connection Pooling (
ThreadedConnectionPool) production mein must-use hai — har request par new connection banana expensive hai - 🔄 Transactions aur Savepoints complex operations ke liye use karo — partial rollback possible hai without losing entire transaction
- 📖 RealDictCursor use karo taaki rows Python dictionaries ke roop mein milein — code readable aur maintainable banta hai
- 🗄️ JSONB aur Arrays PostgreSQL ke unique features hain — NoSQL-like flexibility relational database ke andar milti hai
- ⚡ UPSERT (
ON CONFLICT DO UPDATE) atomic insert-or-update operation hai — race conditions se bachata hai - 🔍 Indexes (B-tree, GIN for JSONB) create karo frequently queried columns par — query performance dramatically improve hoti hai
- 🛡️ Error handling specific exceptions ke saath karo (
UniqueViolation,ForeignKeyViolation) — generic except se avoid karo - 🌐 Environment variables mein credentials rakho — KABHI bhi passwords code mein hardcode mat karo, use
os.getenv()
❓ FAQ
Q1: psycopg2 aur psycopg2-binary mein kya difference hai?
Answer: psycopg2-binary pre-compiled binary wheels include karta hai — easy install, no C compiler needed. psycopg2 source se compile hota hai aur production ke liye recommended hai kyunki iska libpq system library se link hota hai (better performance, security updates automatic milte hain). Development mein -binary use karo, production deployment mein source version.
Q2: Connection pool ka size kitna rakhna chahiye?
Answer: General rule: pool_size = (2 × CPU cores) + effective_spindle_count. Agar 4 CPU cores hain aur SSD hai, toh maxconn=10 accha starting point hai. Zyada connections se PostgreSQL slow hota hai (context switching). Monitoring karo aur tune karo — PgBouncer external pooler bhi consider karo high-traffic applications ke liye.
Q3: JSONB aur JSON mein kya difference hai PostgreSQL mein?
Answer: JSON raw text store karta hai — har access par parse hota hai. JSONB binary format mein store karta hai — GIN index support karta hai, queries fast hain, operators (@>, ?, ?|) support karte hai. JSONB slightly zyada storage leta hai but performance bahut better hai. Hamesha JSONB use karo jab tak raw JSON preservation zaroori na ho.
Q4: Server-side cursor kab use karna chahiye?
Answer: Jab query result bahut bada ho (thousands/millions rows), tab server-side (named) cursor use karo. Normal cursor fetchall() se saari rows memory mein load karta hai. Named cursor (cursor(name="my_cursor")) server par results hold karta hai aur fetchmany(batch_size) se chunks mein data leta hai. Memory efficient aur large dataset processing ke liye essential.
Q5: psycopg2 mein autocommit kaise enable karein?
Answer:
conn = psycopg2.connect(**DB_CONFIG)
conn.autocommit = True # Har statement auto-commit hogaAutocommit DDL statements (CREATE TABLE, ALTER TABLE) ke liye useful hai. DML operations (INSERT/UPDATE/DELETE) ke liye explicit transactions better hain — rollback ka option rehta hai.
Q6: asyncpg aur psycopg2 mein kaunsa choose karein?
Answer: psycopg2 synchronous applications ke liye (Flask, Django default). asyncpg async frameworks ke liye (FastAPI, aiohttp) — 3-5x faster hai psycopg2 se kyunki native async protocol use karta hai. Agar aapka application async hai toh asyncpg, warna psycopg2 best hai. psycopg3 (naya) dono sync aur async support karta hai.
Q7: PostgreSQL mein full-text search kaise implement karein Python se?
Answer: PostgreSQL ka built-in FTS use karo:
Simple use cases ke liye PostgreSQL FTS kaafi hai — Elasticsearch ki zaroorat nahi.
Q8: Multiple databases ko ek saath connect kaise karein?
Answer: Har database ke liye alag connection pool banao:
pool_db1 = psycopg2.pool.ThreadedConnectionPool(2, 10, database="db1", ...)
pool_db2 = psycopg2.pool.ThreadedConnectionPool(2, 10, database="db2", ...)Cross-database queries PostgreSQL mein directly possible nahi hain (unlike MySQL). dblink extension ya application-level join use karo. Better approach: schema separation same database mein.
🎯 Interview Questions
Q1: psycopg2 mein connection pooling kyu zaroori hai?
Answer: Har database connection establish karna expensive operation hai — TCP handshake, authentication, memory allocation server par. Connection pooling se pre-created connections reuse hote hain. Benefits: (1) Reduced latency — no connection setup per request, (2) Resource management — limit on max connections, (3) Better throughput — connections ready to use. ThreadedConnectionPool thread-safe hai multi-threaded applications ke liye. Production mein PgBouncer jaise external poolers bhi use hote hain.
Q2: SQL Injection kya hai aur psycopg2 mein kaise prevent karein?
Answer: SQL Injection tab hota hai jab user input directly SQL query mein concatenate hota hai — attacker malicious SQL inject kar sakta hai. Prevention: (1) Parameterized queries: cursor.execute("SELECT * FROM users WHERE id = %s", (user_id,)) — psycopg2 automatically escaping karta hai. (2) psycopg2.sql module: Dynamic identifiers (table/column names) ke liye sql.Identifier() use karo. (3) KABHI f-strings ya string formatting SQL mein use mat karo.
Q3: SERIALIZABLE isolation level kya hai aur kab use karein?
Answer: PostgreSQL 4 isolation levels support karta hai: READ UNCOMMITTED, READ COMMITTED (default), REPEATABLE READ, SERIALIZABLE. SERIALIZABLE strictest hai — transactions aise behave karte hain jaise serially execute ho rahe hain. Use case: Financial transactions, inventory management jahan consistency critical hai. Implementation:
conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_SERIALIZABLE)Trade-off: Higher isolation = more serialization failures (retry logic zaroori).
Q4: PostgreSQL mein JSONB indexing kaise kaam karti hai?
Answer: JSONB GIN (Generalized Inverted Index) support karta hai. Two operator classes: (1) jsonb_ops (default) — supports @>, ?, ?&, ?| operators. (2) jsonb_path_ops — sirf @> support karta hai but smaller index, faster lookups. Example: CREATE INDEX idx_meta ON students USING gin(metadata jsonb_path_ops). Query WHERE metadata @> '{"city": "Delhi"}' index use karegi. Expression index bhi possible: CREATE INDEX idx_city ON students ((metadata->>'city')).
Q5: psycopg2 mein with statement kaise kaam karta hai connection ke saath?
Answer: with conn: block commit karta hai success par aur rollback karta hai exception par — BUT connection close NAHI karta! with conn.cursor() as cur: cursor close karta hai block end par. Important distinction:
with conn: # Auto commit/rollback (connection stays open)
with conn.cursor() as cur: # Auto close cursor
cur.execute("...")
conn.close() # Explicit close zaroori!Full cleanup ke liye dono with blocks aur explicit close() use karo, ya connection pool se manage karo.
Q6: COPY command kya hai aur bulk data load mein kaise use karein?
Answer: PostgreSQL ka COPY command fastest bulk loading method hai — INSERT se 10-100x faster. psycopg2 mein copy_expert() use karo:
with open('students.csv', 'r') as f:
cursor.copy_expert("COPY students (name, email, age) FROM STDIN WITH CSV HEADER", f)
conn.commit()Export ke liye: COPY students TO STDOUT WITH CSV HEADER. Large datasets (millions of rows) ke liye COPY best hai — binary format aur bhi fast hai.
Q7: Dead connections aur connection timeout kaise handle karein?
Answer: Production mein connections die ho sakti hain (network issues, server restart). Solutions: (1) keepalives configure karo:
conn = psycopg2.connect(..., keepalives=1, keepalives_idle=30, keepalives_interval=10, keepalives_count=5)(2) Connection validation pool se lene ke baad: SELECT 1 execute karo. (3) Retry logic implement karo with exponential backoff. (4) PgBouncer connection health checks automatically handle karta hai.
Q8: Explain the difference between fetchone(), fetchmany(), and fetchall().
Answer: (1) fetchone() — single row return karta hai (ya None). Best jab ek hi row expected ho (e.g., WHERE id = %s). (2) fetchmany(size) — specified number of rows return karta hai. Memory-efficient batch processing ke liye. (3) fetchall() — ALL remaining rows memory mein load karta hai. Small result sets ke liye theek hai but large data par OOM risk. Best practice: Large data ke liye named cursor + fetchmany, small data ke liye fetchall.
Q9: psycopg2 mein LISTEN/NOTIFY kaise implement karein real-time notifications ke liye?
Answer: PostgreSQL ka LISTEN/NOTIFY pub-sub mechanism hai:
Trigger function se NOTIFY fire karo: PERFORM pg_notify('new_student', row_to_json(NEW)::text). Real-time features bina polling ke possible!
Q10: Database migrations Python mein kaise manage karein?
Answer: Options: (1) Alembic (SQLAlchemy ke saath) — most popular, version control for schema. (2) django-migrations (Django projects). (3) Manual approach with version table:
Best practice: Migrations ko version control mein rakho, idempotent banao (IF NOT EXISTS), aur rollback scripts bhi likho.
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for PostgreSQL with Python (psycopg2).
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, postgresql, with
Related Python Master Course Topics