title: The right way to use gevent with sqlalchemy
body: <p>I simplify a complex system of gevent with sqlachemy to a simple <a href="https://gist.github.com/tonywangcn/6dadbd58d7778063b0c7969f62505537" rel="nofollow noreferrer">demo code</a>.</p>
  
  <p>Before using of sqlachemy, pymysql is my best choice for communication between MySql and gevent because of its easy and direct usage. But now I feel a little confusing, what's the best way to make the two (gevent and sqlachemy) work efficiently.</p>
  
  <p>In pymysql case, though some "_io.BufferedReader " error would happen, it doesn't affect the INSERT or UPDATE operations.</p>
  
  <p>In sqlalchemy case, it's quite different in the opposite. Much more errors and little success.</p>
  
  <p>After searching around for such errors, some solutions[<a href="https://groups.google.com/forum/#!searchin/gevent/SQLAlchemy/gevent/eGLfR7JV0kk/hg1kicBJeQkJ]" rel="nofollow noreferrer">https://groups.google.com/forum/#!searchin/gevent/SQLAlchemy/gevent/eGLfR7JV0kk/hg1kicBJeQkJ]</a> for similar error didn't work.</p>
  
  <p>Demo code :</p>
  
  <p><a href="https://gist.github.com/tonywangcn/6dadbd58d7778063b0c7969f62505537" rel="nofollow noreferrer">https://gist.github.com/tonywangcn/6dadbd58d7778063b0c7969f62505537</a></p>
  
  <h1>log of sqlalchemy</h1>
  
  <pre><code>2017-07-15 07:29:25,930 - log.py - log - 109  - 20291 -  (DummyThread-8) -INFO - {'ip': '112.201.247.130', 'port': 41607, 'types': 0, 'score': 10, 'updatetime': datetime.datetime(2017, 7, 14, 23, 29, 25, 929929)}
  2017-07-15 07:29:25,931 INFO sqlalchemy.engine.base.Engine ROLLBACK
  2017-07-15 07:29:25,931 - log.py - log - 109  - 20291 -  (DummyThread-9) -INFO - ROLLBACK
  2017-07-15 07:29:25,931 - pool.py - pool - 696  - 20291 -  (DummyThread-9) -ERROR - Exception during reset or similar
  Traceback (most recent call last):
    File "/Users/jiang/code/.env/lib/python3.6/site-packages/sqlalchemy/pool.py", line 687, in _finalize_fairy
      fairy._reset(pool)
    File "/Users/jiang/code/.env/lib/python3.6/site-packages/sqlalchemy/pool.py", line 827, in _reset
      self._reset_agent.rollback()
    File "/Users/jiang/code/.env/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1621, in rollback
      self._do_rollback()
    File "/Users/jiang/code/.env/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1659, in _do_rollback
      self.connection._rollback_impl()
    File "/Users/jiang/code/.env/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 703, in _rollback_impl
      self._handle_dbapi_exception(e, None, None, None, None)
    File "/Users/jiang/code/.env/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1405, in _handle_dbapi_exception
      util.reraise(*exc_info)
    File "/Users/jiang/code/.env/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 187, in reraise
      raise value
    File "/Users/jiang/code/.env/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 701, in _rollback_impl
      self.engine.dialect.do_rollback(self.connection)
    File "/Users/jiang/code/.env/lib/python3.6/site-packages/sqlalchemy/dialects/mysql/base.py", line 1598, in do_rollback
      dbapi_connection.rollback()
    File "/Users/jiang/code/.env/lib/python3.6/site-packages/pymysql/connections.py", line 793, in rollback
      self._read_ok_packet()
    File "/Users/jiang/code/.env/lib/python3.6/site-packages/pymysql/connections.py", line 767, in _read_ok_packet
      pkt = self._read_packet()
    File "/Users/jiang/code/.env/lib/python3.6/site-packages/pymysql/connections.py", line 987, in _read_packet
      packet_header = self._read_bytes(4)
    File "/Users/jiang/code/.env/lib/python3.6/site-packages/pymysql/connections.py", line 1021, in _read_bytes
      data = self._rfile.read(num_bytes)
  RuntimeError: reentrant call inside &lt;_io.BufferedReader name=8&gt;
  Traceback (most recent call last):
    File "/Users/jiang/code/.env/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 2255, in _flush
      flush_context.execute()
    File "/Users/jiang/code/.env/lib/python3.6/site-packages/sqlalchemy/orm/unitofwork.py", line 389, in execute
      rec.execute(self)
    File "/Users/jiang/code/.env/lib/python3.6/site-packages/sqlalchemy/orm/unitofwork.py", line 548, in execute
      uow
    File "/Users/jiang/code/.env/lib/python3.6/site-packages/sqlalchemy/orm/persistence.py", line 181, in save_obj
      mapper, table, insert)
    File "/Users/jiang/code/.env/lib/python3.6/site-packages/sqlalchemy/orm/persistence.py", line 835, in _emit_insert_statements
      execute(statement, params)
    File "/Users/jiang/code/.env/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 945, in execute
      return meth(self, multiparams, params)
    File "/Users/jiang/code/.env/lib/python3.6/site-packages/sqlalchemy/sql/elements.py", line 263, in _execute_on_connection
      return connection._execute_clauseelement(self, multiparams, params)
    File "/Users/jiang/code/.env/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1053, in _execute_clauseelement
      compiled_sql, distilled_params
    File "/Users/jiang/code/.env/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1189, in _execute_context
      context)
    File "/Users/jiang/code/.env/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1405, in _handle_dbapi_exception
      util.reraise(*exc_info)
    File "/Users/jiang/code/.env/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 187, in reraise
      raise value
    File "/Users/jiang/code/.env/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1182, in _execute_context
      context)
    File "/Users/jiang/code/.env/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 470, in do_execute
      cursor.execute(statement, parameters)
    File "/Users/jiang/code/.env/lib/python3.6/site-packages/pymysql/cursors.py", line 166, in execute
      result = self._query(query)
    File "/Users/jiang/code/.env/lib/python3.6/site-packages/pymysql/cursors.py", line 322, in _query
      conn.query(q)
    File "/Users/jiang/code/.env/lib/python3.6/site-packages/pymysql/connections.py", line 856, in query
      self._affected_rows = self._read_query_result(unbuffered=unbuffered)
    File "/Users/jiang/code/.env/lib/python3.6/site-packages/pymysql/connections.py", line 1057, in _read_query_result
      result.read()
    File "/Users/jiang/code/.env/lib/python3.6/site-packages/pymysql/connections.py", line 1340, in read
      first_packet = self.connection._read_packet()
    File "/Users/jiang/code/.env/lib/python3.6/site-packages/pymysql/connections.py", line 1004, in _read_packet
      recv_data = self._read_bytes(bytes_to_read)
    File "/Users/jiang/code/.env/lib/python3.6/site-packages/pymysql/connections.py", line 1018, in _read_bytes
      self._sock.settimeout(self._read_timeout)
  AttributeError: 'NoneType' object has no attribute 'settimeout'
  
  During handling of the above exception, another exception occurred:
  
  Traceback (most recent call last):
    File "/Users/jiang/code/.env/lib/python3.6/site-packages/gevent/greenlet.py", line 536, in run
      result = self._run(*self.args, **self.kwargs)
    File "test.py", line 227, in insert_data
      sqlhelper.insert(data)
    File "test.py", line 102, in insert
      self.session.commit()
    File "/Users/jiang/code/.env/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 906, in commit
      self.transaction.commit()
    File "/Users/jiang/code/.env/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 461, in commit
      self._prepare_impl()
    File "/Users/jiang/code/.env/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 441, in _prepare_impl
      self.session.flush()
    File "/Users/jiang/code/.env/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 2171, in flush
      self._flush(objects)
    File "/Users/jiang/code/.env/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 2291, in _flush
      transaction.rollback(_capture_exception=True)
    File "/Users/jiang/code/.env/lib/python3.6/site-packages/sqlalchemy/util/langhelpers.py", line 76, in __exit__
      compat.reraise(type_, value, traceback)
    File "/Users/jiang/code/.env/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 187, in reraise
      raise value
    File "/Users/jiang/code/.env/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 2291, in _flush
      transaction.rollback(_capture_exception=True)
    File "/Users/jiang/code/.env/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 477, in rollback
      self._assert_active(prepared_ok=True, rollback_ok=True)
    File "/Users/jiang/code/.env/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 285, in _assert_active
      raise sa_exc.ResourceClosedError(closed_msg)
  sqlalchemy.exc.ResourceClosedError: This transaction is closed
  Sat Jul 15 07:29:26 2017 &lt;Greenlet at 0x10853c3d8: insert_data&gt; failed with ResourceClosedError
  </code></pre>
  
  <h2>log of gevent with pymysql</h2>
  
  <pre><code>    2017-07-15 07:26:39,545 - test.py - test - 62  - 20278 -  (DummyThread-1) -ERROR - reentrant call inside &lt;_io.BufferedReader name=6&gt;
      2017-07-15 07:26:39,545 - test.py - test - 62  - 20278 -  (DummyThread-2) -ERROR - reentrant call inside &lt;_io.BufferedReader name=6&gt;
      2017-07-15 07:26:39,546 - test.py - test - 62  - 20278 -  (DummyThread-3) -ERROR - reentrant call inside &lt;_io.BufferedReader name=6&gt;
      2017-07-15 07:26:39,546 - test.py - test - 62  - 20278 -  (DummyThread-4) -ERROR - reentrant call inside &lt;_io.BufferedReader name=6&gt;
      2017-07-15 07:26:49,651 - test.py - test - 62  - 20278 -  (DummyThread-5) -ERROR - reentrant call inside &lt;_io.BufferedReader name=6&gt;
      2017-07-15 07:26:49,652 - test.py - test - 62  - 20278 -  (DummyThread-6) -ERROR - reentrant call inside &lt;_io.BufferedReader name=6&gt;
  </code></pre>