mysql循环处理结果集

import mysql.connector

# 连接数据库
conn = mysql.connector.connect(user='user', password='password', host='host', database='database')
cursor = conn.cursor()

# 查询数据
query = "SELECT * FROM table_name"
cursor.execute(query)

# 循环处理结果集
batch_size = 1000
batch = []
for row in cursor:
    batch.append(row)
    if len(batch) >= batch_size:
        # 批量插入示例
        insert_query = "INSERT INTO target_table (col1, col2) VALUES (%s, %s)"
        cursor.executemany(insert_query, batch)
        conn.commit()
        batch = []

# 处理剩余数据
if batch:
    cursor.executemany(insert_query, batch)
    conn.commit()

# 关闭连接
cursor.close()
conn.close()

import mysql.connector

# 连接数据库
conn = mysql.connector.connect(user='user', password='password', host='host', database='database')
cursor = conn.cursor(dictionary=True)

# 分页查询
page_size = 1000
offset = 0
while True:
    query = f"SELECT * FROM table_name LIMIT {page_size} OFFSET {offset}"
    cursor.execute(query)
    rows = cursor.fetchall()
    if not rows:
        break
    
    # 循环处理结果集
    for row in rows:
        # 处理每一行数据
        pass
    
    offset += page_size

# 关闭连接
cursor.close()
conn.close()