title: Efficiently updating database using SQLAlchemy ORM
body: <p>I'm starting a new application and looking at using an ORM -- in particular, SQLAlchemy.</p>
  
  <p>Say I've got a column 'foo' in my database and I want to increment it.  In straight sqlite, this is easy:</p>
  
  <pre><code>db = sqlite3.connect('mydata.sqlitedb')
  cur = db.cursor()
  cur.execute('update table stuff set foo = foo + 1')
  </code></pre>
  
  <p>I figured out the SQLAlchemy SQL-builder equivalent:</p>
  
  <pre><code>engine = sqlalchemy.create_engine('sqlite:///mydata.sqlitedb')
  md = sqlalchemy.MetaData(engine)
  table = sqlalchemy.Table('stuff', md, autoload=True)
  upd = table.update(values={table.c.foo:table.c.foo+1})
  engine.execute(upd)
  </code></pre>
  
  <p>This is slightly slower, but there's not much in it.</p>
  
  <p>Here's my best guess for a SQLAlchemy ORM approach:</p>
  
  <pre><code># snip definition of Stuff class made using declarative_base
  # snip creation of session object
  for c in session.query(Stuff):
      c.foo = c.foo + 1
  session.flush()
  session.commit()
  </code></pre>
  
  <p>This does the right thing, but it takes just under fifty times as long as the other two approaches.  I presume that's because it has to bring all the data into memory before it can work with it.</p>
  
  <p>Is there any way to generate the efficient SQL using SQLAlchemy's ORM?  Or using any other python ORM?  Or should I just go back to writing the SQL by hand?</p>