MySQL Connection not available when use SQLAlchemy(MySQL) and Flask

Question

I'm getting this error sometime (sometime is ok, sometime is wrong):

sqlalchemy.exc.OperationalError: (OperationalError) MySQL Connection not available.

while using session.query

I'm writing a simple server with Flask and SQLAlchemy (MySQL). My app.py like this:

Session = sessionmaker(bind=engine)
session = Session()

@app.route('/foo')
def foo():
    try:
        session.query(Foo).all()
    except Exception:
        session.rollback()

Update I also create new session in another file and call it in app.py

Session = sessionmaker(bind=engine)
session = Session()

def foo_helper(): #call in app.py
    session.query(Something).all()

Update 2 My engine:

engine = create_engine('path')

How can I avoid that error?

Thank you!

How do you create engine? Are you sure your connection details are right? — dirn
– dirn, Commented Nov 12, 2014 at 17:40
And be sure that you have the necessary package to actually connect to MySQL. — Celeo
– Celeo, Commented Nov 12, 2014 at 19:12
I connect to MySQL successfully. My server run OK except it down, because that error, sometime (not always). I mean I'm sure the engine is right — Tuan Chau
– Tuan Chau, Commented Nov 12, 2014 at 23:35
Check out Flask-SQLAlchemy, it abstracts a lot of this away for you. — Rachel Sanders
– Rachel Sanders, Commented Nov 12, 2014 at 23:36
It means that you are trying to make connection but no connection available. Try to increase pool_size — Sagar
– Sagar, Commented Jan 12, 2024 at 12:45

Nima Soroush · Accepted Answer · 2014-11-12 23:33:52Z

7

Make sure the value of ‘pool_recycle option’ is less than your MYSQLs wait_timeout value when using SQLAlchemy ‘create_engine’ function.

engine = create_engine("mysql://username:password@localhost/myDatabase", pool_recycle=3600)

Try to use scoped_session to make your session:

from sqlalchemy.orm import scoped_session, sessionmaker
session = scoped_session(sessionmaker(autocommit=False, autoflush=False, bind=engine))

and close/remove your session after retrieving your data.

session.query(Foo).all()
session.close()

answered Nov 12, 2014 at 23:33

Nima Soroush

12.9k4 gold badges54 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Tuan Chau Over a year ago

By the last solution, you mean I will need to close and reopen everytime I query, don't you?

Nima Soroush Over a year ago

Yes. scoped_session offers all of the methods of the underlying Session via a proxy pattern, so you can call close() on scoped_session, which calls close() on the actual session, or you can call remove(), which emits close() then removes the Session object itself from the registry. The latter has the advantage that any particular state established on the session, such as a Connection-based bind (see the example in sqlalchemy.org/docs/orm/… ), or other particular constructor options, are discarded

Collectives™ on Stack Overflow

MySQL Connection not available when use SQLAlchemy(MySQL) and Flask

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related