offset/limit programmatically with python mysqldb

Im trying to process data from a database table. Loading them all simultaneously will hog most o开发者_如何学编程f computer resource. Heres my current code

cursor.execute("SELECT * from sometable")
rs = cursor.fetchall()
.
# process 1: set operation
.
for a in rs:
    # process 2: put data in another db

Is there a way to reduce resource usage? Like getting rows bit by bit under a loop?

You can use cursor.fetchone(). Read about it here. Example usage:

cursor.execute(query)
numrows = int(cursor.rowcount)
for x in range(0,numrows):
    row = cursor.fetchone()

You can also consider using LIMIT in the mysql query:

cursor.execute("SELECT * from sometable LIMIT 0, 100")

Finally, avoid using the star operator and only select the columns you need.

Using cursor.fetchone() will probably still cause resource issues because of the SELECT * statement before it. I believe the best way to do this is to limit the query results with LIMIT and just loop through them all.