Simple example of using a Python generator to fetch a row from a MySQL database

db_row_generator.py

#!/usr/bin/env python

"""
Here is a simple example of how you can
use a generator to fetch a row from a database

"""

import MySQLdb


def subscriber_generator():
    """ 
    Simple generator example to return a row
    from a MySQL database

    """

    # Simplicity purposes, we're connecting locally
    db = MySQLdb.connect(user='root',db='your_db_name')

    # Get a cursor
    cursor = db.cursor()

    # Execute your MySQL query
    cursor.execute(
        'SELECT id,first_name FROM subscribers_subscribers '
        + 'WHERE id > %s AND id < %s '
        + 'ORDER BY first_name ASC', (1234,1334))

    # Iterate through fetchall(), but yield the row
    for row in cursor.fetchall():
        yield row 

def main():
    """ 
    Main

    """
    # You can use the next() method to get a row
    print(subscriber_generator().next())

    # Or you can iterate through all with a for loop
    for row in subscriber_generator():
        print(row)


if '__main__' == __name__:
    main()

I think it's not the best use of memory resources

How about replacing this

for row in cursor.fetchall():
        yield row 

with this

batch_size = 1000 # your size

while True:
    rows = cursor.fetchmany(batch_size)

    if not rows:
        break

    yield rows

Nice point @rollmate

And thank you, @drincruz, for this explanation; it was enlightening.