Fetching Query Results: fetchone(), fetchall() and rowcount (Class 12 Computer Science)

Understand Fetching Query Results: fetchone(), fetchall() and rowcount

cur.execute("SELECT Name, Marks FROM STUDENT ORDER BY Marks DESC")
print(cur.fetchone())
print(cur.fetchone())
print(cur.fetchall())

('Joy', 91)
('Asha', 88)
[('Meena', 79), ('Ravi', 64), ('Kiran', 55)]

cur.execute("SELECT * FROM STUDENT WHERE Marks > 75 ORDER BY RollNo")
data = cur.fetchall()
for r in data:
    print(r[1], "scored", r[3])
print(cur.rowcount, "records")

Asha scored 88
Meena scored 79
Joy scored 91
3 records

cur.execute("SELECT COUNT(*), AVG(Marks) FROM STUDENT")
rec = cur.fetchone()
print(rec[0], rec[1])

Fetching Query Results: fetchone(), fetchall() and rowcount: key facts

fetchone and fetchall

fetchone(): next row as a tuple, None when finished | fetchall(): all remaining rows as a list of tuples, [] when none

A row field is read by index: r[0], r[1].

rowcount

SELECT: number of rows in the result (read after fetchall) | INSERT, UPDATE, DELETE: number of rows affected

Use it to print the count of records.

Position of the cursor

• •Each fetchone moves to the next row
• •fetchall returns only what is left
• •After all rows are read, fetchone gives None

Fetch order follows ORDER BY.

Single-row queries

• •COUNT, SUM, AVG, MAX, MIN give one row
• •Use fetchone() and index the result
• •AVG is returned as a decimal value

Example: rec = cur.fetchone(); rec[0].

How to answer Fetching Query Results: fetchone(), fetchall() and rowcount questions

1. 1Work out the rows the SELECT returns, in the order given (ORDER BY decides it).
2. 2Each fetchone() takes the next unread row; fetchall() takes everything unread.
3. 3Write each fetched value in its Python form: a tuple in brackets, a list of tuples in square brackets, strings in quotes.
4. 4For a loop, apply the print statement to each row, using indexes for fields.
5. 5For rowcount, give the number of rows the query matched.
6. 6If the result is empty, fetchone gives None and fetchall gives an empty list.

Fetching Query Results: fetchone(), fetchall() and rowcount in real life

The idea, met in an everyday situation. Answer the question in your own words, then compare with the model answer. The one-line hook is what to remember.

Tricks to remember Fetching Query Results: fetchone(), fetchall() and rowcount

Short memory aids. Say each one aloud once, then use it on a question the same day.

• One versus all

  fetchone takes one row; fetchall takes all the rest.

  Use it when: Choosing the method.

• Row = tuple, result = list

  A row is a tuple; fetchall gives a list of those tuples.

  Use it when: Types of results.

• None after the last

  fetchone returns None when no rows remain.

  Use it when: Empty result cases.

• Index from 0

  r[0] is the first selected column.

  Use it when: Reading fields.

Fetching Query Results: fetchone(), fetchall() and rowcount: solved examples

Ordered from board level to HOTS. Try each one before you open the solution.

cur.execute("SELECT Name, Marks FROM STUDENT ORDER BY Marks DESC")
print(cur.fetchone())
print(cur.fetchone())
print(cur.fetchall())

cur.execute("SELECT COUNT(*), AVG(Marks), MAX(Marks) FROM STUDENT")
rec = cur.fetchone()
print("Students:", rec[0])
print("Average:", rec[1])
print("Highest:", rec[2])

Where students lose marks in Fetching Query Results: fetchone(), fetchall() and rowcount

The slips examiners see most often, and what to write instead.

• ✗Mistake: Writing a row without tuple brackets

  fetchone returns a tuple, printed in round brackets with quotes on strings.

  Wrong working

  Asha 88

  Right working

  ('Asha', 88)

  ✓Do this: Write the printed form exactly.

  Usually costs: 1 mark

• ✗Mistake: Using fetchall before execute

  The cursor has no result until execute is called.

  Wrong working

  data = cur.fetchall()
  cur.execute("SELECT * FROM STUDENT")

  Right working

  cur.execute("SELECT * FROM STUDENT")
  data = cur.fetchall()

  ✓Do this: Execute first, then fetch.

  Usually costs: 1 mark

• ✗Mistake: Reading rowcount before fetching

  rowcount is reliable after the rows are fetched.

  Wrong working

  print(cur.rowcount) just after execute

  Right working

  data = cur.fetchall()
  print(cur.rowcount)

  ✓Do this: Fetch first, then print rowcount.

  Usually costs: 0.5 mark

Practice questions on Fetching Query Results: fetchone(), fetchall() and rowcount

Write each answer on paper first. Open the hint only if you are stuck, and the answer only when you have finished.

1. Q1 · 1 mark · MCQBoard level

   What does fetchone() return when there are no more rows to read?
   (a) 0 (b) an empty list (c) None (d) an error

   Hint

   Think of nothing.

   Answer

   (c) None

2. Q2 · 1 mark · Very short answerBoard level

   Each row returned by fetchall() is of which Python type?

   Hint

   A row is shown in round brackets.

   Answer

   A tuple (and fetchall itself is a list of such tuples).

3. Q3 · 2 marks · Short answerHarder board level

   After cur.execute("SELECT Name FROM STUDENT WHERE City = 'Delhi' ORDER BY RollNo"), write the value of cur.fetchall().

   Hint

   Two students live in Delhi.

   Answer

   [('Asha',), ('Joy',)]. Each row is a one-element tuple, so it is written with a trailing comma.

4. Q4 · 1 mark · Assertion-reasonHarder board level

   Assertion (A): After fetchall() has read the whole result, a call to fetchone() returns None.
   Reason (R): fetchall() moves the cursor past the last row of the result.
   (a) Both A and R are true and R is the correct explanation of A. (b) Both A and R are true but R is not the correct explanation of A. (c) A is true but R is false. (d) A is false but R is true.

   Hint

   Which rows remain?

   Answer

   (a) Both are true and R explains A.

5. Q5 · 3 marks · Short answerHOTS / competency

   Write a program fragment that asks for a city name, shows the names and marks of the students from that city, and prints "No record found" if there is none. Explain the test used for "none".

   Hint

   Use the format or %s from the next topic, then test the list.

   Answer

   city = input("City: ")
   cur.execute("SELECT Name, Marks FROM STUDENT WHERE City = %s", (city,))
   data = cur.fetchall()
   if data == []:
       print("No record found")
   else:
       for r in data:
           print(r[0], r[1])

   When no row matches, fetchall() returns an empty list, so data == [] is True. For input Delhi it prints Asha 88 and Joy 91.

Test your recall

Answer in your head or on paper first, then open the card. Pulling an answer out of memory makes it stick far better than re-reading.

1. 1.What does fetchone() return?Show answer

   The next row as a tuple, or None.

2. 2.What does fetchall() return?Show answer

   A list of tuples (all remaining rows).

3. 3.What does rowcount give after a SELECT with fetchall?Show answer

   The number of rows retrieved.

4. 4.What is an empty fetchall result?Show answer

   An empty list.

5. 5.How is a column of a fetched row read?Show answer

   By index, such as r[0].

6. 6.Which method comes before any fetch?Show answer

   execute().

Fetching Query Results: fetchone(), fetchall() and rowcount: frequently asked questions