CODEWITHSUNDEEP
pythonpymssql
user617861
user617861

Reputation: 73

Can not see the DB query output in python

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

Upvotes: 3

Views: 4726

Answers (2)

TyrantWave
TyrantWave

Reputation: 4673

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)

Upvotes: 1

kefeizhou
kefeizhou

Reputation: 6552

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

Upvotes: 3

Related Questions