Fetching row by row from MySQL in Python

Writing this post as a note to myself. Many times we want to fetch records from MySQL row by row. We try to do that by the following code

import MySQLdb

conn = MySQLdb.connect(user="user", passwd="password", db="dbname")
cur = conn.cursor()

cur.execute("SELECT id, name FROM students")
row = cur.fetchone()
while row is not None:
    print row[0], row[1]
    row = cur.fetchone()

cur.close()
conn.close()

But remember that the default cursor fetches all data at once from the server, it does not matter that if you use fetchall or fetchone.

You have to use a different cursor which supports server side resultsets, like SSCursor or SSDictCursor.

import MySQLdb
import MySQLdb.cursors


conn = MySQLdb.connect(user="user", passwd="password", db="dbname", 
                       cursorclass = MySQLdb.cursors.SSCursor)
cur = conn.cursor()

cur.execute("SELECT id, name FROM students")
row = cur.fetchone()
while row is not None:
    print row[0], row[1]
    row = cur.fetchone()

cur.close()
conn.close()