What is the best way to fetch huge data from mysql with sqlalchemy?
<p>I want to process over 10 millions data stored in MySQL. So I wrote this to slice the sql to several parts then concatenate the data for latter process. It works well if <code>count &lt; 2 millions</code>. However when the <code>count</code> rise, the time sqlalchemy consumes goes much longer.</p>

<pre><code>def fetch_from_sql(_sql_pat, count):
    """
    :param _sql_pat: SELECT id, data FROM a.b LIMIT {},{};
    :param count: how many data you want to fetch from mysql
    :return: generator
    """
    def gen_connect(sql):
        __engine = create_engine(db_config['SQLALCHEMY_DATABASE_URI'])
        with __engine.connect() as c:
            for row in c.execute(sql)
                yield row

    def gen_range(limit, step):
        if step &gt; limit:
            yield 0, limit
        else:
            R = range(0, limit + 1, step)
            for idx, v in enumerate(R):
                if idx == 0:
                    yield v, step
                elif limit - v &gt;= step:
                    yield v + 1, step
                else:
                    yield v + 1, limit - v

    sqls = [_sql_pat.format(start, step) for start, step in gen_range(count, 100000)]
    sources = (gen_connect(sql) for sql in sqls)
    for s in sources:
        for item in s:
            yield item
        gc.collect()
</code></pre>

<p>The <strong>question</strong> is why the sqlalchemy take more and more time (I logged time and post below), and what is the best way to deal with this situationï¼</p>

<pre><code>Dumped 10000 items, at 2016-10-08 11:55:33
Dumped 1000000 items, at 2016-10-08 11:59:23
Dumped 2000000 items, at 2016-10-08 12:05:07
Dumped 3000000 items, at 2016-10-08 13:54:05
</code></pre>

<p>This is because you're using <code>LIMIT</code>/<code>OFFSET</code>, so when you specify offset 3000000, for example, the database has to skip over 3000000 records.</p>

<p>The correct way to do this is to <code>ORDER BY</code> some indexed column, like the primary key <code>id</code> column, for example, then do a <code>WHERE id &gt; :last_fetched_id</code>.</p>

python sqlalchemy