這個問題，讓我卡關很久。只好完全避開使用 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
rowcount always return -1 while select some record without nothing #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