Take the 2-minute tour ×

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.

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.

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.

Any advice on how to create a new connection, reuse the existing one, or accomplish this another way is appreciated.

s = select([text("%s as fid" % id_field),
            text("%s.STAsText() as wkt" % geom_field)],

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.close() #or not - no effect..

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.

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...

I now get connection busy errors.

DBAPIError: (Error) ('HY000', '[HY000] [Microsoft][ODBC SQL Server Driver]Connection is busy with results for another hstmt (0) (SQLExecDirectW)')

It looks like this could be a problem with the ODBC driver - http://sourceitsoftware.blogspot.com/2008/06/connection-is-busy-with-results-for.html

Further Update:

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.

SQL: Batch Starting   Select...
SQL: Batch Starting   Update...

I believe this is an issue with pyodbc 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.

I also tried the SQL Server Native Client 10.0 driver which is meant to allow MARS - Multiple Active Record Sets 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.

share|improve this question
Why don't you write it in pure SQL, as a single update statement? Looks doable to me. –  Maxim Sloyko Feb 5 '11 at 11:40
The contents of on of the fields is a binary object that needs to be processed by a separate non-SQL function - so I need some sort of cursor. –  geographika Feb 5 '11 at 13:06
add comment

3 Answers

Try using a Session.

rs = s.execute() then becomes session.execute(rs) and you can replace the last three lines with session.execute(update_sql). I'd also suggest configuring your Session with autocommit off and call session.commit() at the end.

share|improve this answer
My updated code works fine for a few records but hangs with a few hundred records. –  geographika Feb 5 '11 at 13:34
Can you try to enable logging on the client side? It's even better if you can have a log of all statements on the server. –  jd. Feb 5 '11 at 14:22
Why create a new session in the loop? Using the same session would work at least just as well and also allow all your updates to belong to the same transaction. –  jd. Feb 5 '11 at 14:47
I have removed the new session, but still it seems the connection is busy with the outer loop –  geographika Feb 5 '11 at 15:12
The code as it is looks fine to me, without logs showing at what point the freezing occurs, it's hard to say what goes wrong. –  jd. Feb 6 '11 at 7:35
add comment

Can I suggest that when your process hangs you do a sp_who2 on the Sql box and see what is happening. Check for blocked spid's and see if you can find anything in the Sql code that can suggest what is happening. If you do find a spid that is blocking others you can do a dbcc inputbuffer(*spidid*) and see if that tells you what the query was it executed. Otherwise you can also attach the Sql profiler and trace your calls.

In some cases it could also be parallelism on the Sql server that cause blocks. Unless this is a data warehouse, I suggest turn your Max DOP off, (set it to 1). Let me know and when I check this again in the morning and you need help, I'll be glad to help.

share|improve this answer
Thanks for your reply. SQL Profiler details added above. –  geographika Feb 6 '11 at 19:21
Not a problem. I have no Alchemy knowledge at all, so I cannot comment on it. But a lot of these LINQ type applications I have come across have this issue. If it is possible see if you can set the reader not to block the writer. So set "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED" which will make sure your reads are not blocking your writes, or if possible set WITH(NOLOCK) which will have the same effect. –  Ryk Feb 7 '11 at 4:38
I tried both settings each had the same result: Error: ('HY000', '[HY000] [Microsoft][SQL Server Native Client 10.0]Connection is busy with results for another command (0) (SQLExecDirectW)') –  geographika Feb 8 '11 at 8:23
Interesting. Might be a connection issue. –  Ryk Feb 8 '11 at 9:01
add comment

Until I find another solution I am using a single connection and custom SQL to return sets of records, and updating these in batches. I don't think what I am doing is a particulary unique case, so I am not sure why I cannot handle multiple result sets simultaneously.

Below works but is very, very slow..

cnxn = pyodbc.connect(conn_string, autocommit=True)
cursor = cnxn.cursor()

#get total recs in the database
s = "select count(fid) as count from table"
count = cursor.execute(s).fetchone().count

#choose number of records to update in each iteration
batch_size = 100
for i in range(1,count, batch_size):
    #sql to bring back relevant records in each batch
    s = """SELECT fid, wkt from(select ROW_NUMBER() OVER(ORDER BY FID ASC) AS 'RowNumber'
,GEOM29902.STAsText() as wkt
    FROM %s) features
    where RowNumber >= %i and RowNumber <= %i""" % (full_name,i,i+batch_size)

    rs = cursor.execute(s).fetchall()
    for row in rs:
        new_wkt = ReprojectFeature(row.wkt)
        #...create update sql statement for the record
        counter += 1
share|improve this answer
Please try this and let me know. s = "select count(fid) as count from table WITH(NOLOCK)" and also ".... FROM %s) features WITH(NOLOCK) ...." –  Ryk Feb 7 '11 at 21:31
add comment

Your Answer


