cursor procedure with python mssql get result

1#**********************************************************************
2# FILENAME :    CallSPMultiResultSet.py
3#
4# DESCRIPTION :
5#               Simple ODBC (pyodbc) example to SELECT data from two tables
6#               via a stored procedure, returning more than one set of
7#		results.
8#
9#               Illustrates the most basic call, in the form :
10#
11#               {CALL pyMulti_Result_Sets ()}
12#
13# ODBC USAGE :
14#               Connects to Data Source using Data Source Name
15#               Creates cursor on the connection
16#               Drops and recreates a procedure 'pySelect_Records'
17#               Executes the procedure using cursor.execute()
18#               Calls cursor.fetchall() to retrieve a rowset of all rows
19#               For each record displays column values
20#			Calls cursor.nextset() to check another set of results are
21#			available.
22#			For each record displays column values
23#               Closes and deletes cursor and closed connection
24#
25import pyodbc
26
27# Function to display the contents of a record
28def printRec (rec):
29
30    print "\nPersonID   : ", rec[0]
31
32    print "First Name : ",          # Comma on end stops new line being output
33    if rec[1]!=None:                # None appears for empty column
34        print rec[1][0:10]          # Print string from 0 upto 10
35    else:
36        print "-"                   # Print - for empty column
37
38    print "Last Name  : ",
39    if rec[2]!=None:
40        print rec[2][0:10]
41    else:
42        print "-"
43
44    print "Address    : ",
45    if rec[3]!=None:
46        print rec[3][0:10]
47    else:
48        print "-"
49
50    print "City       : ",
51    if rec[4]!=None:
52        print rec[4][0:10]
53    else:
54        print "-"
55
56# Stored Procedure Create Statement
57sqlCreateSP="CREATE PROCEDURE pyMulti_Result_Sets AS \
58				SELECT top 30 PersonID, FirstName, LastName, Address, City \
59				FROM TestTBL1 ORDER BY PersonID; \
60				SELECT top 30 PersonID, FirstName, LastName, Address, City \
61				FROM TestTBL1Copy ORDER BY PersonID"
62
63# Stored Procedure Drop Statement
64sqlDropSP="IF EXISTS (SELECT * FROM sys.objects \
65           WHERE type='P' AND name='pyMulti_Result_Sets') \
66           DROP PROCEDURE pyMulti_Result_Sets"
67
68# Stored Procedure Call Statement
69sqlExecSP="{call pyMulti_Result_Sets ()}"
70
71# Connect to datasource
72conn=pyodbc.connect('DSN=DATASOURCE', autocommit=True)
73
74# Create cursor associated with connection
75cursor=conn.cursor()
76
77print "\nStored Procedure is : pyMulti_Result_Sets"
78
79# Drop SP if exists
80cursor.execute(sqlDropSP)
81
82# Create SP using Create statement
83cursor.execute(sqlCreateSP)
84
85# Call SP and trap Error if raised
86try:
87    cursor.execute(sqlExecSP)
88except pyodbc.Error, err:
89    print 'Error !!!!! %s' % err
90
91print "\nFirst Set of Results :"
92print   "----------------------"
93
94# Fetch all rowset from execute
95recs=cursor.fetchall()
96
97# Process each record individually
98for rec in recs:
99	printRec(rec)
100
101print "\nSecond Set of Results :"
102print   "-----------------------"
103
104if cursor.nextset()==True:
105    for rec in cursor:
106        printRec(rec)
107
108print ("\nComplete.")
109
110# Close and delete cursor
111cursor.close()
112del cursor
113
114# Close Connection
115conn.close()
116
117