StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>The basic <code>MySQLdb</code> cursor fetches the entire query result at once from the server. This can consume a lot of memory and time. Use <a href="http://mysql-python.sourceforge.net/MySQLdb.html" rel="nofollow noreferrer">MySQLdb.cursors.SSCursor</a> when you want to make a huge query and pull results from the server one at a time.</p> <p>Therefore, try passing <code>connect_args={'cursorclass': MySQLdb.cursors.SSCursor}</code> when creating the <code>engine</code>:</p> <pre><code> from sqlalchemy import create_engine, MetaData import MySQLdb.cursors engine = create_engine('mysql://root:zenoss@localhost/e2', connect_args={'cursorclass': MySQLdb.cursors.SSCursor}) meta = MetaData(engine, reflect=True) conn = engine.connect() rs = s.execution_options(stream_results=True).execute() </code></pre> <p>See <a href="http://www.sqlalchemy.org/trac/ticket/1089" rel="nofollow noreferrer">http://www.sqlalchemy.org/trac/ticket/1089</a></p> <hr> <p>Note that using SSCursor locks the table until the fetch is complete. This affects other cursors using the same connection: Two cursors from the same connection can not read from the table concurrently.</p> <p>However, cursors from different connections can read from the same table concurrently.</p> <p>Here is some code demonstrating the problem:</p> <pre><code>import MySQLdb import MySQLdb.cursors as cursors import threading import logging import config logger = logging.getLogger(__name__) query = 'SELECT * FROM huge_table LIMIT 200' def oursql_conn(): import oursql conn = oursql.connect( host=config.HOST, user=config.USER, passwd=config.PASS, db=config.MYDB) return conn def mysqldb_conn(): conn = MySQLdb.connect( host=config.HOST, user=config.USER, passwd=config.PASS, db=config.MYDB, cursorclass=cursors.SSCursor) return conn def two_cursors_one_conn(): """Two SSCursors can not use one connection concurrently""" def worker(conn): cursor = conn.cursor() cursor.execute(query) for row in cursor: logger.info(row) conn = mysqldb_conn() threads = [threading.Thread(target=worker, args=(conn, )) for n in range(2)] for t in threads: t.daemon = True t.start() # Second thread may hang or raise OperationalError: # File "/usr/lib/pymodules/python2.7/MySQLdb/cursors.py", line 289, in _fetch_row # return self._result.fetch_row(size, self._fetch_type) # OperationalError: (2013, 'Lost connection to MySQL server during query') for t in threads: t.join() def two_cursors_two_conn(): """Two SSCursors from independent connections can use the same table concurrently""" def worker(): conn = mysqldb_conn() cursor = conn.cursor() cursor.execute(query) for row in cursor: logger.info(row) threads = [threading.Thread(target=worker) for n in range(2)] for t in threads: t.daemon = True t.start() for t in threads: t.join() logging.basicConfig(level=logging.DEBUG, format='[%(asctime)s %(threadName)s] %(message)s', datefmt='%H:%M:%S') two_cursors_one_conn() two_cursors_two_conn() </code></pre> <p>Note that <a href="http://pythonhosted.org/oursql/" rel="nofollow noreferrer">oursql</a> is an alternative set of MySQL bindings for Python. oursql cursors are true server-side cursors which <a href="http://pythonhosted.org/oursql/tutorial.html#row-streaming-out" rel="nofollow noreferrer">fetch rows lazily by default</a>. With <code>oursql</code> installed, if you change</p> <pre><code>conn = mysqldb_conn() </code></pre> <p>to</p> <pre><code>conn = oursql_conn() </code></pre> <p>then <code>two_cursors_one_conn()</code> runs without hanging or raising an exception.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload