1. Home
  2. Fetching records using fetchone() and fetchmany()

Fetching records using fetchone() and fetchmany()

Updated on Jan 07, 2020

Up until now we have been using fetchall() method of cursor object to fetch the records. This process of accessing all records in one go is not every efficient. As a result MySQLdb has fetchone() and fetchmany() methods of cursor object to fetch records more efficiently.

Method Description
fetchone() This method returns one record as a tuple, If there are no more records then it returns None.
fetchmany(number_of_records) This method accepts number of records to fetch and returns tuple where each records itself is a tuple. If there are not more records then it returns an empty tuple.

Using fetchone() #

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
from __future__ import print_function

import MySQLdb as my

try:

    db = my.connect(host="127.0.0.1",
                    user="root",
                    passwd="",
                    db="world"
                    )

    cursor = db.cursor()

    sql = "select * from city where id < 10"
    number_of_rows = cursor.execute(sql)

    print(cursor.fetchone())  # fetch the first row only

    db.close()

except my.DataError as e:
    print("DataError")
    print(e)

except my.InternalError as e:
    print("InternalError")
    print(e)

except my.IntegrityError as e:
    print("IntegrityError")
    print(e)

except my.OperationalError as e:
    print("OperationalError")
    print(e)

except my.NotSupportedError as e:
    print("NotSupportedError")
    print(e)

except my.ProgrammingError as e:
    print("ProgrammingError")
    print(e)

except :
    print("Unknown error occurred")

Looping over the result using fetchone() #

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
from __future__ import print_function

import MySQLdb as my

try:

    db = my.connect(host="127.0.0.1",
                    user="root",
                    passwd="",
                    db="world"
                    )

    cursor = db.cursor()

    sql = "select * from city where id < 10"
    number_of_rows = cursor.execute(sql)

    while True:
        row = cursor.fetchone()
        if row == None:
            break
        print(row)

    db.close()

except my.DataError as e:
    print("DataError")
    print(e)

except my.InternalError as e:
    print("InternalError")
    print(e)

except my.IntegrityError as e:
    print("IntegrityError")
    print(e)

except my.OperationalError as e:
    print("OperationalError")
    print(e)

except my.NotSupportedError as e:
    print("NotSupportedError")
    print(e)

except my.ProgrammingError as e:
    print("ProgrammingError")
    print(e)

except :
    print("Unknown error occurred")

Using fetchmany() #

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
from __future__ import print_function

import MySQLdb as my

try:

    db = my.connect(host="127.0.0.1",
                    user="root",
                    passwd="",
                    db="world"
                   )

    cursor = db.cursor()

    sql = "select * from city where id < 10"
    number_of_rows = cursor.execute(sql)

    print(cursor.fetchmany(2))  # fetch first 2 rows only

    db.close()

except my.DataError as e:
    print("DataError")
    print(e)

except my.InternalError as e:
    print("InternalError")
    print(e)

except my.IntegrityError as e:
    print("IntegrityError")
    print(e)

except my.OperationalError as e:
    print("OperationalError")
    print(e)

except my.NotSupportedError as e:
    print("NotSupportedError")
    print(e)

except my.ProgrammingError as e:
    print("ProgrammingError")
    print(e)

except :
    print("Unknown error occurred")

Looping over the result using fetchmany() #

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
from __future__ import print_function

import MySQLdb as my

try:

    db = my.connect(host="127.0.0.1",
                    user="root",
                    passwd="",
                    db="world"
                   )

    cursor = db.cursor()

    sql = "select * from city where id < 10"
    number_of_rows = cursor.execute(sql)

    while True:
        two_rows = cursor.fetchmany(2)
        if not two_rows:
            break
        print(two_rows)

    db.close()

except my.DataError as e:
    print("DataError")
    print(e)

except my.InternalError as e:
    print("InternalError")
    print(e)

except my.IntegrityError as e:
    print("IntegrityError")
    print(e)

except my.OperationalError as e:
    print("OperationalError")
    print(e)

except my.NotSupportedError as e:
    print("NotSupportedError")
    print(e)

except my.ProgrammingError as e:
    print("ProgrammingError")
    print(e)

except :
    print("Unknown error occurred")

Other Tutorials (Sponsors)

Recent Posts