Can not see the DB query output in python

Question

I'm execute a simple mssql query from python. I can see in the profiler that the query reach the DB. The query has 1 row of answer. I fail to see the output in the Python shell

I run the code below

import pymssql 
conn = pymssql.connect(host='SQL01', user='user', password='password', database='mydatabase', as_dict=True) 
cur = conn.cursor()  
cur.execute('SELECT * FROM persons WHERE salesrep=%s', 'John Doe') 
for row in cur:     
    print "ID=%d, Name=%s" % (row['id'], row['name']) 

Pleas advise Thanks, Assaf

Answer 1

You can call fetchone() or fetchall() after execute to get the data from that query.

import pymssql 
conn = pymssql.connect(host='SQL01', user='user', password='password', database='mydatabase', as_dict=True) 
cur = conn.cursor()  
cur.execute('SELECT * FROM persons WHERE salesrep=%s', 'John Doe') 
print cur.fetchall()

Answer 2

import pymssql  
conn = pymssql.connect(host='SQL01', user='user', password='password', database='mydatabase', as_dict=True)  
cur = conn.cursor()   
users = cur.execute('SELECT * FROM persons WHERE salesrep=%s', 'John Doe').fetchall()  
conn.close()   

for row in users:     
    print "ID=%d, Name=%s" % (row['id'], row['name'])

Try assigning the results to something instead of using the cursor.

cur.execute() is a function, as such while it does return a value (which you saw), you're not assigning it to anything, so when you go to do the for loop, there's nothing to loop over.

If you don't want to store the result, you could do this (rather messy) version:

import pymssql  
conn = pymssql.connect(host='SQL01', user='user', password='password', database='mydatabase', as_dict=True)  
cur = conn.cursor() 
sql = 'SELECT * FROM persons WHERE salesrep=%s'

for row in cur.execute(sql, 'John Doe').fetchall():
    print "ID=%d, Name=%s" % (row['id'], row['name'])

conn.close()   

This one does the for loop over the result of the cur.execute(), but I really advise against this

(Minor addendum: I forgot the fetchall's, I'm so used to putting this in a function. Sorry)