如何在循环 pyodbc " for row in cursor.execute"更新行？

Question

这是我的代码。我只是想在循环中更新当前行。

import pyodbc
import requests, re, time
from datetime import datetime
proxy = { 'http': '77.237.228.203:1212'}
user_agent = {'user-agent': 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10.9; rv:32.0) Gecko/20100101 Firefox/32.0'}
cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=<servername>;DATABASE=<databasename>;UID=<username>;PWD=<password>');
cursor = cnxn.cursor()
for rows in cursor.execute("""SELECT DocumentLink,TrackingNumber,DocumentClass,TXDocNumberSequence
                             FROM TXWellDocumentList
                             WHERE Downloaded='NO'"""):
    row = cursor.fetchone()
    url = str(row.DocumentLink)
    print url
    if row[0]:
        fileName = 'TX_'+str(row.TrackingNumber)+'_'+str(row.DocumentClass)+'_'+str(row.TXDocNumberSequence)
        print fileName
        content = {}
        r = requests.get(url, proxies=proxy,headers=user_agent)
        content = r.headers; 
        extType= content['content-type']
        ext = re.search(r'/(w+)',extType).group(1);print ext;
        size = content['content-length']*.001
        if ext=='pdf':
            with open(fileName+'.'+ext, "wb") as datum:
                datum.write(r.content)
            datum.close()
        else:
            with open(fileName+'.'+'tif', "wb") as datum:
                datum.write(r.content)
            datum.close()
        dt = str(datetime.now()).strip(',')
        #update table
        update = """UPDATE TXWellDocumentList
        SET Downloaded=?, DownLoadedAs=?,DownLoadedWhen=?,DownLoadedSizeKB=?
        WHERE TXDocNumberSequence=?"""
        result = cursor.execute(update,'Yes', fileName+'.'+ext, dt, size,uniqueID )
    time.sleep(5)
    else:
        print "empty"

在更新表部分中，我想用新变量更新当前行。可惜这行不通。

row.DownLoadedAs = fileName
row.DownLoadedWhen = dt
row.DownLoadedSizeKB = size
cnxn.commit()

我可以从这里更新这行，还是我必须在循环外使用长更新游标执行更新?

当我这样做的时候:

update = """UPDATE TXWellDocumentList
                    SET Downloaded=?, DownLoadedAs=?,DownLoadedWhen=?,DownLoadedSizeKB=?
                    WHERE TXDocNumberSequence=?"""
result = cursor.execute(update,'Yes', fileName+'.'+ext, dt, size,uniqueID )

我得到一个错误说:ProgrammingError: No results。

Answer 1

您需要一个UPDATE查询将其发送到数据库;pyodbc不是一个ORM。下面是你要做的一个简单的例子:

import pyodbc, os
conn_str = 'DRIVER={SQL Server};SERVER=<servername>;DATABASE=<databasename>;UID=<username>;PWD=<password>'
conn = pyodbc.connect(conn_str, autocommit=True)
sql = """
    SELECT my_id, my_field
    FROM my_table
"""
cur = conn.cursor()
rows = cur.execute(sql)
for row in rows:
    my_field_new_value = "This is a new value!"
    row_count = cur.execute("UPDATE my_table SET my_field=? WHERE my_id=?", my_field_value, row.my_id).rowcount
    if row_count == 0:
        print("Warning: no row updated)
# Close our DB cursors and connections.
cur.close()
conn.close()

你可能需要修改你的原始查询(这将是有效的，如果你的IDENTITY增量字段被称为id;你可以给它取别的名字):

for rows in cursor.execute("""SELECT id, DocumentLink, TrackingNumber, DocumentClass, TXDocNumberSequence
                             FROM TXWellDocumentList
                             WHERE Downloaded='NO'"""):

然后，在更新中，使用上面选择的id来执行更新:

result = cursor.execute(update, 'Yes', fileName+'.' + ext, dt, size, row.id)

你能试一下吗?