Interface of Python with SQL Database

🌐 1. Introduction

We can connect Python to databases like MySQL to store, retrieve, and manipulate data.
This is very useful for creating database based applications in Python like inventory systems, school databases, library management system etc.

To connect Python program with MySQL database, we generally use a connector module such as:

import mysql.connector

🔌 2. Steps to connect MySQL with Python:

To work with a MySQL database, we need to follow the following:

Step 1: Import the connector module

import mysql.connector

Step 2: Establish connection

conn = mysql.connector.connect(
    host="localhost",
    user="root",
    password="yourpassword",
    database="school"
)

• host → it is a server name (usually localhost)
• user → MySQL username
• password → MySQL password
• database → name of database you want to use

Step 3: Create a cursor object

cursor = conn.cursor()

• The cursor acts like a control structure that allows Python to execute SQL commands.

⚙️ 3. Executing SQL Queries

We can use cursor.execute() function to run SQL commands.

a) INSERT Query

query = "INSERT INTO student (roll, name, marks) VALUES (%s, %s, %s)"
data = (101, 'Amit', 89)
cursor.execute(query, data)
conn.commit()   # Saves the change in the database

Note:

• %s is a placeholder used for values — prevents SQL injection and improves safety.
• Always call commit() after INSERT, UPDATE, or DELETE.

b) UPDATE Query

query = "UPDATE student SET marks = %s WHERE roll = %s"
data = (95, 101)
cursor.execute(query, data)
conn.commit()

c) DELETE Query

query = "DELETE FROM student WHERE roll = %s"
data = (101,)
cursor.execute(query, data)
conn.commit()

📜 4. Displaying / Retrieving Data

To fetch/extract records from a table:

cursor.execute("SELECT * FROM student")
result = cursor.fetchall()  # Fetches all rows

for row in result:
    print(row)

Other useful fetch methods:

• fetchone() → returns the next row of a query result. It returns one record at a time.
• fetchall() → returns all rows/records stored in the table.
• fetchmany()→ It is used to fetch a specific number of rows/records from the result of a query.
• rowcount → returns the number of rows affected or fetched.

Example:

cursor.execute("SELECT * FROM student")
print("Total Records:", cursor.rowcount)

record = cursor.fetchone()
print("First Record:", record)

🔹 Example of fetchmany():

import mysql.connector

conn = mysql.connector.connect(host='localhost', user='root', password='1234', database='school')
cursor = conn.cursor()

cursor.execute("SELECT * FROM student")

# Fetch only 3 rows
records = cursor.fetchmany(3)

for row in records:
    print(row)

conn.close()

👉 This will fetch only the first 3 records from the student table.

💾 5. Creating Database Connectivity Applications

You can create small programs that connect Python to MySQL for:

• Student Record System
• Library Management System
• Employee Payroll Application

Example:

import mysql.connector

# Step 1: Connect
conn = mysql.connector.connect(host='localhost', user='root', password='1234', database='school')
cursor = conn.cursor()

# Step 2: Perform Operation
query = "INSERT INTO student (roll, name, marks) VALUES (%s, %s, %s)"
data = (102, 'Riya', 92)
cursor.execute(query, data)

# Step 3: Commit and Close
conn.commit()
print(cursor.rowcount, "record inserted.")
conn.close()

🧠 6. Using %s and format() for Queries

You can use placeholders to insert variables safely into SQL statements.

Using %s

query = "INSERT INTO student VALUES (%s, %s, %s)"
values = (103, 'Anil', 85)
cursor.execute(query, values)

Using format()

roll = 104
name = 'Sita'
marks = 90
query = "INSERT INTO student VALUES ({}, '{}', {})".format(roll, name, marks)
cursor.execute(query)

⚠️ Note: The %s method is preferred as it automatically handles data types and prevents SQL injection.

📘 Summary Table

Function / Method	Purpose
connect()	Establishes connection with MySQL server
cursor()	Creates a cursor object for executing SQL commands
execute()	Executes an SQL query
commit()	Saves the changes made by queries
fetchone()	Fetches one record from the result set
fetchall()	Fetches all records from the result set
fetchmany()	It is used to fetch a specific number of rows from the result of a query.
rowcount	Returns number of rows affected/fetched

🧩 Example Program: Display All Students

import mysql.connector

conn = mysql.connector.connect(host='localhost', user='root', password='1234', database='school')
cursor = conn.cursor()

cursor.execute("SELECT * FROM student")
records = cursor.fetchall()

print("Roll\tName\tMarks")
for r in records:
    print(r[0], "\t", r[1], "\t", r[2])

conn.close()

✅ Key Points to Remember

• Always close the connection after completing operations using conn.close().
• Use commit() to permanently save changes.
• Handle exceptions using try and except for safety.
• Prefer parameterized queries (%s) for better security.

---