SQL Alchemy timing out when executing large query

Question

I have a large query to execute through SQL Alchemy which has approximately 2.5 million rows. It's connecting to a MySQL database. When I do:

transactions = Transaction.query.all()

It eventually times out around ten minutes. And gets this error: sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (2013, 'Lost connection to MySQL server during query')

I've tried setting different parameters when doing create_engine like:

create_engine(connect_args={'connect_timeout': 30})

What do I need to change so the query will not timeout?

I would also be fine if there is a way to paginate the results and go through them that way.

There is a page in [the MySQL docs](https://dev.mysql.com/doc/refman/8.0/en/gone-away.html) that might help you with debugging. — SuperShoot, Nov 23 '18 at 21:21
Also [this answer](https://stackoverflow.com/a/7390660/6560549) covers pagination. — SuperShoot, Nov 23 '18 at 21:23

score 0 · Accepted Answer · answered Nov 29 '18 at 15:02

Solved by pagination:

    page_size = 10000  # get x number of items at a time
    step = 0
    while True:
        start, stop = page_size * step, page_size * (step+1)
        transactions = sql_session.query(Transaction).slice(start, stop).all()
        if transactions is None:
            break
        for t in transactions:
            f.write(str(t))
            f.write('\n')
        if len(transactions) < page_size:
            break
        step += 1
    f.close()

SQL Alchemy timing out when executing large query

1 Answers1

Linked