Retrieve data from sql server database using Python

Question

I am trying to execute the following script. but I don't get neither the desired results nor a error message ,and I can't figure out where I'm doing wrong.

import pyodbc 
cnxn = pyodbc.connect("Driver={SQL Server Native Client 11.0};"
                        "Server=mySRVERNAME;"
                        "Database=MYDB;"
                        "uid=sa;pwd=MYPWD;"
                        "Trusted_Connection=yes;")


cursor = cnxn.cursor()
cursor.execute('select DISTINCT firstname,lastname,coalesce(middlename,\' \') as middlename from Person.Person')

for row in cursor:
    print('row = %r' % (row,))

any ideas ? any help is appreciated :)

Solution

You have to use a fetch method along with cursor. For Example

for row in cursor.fetchall():
    print('row = %r' % (row,))

EDIT :

The fetchall function returns all remaining rows in a list.

    If there are no rows, an empty list is returned. 
    If there are a lot of rows, *this will use a lot of memory.* 

Unread rows are stored by the database driver in a compact format and are often sent in batches from the database server.

Reading in only the rows you need at one time will save a lot of memory.

If we are going to process the rows one at a time, we can use the cursor itself as an interator Moreover we can simplify it since cursor.execute() always returns a cursor :

for row in cursor.execute("select bla, anotherbla from blabla"): 
    print row.bla, row.anotherbla

Documentation

Comments

[Mohamed Azizi]

it work but I want to know what is the difference between using only cursor and cursor.fetchall(), Because in some examples only cursor do the same as cursor.fetchall() ?

[Hari Krishnan]

cursor is just a pointer to the database. After the executing a query we have explicitly fetch the rows that has been affected by the query so we use fetchall() method to do that.

[Mohamed Azizi]

I mean is it possible just to loop over the bare cursor object ?

[Hari Krishnan]

as far as i know, you can't. It'll end up causing a RunTimeError

[Mohamed Azizi]

I'll keep searching for the reason, I know it's possible cuz I've been using it and also I know it's efficient with large results sets. once I found something I'll share it :),