Skip to content

Instantly share code, notes, and snippets.

@drincruz
Created August 22, 2014 16:17
Show Gist options
  • Save drincruz/b3904cc338fc60a19f44 to your computer and use it in GitHub Desktop.
Save drincruz/b3904cc338fc60a19f44 to your computer and use it in GitHub Desktop.
Simple example of using a Python generator to fetch a row from a MySQL database
#!/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()
@rollmate
Copy link

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

@odravison
Copy link

Nice point @rollmate

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment