Best way to iterate through all rows in a DB-table

user_138584 0
Viewed 38033

I often write little Python scripts to iterate through all rows of a DB-table. For example sending all to all subscribers a email.

I do it like this

conn = MySQLdb.connect(host = hst, user = usr, passwd = pw, db = db)
cursor = conn.cursor()
subscribers = cursor.execute("SELECT * FROM tbl_subscriber;")

for subscriber in subscribers:
 ...

conn.close()

I wonder if there is a better way to do this cause it is possible that my code loads thousands of rows into the memory.

I thought about that it could be done better with LIMIT. Maybe something like that:

"SELECT * FROM tbl_subscriber LIMIT %d,%d;" % (actualLimit,steps)

Whats the best way to do it? How would you do it?

mysql python database
6 Answers

RS_1st = win32com.client.Dispatch(r’ADODB.Recordset’) RS_1st.Open(“SELECT colA_, colB_, colC_ FROM tbl_subscriber “, Conn, CursorType = 3, LockType = 1)

for colA_, colB_, colC_ in numpy.transpose(RS_1st.GetRows()).tolist():

OR

for arrCol_ in numpy.transpose(RS_1st.GetRows()).tolist():

OR

cursor.execute("SELECT * FROM tbl_subscriber;")

for subscriber in cursor.fetchall():

user_155114480
Related