CODEWITHSUNDEEP
pyodbc
bluerubez
bluerubez

Reputation: 33

How to update row while in " for row in cursor.execute" loop pyodbc?

Here is my code. I just want to update the current row while i am in the loop.

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"

In the update table section i want to update the current row with new variables. Unfortunately this doesn't work.

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

Can i update this row from here or do i have to update with a long update cursor execution outside of the loop?

When i do:

update = """UPDATE TXWellDocumentList
                    SET Downloaded=?, DownLoadedAs=?,DownLoadedWhen=?,DownLoadedSizeKB=?
                    WHERE TXDocNumberSequence=?"""

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

I get an error saying: ProgrammingError: No results. Previous SQL was not a query.

Upvotes: 0

Views: 5174

Answers (1)

FlipperPA
FlipperPA

Reputation: 14331

You need an UPDATE query to send it to the database; pyodbc is not an ORM. Here's a simplified example of what you're trying to do:

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()

You may need to amend your original query (this will be valid, if your IDENTITY increment field is called id; you may have it named something else):

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

Then, in your update, use the id you select above to perform the update:

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

Can you give that a try?

Upvotes: 2

Related Questions