#**********************************************************************
# FILENAME : CallSPFindID.py
#
# DESCRIPTION :
# This example simulates using parameter arrays with a stored
# procedure by emulating the parameter arrays using tuples in
# Python.
#
# We take a random list of identity values and select records
# based on them, returning any matching record and an index value
# to indicate which tuple generated the result.
#
# Stored procedures can only be called though the cursor
# execute() or executemany() methods at this time since
# CallProc(), the intended method, has yet to be implemented.
#
# Input parameters are catered for but output parameters and procedure
# return values are not. Although limited, these can be be emulated
# via result set generating SELECTs, the output from which can be
# accessed though nextset(), fetchone() and fetchall() in the program.
# This is demonstrated by this example.
#
# ODBC USAGE :
#
# Emulates parameter array by creating a tuple array containing
# pairs of parameters representing a random PersonID (identity)
# value and an index value to indicate which tuple was used to
# generate the result.
#
# Connects to Data Source using Data Source Name
# Creates cursor on the connection
# Drops and recreates a procedure 'pyFind_ByID' which takes
# two parameters, one for each tuple entry.
# For each tuple,
# executes the procedure using cursor.execute()
# retrieves the results using cursor.fetchall() for the
# record details and cursor.nextset() and 'for rec in cursor'
# to retrieve the tuple index which was passed as param 2
# Closes and deletes cursor and closed connection
import pyodbc
# Emulating parameter arrays using tuples in python. The pairs of numbers
# are random PersonIDs and array indexs which map to @P1 and @P2 in the
# stored procedure.
params = [ (26, 0),\
(34, 1),\
(15, 2),\
(17, 3),\
(44, 4),\
(21, 5),\
(56, 6),\
(33, 7),\
(78, 8),\
(45, 9),\
(37, 10),\
(32, 11),\
(74, 12),\
(69, 13),\
(56, 14),\
(13, 15),\
(15, 16),\
(92, 17),\
(67, 18),\
(72, 19),\
(57, 20),\
(44, 21),\
(38, 22),\
(73, 23),\
(26, 24),\
(42, 25),\
]
# Stored Procedure Create Statement
sqlCreateSP="CREATE PROCEDURE pyFind_ByID \
(@pPersonID int, @pPersonIX int) AS \
DECLARE @RecCount INT;\
SELECT PersonID, FirstName, \
LastName, Address, City FROM TestTBL1 WHERE PersonID=@pPersonID \
ORDER BY PersonID; \
SELECT @pPersonIX;"
# Stored Procedure Drop Statement
sqlDropSP="IF EXISTS (SELECT * FROM sys.objects \
WHERE type='P' AND name='pyFind_ByID') \
DROP PROCEDURE pyFind_ByID"
# Stored Procedure Call Statement
sqlExecSP = "{CALL pyFind_ByID(?, ?)}";
# Connect to datasource
conn=pyodbc.connect('DSN=DATASOURCE', autocommit=True)
# Create cursor associated with connection
cursor=conn.cursor()
print "\nStored Procedure is : pyFind_ByID"
# Drop SP if exists
cursor.execute(sqlDropSP)
# Create SP using Create statement
cursor.execute(sqlCreateSP)
# Call SP with pairs of param values. We could use cursor.executemany() here
# to take the whole tuple list, but this would only give us the results from
# the last param tuple. i.e. the record with PersonID = 42 and index 25.
# We want the results for each tuple and since drivers often emulate parameter
# arrays by executing an SQL statement once for each set of parameter values,
# this is effectively what is done here.
for id in params:
try:
cursor.execute(sqlExecSP, id)
except pyodbc.Error, err:
print 'Error !!!!! %s' % err
recs = cursor.fetchall() # rows generated by parameter 1
for rec in recs:
print "\nPersonID : ", rec[0]
print "First Name : ", # Comma on end stops new line being output
if rec[1]!=None: # None indicates 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 "-"
if cursor.nextset()==True: # value passed IN as as parameter 2
for rec in cursor:
print "\nSelection was on (@p1) : ", params[rec[0]][0]
print "Tuple index was (@p2) : ", rec[0]
print ("\nComplete.")
# Close and delete cursor
cursor.close()
del cursor
# Close Connection
conn.close()
See Also
