MYSQL query in a finite loop with sqlalchemy lost connection

Question

I`m building a finance web app with flask, sqlalchemy and MYSQL.

I try to query a collection of trading data in a stocks list loop, but it shows lost connection after some steps (about first 5~10 steps randomly).

for stock in stocks: #about 2000~3000 items
   engine = create_engine(os.getenv('MYSQL_DATABASE_URI')
   sql = 'select * from ..."
   df = pd.read_sql_query(sql, engine)
   return df

The error message is as following:

sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (2013, 'Lost connection to MySQL server during query') (Background on this error at: http://sqlalche.me/e/13/e3q8)

Any direction would be greatly appreciated!

Are you closing your connection? If you do SHOW PROCESSLIST; in MySQL you can see all your open connections. — CodeLikeBeaker

ohthatgeoff ohthatgeoff · Accepted Answer · 2020-12-31T20:49:47

I would suggest reusing a single engine object. First create the engine object.

engine = create_engine(os.getenv('MYSQL_DATABASE_URI')

for stock in stocks: #about 2000~3000 items
  sql = 'select * from ...'
  df = pd.read_sql_query(sql, engine)
  return df

Although this doesn't make a lot of sense because there is a return statement inside this loop so the loop never finishes.

Maybe make a list and return that instead?

engine = create_engine(os.getenv('MYSQL_DATABASE_URI')
values = []
for stock in stocks: #about 2000~3000 items
  sql = 'select * from ...'
  values.append(pd.read_sql_query(sql, engine))
return values

MYSQL query in a finite loop with sqlalchemy lost connection

1 Answers