cursor procedure with python mssql get result code example

Example: cursor procedure with python mssql get result

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