MySQL Connectivity With Python - One By One (
Page 4 of 7 )
You can also use the fetchone() method of the cursor object to get and display rows one at a time, instead of all at once. Consider the following example, which demonstrates how this might work:
#!/usr/bin/python
# import MySQL module
import MySQLdb
# connect
db = MySQLdb.connect(host="localhost", user="joe", passwd="secret",
db="db56a")
# create a cursor
cursor = db.cursor()
# execute SQL statement
cursor.execute("SELECT * FROM animals")
# get the number of rows in the resultset
numrows = int(cursor.rowcount)
# get and display one row at a time
for x in range(0,numrows):
row = cursor.fetchone()
print row[0], "-->", row[1]
In this case, the rowcount() method of the cursor object is first used to find out the number of rows in the resultset. This number is then used in a "for" loop to iterate over the resultset, with the fetchone() method used to move forward through the resultset and sequentially display the contents of each row.
You can get MySQLdb to give you a specific subset of the resultset with the cursor object's fetchmany() method, which allows you to specify the size of the returned resultset. Consider the following example, which demonstrates:
#!/usr/bin/python
# import MySQL module
import MySQLdb
# connect
db = MySQLdb.connect(host="localhost", user="joe", passwd="secret",
db="db56a")
# create a cursor
cursor = db.cursor()
# execute SQL statement
cursor.execute("SELECT * FROM animals")
# limit the resultset to 3 items
result = cursor.fetchmany(3)
# iterate through resultset
for record in result:
print record[0] , "-->", record[1]
