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 connecting MySQL with Python using mysql-connector-python, performing CRUD operations, connection pooling, stored procedures, and best practices.
Introduction
MySQL duniya ka sabse popular open-source relational database hai. Python ke saath use karne ke liye mysql-connector-python (Oracle official) ya PyMySQL use karte hain.
bash example
# Installation
pip install mysql-connector-python
# OR
pip install PyMySQL2. Create Database and Tables
python example
import mysql.connector
from mysql.connector import Error
# Database create karo
def setup_database():
try:
# Pehle database ke bina connect karo
conn = mysql.connector.connect(
host="localhost",
user="root",
password="your_password"
)
cursor = conn.cursor()
# Database create
cursor.execute("CREATE DATABASE IF NOT EXISTS school_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci")
print("Database created!")
# Database select
cursor.execute("USE school_db")
# Tables create
cursor.execute("""
CREATE TABLE IF NOT EXISTS students (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
age INT CHECK (age >= 10 AND age <= 100),
grade CHAR(2),
marks DECIMAL(5,2),
enrolled_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
is_active BOOLEAN DEFAULT TRUE
) ENGINE=InnoDB
""")
cursor.execute("""
CREATE TABLE IF NOT EXISTS courses (
id INT AUTO_INCREMENT PRIMARY KEY,
course_name VARCHAR(100) NOT NULL,
instructor VARCHAR(100),
credits INT DEFAULT 3,
max_students INT DEFAULT 30
) ENGINE=InnoDB
""")
cursor.execute("""
CREATE TABLE IF NOT EXISTS enrollments (
student_id INT,
course_id INT,
enrollment_date DATE DEFAULT (CURDATE()),
score DECIMAL(5,2),
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE
) ENGINE=InnoDB
""")
conn.commit()
print("All tables created!")
except Error as e:
print(f"Error: {e}")
finally:
if conn.is_connected():
cursor.close()
conn.close()
setup_database()3. INSERT Operations
Example
import mysql.connector
from mysql.connector import Error
def insert_student(conn, name, email, age, grade, marks):
"""Single student insert"""
try:
cursor = conn.cursor()
query = """
INSERT INTO students (name, email, age, grade, marks)
VALUES (%s, %s, %s, %s, %s)
"""
cursor.execute(query, (name, email, age, grade, marks))
conn.commit()
print(f"Student '{name}' inserted with ID: {cursor.lastrowid}")
return cursor.lastrowid
except Error as e:
print(f"Error inserting student: {e}")
conn.rollback()
return None
finally:
cursor.close()
def bulk_insert_students(conn, students_data):
"""Multiple students batch insert"""
try:
cursor = conn.cursor()
query = """
INSERT INTO students (name, email, age, grade, marks)
VALUES (%s, %s, %s, %s, %s)
"""
cursor.executemany(query, students_data)
conn.commit()
print(f"Inserted {cursor.rowcount} students!")
except Error as e:
print(f"Error in bulk insert: {e}")
conn.rollback()
finally:
cursor.close()
# Usage
students = [
("Alice Johnson", "alice@school.com", 20, "A", 95.5),
("Bob Smith", "bob@school.com", 22, "B", 82.0),
("Charlie Brown", "charlie@school.com", 21, "A", 91.0),
("Diana Prince", "diana@school.com", 19, "B", 78.5),
("Eve Wilson", "eve@school.com", 23, "A", 88.0),
]
# conn = create_connection(...)
# bulk_insert_students(conn, students)
📝 Hindi Explanation
MySQL parameterized queries%splaceholder use karte hain (SQLite?use karta hai).executemany()batch insert ke liye bahut efficient hai — single query ke overhead se zyada rows insert hoti hain. HAMESHA parameterized queries use karo — SQL injection se bachao.
4. SELECT Operations
Example
import mysql.connector
def get_all_students(conn):
"""Saare students fetch karo"""
cursor = conn.cursor(dictionary=True) # dict=True returns dicts
cursor.execute("SELECT * FROM students ORDER BY marks DESC")
students = cursor.fetchall()
cursor.close()
return students
def get_students_by_grade(conn, grade):
"""Grade se filter karo"""
cursor = conn.cursor(dictionary=True)
cursor.execute(
"SELECT name, marks, age FROM students WHERE grade = %s ORDER BY marks DESC",
(grade,)
)
return cursor.fetchall()
def search_students(conn, min_marks=0, max_age=100, grade=None):
"""Dynamic WHERE clause"""
cursor = conn.cursor(dictionary=True)
query = "SELECT * FROM students WHERE marks >= %s AND age <= %s"
params = [min_marks, max_age]
if grade:
query += " AND grade = %s"
params.append(grade)
query += " ORDER BY marks DESC LIMIT 10"
cursor.execute(query, params)
return cursor.fetchall()
def get_student_stats(conn):
"""Statistics query"""
cursor = conn.cursor(dictionary=True)
cursor.execute("""
SELECT
grade,
COUNT(*) AS count,
AVG(marks) AS avg_marks,
MAX(marks) AS max_marks,
MIN(marks) AS min_marks
FROM students
GROUP BY grade
ORDER BY avg_marks DESC
""")
return cursor.fetchall()
def get_top_students(conn, limit=5):
"""Top N students"""
cursor = conn.cursor(dictionary=True)
cursor.execute("""
SELECT id, name, grade, marks
FROM students
WHERE is_active = TRUE
ORDER BY marks DESC
LIMIT %s
""", (limit,))
return cursor.fetchall()
# Demo output
sample_students = [
{'id': 1, 'name': 'Alice', 'grade': 'A', 'marks': 95.5},
{'id': 2, 'name': 'Charlie', 'grade': 'A', 'marks': 91.0},
{'id': 3, 'name': 'Eve', 'grade': 'A', 'marks': 88.0},
]
print("Top students:")
for s in sample_students:
print(f" {s['name']}: {s['marks']}")
5. UPDATE and DELETE
Example
import mysql.connector
from mysql.connector import Error
def update_student_marks(conn, student_id, new_marks):
"""Student marks update karo"""
cursor = conn.cursor()
try:
cursor.execute(
"UPDATE students SET marks = %s WHERE id = %s",
(new_marks, student_id)
)
conn.commit()
if cursor.rowcount > 0:
print(f"Updated {cursor.rowcount} student(s)")
return True
else:
print(f"Student {student_id} not found")
return False
except Error as e:
print(f"Update error: {e}")
conn.rollback()
return False
finally:
cursor.close()
def bulk_update_grades(conn, grade_rules):
"""Marks ke basis par grades update karo"""
cursor = conn.cursor()
try:
cases = " ".join([f"WHEN marks >= {min_m} AND marks < {max_m} THEN '{grade}'"
for grade, min_m, max_m in grade_rules])
query = f"""
UPDATE students
SET grade = CASE {cases} ELSE 'F' END
WHERE is_active = TRUE
"""
cursor.execute(query)
conn.commit()
print(f"Updated grades for {cursor.rowcount} students")
except Error as e:
conn.rollback()
print(f"Error: {e}")
finally:
cursor.close()
def soft_delete_student(conn, student_id):
"""Soft delete — record rehta hai, bas inactive hota hai"""
cursor = conn.cursor()
cursor.execute(
"UPDATE students SET is_active = FALSE WHERE id = %s",
(student_id,)
)
conn.commit()
print(f"Student {student_id} deactivated (soft delete)")
cursor.close()
def hard_delete_student(conn, student_id):
"""Hard delete — record permanently remove"""
cursor = conn.cursor()
cursor.execute("DELETE FROM students WHERE id = %s", (student_id,))
conn.commit()
affected = cursor.rowcount
cursor.close()
return affected > 0
# Grade rules: (grade, min_marks, max_marks)
grade_rules = [
('A', 90, 101),
('B', 75, 90),
('C', 60, 75),
('D', 45, 60),
]
6. Connection Pooling
Example
import mysql.connector
from mysql.connector import pooling
# Connection pool create karo
pool_config = {
"pool_name": "school_pool",
"pool_size": 5,
"pool_reset_session": True,
"host": "localhost",
"database": "school_db",
"user": "root",
"password": "your_password",
"charset": "utf8mb4",
}
try:
connection_pool = mysql.connector.pooling.MySQLConnectionPool(**pool_config)
print(f"Connection pool created: size {pool_config['pool_size']}")
except Exception as e:
print(f"Pool creation error: {e}")
def execute_query_with_pool(pool, query, params=None, fetch=True):
"""Pool se connection lo, query execute karo, connection wapas karo"""
connection = None
try:
connection = pool.get_connection()
cursor = connection.cursor(dictionary=True)
cursor.execute(query, params or ())
if fetch:
result = cursor.fetchall()
return result
else:
connection.commit()
return cursor.rowcount
except mysql.connector.Error as e:
if connection:
connection.rollback()
print(f"Query error: {e}")
return None
finally:
if connection and connection.is_connected():
cursor.close()
connection.close() # Connection pool ko wapas jaati hai, close nahi hoti!
# Usage
# students = execute_query_with_pool(connection_pool, "SELECT * FROM students LIMIT 5")
📝 Hindi Explanation
Connection Pool N connections maintain karta hai. Jab aapko connection chahiye, pool se lelo — ek naya TCP connection banana expensive hai (~100ms). Pool se connection milna fast (~1ms) hai. connection.close() pool mein connection wapas karta hai, destroy nahi karta. Production applications mein connection pooling ZARURI hai.7. Stored Procedures
python example
import mysql.connector
# Stored procedure create karo (MySQL client ya script se)
CREATE_PROCEDURE = """
DELIMITER //
CREATE PROCEDURE GetStudentsByGrade(IN grade_param CHAR(2))
BEGIN
SELECT id, name, marks, age
FROM students
WHERE grade = grade_param AND is_active = TRUE
ORDER BY marks DESC;
END //
DELIMITER ;
"""
def call_stored_procedure(conn, proc_name, params=()):
"""Stored procedure call karo"""
cursor = conn.cursor(dictionary=True)
cursor.callproc(proc_name, params)
results = []
for result_set in cursor.stored_results():
results.extend(result_set.fetchall())
cursor.close()
return results
# conn = create_connection(...)
# students = call_stored_procedure(conn, "GetStudentsByGrade", ("A",))8. Transactions
Example
import mysql.connector
from mysql.connector import Error
def transfer_scores(conn, from_student, to_student, points):
"""Atomic score transfer"""
cursor = conn.cursor()
try:
# Autocommit off karo
conn.autocommit = False
# Check sender's balance
cursor.execute("SELECT marks FROM students WHERE id = %s FOR UPDATE", (from_student,))
result = cursor.fetchone()
if not result or result[0] < points:
raise ValueError("Insufficient marks!")
# Deduct from sender
cursor.execute(
"UPDATE students SET marks = marks - %s WHERE id = %s",
(points, from_student)
)
# Add to receiver
cursor.execute(
"UPDATE students SET marks = marks + %s WHERE id = %s",
(points, to_student)
)
conn.commit()
print(f"Transferred {points} points successfully!")
except Error as e:
conn.rollback()
print(f"Transfer failed: {e}")
raise
except ValueError as e:
conn.rollback()
print(f"Validation failed: {e}")
finally:
conn.autocommit = True
cursor.close()
9. Error Handling
python example
import mysql.connector
from mysql.connector import errorcode
def robust_insert(conn, data):
cursor = conn.cursor()
try:
cursor.execute(
"INSERT INTO students (name, email, age) VALUES (%s, %s, %s)",
data
)
conn.commit()
return cursor.lastrowid
except mysql.connector.Error as err:
conn.rollback()
if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
print("Access denied!")
elif err.errno == errorcode.ER_BAD_DB_ERROR:
print("Database does not exist!")
elif err.errno == errorcode.ER_DUP_ENTRY:
print(f"Duplicate entry: {err.msg}")
elif err.errno == errorcode.ER_DATA_TOO_LONG:
print(f"Data too long for column")
else:
print(f"MySQL Error {err.errno}: {err.msg}")
return None
finally:
cursor.close()10. Best Practices Summary
python example
import mysql.connector
from contextlib import contextmanager
@contextmanager
def mysql_connection(host, user, password, database):
"""Context manager for MySQL connection"""
conn = None
try:
conn = mysql.connector.connect(
host=host, user=user, password=password,
database=database, charset='utf8mb4', autocommit=False
)
yield conn
conn.commit()
except Exception:
if conn: conn.rollback()
raise
finally:
if conn and conn.is_connected():
conn.close()
# Usage
# with mysql_connection("localhost", "root", "pass", "school_db") as db:
# cursor = db.cursor(dictionary=True)
# cursor.execute("SELECT * FROM students")
# print(cursor.fetchall())| Feature | SQLite | MySQL |
|---|---|---|
| Installation | Built-in | Separate server |
| Concurrency | Limited | Excellent |
| Data types | Dynamic | Strict |
| Scale | Small-Medium | Large |
| Use case | Development/Mobile | Production web apps |
MySQL is perfect for: Production web applications, high-concurrency systems, complex queries, large datasets, team collaboration.
📤 Expected Outputs
Connection Setup Output:
Output
Connected to MySQL Server version 8.0.35 Connected to: school_db
Database & Tables Creation Output:
Output
Database created! All tables created!
INSERT Operations Output:
Output
Student 'Alice Johnson' inserted with ID: 1 Inserted 5 students!
SELECT Operations Output:
Output
Top students: Alice: 95.5 Charlie: 91.0 Eve: 88.0
UPDATE and DELETE Output:
Output
Updated 1 student(s) Updated grades for 5 students Student 3 deactivated (soft delete)
Connection Pooling Output:
Output
Connection pool created: size 5
Stored Procedures Output:
Output
[{'id': 1, 'name': 'Alice Johnson', 'marks': Decimal('95.50'), 'age': 20},
{'id': 3, 'name': 'Charlie Brown', 'marks': Decimal('91.00'), 'age': 21},
{'id': 5, 'name': 'Eve Wilson', 'marks': Decimal('88.00'), 'age': 23}]Transactions Output:
Output
Transferred 5 points successfully!
Error Handling Output:
Output
Duplicate entry: Duplicate entry 'alice@school.com' for key 'students.email'
Context Manager Output:
Output
[{'id': 1, 'name': 'Alice Johnson', 'email': 'alice@school.com', 'age': 20, 'grade': 'A', 'marks': Decimal('95.50')},
{'id': 2, 'name': 'Bob Smith', 'email': 'bob@school.com', 'age': 22, 'grade': 'B', 'marks': Decimal('82.00')}]⚠️ Common Mistakes
❌ Mistake 1: Connection close karna bhool jaana
python example
# ❌ WRONG - Connection leak!
conn = mysql.connector.connect(host="localhost", user="root", password="pass", database="mydb")
cursor = conn.cursor()
cursor.execute("SELECT * FROM students")
data = cursor.fetchall()
# Connection close nahi ki! 😱python example
# ✅ RIGHT - Always use try/finally or context manager
conn = mysql.connector.connect(host="localhost", user="root", password="pass", database="mydb")
try:
cursor = conn.cursor()
cursor.execute("SELECT * FROM students")
data = cursor.fetchall()
finally:
if conn.is_connected():
cursor.close()
conn.close()Galti kya hai? Agar connection close nahi karte, toh MySQL server pe connections accumulate hote hain aur eventually max_connections limit hit hoti hai. Production mein server crash ho sakta hai! 💥❌ Mistake 2: String formatting se SQL injection
python example
# ❌ WRONG - SQL Injection vulnerability!
name = input("Enter name: ")
cursor.execute(f"SELECT * FROM students WHERE name = '{name}'")
# Attacker input: ' OR '1'='1' -- drops all data!python example
# ✅ RIGHT - Parameterized query
name = input("Enter name: ")
cursor.execute("SELECT * FROM students WHERE name = %s", (name,))Galti kya hai? f-string ya%formatting se SQL injection attack hota hai.%splaceholder use karo — MySQL driver automatically escaping handle karta hai.
❌ Mistake 3: commit() bhool jaana
python example
# ❌ WRONG - Data save nahi hoga!
cursor.execute("INSERT INTO students (name, email) VALUES (%s, %s)", ("Test", "test@mail.com"))
conn.close() # commit nahi kiya!python example
# ✅ RIGHT - commit() zaroori hai
cursor.execute("INSERT INTO students (name, email) VALUES (%s, %s)", ("Test", "test@mail.com"))
conn.commit() # ✅ Data actually saved!
conn.close()Galti kya hai? MySQL meinautocommit=Falsehone par INSERT/UPDATE/DELETE ke baadcommit()mandatory hai. Warna data rollback ho jaata hai!
❌ Mistake 4: Single value tuple mein comma bhoolna
python example
# ❌ WRONG - TypeError aayega!
cursor.execute("SELECT * FROM students WHERE grade = %s", ("A"))
# "A" ek string hai, tuple nahi!python example
# ✅ RIGHT - Trailing comma se tuple banta hai
cursor.execute("SELECT * FROM students WHERE grade = %s", ("A",))Galti kya hai? Python mein single-element tuple ke liye trailing comma("A",)zaroori hai. Without comma,("A")bas ek string hai parentheses mein.
❌ Mistake 5: fetchall() large dataset pe memory overflow
python example
# ❌ WRONG - 10 million rows memory mein load!
cursor.execute("SELECT * FROM huge_table")
all_rows = cursor.fetchall() # 💥 RAM explode!python example
# ✅ RIGHT - Batch fetch karo
cursor.execute("SELECT * FROM huge_table")
while True:
rows = cursor.fetchmany(1000)
if not rows:
break
process_batch(rows)Galti kya hai?fetchall()saari rows memory mein load karta hai. Large tables ke liyefetchmany(size)yaLIMIT/OFFSETuse karo.
❌ Mistake 6: Pool connection ko properly return na karna
python example
# ❌ WRONG - Exception pe connection leak!
conn = pool.get_connection()
cursor = conn.cursor()
cursor.execute("SELECT * FROM students")
# Agar yahan exception aaye toh connection wapas nahi jaati pool meinpython example
# ✅ RIGHT - try/finally se guarantee
conn = pool.get_connection()
try:
cursor = conn.cursor()
cursor.execute("SELECT * FROM students")
data = cursor.fetchall()
finally:
if conn.is_connected():
cursor.close()
conn.close() # Pool ko wapas jaati haiGalti kya hai? Pool se li gayi connection ko hamesha finally block mein close karo. Warna pool exhaust ho jaata hai aur naye requests hang hoti hain.
✅ Key Takeaways
- 🔗 Connection management — Hamesha
try/finallyya context manager use karo connection close karne ke liye. Connection leak production mein sabse common bug hai.
- 🛡️ Parameterized queries — KABHI BHI f-string ya string concatenation se SQL mat banao.
%splaceholder use karo — SQL injection se 100% protection milti hai.
- 💾 commit() is mandatory —
autocommit=False(default) hone par INSERT/UPDATE/DELETE ke baadconn.commit()zaroori hai, warna data save nahi hota.
- 🏊 Connection pooling — Production mein hamesha connection pool use karo. Naya connection banana ~100ms lagta hai, pool se milna ~1ms.
pool_size=5se start karo.
- 🔄 Transactions — Multiple related operations ko ek transaction mein wrap karo.
FOR UPDATEse row-level locking use karo race conditions se bachne ke liye.
- 📦 Batch operations —
executemany()use karo multiple inserts ke liye. Individual inserts se 10x faster hai.
- 🎯 cursor(dictionary=True) — Dictionary cursor use karo readable code ke liye. Column names se access karna index se better hai:
row['name']vsrow[0].
- ⚠️ Error handling —
mysql.connector.errorcodese specific errors handle karo (ER_DUP_ENTRY, ER_ACCESS_DENIED, etc.). Generic exception catching avoid karo.
- 📊 fetchmany() for large data —
fetchall()se memory overflow ho sakta hai. Large datasets ke liyefetchmany(batch_size)ya server-side cursor use karo.
- 🔒 Soft delete over hard delete —
is_active = FALSEkaro instead of actual DELETE. Data recovery possible rehti hai aur audit trail maintain hota hai.
❓ FAQ
Q1: mysql-connector-python aur PyMySQL mein kya difference hai?
Answer: mysql-connector-python Oracle ka official connector hai — pure Python implementation. PyMySQL community-maintained hai aur thoda faster hai. Dono ka API almost same hai. Production ke liye mysql-connector-python recommended hai kyunki Oracle officially support karta hai. Agar Django use kar rahe ho toh PyMySQL better choice hai kyunki Django ka ORM iske saath better integrate hota hai.
Q2: Connection pooling kab use karna chahiye?
Answer: Jab bhi aapki application multiple concurrent database requests handle karti hai — web apps, APIs, background workers mein pooling ZARURI hai. Single-script analysis ya one-time migration scripts mein pool ki zaroorat nahi hai. Rule of thumb: agar 2+ threads/requests simultaneously DB access karte hain, pool use karo. pool_size = (number of CPU cores × 2) + 1 se start karo.
Q3: %s aur %(name)s mein kya difference hai?
Answer: %s positional placeholder hai — order matter karta hai. %(name)s named placeholder hai — dictionary pass karte hain. Example:
python example
# Positional
cursor.execute("SELECT * FROM students WHERE grade = %s AND age > %s", ("A", 18))
# Named
cursor.execute("SELECT * FROM students WHERE grade = %(grade)s AND age > %(age)s",
{"grade": "A", "age": 18})Named placeholders zyada readable hain jab query mein bahut parameters hon.
Q4: MySQL mein DECIMAL vs FLOAT kab use karein?
Answer: Money, marks, scores jaise exact values ke liye HAMESHA DECIMAL use karo. FLOAT/DOUBLE mein floating-point precision errors aate hain (e.g., 0.1 + 0.2 != 0.3). DECIMAL(5,2) means total 5 digits, 2 decimal places (max 999.99). Scientific calculations mein FLOAT theek hai, but financial data mein KABHI nahi!
Q5: FOR UPDATE kya karta hai transactions mein?
Answer: SELECT ... FOR UPDATE selected rows par exclusive lock lagata hai. Jab tak transaction commit/rollback nahi hota, doosra transaction un rows ko modify nahi kar sakta. Ye race conditions prevent karta hai. Example: bank transfer mein pehle balance check karo FOR UPDATE ke saath, phir deduct karo — beech mein koi aur balance change nahi kar sakta.
Q6: MySQL server se connection fail ho rahi hai — kya check karein?
Answer: Common checks:
- MySQL service running hai? →
sudo systemctl status mysql - Host/port correct hai? → Default port
3306hai - User permissions check karo →
GRANT ALL ON db.* TO 'user'@'localhost' - Firewall block toh nahi? → Remote connection pe port allow karo
max_connectionslimit reached? →SHOW STATUS LIKE 'Threads_connected'- Password correct hai? → MySQL Workbench se test karo pehle
Q7: cursor.rowcount kabhi -1 kyun return karta hai?
Answer: rowcount SELECT queries ke baad -1 return kar sakta hai jab tak fetchall() ya fetchone() call na ho. MySQL connector mein SELECT ke baad rowcount reliable nahi hota. INSERT/UPDATE/DELETE ke baad ye accurately affected rows batata hai. Agar SELECT ka count chahiye toh len(cursor.fetchall()) ya SELECT COUNT(*) use karo.
Q8: Connection pool exhaust ho jaaye toh kya hoga?
Answer: Jab pool ki saari connections busy hain aur naya request aata hai, toh PoolError raise hota hai. Solutions:
pool_sizeincrease karo (but carefully — MySQL kamax_connectionsbhi check karo)- Connection jaldi release karo —
finallyblock meinclose()guaranteed rakhein - Connection timeout set karo:
connection_timeout=30 - Queue-based approach use karo heavy load ke liye
🎯 Interview Questions
Q1: MySQL mein InnoDB aur MyISAM engines mein kya difference hai?
Answer:
- InnoDB: Transactions support karta hai (ACID compliant), row-level locking, foreign keys, crash recovery. Production applications ke liye default choice hai.
- MyISAM: Transactions support NAHI karta, table-level locking, faster read operations (simple SELECTs), full-text search (older versions mein). Ab mostly deprecated hai.
- Interview tip: "InnoDB is the default and recommended engine for all production use cases because it supports ACID transactions, foreign key constraints, and row-level locking which enables better concurrency."
Q2: SQL Injection kya hai aur Python mein kaise prevent karein?
Answer: SQL Injection ek security vulnerability hai jahan attacker malicious SQL code inject karta hai user input ke through.
python example
# Vulnerable code:
query = f"SELECT * FROM users WHERE name = '{user_input}'"
# Attacker input: ' OR '1'='1'; DROP TABLE users; --
# Safe code:
cursor.execute("SELECT * FROM users WHERE name = %s", (user_input,))Prevention techniques: (1) Parameterized queries, (2) Input validation, (3) Least privilege DB users, (4) ORM use karo (SQLAlchemy, Django ORM). Interview mein parameterized queries ka example dena ZARURI hai.
Q3: Connection Pooling kya hai aur kyun zaroori hai?
Answer: Connection pooling ek technique hai jahan pre-created database connections ka pool maintain kiya jaata hai reuse ke liye. Har request pe naya connection banana expensive hai (TCP handshake + authentication = ~100-200ms). Pool se existing connection milna ~1-2ms lagta hai.
- Benefits: Reduced latency, controlled resource usage, better scalability
- Implementation:
mysql.connector.pooling.MySQLConnectionPool(pool_size=5) - Best practice: Pool size = (CPU cores × 2) + 1. Too many connections se MySQL server pe load aata hai.
Q4: Transaction ka ACID property explain karo with MySQL example.
Answer:
- A (Atomicity): Saare operations ek unit — sab succeed ya sab fail. Bank transfer mein debit+credit dono hone chahiye.
- C (Consistency): Transaction ke baad DB valid state mein rahe. Constraints violate nahi hone chahiye.
- I (Isolation): Concurrent transactions ek doosre ko interfere na karein.
FOR UPDATElock lagata hai. - D (Durability): Committed data permanent hai, server crash ke baad bhi survive karta hai (InnoDB redo logs).
python example
conn.autocommit = False
try:
cursor.execute("UPDATE accounts SET balance = balance - 500 WHERE id = 1")
cursor.execute("UPDATE accounts SET balance = balance + 500 WHERE id = 2")
conn.commit() # Atomic — dono succeed
except:
conn.rollback() # Atomic — dono failQ5: fetchone(), fetchall(), aur fetchmany() mein kya difference hai? Kab kaunsa use karein?
Answer:
- fetchone(): Single row return karta hai. Use when: single result expected (e.g.,
SELECT COUNT(*), login check) - fetchall(): All rows at once. Use when: small result set (<1000 rows), need complete data in memory
- fetchmany(size): Batch of N rows. Use when: large dataset, memory-efficient processing
python example
# Large table processing
cursor.execute("SELECT * FROM million_rows_table")
while batch := cursor.fetchmany(5000):
process(batch) # Memory efficient!Interview mein mention karo: "fetchall() on a 10M row table can cause OOM (Out of Memory). Always use fetchmany() or server-side cursors for large datasets."
Q6: MySQL mein Indexing kya hai aur performance kaise improve karti hai?
Answer: Index ek data structure (B-Tree by default) hai jo SELECT queries ko fast banata hai — like a book's index.
- Without index: Full table scan (O(n)) — slow for large tables
- With index: B-Tree lookup (O(log n)) — significantly faster
- Types: PRIMARY KEY (auto-indexed), UNIQUE, INDEX, COMPOSITE INDEX, FULLTEXT
sql example
-- Slow: Full table scan
SELECT * FROM students WHERE email = 'alice@school.com';
-- Fast: Index on email column
CREATE INDEX idx_email ON students(email);
-- Ab same query O(log n) mein chalegiTrade-off: Index INSERT/UPDATE slow karti hai kyunki index bhi update hota hai. Read-heavy tables pe zyada indexes lagao, write-heavy pe kam.
Q7: Python mein MySQL ke saath context manager kaise implement karein?
Answer:
python example
from contextlib import contextmanager
import mysql.connector
@contextmanager
def get_db_connection(config):
conn = mysql.connector.connect(**config)
try:
yield conn
conn.commit()
except Exception:
conn.rollback()
raise
finally:
conn.close()
# Usage - connection auto-managed!
with get_db_connection(config) as conn:
cursor = conn.cursor(dictionary=True)
cursor.execute("SELECT * FROM students")
print(cursor.fetchall())
# Connection automatically closed, committed or rolled backContext manager ensures: (1) Automatic cleanup, (2) Exception-safe rollback, (3) No connection leaks. Interview mein ye pattern dikhana shows production-readiness.
Q8: MySQL mein EXPLAIN ka use kya hai aur Python se kaise run karein?
Answer: EXPLAIN query execution plan dikhata hai — MySQL query kaise execute karega (index use karega ya full scan).
python example
cursor.execute("EXPLAIN SELECT * FROM students WHERE grade = 'A'")
plan = cursor.fetchall()
# Output shows: type (ref/ALL), possible_keys, rows scanned, Extra- type=ALL: Full table scan (BAD) — index missing hai
- type=ref: Index used (GOOD)
- type=const: Primary key lookup (BEST)
Performance optimization mein EXPLAIN pehla step hai. Interview mein "I always use EXPLAIN to analyze slow queries before adding indexes" bolna impressive lagta hai.
Q9: executemany() internally kaise kaam karta hai aur kab use karna chahiye?
Answer: executemany() multiple INSERT/UPDATE statements ko batch mein execute karta hai. Internally ye:
- Single prepared statement banata hai
- Multiple parameter sets ke saath baar-baar execute karta hai
- Network round-trips reduce karta hai
python example
# ❌ Slow: 1000 individual inserts (1000 round-trips)
for student in students_list:
cursor.execute("INSERT INTO students VALUES (%s,%s)", student)
# ✅ Fast: executemany (batch optimization)
cursor.executemany("INSERT INTO students VALUES (%s,%s)", students_list)Performance: 1000 individual inserts ~5 seconds, executemany ~0.5 seconds. Use when: bulk inserts, batch updates, data migration.
Q10: Production mein MySQL connection ka best practice architecture kya honi chahiye?
Answer: Production setup mein ye layers honi chahiye:
- Connection Pool: 5-20 connections pre-created
- Retry Logic: Transient failures pe auto-retry (max 3 attempts)
- Health Check: Connection validity check before use
- Timeout:
connection_timeout=10,read_timeout=30 - Secrets Management: Password hardcode NAHI — environment variables ya AWS Secrets Manager use karo
Example
import os
from mysql.connector import pooling
pool = pooling.MySQLConnectionPool(
pool_size=10,
host=os.environ["DB_HOST"],
user=os.environ["DB_USER"],
password=os.environ["DB_PASSWORD"],
database=os.environ["DB_NAME"],
connection_timeout=10,
charset="utf8mb4",
)
Interview mein mention karo: "Never hardcode credentials, use connection pooling, implement retry logic, and set appropriate timeouts."
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for MySQL with Python (mysql-connector-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, mysql, with
Related Python Master Course Topics
Continue learning this concept
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.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.DatabasesORM Introduction with SQLAlchemyIntroduction to ORM (Object-Relational Mapping) with SQLAlchemy - models, sessions, queries, relationships, migrations with Alembic, and best practices with Hindi explanations.DatabasesSQLite3 with PythonComplete guide to SQLite3 with Python - creating databases, CRUD operations, transactions, error handling, and best practices with detailed Hindi explanations and code examples.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.