SQL and Python Connectivity
1. Theoretical Concepts of Database Connectivity
Database connectivity creates a bridge between a programming language (Python) and
a database system (MySQL). This follows a Client-Server Architecture:
• The Client (Front-end): The Python program that sends requests.
• The Server (Back-end): The MySQL database that processes requests and
stores data.
• The Connector: A specific library (API) that acts as a translator between Python
and MySQL. We use mysql-connector-python.
Getty Images
Explore
2. The Workflow: A Restaurant Analogy
To understand the steps, imagine you are at a restaurant ordering food.
1. Connection (connect()): You walk in and sit at a table. You establish a link
between yourself (Python) and the Restaurant (Database).
2. Cursor (cursor()): You cannot enter the kitchen yourself. You need a Waiter. The
Cursor is the waiter who takes your orders (Queries) to the kitchen and brings the
food (Data) back.
3. Execution (execute()): You tell the waiter: "Bring me a Burger." This is sending the
SQL command.
4. Commit (commit()): If you ask to change the menu (Insert/Update/Delete), the
Manager must approve it to make it permanent. commit() is that stamp of
approval.
5. Fetch (fetchall()): The waiter returns with food on a tray. You must pick up the
food (Fetch) to eat it (use it in Python).
3. Key Functions and Steps
Step 1: The Connection Object
Theory: Represents the session/link with the database server.
• Syntax: mydb = [Link](host="...", user="...", password="...",
database="...")
• Arguments:
o host: Server address (e.g., 'localhost').
o user & password: Authentication credentials.
o database: The specific database to work on.
Step 2: The Cursor Object
Theory: A temporary work area in memory. It allows Python to process rows one by one,
rather than loading the entire database into RAM.
• Syntax: mycursor = [Link]()
Step 3: Execution
Theory: Compiles and sends the SQL command to the server.
• Syntax: [Link]("SELECT * FROM Student")
Step 4: Transaction Management (Commit)
Theory: Required for DML commands (INSERT, UPDATE, DELETE). It permanently saves
changes. If omitted, the database performs a Rollback (undoes changes) when the
connection closes.
• Syntax: [Link]()
Step 5: Data Retrieval (Fetch)
Used after SELECT queries to pull data from the cursor into Python variables.
• fetchone(): Returns the next single row as a tuple. Returns None if empty.
• fetchall(): Returns all remaining rows as a list of tuples. Returns [] if empty.
• rowcount: A property that returns the number of rows affected (by
Update/Delete) or read (by Select).
Step 6: Cleanup
Theory: Frees up system resources (memory, network ports).
• Syntax: [Link]() and [Link]().
4. Parameter Substitution: %s vs format()
When sending user input (like a name) to the database, you have two options. One is
safe, the other is dangerous.
A. The Safe Way: %s (Parameterized Queries)
This prevents SQL Injection (hacking the DB via input fields). The database driver
handles the input as raw data, not executable code.
• Syntax:
Python
sql = "SELECT * FROM Student WHERE Name = %s"
val = ("Alice", ) # Tuple
[Link](sql, val)
B. The Unsafe Way: format()
Python simply pastes the text together. If a user inputs malicious code (e.g., Alice';
DROP TABLE Student;), the database will execute it!
• Syntax (Avoid this):
Python
sql = "SELECT * FROM Student WHERE Name = '{}'".format(user_input)
5. Complete Practical Application
Here is a complete Python script that Connects, Creates, Inserts, Updates, Fetches,
and Cleans up.
Python
import [Link]
# --- STEP 1: CONNECT ---
mydb = [Link](
host="localhost",
user="root",
password="password123",
database="SchoolDB"
)
cursor = [Link]()
print("1. Connected to Database.")
# --- STEP 2: INSERT DATA (DML) ---
# We use %s placeholders for safety.
# Note: %s is used for all data types (Int, Date, String) in Python SQL connectors.
sql_insert = "INSERT INTO STUDENT (RollNo, Name, Score) VALUES (%s, %s, %s)"
val = (101, "Amit", 88.5)
[Link](sql_insert, val)
[Link]() # Mandatory for Insert
print(f"2. Record Inserted. Rows Affected: {[Link]}")
# --- STEP 3: UPDATE DATA (DML) ---
# Let's give Amit bonus marks
sql_update = "UPDATE STUDENT SET Score = Score + 5 WHERE RollNo = %s"
val_update = (101, ) # Tuple with one item needs a comma
[Link](sql_update, val_update)
[Link]() # Mandatory for Update
print(f"3. Record Updated. Rows Affected: {[Link]}")
# --- STEP 4: FETCH AND DISPLAY (SELECT) ---
print("\n4. Reading Data:")
[Link]("SELECT * FROM STUDENT")
# The cursor now holds the data. We fetch it.
# Let's use fetchall() to get everything.
all_rows = [Link]()
for row in all_rows:
# row is a tuple: (101, 'Amit', 93.5)
print(row)
# --- STEP 5: CLEANUP ---
[Link]()
[Link]()
print("\n5. Connection Closed.")
6. Summary Checklist
Step Function Purpose
Start connect() Open connection to DB.
Prepare cursor() Create the "waiter" to handle queries.
Action execute() Run the SQL command.
Save commit() Only for INSERT/UPDATE/DELETE. Saves changes.
Retrieve fetchall() Only for SELECT. Gets the data out.
End close() Clean up connection.