Result rowcount returns -1 on UPDATE with version 1.0+
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.
Comments (7)
-
repo owner -
repo owner - changed status to resolved
- 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>>
-
repo owner - 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>>
- 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:
-
repo owner targeted at 1.0.12 thanks for reporting!
-
reporter That was quick! Thank you for fixing this :)
-
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.
-
repo owner 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.
- Log in to comment
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.