#**********************************************************************
# FILENAME : CallSPMultiResultSet.py
#
# DESCRIPTION :
# Simple ODBC (pyodbc) example to SELECT data from two tables
# via a stored procedure, returning more than one set of
# results.
#
# Illustrates the most basic call, in the form :
#
# {CALL pyMulti_Result_Sets ()}
#
# ODBC USAGE :
# Connects to Data Source using Data Source Name
# Creates cursor on the connection
# Drops and recreates a procedure 'pySelect_Records'
# Executes the procedure using cursor.execute()
# Calls cursor.fetchall() to retrieve a rowset of all rows
# For each record displays column values
# Calls cursor.nextset() to check another set of results are
# available.
# For each record displays column values
# Closes and deletes cursor and closed connection
#
import pyodbc
# Function to display the contents of a record
def printRec (rec):
print "\nPersonID : ", rec[0]
print "First Name : ", # Comma on end stops new line being output
if rec[1]!=None: # None appears for empty column
print rec[1][0:10] # Print string from 0 upto 10
else:
print "-" # Print - for empty column
print "Last Name : ",
if rec[2]!=None:
print rec[2][0:10]
else:
print "-"
print "Address : ",
if rec[3]!=None:
print rec[3][0:10]
else:
print "-"
print "City : ",
if rec[4]!=None:
print rec[4][0:10]
else:
print "-"
# Stored Procedure Create Statement
sqlCreateSP="CREATE PROCEDURE pyMulti_Result_Sets AS \
SELECT top 30 PersonID, FirstName, LastName, Address, City \
FROM TestTBL1 ORDER BY PersonID; \
SELECT top 30 PersonID, FirstName, LastName, Address, City \
FROM TestTBL1Copy ORDER BY PersonID"
# Stored Procedure Drop Statement
sqlDropSP="IF EXISTS (SELECT * FROM sys.objects \
WHERE type='P' AND name='pyMulti_Result_Sets') \
DROP PROCEDURE pyMulti_Result_Sets"
# Stored Procedure Call Statement
sqlExecSP="{call pyMulti_Result_Sets ()}"
# Connect to datasource
conn=pyodbc.connect('DSN=DATASOURCE', autocommit=True)
# Create cursor associated with connection
cursor=conn.cursor()
print "\nStored Procedure is : pyMulti_Result_Sets"
# Drop SP if exists
cursor.execute(sqlDropSP)
# Create SP using Create statement
cursor.execute(sqlCreateSP)
# Call SP and trap Error if raised
try:
cursor.execute(sqlExecSP)
except pyodbc.Error, err:
print 'Error !!!!! %s' % err
print "\nFirst Set of Results :"
print "----------------------"
# Fetch all rowset from execute
recs=cursor.fetchall()
# Process each record individually
for rec in recs:
printRec(rec)
print "\nSecond Set of Results :"
print "-----------------------"
if cursor.nextset()==True:
for rec in cursor:
printRec(rec)
print ("\nComplete.")
# Close and delete cursor
cursor.close()
del cursor
# Close Connection
conn.close()
See Also
