Retrieving Data from MySQL using Python (CBSE Class 12 Computer Science - 083)

After inserting, updating, and deleting records in a database, the next step is to retrieve and display data. Python provides the execute() method to execute the SELECT query and the fetchone() and fetchall() methods to retrieve records from the result set.

Learning Objectives

• Execute SELECT queries using Python.
• Retrieve records using fetchone().
• Retrieve records using fetchall().
• Display records using loops.
• Understand the differences between fetchone() and fetchall().

---

Sample Student Table

StudentID	Name	City	Marks
101	Riya	Jaipur	95
102	Aman	Delhi	91
103	Priya	Jaipur	88
104	Rahul	Mumbai	76

---

Using SELECT with Python

The SELECT statement retrieves data from one or more database tables. In Python, the query is executed using the execute() method.

Syntax

sql = "SELECT * FROM Student"

cursor.execute(sql)

---

The fetchone() Method

The fetchone() method retrieves only the next available row from the result set. It returns a tuple containing the column values of a single record.

Syntax

record = cursor.fetchone()

---

Example: Display One Record

import mysql.connector

con = mysql.connector.connect(
    host="localhost",
    user="root",
    password="root",
    database="School"
)

cur = con.cursor()

cur.execute("SELECT * FROM Student")

record = cur.fetchone()

print(record)

con.close()

Output

(101, 'Riya', 'Jaipur', 95)

---

Using fetchone() Multiple Times

Each call to fetchone() retrieves the next record from the result set.

cur.execute("SELECT * FROM Student")

print(cur.fetchone())
print(cur.fetchone())
print(cur.fetchone())

Output

(101, 'Riya', 'Jaipur', 95)
(102, 'Aman', 'Delhi', 91)
(103, 'Priya', 'Jaipur', 88)

---

The fetchall() Method

The fetchall() method retrieves all remaining records from the result set and returns them as a list of tuples.

Syntax

records = cursor.fetchall()

---

Example: Display All Records

import mysql.connector

con = mysql.connector.connect(
    host="localhost",
    user="root",
    password="root",
    database="School"
)

cur = con.cursor()

cur.execute("SELECT * FROM Student")

records = cur.fetchall()

print(records)

con.close()

Output

[
(101, 'Riya', 'Jaipur', 95),
(102, 'Aman', 'Delhi', 91),
(103, 'Priya', 'Jaipur', 88),
(104, 'Rahul', 'Mumbai', 76)
]

---

Displaying Records Using a Loop

The records returned by fetchall() can be displayed one by one using a for loop.

cur.execute("SELECT * FROM Student")

records = cur.fetchall()

for row in records:
    print(row)

Output

(101, 'Riya', 'Jaipur', 95)
(102, 'Aman', 'Delhi', 91)
(103, 'Priya', 'Jaipur', 88)
(104, 'Rahul', 'Mumbai', 76)

---

Displaying Individual Column Values

Each record is returned as a tuple. Individual values can be accessed using indexing.

cur.execute("SELECT * FROM Student")

records = cur.fetchall()

for row in records:
    print("Student ID :", row[0])
    print("Name       :", row[1])
    print("City       :", row[2])
    print("Marks      :", row[3])
    print()

---

Retrieving Selected Columns

cur.execute("""
SELECT Name, Marks
FROM Student
""")

records = cur.fetchall()

for row in records:
    print(row)

Output

('Riya', 95)
('Aman', 91)
('Priya', 88)
('Rahul', 76)

---

Retrieving Records with WHERE Clause

cur.execute("""
SELECT *
FROM Student
WHERE City='Jaipur'
""")

records = cur.fetchall()

for row in records:
    print(row)

---

Difference Between fetchone() and fetchall()

fetchone()	fetchall()
Returns one record.	Returns all records.
Returns a tuple.	Returns a list of tuples.
Useful for processing one record at a time.	Useful for displaying all records.
Returns None when no records remain.	Returns an empty list if no records are found.

---

Execution Flow

Connect Database
      │
      ▼
Create Cursor
      │
      ▼
execute(SELECT Query)
      │
      ▼
fetchone() / fetchall()
      │
      ▼
Display Records
      │
      ▼
Close Connection

---

Common Errors

Error	Reason
ProgrammingError	Calling fetchone() or fetchall() before execute().
TypeError	Trying to access a record when fetchone() returns None.
IndexError	Accessing a tuple index that does not exist.
No Output	The SELECT query returned no matching records.

---

Exam Tips

---

Frequently Asked Questions (FAQs)

1. Which method retrieves a single record?

The fetchone() method retrieves one record at a time.

2. Which method retrieves all records?

The fetchall() method retrieves all remaining records.

3. What does fetchall() return?

It returns a list of tuples.

4. What does fetchone() return when no records remain?

It returns None.

5. Can WHERE be used in SELECT queries executed from Python?

Yes. Any valid SQL SELECT query can be executed using the execute() method.

---

Summary

• The SELECT statement retrieves records from a database.
• execute() runs the SELECT query.
• fetchone() retrieves one record at a time.
• fetchall() retrieves all records as a list of tuples.
• Records are commonly displayed using a for loop.
• Each row returned by MySQL is represented as a tuple in Python.
• These methods are fundamental for building Python-MySQL database applications.