1. Home
  2. MySQLdb Fetching results

MySQLdb Fetching results

Updated on Jan 07, 2020

In the previous post we have seen how to execute sql queries using execute() method. The execute() method returns affected rows, it doesn't return the result. To fetch result we use the fetchall() method of the cursor object.

Syntax: cursor.fetchall()

On success it returns tuple of rows where each row is a tuple.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
from __future__ import print_function

import MySQLdb as my

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

cursor = db.cursor()

number_of_rows = cursor.execute("select * from city");

result = cursor.fetchall()

print(result)

db.close()

The above code will print all the rows in the city table.

You can also use for loop to iterate over the results.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
from __future__ import print_function

import MySQLdb as my

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

cursor = db.cursor()

number_of_rows = cursor.execute("select * from city");

result = cursor.fetchall()
for row in result:
  print(row)

db.close()

Some more examples.

Example 1:

 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
from __future__ import print_function

import MySQLdb as my

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

cursor = db.cursor()

id = 10

operation = ">"

sql = "select * from city where id {} {}".format(operation, id)

number_of_rows = cursor.execute(sql)

result = cursor.fetchall()
for row in result:
  print(row[0], row[1])

db.close()

Example 2:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
from __future__ import print_function

import MySQLdb as my

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

cursor = db.cursor()
city = "%pur%"

sql = "select * from city where name like '{}'".format(city)

number_of_rows = cursor.execute(sql)

result = cursor.fetchall()
for row in result:
  print(row[0], row[1])

db.close()

In the next post, we discuss how to insert rows into the database.

Other Tutorials (Sponsors)

Recent Posts