Result rowcount returns -1 on UPDATE with version 1.0+

[Tuomas Mursu]

Releases 0.9.7 and 1.0.11 seem to have some differences in returned rowcount when doing an update.

Simple example:

import logging
logging.basicConfig(level=logging.INFO)
log = logging.getLogger()
logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)

from sqlalchemy import create_engine, text

engine = create_engine('mssql+pyodbc://mydsn')

with engine.connect() as cnx:
    query = text('''UPDATE FOOTABLE SET DESCRIPTION='asd' WHERE ID=123''')
    result = cnx.execute(query)
    log.info('supports_sane_multi_rowcount: {0}'.format(result.supports_sane_multi_rowcount()))
    log.info('supports_sane_rowcount: {0}'.format(result.supports_sane_rowcount()))
    log.info('Update rowcount: {0}'.format(result.rowcount))

Log output on 0.9.7:

#!

INFO:sqlalchemy.engine.base.Engine:
            SELECT default_schema_name FROM
            sys.database_principals
            WHERE principal_id=database_principal_id()

INFO:sqlalchemy.engine.base.Engine:()
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:()
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test unicode returns' AS NVARCHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:()
INFO:sqlalchemy.engine.base.Engine:UPDATE FOOTABLE SET DESCRIPTION='asd' WHERE ID=123
INFO:sqlalchemy.engine.base.Engine:()
INFO:sqlalchemy.engine.base.Engine:COMMIT
INFO:root:supports_sane_multi_rowcount: False
INFO:root:supports_sane_rowcount: True
INFO:root:Update rowcount: 1

Log output on 1.0.11:

#!

INFO:sqlalchemy.engine.base.Engine:
            SELECT default_schema_name FROM
            sys.database_principals
            WHERE principal_id=database_principal_id()

INFO:sqlalchemy.engine.base.Engine:()
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:()
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test unicode returns' AS NVARCHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:()
INFO:sqlalchemy.engine.base.Engine:UPDATE FOOTABLE SET DESCRIPTION='asd' WHERE ID=123
INFO:sqlalchemy.engine.base.Engine:()
INFO:sqlalchemy.engine.base.Engine:COMMIT
INFO:root:supports_sane_multi_rowcount: False
INFO:root:supports_sane_rowcount: True
INFO:root:Update rowcount: -1

The database row does exist and the update still actually works, but I'd need the count returned. I'm using Windows 10, pyodbc 3.0.10, Python 3.3, everything 64-bit.

[Mike Bayer]

the pyodbc driver is one of few where the cursor.rowcount accessor does not work if the cursor has been closed. There is an "eager fetch" of this rowcount which occurs in the engine for all those statements that are "CRUD", which means, they are an update() or delete() construct. This check does not occur for text() or plain strings. In 0.9, the eager fetch was unconditional. The logic was changed to save on performance, CRUD statements via text() are unusual. I will attempt to amend the check here to allow text() through.

[Mike Bayer]

• Fixed 1.0 regression where the eager fetch of cursor.rowcount was no longer called for an UPDATE or DELETE statement emitted via plain text or via the :func:.text construct, affecting those drivers that erase cursor.rowcount once the cursor is closed such as SQL Server ODBC and Firebird drivers. fixes #3622

→ <<cset 197ffa2be2ca>>

[Mike Bayer]

• Fixed 1.0 regression where the eager fetch of cursor.rowcount was no longer called for an UPDATE or DELETE statement emitted via plain text or via the :func:.text construct, affecting those drivers that erase cursor.rowcount once the cursor is closed such as SQL Server ODBC and Firebird drivers. fixes #3622

(cherry picked from commit 197ffa2be2cadce3df8bfb0799b3c80158250286)

→ <<cset 6ba8382fa8b9>>

[Mike Bayer]

targeted at 1.0.12 thanks for reporting!

[Tuomas Mursu]

That was quick! Thank you for fixing this :)

[Matt Warren]

Is this still an issue for 1.0.9? I recently upgraded to Anaconda 2.4.1. I am using sqlalchemy to execute a stored procedure which updates rows, and display the number of rows updated in the Python console via .rowcount. This used to work correctly but since I upgraded the stored procedure executes correctly but I always get '-1 rows affected'. If I execute directly in SQL Server it displays the correct number of rows updated.

[Mike Bayer]

assuming you're using textual SQL, yes, as stated the fix is targeted for release 1.0.12.

the following workaround may allow it to work on 1.0.9:

from sqlalchemy import create_engine, event

engine = create_engine("pyodbc://...")

@event.listens_for(engine, "after_cursor_execute")
def _refresh_cursor(conn, cursor, statement, parameters, context, executemany):
    cursor.rowcount

then use the engine normally.