My bottle/sqlalchemy app is hosted on pythonanywhere.com, not sure if this matters, I hope not. The app throws 'Lost connection to MySQL server during query' after I leave it for a while. Then I refresh it and it works fine.
Please note that I also have the same query implemented without sqlachemy, using only MySQLdb. That implementation works fine all the time, it never throws exception, because it establishes a new connection every time.
I assume, in a hosted env like pythonanywhere, I cannot fiddle with mysql config that may affect this error, e.g max_allowed_packet or timeout.
How should I create the sqlalchemy engine and the session to solve this problem?
bottle_app.py:
db_host = 'localhost'
db_user = 'root'
db_password = 'gggggg'
db_dbname = 'test'
#web framework imports
from bottle import default_app, route, run, template, redirect
#sqlalchemy and mysql setup
mysql_connect_string = 'mysql+mysqldb://%s:%s@%s/%s?charset=utf8' % (db_user, db_password, db_host, db_dbname)
from sqlalchemy import create_engine
engine = create_engine(mysql_connect_string)
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
Base.metadata.bind = engine
from sqlalchemy import Column, Integer, String, Float, SmallInteger
from sqlalchemy.orm import sessionmaker
DBSession = sessionmaker()
DBSession.bind = engine
#import mysql package for raw SQL
import MySQLdb
#sqlalchemy model class for table 'nevek'
class Person(Base):
__tablename__ = 'nevek'
# Here we define columns for the table nevek
id = Column(Integer, primary_key=True)
name = Column(String(250), nullable=False)
lev = Column(SmallInteger)
point = Column(Float)
play = Column(Integer)
kmp = Column(Float)
#get records from table nevek with sqlalchemy
def get_nevek_from_db():
session = DBSession()
result = session.query(Person).all()
session.close()
return result
#basic handler will redirect to nevek
@route('/')
def hello_world():
redirect('/nevek')
@route('/nevek')
def nevek():
return template('nevek-obj', nevek=get_nevek_from_db())
#get records from table nevek
def get_raw_sql(sql):
conn = MySQLdb.connect(host=db_host, user=db_user,
passwd=db_password, db=db_dbname, charset='utf8')
cur = conn.cursor()
cur.execute(sql)
res = cur.fetchall()
cur.close()
conn.close()
return res
@route('/nevek-raw')
def nevek_raw():
return template('nevek-tuple', nevek=get_raw_sql("SELECT * FROM nevek"))
#this will be imported and run by the wsgi.py (in hosted env)
application = default_app()
#this will be used when running on your own machine
if __name__ == '__main__':
run(application)
Exception:
2015-02-25 12:43:57,107 :Traceback (most recent call last):
2015-02-25 12:43:57,108 : File "/usr/local/lib/python2.7/dist-packages/bottle.py", line 764, in _handle
2015-02-25 12:43:57,108 : return route.call(**args)
2015-02-25 12:43:57,108 : File "/usr/local/lib/python2.7/dist-packages/bottle.py", line 1575, in wrapper
2015-02-25 12:43:57,108 : rv = callback(*a, **ka)
2015-02-25 12:43:57,108 : File "/home/bpgergo/bridge/bottle_app.py", line 49, in nevek
2015-02-25 12:43:57,108 : return template('nevek-obj', nevek=get_nevek_from_db())
2015-02-25 12:43:57,108 : File "/home/bpgergo/bridge/bottle_app.py", line 38, in get_nevek_from_db
2015-02-25 12:43:57,109 : result = session.query(Person).all()
2015-02-25 12:43:57,109 : File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/query.py", line 2241, in all
2015-02-25 12:43:57,109 : return list(self)
2015-02-25 12:43:57,109 : File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/query.py", line 2353, in __iter__
2015-02-25 12:43:57,109 : return self._execute_and_instances(context)
2015-02-25 12:43:57,109 : File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/query.py", line 2368, in _execute_and_instances
2015-02-25 12:43:57,109 : result = conn.execute(querycontext.statement, self._params)
2015-02-25 12:43:57,109 : File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 662, in execute
2015-02-25 12:43:57,109 : params)
2015-02-25 12:43:57,109 : File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 761, in _execute_clauseelement
2015-02-25 12:43:57,109 : compiled_sql, distilled_params
2015-02-25 12:43:57,109 : File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 874, in _execute_context
2015-02-25 12:43:57,109 : context)
2015-02-25 12:43:57,109 : File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1024, in _handle_dbapi_exception
2015-02-25 12:43:57,109 : exc_info
2015-02-25 12:43:57,109 : File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/util/compat.py", line 196, in raise_from_cause
2015-02-25 12:43:57,109 : reraise(type(exception), exception, tb=exc_tb)
2015-02-25 12:43:57,109 : File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 867, in _execute_context
2015-02-25 12:43:57,109 : context)
2015-02-25 12:43:57,110 : File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/default.py", line 324, in do_execute
2015-02-25 12:43:57,110 : cursor.execute(statement, parameters)
2015-02-25 12:43:57,110 : File "/usr/local/lib/python2.7/dist-packages/MySQLdb/cursors.py", line 205, in execute
2015-02-25 12:43:57,110 : self.errorhandler(self, exc, value)
2015-02-25 12:43:57,110 : File "/usr/local/lib/python2.7/dist-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
2015-02-25 12:43:57,110 : raise errorclass, errorvalue
2015-02-25 12:43:57,110 :OperationalError: (OperationalError) (2013, 'Lost connection to MySQL server during query') 'SELECT nevek.id AS nevek_id, nevek.name AS nevek_name, nevek.lev AS nevek_lev, nevek.point AS nevek_point, nevek.play AS nevek_play, nevek.kmp AS nevek_kmp \nFROM nevek' ()
Can you please try it and let me know
By using pool_recycle I think you can over come it.
http://docs.sqlalchemy.org/en/rel_0_9/dialects/mysql.html#connection-timeouts
replace
engine = create_engine(mysql_connect_string)
by
engine = create_engine(mysql_connect_string, pool_size=100, pool_recycle=280)
Connection Timeouts
MySQL features an automatic connection close behavior, for connections that have been idle for eight hours or more. To circumvent having this issue, use the pool_recycle option which controls the maximum age of any connection:
engine = create_engine('mysql+mysqldb://...', pool_recycle=3600)