rowcount does not work

[GoogleCodeExporter]

I have just tried on Linux with pymssql-1.9.908 the following script:

from pymssql import connect

CREATE = 'CREATE TABLE foo(i INTEGER PRIMARY KEY, t VARCHAR(100))'
DROP = 'DROP TABLE foo'
INSERT = 'INSERT INTO foo VALUES (%d, %s)'
UPDATE = 'UPDATE foo SET t=%s WHERE i=%d'
SELECT = 'SELECT * FROM foo'
DELETE = 'DELETE FROM foo'

def main():
    conn = connect('itdbserver2', 'python', 'p1tone', 'data_manag')
    curs = conn.cursor()
    curs.execute(CREATE)
    try:
        curs.execute(INSERT, (1, 'line1'))
        print 'INSERT', curs.rowcount
        curs.execute(UPDATE, ('*line1*', 1))
        print 'UPDATE', curs.rowcount
        curs.execute(SELECT)
        print curs.fetchall()
        print 'SELECT', curs.rowcount
        curs.execute(DELETE)
        print 'DELETE', curs.rowcount
    finally:
        curs.execute(DROP)

I get:

INSERT 0
UPDATE 0
[(1, '*line1*')]
SELECT 1
DELETE 0

In other words, the rowcount after INSERT, UPDATE and DELETE is always 0
and this is wrong :-(

Original issue reported on code.google.com by michele....@gmail.com on 1 Jul 2010 at 9:22

[GoogleCodeExporter]

I'm having the same issue on Python 2.6.5 32bit in Windows 7 64-bit and pymsql 
version 1.0.2 with the following code:

    cur.execute('update stuff...',data)
    if not cur.rowcount:
      cur.execute('insert stuff',data)

rowcount always returns 0. So after updating the row the code attempts to 
insert a duplicate.

Original comment by caspar.l...@gmail.com on 8 Jul 2010 at 11:07

[GoogleCodeExporter]

It used to work on 
Python 2.5.2 (r252:60911, Feb 21 2008, 13:11:45) [MSC v.1310 32 bit (Intel)] on 
win32
>>> pymssql.__version__
'0.8.0'

Original comment by caspar.l...@gmail.com on 8 Jul 2010 at 11:09

[GoogleCodeExporter]

Yes this changed.  An effective work around is to use cur._source.rows_affected 
which will have the correct rows_affected.

Original comment by jason.s.holmes on 20 Jul 2010 at 5:00

[GoogleCodeExporter]

sorry my above comment was incorrect.  I mean 
cursor._source._conn.rows_affected will reveal the correct number of rows 
affected.

Original comment by jason.s.holmes on 20 Jul 2010 at 6:14

[GoogleCodeExporter]

awesome, thanks for the quick update Jason, its immediately useful to us.

Original comment by david.ba...@gmail.com on 21 Jul 2010 at 7:58

[GoogleCodeExporter]

According to Python Database API Specification v2.0
rowcount is supposed to contain the rows affected.  I suppose since they 
haven't released pymssql 2.0 yet it is still acceptable to claim DB-API 2.0 
compliance.  It sure makes migrating code to use pymssql 1.9.x difficult 
however.

Original comment by jason.s.holmes on 26 Jul 2010 at 11:18

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

This isn't working for me. Even on successful updates, 
cursor._source._con._rows_affected is returning -1. Any suggestions? 

Original comment by sha...@gmail.com on 23 Aug 2010 at 8:09

[GoogleCodeExporter]

Personally, I'm switching to pyodbc.  There are too many bugs in 1.0.2 and 
1.9.x seems too far way.

Original comment by jason.s.holmes on 23 Aug 2010 at 8:20

[GoogleCodeExporter]

I tried pyodbc. It doesn't really work on OS X and it's too much trouble to get 
set up anyway (well, that's true of ODBC in any form)

Original comment by sha...@gmail.com on 23 Aug 2010 at 10:14

[GoogleCodeExporter]

This is now fixed in r209.

$ python pymssqltest.py 
INSERT 1
UPDATE 1
[(1, '*line1*')]
SELECT 1
DELETE 1

Original comment by dam...@gmail.com on 2 Nov 2010 at 9:34

• Changed state: Fixed

[GoogleCodeExporter]

Excellent work damoxc.
Thanks dude.

Original comment by david.ba...@gmail.com on 3 Nov 2010 at 3:41