How to strip "()," from Python, PyODBC, SQL Returns?

Question

Hi,

I grabbed a bunch of SQL IDs I need to update with Python and PyODBC:

import pyodbc
cnxn = pyodbc.connect('DSN=YesOne;PWD=xxx')
cursor = cnxn.cursor()
cursor.execute("SELECT ID FROM One.dbo.Two WHERE STATUS = 'OnGoing' AND ID = ''")

I have a second bit of code that updates the IDs:

cursor.execute("Update One.dbo.Two SET STATUS = 'Completed', Modified = 'Today' WHERE ID = '1051'")

The problem is when I view the IDs grabbed in Python earlier I get either:

row = cursor.fetchall()
f row:
    print row

[(1016, ), (1017, ), (1019, ), (1020, ), (1021, ), (1025, ), (1026, ), (1027, ), (1029, ), (1048, ), (1049, )]

or

if row:
    print row[3]

(1020, )

I need just the number so I can run my second part of the script for the:

WHERE ID = '1051'"

part. I tried with:

count = len(row)
while count > 0:
    newrow = row[count-1]
    print 'SELECT ID FROM One.dbo.Two WHERE ID = ' + str(newrow)
    count = count-1

and it gave me:

SELECT ID FROM One.dbo.Two WHERE ID = (1049, )
SELECT ID FROM One.dbo.Two WHERE ID = (1048, )
etc...

And I tried:

str(row[1]).lstrip('(),')

and I got:

'1017, )'

How do I strip the characters from the ID so I can reuse the ID?

Thanks,

Adrian

Answer 1

First of all:

rows = [x[0] for x in cursor.fetchall()]

Then abandon your terrible while loop:

for row in rows:
  print 'SELECT ID FROM One.dbo.Two WHERE ID = %s' % row