使用块从MySQL数据库中检索大数据，并将其保存为数据帧

Question

我想从一个SQL数据库中检索大约1亿行和30列数据到一个数据框中，在这个数据框中我可以根据特定的要求进行排序和过滤。我只有2千兆内存。即使我在使用chunksize，一切都停止了。这是我的密码

import pymysql
chunksize = 100
import pandas as pd
import pymysql.cursors
from urllib import parse```

sqlEngine = create_engine('mysql+pymysql://username:%s@localhost/db' % parse.unquote_plus('password'))
dbConnection    = sqlEngine.connect()

for chunk in pd.read_sql("select * from db.db_table", dbConnection, chunksize = chunksize):
    print(chunk)

Do somrthing with chunk(chunk is the dataframe that has all the 100 million columns )

我已经缩小了我的尺寸，但仍然没有得到任何东西

Answer 1

详细阐述一下我的评论，大概是这样的

不过，我预计，要在2GB内存中容纳1亿行x30列，您会遇到困难

df = None
for offset in itertools.count(step=chunksize):
    print("Reading chunk %d..." % offset)
    query = "select * from db.db_table order by id limit %d offset %d" % (chunksize, offset)
    chunk_df = pd.read_sql(query, dbConnection)
    if not chunk_df:  # TODO: this check might not be correct
        # No data in new chunk, so we probably have it all
        break
    if not df:
        df = chunk_df
    else:
        df = pd.concat([df, chunk_df], copy=False)

# do things with DF