Created
August 22, 2014 16:17
-
-
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
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/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
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
for Python 3.12 I used:
print(next(subscriber_generator()))