rowcont only after fetch

[Paolo De Stefani]

I read in the user guide that cursor.rowcont now:

Returns number of rows affected by last operation. In case of SELECT statements it returns meaningful information ONLY AFTER ALL ROWS HAVE BEEN FETCHED.

but in the 0.8 version of pymssql i could get this information BEFORE fetching the rows. Why now is different ?
I was able to know if a record/key exists using:

    cur.execute("""SELECT cod_naz FROM CA_NAZIONI WHERE cod_naz = %s;""", (codice,))
    if cur.rownumber == 1:
        return True
    return False

How can i do now ?

[A]

The more obvious query would be
SELECT COUNT(*) FROM CA_NAZIONI WHERE cod_naz = %s
...

[Paolo De Stefani]

Sorry but maybe i was not clear. I want to know if a code (ex. a customer code) is already in a table in python. The code is always a primary key. So i made a function:

def exists_customer(code):
    cur.execute("""SELECT cod_naz FROM CA_NAZIONI WHERE cod_naz = %s;""", (code,))
    if cur.rowcount == 1:
        return True
    return False

so calling this for ex. exists_customer('010203') return me true or false. This no more work in pymssql 1.0.1 but worked in pymssql 0.8. I solved changing the function in this way:

def exists_customer(code):
    cur.execute("""SELECT cod_naz FROM CA_NAZIONI WHERE cod_naz = %s;""", (code,))
    if cur.fetchone():
        return True
    return False

But the meanning of my question is: why changed this behaviour ? And what other changes that break compatibility with pymssql 0.8 i will find ?
Anyway i think that cursor.rowcount should be set before fetching the rows.

Thank you.

[A]

> Sorry but maybe i was not clear. I want to know if a code (ex. a customer code) is already in a table in python. The code is always a primary key.
This task is solvable by using COUNT(*). You can make SELECT COUNT(*) FROM table WHERE condition and if the result is > 0, or in your case 1, it means there are row(s) in the table that satisfy your condition. This is the most natural way of making such checks. This way you would never notice difference in rowcount behaviour.

def exists_customer(code):
cur.execute("SELECT COUNT(*) FROM CA_NAZIONI WHERE cod_naz = %s", (code,)) 
return cur.fetchone()[0] > 0

# this is also valid, but only for PKs, and it uses iterator; effect is the same as above:
return cur.next()[0] == 1

Why has the behaviour changed? Because in previous pymssql versions it was _emulated_. First, all rows were fetched into memory, and then rowcount was easily calculated. It would be convenient for your purpose, but other users complained that they were unable to process huge result sets (like millions of rows or more) because of out of memory errors. And why it's unavailable before iterating rows? Because this is how the protocol is designed. Look here:
http://msdn.microsoft.com/en-us/library/aa937020\(SQL.80).aspx - "Remarks" section
or here
(http://manuals.sybase.com/onlinebooks/group-cnarc/cng1110e/dblib/@Generic__BookTextView/12608;pt=39614)

Workaround that best imitate old behaviour:

def exists_customer(code):
cur.execute("""SELECT cod_naz FROM CA_NAZIONI WHERE cod_naz = %s;""", (code,)) 
cur._result = cur.fetchall()   # fetch everything into memory, you couldn't turn this off
if cur.rowcount == 1: 
return True 
return False

Hope this helps...

[Paolo De Stefani]

Well i think my solution is better: cur.fetchone() in this case returns a list with 1 element (=true in python) or an empty list (=false in python). That's enough for me.
Thank you for clear me the reason of this behaviuor but i think this is a limit of the microsoft dblibrary. I already can count the rows after fetching them in python, i need that information before fetching rows. I think python db api follow me:

.rowcount
         
            This read-only attribute specifies the number of rows that
            the last .execute*() produced (for DQL statements like
            'select') or affected (for DML statements like 'update' or
            'insert').
           
            The attribute is -1 in case no .execute*() has been
            performed on the cursor or the rowcount of the last
            operation is cannot be determined by the interface. [7]

            Note: Future versions of the DB API specification could
            redefine the latter case to have the object return None
            instead of -1.

even if here thay don't talk about before/after fetching rows.
Anyway my solution is good.
Thank you for your quick answer and overall great work on pymssql.