title: How to update records in SQL Alchemy in a Loop
body: <p>I am trying to use SQLSoup - the SQLAlchemy extention, to update records in a SQL Server 2008 database. I am using pyobdc for the connections. There are a number of issues which make it hard to find a relevant example. </p>
  
  <p>I am reprojection a geometry field in a very large table (2 million + records), so many of the standard ways of updating fields cannot be used. I need to extract coordinates from the geometry field to text, convert them and pass them back in. All this is fine, and all the individual pieces are working. </p>
  
  <p>However I want to execute a SQL Update statement on each row, while looping through the records one by one. I assume this places locks on the recordset, or the connection is in use - as if I use the code below it hangs after successfully updating the first record. </p>
  
  <p>Any advice on how to create a new connection, reuse the existing one, or accomplish this another way is appreciated. </p>
  
  <pre><code>s = select([text("%s as fid" % id_field),
              text("%s.STAsText() as wkt" % geom_field)],
             from_obj=[feature_table])
  
  rs = s.execute()
  
  for row in rs:
      new_wkt = ReprojectFeature(row.wkt)
  
      update_value = "geometry :: STGeomFromText('%s',%s)" % (new_wkt, "3785")
      update_sql = ("update %s set GEOM3785 = %s where %s = %i" %
                    (full_name, update_value, id_field, row.fid))
  
      conn = db.connection()
      conn.execute(update_sql)
      conn.close() #or not - no effect..
  </code></pre>
  
  <p>Updated working code now looks like this. It works fine on a few records, but hangs on the whole table, so I guess it is reading in too much data. </p>
  
  <pre><code>db = SqlSoup(conn_string)
  #create outer query
  
  Session = sessionmaker(autoflush=False, bind=db.engine)
  session = Session()
  rs = session.execute(s)
  
  for row in rs: 
      #create update sql...
      session.execute(update_sql)
  session.commit()
  </code></pre>
  
  <p>I now get connection busy errors.</p>
  
  <blockquote>
    <p>DBAPIError: (Error) ('HY000', '[HY000] [Microsoft][ODBC SQL Server Driver]Connection is busy with results for another hstmt (0) (SQLExecDirectW)')</p>
  </blockquote>
  
  <p>It looks like this could be a problem with the ODBC driver - <a href="http://sourceitsoftware.blogspot.com/2008/06/connection-is-busy-with-results-for.html" rel="nofollow">http://sourceitsoftware.blogspot.com/2008/06/connection-is-busy-with-results-for.html</a></p>
  
  <p><strong>Further Update</strong>:</p>
  
  <p>On the server using profiler, it shows the select statement then the first update statement are "starting" but neither complete. 
  If I set the Select statement to return the top 10 rows, then it does complete and the updates run. </p>
  
  <pre><code>SQL: Batch Starting   Select...
  SQL: Batch Starting   Update...
  </code></pre>
  
  <p>I believe this is an issue with <a href="http://code.google.com/p/pyodbc/" rel="nofollow">pyodbc</a> and SQL Server drivers. If I remove SQL Alchemy and execute the same SQL with pyodbc it also hangs. Even if I create a new connection object for the updates. </p>
  
  <p>I also tried the SQL Server Native Client 10.0 driver which is meant to allow MARS - <a href="http://www.sqlteam.com/article/multiple-active-result-sets-mars" rel="nofollow">Multiple Active Record Sets</a> but it made no difference. In the end I have resorted to "paging the results" and updating these batches using pyodbc and SQL (see below), however I thought SQLAlchemy would have been able to do this for me automatically. </p>