title: sqlalchemy and huge table in postgresql
body: <p>I have a table in a postgresql database that is ~900,000 rows. I want to copy it row by row to another table with some extra columns after transforming each row and adding data to the new columns.
  The problem is that RAM gets full.</p>
  
  <p>Here is the relevant part of the code:</p>
  
  <pre><code>engine = sqlalchemy.create_engine(URL(**REMOTE), echo=False)
  Session = sessionmaker(bind=engine)
  session = Session()
  n=1000
  counter=1
  for i in range(1,total+1,n):
      ids=str([j for j in range(i,i+n)])
      **q="SELECT * from table_parts where id in (ids)"%ids**
      r=session.execute(q).fetchall()
      for element in r:
          data={}
          ....
         [taking data from each row, extracting string,calculation,
          and filling extra columns that the new table has]
         ...
      query=query.bindparams(**data)
      try:
          session.execute(query)
      except:
          session.rollback()
          raise 
      if counter%n==0:
          print COMMITING....",counter,datetime.datetime.now("%H:%M:%S")
             session.commit()
      counter+=1
  </code></pre>
  
  <p>The queries are correct, so there is no errors there. Before I press Ctrl+C, the new table gets correctly updated.</p>
  
  <p>The problem seems to the query:
  "<code>SELECT * from table_parts where id in (1,2,3,4...1000)</code>"
  I already tried with a postgresql array.</p>
  
  <p>Things I have already tried:</p>
  
  <ul>
  <li><p><code>results = (connection
         .execution_options(stream_results=True)  # Added this line
         .execute(query))</code>
  <a href="http://dev.mobify.com/blog/sqlalchemy-memory-magic/" rel="nofollow noreferrer" title="from here">from here</a> . As far as I know this uses a server side cursor when used with postgresql. I ditched the session object I have in my posted code and used <code>engine.connect()</code></p>
  
  <ul>
  <li>creating a <strong>new connection</strong> object on each iteration, surprisingly this does not work either. RAM gets full </li>
  </ul></li>
  </ul>
  
  <p>from the documentation ,</p>
  
  <p><a href="http://docs.sqlalchemy.org/en/latest/orm/query.html#sqlalchemy.orm.query.Query.execution_options" rel="nofollow noreferrer">Note that the stream_results execution option is enabled automatically if the yield_per() method is used.</a></p>
  
  <p>so the yield_per from the query api is the same with the stream_result option mentioned above</p>
  
  <p>thanks</p>