title: SQLAlchemy not returning selected data
body: <p>I'm using SQLAlchemy as the ORM within an application i've been building for some time.</p>
  
  <p>So far, it's been quite a painless ORM to implement and use, however, a recent feature I'm working on requires a persistent &amp; distributed queue (list &amp; worker) style implementation, which I've built in MySQL and Python.</p>
  
  <p>It's all worked quite well until I tested it in a scaled environment.
  I've used InnoDB row level locking to ensure each row is only read once, while the row is locked, I update an '<em><code>in_use</code></em>' value, to make sure that others don't grab at the entry.</p>
  
  <p>Since MySQL doesn't offer a "NOWAIT" method like Postgre or Oracle does, I've run into locking issues where worker threads hang and wait for the locked row to become available.</p>
  
  <p>In an attempt to overcome this limitation, I've tried to put all the required processing into a single statement, and run it through the ORM's <em><code>execute()</code></em> method, although, SQLAlchemy is refusing to return the query result.</p>
  
  <p>Here's an example.</p>
  
  <p>My SQL statement is:</p>
  
  <pre><code>SELECT id INTO @update_id FROM myTable WHERE in_use=0 ORDER BY id LIMIT 1 FOR UPDATE;
  UPDATE myTable SET in_use=1 WHERE id=@update_id;
  SELECT * FROM myTable WHERE id=@update_id;
  </code></pre>
  
  <p>And I run this code in the console:</p>
  
  <pre><code>engine = create_engine('mysql://&lt;user details&gt;@&lt;server details&gt;/myDatabase', pool_recycle=90, echo=True)
  result = engine.execute(sqlStatement)
  result.fetchall()
  </code></pre>
  
  <p>Only to get this result</p>
  
  <pre><code>[]
  </code></pre>
  
  <p>I'm certain the statement is running since I can see the update take effect in the database, and if I execute through the mysql terminal or other tools, I get the modified row returned.
  It just seems to be SQLAlchemy that doesn't want to acknowledge the returned row.</p>
  
  <p>Is there anything specific that needs to be done to ensure that the ORM picks up the response?</p>
  
  <p>Cheers</p>