[python] pyodbc rowcount always return -1

這個問題，讓我卡關很久。只好完全避開使用 rowcount. 改用 fetchone() 的傳回值來判斷是否有資料。

另外測到 pyodbc 在重開 sql server 之後會造成 pyodbc connection 斷線，目前並不會去 re-connect 造成接下來所有的 code 都錯誤。但這前提是 sql server 要「中斷服務 」。

RowCount doesn’t work with pyodbc using SQL Server Native Client driver #55
https://github.com/franz-see/Robotframework-Database-Library/issues/55

pymssql:
https://github.com/pymssql/pymssql

rowcount always return -1 while select some record without nothing #141
https://github.com/pymssql/pymssql/issues/141

I’ve seen this issue for so long I truly didn’t realize it was a bug. I just assumed -1 was uninitialized when the query was a select. Here is some sample code to illustrate the issue.

def sample(sql, params):
    conn = pymssql.connect(host="host", user="username", password="password",
                           database="database", port=1433, charset='UTF-8',
                           login_timeout=0, timeout=0, autocommit=True)
    conn._conn.debug_queries = True
    cur = conn.cursor()
    cur.execute(sql, params)
    # COMMENTED OUT TO SHOW BUG
    # try:
    #     rows = cur.fetchall()
    # except Exception as exc:
    #     rows = []
    print "rowcount", cur.rowcount
    # print "rows", len(rows)
    cur.close()
    conn.close()
    return

sql = """INSERT INTO dbo.County(CID, CountyDescriptiveName) Values (%d, %s) """
params = (2345, 'Another location')
sql1 = "select * from County "

sample(sql, params)
sample(sql1, [])

Executed shows the rowcount works when doing an insert, but not a select.

#INSERT INTO dbo.County(CID, CountyDescriptiveName) Values (2345, 'Another location') #
rowcount 1
#select * from County #
rowcount -1

However, if you uncomment out the portion of the code which attempts to use fetchall() the rowcount is returned properly.

#INSERT INTO dbo.County(CID, CountyDescriptiveName) Values (2345, 'Another location') #
rowcount 1
rows 0
#select * from County #
rowcount 6
rows 6