#**********************************************************************
# FILENAME : CallSPWithParamArray.py
#
# DESCRIPTION :
# This example emulates using a single parameter array with a stored
# procedure by emulating the parameter arrays using tuples in
# python.
#
# We take a random list of identity values and use them to find records
# where they match with the PersonID field of the record.
#
# 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.
#
# ODBC USAGE :
#
# Emulates parameter array by creating a tuple array containing
# a random list of PersonID (identity) values.
#
# Connects to Data Source using Data Source Name
# Creates cursor on the connection
# Drops and recreates a procedure 'pyFind_Record' 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.
# Closes and deletes cursor and closed connection
import pyodbc
# The array is a random list of PersonIDs which are mapped to @P1 in the
# stored procedure.
params = [ (26),(34),(15),(17),(44),(21),(56),(33),(78),\
(45),(37),(32),(74),(69),(66),(13),(15),(92),\
(67),(72),(57),(44),(38),(73),(26),(42),(6) ]
# Stored Procedure Create Statement
sqlCreateSP="CREATE PROCEDURE pyFind_Record (@pPersonID INT) \
AS SELECT PersonID, FirstName, LastName, Address, City \
FROM TestTBL1 WHERE PersonID=@pPersonID;"
# Stored Procedure Drop Statement
sqlDropSP="IF EXISTS (SELECT * FROM sys.objects \
WHERE type='P' AND name='pyFind_Record') \
DROP PROCEDURE pyFind_Record"
# Stored Procedure Call Statement
sqlExecSP = "{CALL pyFind_Record(?)}";
# Connect to datasource
conn=pyodbc.connect('DSN=DATASOURCE', autocommit=True)
# Create cursor associated with connection
cursor=conn.cursor()
print "\nStored Procedure is : pyFind_Record"
# 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 = 6. This is realistic
# since drivers often emulate parameter arrays by executing a SQL statement
# once for each set of parameter values. This is in effect what we are doing
# here.
for id in params:
try:
cursor.execute(sqlExecSP, id)
except pyodbc.Error, err:
print 'Error !!!!! %s' % err
print "\nRequested : ", id # Person ID passed as @P1
recs = cursor.fetchall() # Rows generated by @P1 if any
print "Recs Found : ", len (recs) # Number of records found
for rec in recs: # Display records found for @P1
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 "-"
print ("\nComplete.")
# Close and delete cursor
cursor.close()
del cursor
# Close Connection
conn.close()
Further information
