What is the fetchone() method? Explain its use in MySQL Python?

The fetchone() method in MySQL Python is used to retrieve only the first row from a query result. It returns a single tuple representing one row from the database table.

What is fetchone()?

The fetchone() method is called on a cursor object after executing a SELECT query. Unlike fetchall() which retrieves all rows, fetchone() returns only the first row as a tuple. If no rows are found, it returns None.

Syntax

cursor.fetchone()

How fetchone() Works

The process involves these steps:

• Execute a SELECT query using cursor.execute()
• The cursor object holds the result set
• Call fetchone() to get the first row
• Subsequent calls to fetchone() return the next row (if any)

Example with Sample Data

Let's assume we have a table named "students" with the following data:

+--------+-------+-----------+-------+
|  Name  | Class |   City    | Marks |
+--------+-------+-----------+-------+
| Karan  |   4   | Amritsar  |  95   |
| Sahil  |   6   | Amritsar  |  93   |
| Kriti  |   3   | Batala    |  88   |
| Khushi |   9   | Delhi     |  90   |
| Kirat  |   5   | Delhi     |  85   |
+--------+-------+-----------+-------+

Basic fetchone() Example

import mysql.connector

# Establish connection
db = mysql.connector.connect(
    host="localhost",
    user="your_username", 
    password="your_password",
    database="your_database"
)

cursor = db.cursor()

# Execute query
query = "SELECT * FROM students"
cursor.execute(query)

# Get first row
first_row = cursor.fetchone()
print("First row:", first_row)

# Get second row (next call to fetchone)
second_row = cursor.fetchone()
print("Second row:", second_row)

# Close connection
cursor.close()
db.close()

First row: ('Karan', 4, 'Amritsar', 95)
Second row: ('Sahil', 6, 'Amritsar', 93)

Using fetchone() in a Loop

import mysql.connector

db = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password", 
    database="your_database"
)

cursor = db.cursor()
cursor.execute("SELECT name, marks FROM students WHERE marks > 90")

# Process rows one by one
while True:
    row = cursor.fetchone()
    if row is None:
        break
    print(f"Student: {row[0]}, Marks: {row[1]}")

cursor.close()
db.close()

Student: Karan, Marks: 95
Student: Sahil, Marks: 93

Key Points

• fetchone() returns a tuple for each row
• Returns None when no more rows are available
• Memory efficient for processing large result sets row by row
• Each call advances to the next row in the result set

Comparison with Other Fetch Methods

Conclusion

The fetchone() method is ideal for retrieving and processing database rows one at a time. It's memory-efficient and useful when you only need the first row or want to iterate through results sequentially.